Use Oracle Data Pump Export to export your existing Oracle Database to migrate to Autonomous Database using Oracle Data Pump Import.

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

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

Oracle recommends using the latest Oracle Data Pump version for importing data from Data Pump files into your Autonomous Database, as it contains enhancements and fixes for a better experience.

1. 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;
   /

   For more information on Oracle Cloud Infrastructure Signing Key based credentials see CREATE_CREDENTIAL Procedure.

   Supported credential types:

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

     For more information on the credential types for Oracle Cloud Infrastructure Cloud Object Storage, see CREATE_CREDENTIAL Procedure.

   • Data Pump supports using an Oracle Cloud Infrastructure Object Storage pre-authenticated URL for the dumpfile parameter. When you use a pre-authenticated URL, providing the credential parameter is required and impdp ignores the credential parameter. When you use a pre-authenticated URL for the dumpfile, you can use a NULL value for the credential in the next step. See Using Pre-Authenticated Requests for more information.

   • Data Pump supports using a resource principal credential with impdp. See Import Data Using Oracle Data Pump with OCI Resource Principal Credential for more information.

2. Run Data Pump Import with the dumpfile 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://namespace-string.objectstorage.us-ashburn-1.oci.customer-oci.com/n/namespace-string/b/bucketname/o/export%l.dmp \
        parallel=16 \
        encryption_pwd_prompt=yes \
        exclude=cluster,indextype,db_link

   Notes for Data Pump parameters:

   • If during the export with expdp you used the encryption_pwd_prompt=yes parameter then use encryption_pwd_prompt=yes and input the same password at the impdp prompt that you specified during the export.

   • The dumpfile parameter supports the %L and %l wildcards in addition to the legacy %U and %u wildcards. For example, dumpfile=export%L.dmp. Use the %L or %l wildcard for exports from Oracle Database Release 12.2 and higher. This wildcard expands the dumpfile file name into a 3-digit to 10-digit, variable-width incrementing integer, starting at 100 and ending at 2147483646.

     Use the legacy %U or %u wildcard for exports from Oracle Database prior to Release 12.2. If you use this option and more than 99 dump files are needed, you must specify multiple dumpfile names, each with the %U or %u parameter.

   • If your bucket and data do not reside in a commercial (OC1) realm, where OCI Dedicated Endpoints are not supported, use the following URI format for dumpfile:

     https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname/o/filename

   • The credential parameter cannot be an Azure service principal, Amazon Resource Name (ARN), or a Google service account. See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

   When you use a pre-authenticated URL for dumpfile, you can use a NULL value for the credential.

   For dumpfile, this example uses the recommended URI format using OCI Dedicated Endpoints for commercial realm (OC1). The namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Object Storage Dedicated Endpoints, Regions and Availability Domains, and Understanding Object Storage Namespaces for more information.

   For the best import performance use the HIGH database service for your import connection and set the parallel parameter to one quarter the number of ECPUs (.25 x ECPU count). If you are using OCPU compute model, set the parallel parameter to the number of OCPUs (1 x OCPU count).

   For information on which database service name to connect to run Data Pump Import, see Manage Concurrency and Priorities on Autonomous Database.

   For the dump file URL format for different Cloud Object Storage services, see DBMS_CLOUD URI Formats.

   In this example the following are excluded during the Data Pump Import:

   • Clusters

   • Indextypes

   • Database links

   To perform a full import or to import objects that are owned by other users, you need the DATAPUMP_CLOUD_IMP role.

   You can also use Data Pump Import to import SODA collections on Autonomous Database. See Import SODA Collection Data Using Oracle Data Pump Version 19.6 or Later for more information.

   For information on disallowed objects in Autonomous Database, see SQL Commands.

   See Oracle Data Pump Import and Table Compression for details on table compression using Oracle Data Pump import on Autonomous Database.

   For detailed information on Oracle Data Pump Import parameters see Oracle Database Utilities.

• Import Data Using Oracle Data Pump with OCI Resource Principal Credential
  Oracle Data Pump supports importing data pump files into your Autonomous Database using an Oracle Cloud Infrastructure resource principal as a credential object.

You can import data from Data Pump files into your Autonomous Database using Data Pump client versions 12.2.0.1 and earlier by setting the default_credential parameter.

