Migrate Oracle database to Oracle Cloud Autonomous Database using Data Pump Conventional Export/Import

Migrate on-premises Oracle database to Oracle Cloud Architecture(OCI) Autonomous Database using Data Pump Conventional Export/Import

1) Export Your Existing Oracle Database to Import into Autonomous Database

Oracle recommends using the following Data Pump parameters for faster and easier migration to Autonomous Database:

exclude=cluster,indextype,db_link
parallel=n
schemas=schema_name
dumpfile=export%u.dmp

The exclude parameters ensure that these object types are not exported.

With encryption_pwd_prompt=yes Oracle Data Pump export prompts for an encryption password to encrypt the dump files.

The following example exports the SH schema from a source Oracle Database for migration to a database with 16 CPUs:

expdp sh/sh@orcl \
exclude=cluster,indextype,db_link \
parallel=16 \
schemas=sh \
dumpfile=export%u.dmp \
encryption_pwd_prompt=yes

2) Upload the export dump files to Oracle Object storage.

3) Import Data Using Oracle Data Pump

Data Pump Import supports Oracle Cloud Infrastructure Auth Token based credentials and Oracle Cloud Infrastructure Signing Key based credentials.

In Oracle Data Pump, if your source files reside on Oracle Cloud Infrastructure Object Storage you can use Oracle Cloud Infrastructure native URIs or Swift URIs. 

Importing with Oracle Data Pump and Setting credential Parameter

Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

For example, to create Oracle Cloud Infrastructure Auth Token credentials:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com',
    password => 'password'
  );
END;
/

For more information on Oracle Cloud Infrastructure Auth Token authentication see CREATE_CREDENTIAL Procedure.

For example, to create Oracle Cloud Infrastructure Signing Key based credentials:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'DEF_CRED_NAME',
       user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
       tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
       private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
       fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
END;
/

Run Data Pump Import with the dump file parameter set to the list of file URLs on your Cloud Object Storage and the credential parameter set to the name of the credential you created in the previous step. 

For example:

impdp admin/password@db2022adb_high \       
directory=data_pump_dir \       
credential=def_cred_name \       
dumpfile= https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/export%u.dmp \
parallel=16 \
encryption_pwd_prompt=yes \
exclude=cluster,indextype,db_link