Database Migration

Import your Data directly from Object Storage in Oracle Database 21.3 and 19.12

Introduction

To directly export and import data to and from Object Storage, you need the DBMS_CLOUD PL/SQL package, which was initially introduced in Oracle Autonomous Database. DBMS_CLOUD was made available for non-Autonomous Oracle Databases with version 21c and later backported to 19.9.

However, direct import and export from and to Object Storage using the Data Pump utility is not just a small feature as it initially might appear. It needed more time and effort in development. Now, with versions 21.3 and 19.12, we are (almost) there!

Migrating (very) large databases to Cloud Virtual Machines, Bare Metal, and Exadata Cloud Service was especially challenging when you don’t have sufficient space on the local file system for both the database and dump files. In this case, some workarounds were required, e.g.:

Starting with version 21.3 and 19.12, we don’t need these workarounds anymore. Now we are able to directly import data from Object Storage using Data Pump.

The Environment

  • Oracle Database version 21.3 on VM DB System in Oracle Cloud.
  • Oracle Database version 19.12 on VM DB System in Oracle Cloud.
  • Object Storage bucket.

Preparation

Step1: Install DBMS_CLOUD PL/SQL package

As mentioned, DBMS_CLOUD was made available for database version 19.9 and above, but it is not (yet) included in the database installation. Follow the steps described in

  • How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

to set up and configure DBMS_CLOUD in your database.

Use the LIST_OBJECTS function to validate your configuration:

SQL> select OBJECT_NAME from dbms_cloud.list_objects('OSS_CRED','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/');

OBJECT_NAME
--------------------------------------------------------------------------------
sample1912.dmp
sample213.dmp

Step 2: Enable Object Store ODM Library

Usually, after installing and configuring DBMS_CLOUD, we would be ready to use Object Storage for import and export. However, the import will hit the following error:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea/dumps/sample213.dmp" for read
ORA-27037: unable to obtain file status

Unfortunately, there is still a bug there that we need to fix using the workaround as described in

  • DATA PUMP EXPORT TO OCI OBJECT STORAGE FAILED ORA-39001 ORA-39000 ORA-31641 (Doc ID 2806178.1)

Enabling the ODM library:

[oracle@host213 ~]$ dbshut $ORACLE_HOME
Processing Database instance "CDB213": log file /u01/app/oracle/homes/OraDB21Home1/rdbms/log/shutdown.log

[oracle@host213 ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@host213 lib]$ make -f ins_rdbms.mk opc_on
rm -f /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/lib/odm/libopcodm.so; \
    cp /u01/app/oracle/product/21.0.0/dbhome_1/lib/libopcodm.so /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/lib/odm/libopcodm.so

[oracle@host213 lib]$ dbstart $ORACLE_HOME
Processing Database instance "CDB213": log file /u01/app/oracle/homes/OraDB21Home1/rdbms/log/startup.log

Check your database alert log:

-- database version 21.3
[oracle@host213 ~]$ cd /u01/app/oracle/diag/rdbms/cdb213_fra18w/CDB213/trace
[oracle@host213 trace]$ grep -i "ODM Library Version" alert_CDB213.log
...
Oracle instance running with ODM in SGA: Object Store ODM Library Version 7.0

-- database version 19.12
[oracle@host1912 ~]$ cd /u01/app/oracle/diag/rdbms/cdb1912_fra2cm/CDB1912/trace/
[oracle@host1912 trace]$ grep -i "ODM Library Version" alert_CDB1912.log
...
Oracle instance running with ODM: Object Store ODM Library Version 6.0

As you recognize, 21.3 has the ODM Library version 7, while 19.12 has version 6. This is OK as different releases have different library versions.

Import from Object Storage

Step 3: Import your data directly from Object Storage

Now, we approached the moment of truth!

Import in version 21.3:

[oracle@host213 ~]$ export TNS_ADMIN=/u01/app/oracle/homes/OraDB21Home1/network/admin/
[oracle@host213 ~]$ impdp SYSTEM@PDB213 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample213 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/sample213.dmp