1. 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;
   /

   For more information on Oracle Cloud Infrastructure Signing Key based credentials see CREATE_CREDENTIAL Procedure.

   Supported credential types:

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

     For more information on the credential types for Oracle Cloud Infrastructure Cloud Object Storage, see CREATE_CREDENTIAL Procedure.

   • Data Pump supports using an Oracle Cloud Infrastructure Object Storage pre-authenticated URL for the dumpfile. When you use a pre-authenticated URL, setting the DEFAULT_CREDENTIAL is required and impdp ignores the DEFAULT_CREDENTIAL. When you use a pre-authenticated URL for the dumpfile, you can use a NULL value for the DEFAULT_CREDENTIAL you set in the next step. See Using Pre-Authenticated Requests for more information.

   • Data Pump supports using a resource principal credential with impdp.

2. Set the credential as the default credential for your Autonomous Database, as the ADMIN user. For example:

   ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME'

   The DEFAULT_CREDENTIAL can be an OCI Resource Principal. For example:

   ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'OCI$RESOURCE_PRINCIPAL'

   See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

   Note
   
   The DEFAULT_CREDENTIAL value cannot be an Azure service principal, Amazon Resource Name (ARN), or a Google service account.

   The DEFAULT_CREDENTIAL value can be set to NULL if you are using a pre-authenticated URL.

3. Run Data Pump Import with the dumpfile parameter set to the list of file URLs on your Cloud Object Storage, and set the default_credential keyword. For example:

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

   Notes for the Data Pump parameters:

   • If during the export with expdp you used the encryption_pwd_prompt=yes parameter then use encryption_pwd_prompt=yes and input the same password at the impdp prompt that you specified during the export.

   • The dumpfile parameter supports the %L and %l wildcards in addition to the legacy %U and %u wildcards. For example, dumpfile=export%L.dmp. Use the %L or %l wildcard for exports from Oracle Database Release 12.2 and higher. This wildcard expands the dumpfile file name into a 3-digit to 10-digit, variable-width incrementing integer, starting at 100 and ending at 2147483646.

     Use the legacy %U or %u wildcard for exports from Oracle Database prior to Release 12.2. If you use this option and more than 99 dump files are needed, you must specify multiple dumpfile names, each with the %U or %u parameter.

   • If your bucket and data do not reside in a commercial (OC1) realm, where OCI Dedicated Endpoints are not supported, use the following URI format for dumpfile:

     https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname/o/filename

   For dumpfile, this example uses the recommended URI format using OCI Dedicated Endpoints for commercial realm (OC1). The namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Object Storage Dedicated Endpoints, Regions and Availability Domains, and Understanding Object Storage Namespaces for more information.

   For the best import performance use the HIGH database service for your import connection and set the parallel parameter to one quarter the number of ECPUs (.25 x ECPU count). If you are using OCPU compute model, set the parallel parameter to the number of OCPUs (1 x OCPU count).

   For information on which database service name to connect to run Data Pump Import, Manage Concurrency and Priorities on Autonomous Database.

   For the dump file URL format for different Cloud Object Storage services, see DBMS_CLOUD URI Formats.

   In this example the following are excluded during the Data Pump Import:

   • Clusters

   • Indextypes

   • Database links

The log files for Data Pump Import operations are stored in the directory you specify with the data pump impdp directory parameter.

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'DEF_CRED_NAME',
    object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/import.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name => 'import.log');
END;
/

Provides notes for using Oracle Data Pump import on Autonomous Database.

By default the Oracle Data Pump Import utility imports data with the same compression type as specified for tables on the source database (the database where you exported your data from). If you want to leave compression to Autonomous Database, specify the following parameter when you import your data:

TRANSFORM=TABLE_COMPRESSION_CLAUSE:NONE

The TRANSFORM parameter with this option specifies that Oracle Data Pump Import should ignore the compression type of your source tables. Using this option Oracle Data Pump imports the tables into Autonomous Database using the default compression type, where the default compression type depends on the Autonomous Database workload type:

• Data Warehouse: The default table compression is Hybrid Columnar Compression.

  Oracle recommends using this default if your application primarily uses bulk load operations on your tables, as the loads will compress the data. Query performance on these tables will benefit from compression as queries need to do less IO.

  If you have staging tables replicated from other systems using Oracle GoldenGate or other replication tools, or your application primarily uses row-by-row DML operations on tables, Oracle recommends keeping the tables uncompressed or using Advanced Row Compression.

• Transaction Processing: The default table compression is no compression.

• JSON Database: The default table compression is no compression.

• APEX: The default table compression is no compression.

See TRANSFORM for more information on the Oracle Data Pump Import TRANSFORM parameter.

See About Table Compression for more information.