Let’s us explore how you can perform DataPump import to an Oracle 19c Database using the export dumps on Object Storage.
Here is my demo environment.
Database : Oracle Base Database 19.24
Step 0 – Prerequisites
Ensure that size of an individual export dump file is less than 10000MB , if this is not the case then perform DataPump Export again with below parameter.
filesize=5g
Upload the export dumps to an Object Storage Bucket of our choice.
Create an Auth token for the OCI console user which will be used for creating credentials in Step 2.
Step 1 – Apply latest DataPump Bundle Patch
This is an optional step but highly recommended so that you can avoid the known issues in your version for which fixes are available and included in Bundle patch.
Please refer MOS note MOS Note: 2819284.1 – Data Pump Recommended Proactive Patches For 19.10 and Above and apply the patch for your database version.
I have applied DPBP for 19.24.
Step 2 – Install DBMS_CLOUD Package
We need DBMS_CLOUD package to create a credential which can be used with DataPump to access the export dumps in Object Storage Bucket.
DBMS_CLOUD is not pre-installed in Non-Autonomous Database and we need to install it using steps mentioned in MOS note: 2748362.1.
Some of the steps in MOS note will ask us to give permission to run the DBMS_CLOUD to a role or a user , I have given the permissions to HR01 user. Please note that I am using the same user for Datapump Import.
I have created a credential named CRED_OSS after following all the steps in above MOS note.
Step 3 – Enable ODM Libraries
There are two ODM libraries which needs to be enabled as mentioned below.
Object Storage Library
Direct NFS Library
Perform below steps to enable these libraries.
srvctl stop database -d $ORACLE_UNQNAME
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk opc_on (This is Object Storage Library)
make -f ins_rdbms.mk dnfs_on (This DirectNFS Library)
srvctl start database -d $ORACLE_UNQNAME
Check whether the Libraries are enabled by checking alert log using below command.
grep -i ODM alert.log
Below is sample output.
Step 4 – Prepare Datapump Par File
I have prepared below par file for Import.
CREDENTIAL=CRED_OSS
DUMPFILE=https://<storage_namespace>.swiftobjectstorage.<region-id>.oci.customer-oci.com/v1/<storage_namespace>/<bucket_name>/<export_dump_name_%U.dmp
LOGFILE=<directory_name>:<logifle_name>
METRICS=Y
LOGTIME=ALL
ENCRYPTION_PASSWORD=<password_used_for_encrypting_export_dump>
JOB_NAME=<ImportJob_Name>
Please refer documentation in case you need the steps to obtain your storage namespace.
Step 5 – Perform DataPump Import
I have initiated the import using below command.
impdp hr01/<password>@pdb_tns_alias parfile=imp.par
Import completed successfully without any issues and below is the content of the logfile.
That’s all.
This method of importing from Object Storage is very useful in multiple scenarios especially when you have to perform a migration where you need to use DataPump export/import and you don’t want to place the export dumps on local FS or NFS due to some reason.
Please note that I have done the test with 19.24 database version and this should work for earlier versions (at least from 19.9 and above) but I haven’t tested it and can’t be 100% sure.
Leave a Reply