Import: Release 21.0.0.0.0 - Production on Mon Sep 13 07:55:41 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 21c EE Extreme Perf Release 21.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  SYSTEM/********@PDB213 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample213 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/sample213.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SAMPLE213"."SAMPLETABLE"                  5.062 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Sep 13 07:56:14 2021 elapsed 0 00:00:30

Import in version 19.12:

[oracle@host1912 ~]$ impdp SYSTEM@PDB1912 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample1912 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/sample1912.dmp

Import: Release 19.0.0.0.0 - Production on Mon Sep 13 08:01:00 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  SYSTEM/********@PDB1912 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample1912 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/sample1912.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SAMPLE1912"."SAMPLETABLE"                 5.054 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Sep 13 08:01:10 2021 elapsed 0 00:00:09

Nice! Import from Object Storage is working just fine for both versions, 21.3 and 19.12.

Export to Object Storage

Step 4: Export your data directly to Object Storage

Export in version 21.3:

[oracle@host213 ~]$ export TNS_ADMIN=/u01/app/oracle/homes/OraDB21Home1/network/admin/
[oracle@host213 ~]$ expdp SYSTEM@PDB213 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample213 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/exp_sample213.dmp

Export: Release 21.0.0.0.0 - Production on Mon Sep 13 07:56:56 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 21c EE Extreme Perf Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  SYSTEM/********@PDB213 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample213 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/exp_sample213.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SAMPLE213"."SAMPLETABLE"                  5.062 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea/dumps/exp_sample213.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Sep 13 07:57:35 2021 elapsed 0 00:00:36

Direct export into Object Storage in 21.3 works fine.

Export in version 19.12 using 19.12 client:

[oracle@host1912 ~]$ expdp SYSTEM@PDB1912 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample1912 dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/exp_sample1912.dmp

Export: Release 19.0.0.0.0 - Production on Mon Sep 13 10:30:49 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39208: Parameter KU$_FILE_TYPE_URIDUMP_FILE is invalid for EXPORT jobs.

And when using 21.3 client instead:

[oracle@host213 ~]$ expdp SYSTEM@PDB1912 credential=OSS_CRED directory=DATA_PUMP_DIR schemas=sample1912 dumpfile=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea/dumps/exp_sample1912.dmp

Export: Release 21.0.0.0.0 - Production on Mon Sep 13 17:27:49 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
UDE-00023: parameter credential is incompatible with database version 19.12.0.0.0

So no direct export to Object Storage is supported in 19.12 as of today. There is a bit more work that needs to be done. So stay tuned.

Oracle Autonomous Database

To export data from an Autonomous Database directly into Object Storage, you need the 21.3 client that provides the credential parameter:

[oracle@host213 ~]$ expdp userid=ADMIN@adbpublic_high credential=OSS_CRED2 schemas=sampleadb directory=DATA_PUMP_DIR dumpfile=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea/dumps/exp_sampleadb.dmp

The credential parameter for import was already introduced in 18c. Direct import from Object Storage was already available with the first Autonomous Database version 18c.

Conclusion

Direct import from Object Storage simplifies database migrations significantly. Object Storage also provides you cost-effective storage to archive your dump files for all your Oracle Databases whether Autonomous, Database Cloud, and on-premises.

Autonomous Database

  • Direct import from Object Storage is supported since version 18c.
  • Direct export into Object Storage is supported. It requires expdp client version 21.3.

Non-Autonomous Database version 21.3

  • Direct import from Object Storage is supported.
  • Direct export into Object Storage is supported.

Non-Autonomous Database version 19.12

  • Direct import from Object Storage is supported.
  • Direct export into Object Storage is not yet supported. You have to export to a local file system first and subsequently upload the dump files into Object Storage, e.g. via OCI CLI.

Further Reading

Would you like to get notified when the next post is published?