Load Data Using Oracle Data Pump

Oracle Data Pump offers very fast bulk data and metadata movement between Oracle databases and Autonomous Transaction Processing.

Data Pump Import lets you import data from Data Pump files residing on the Oracle Cloud Infrastructure Object Storage and Oracle Cloud Infrastructure Object Storage Classic. You can save your data to your Cloud Object Store and use Oracle Data Pump to load data to Autonomous Transaction Processing.

Export Data from Your Existing Oracle Database

First you use Oracle Data Pump Export to export your existing Oracle Database schemas. Then you use Oracle Data Pump Import to migrate them to Autonomous Transaction Processing.

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

exclude=cluster, db_link
parallel=n
schemas=schema name
dumpfile=export%u.dmp

Oracle Data Pump Export provides several export modes, Oracle recommends using the schema mode for migrating to Autonomous Transaction Processing. You can list the schemas you want to export by using the schemas parameter.

For a faster migration, export your schemas into multiple Data Pump files and use parallelism. You can specify the dump file name format you want to use with the dumpfile parameter. Set the parallel parameter to at least the number of CPUs you have in your Autonomous Transaction Processing database.

The exclude and data_options parameters ensure that the object types not available in Autonomous Transaction Processing are not exported and table partitions are grouped together so that they can be imported faster to Autonomous Transaction Processing.

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

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

You can use other Data Pump Export parameters, such as compression, depending on your requirements. For more information on Oracle Data Pump Export see Oracle Database Utilities.

Upload the Export Files to Cloud Object Storage

Before you can import the data you exported from the source Oracle Database, you need to upload the export files to cloud object storage.

You can upload the export files to an existing storage bucket in Oracle Cloud Infrastructure Object Storage or an existing storage container in Oracle Cloud Infrastructure Object Storage Classic. Or you use the following procedure to create a new storage bucket and upload the export files to it.

  1. Sign in to your Oracle Cloud Account at cloud.oracle.com.

  2. From the Oracle Cloud Infrastructure left navigation list choose Object Storage and then choose Object Storage from the sublist.

  3. Pick a compartment to create the storage bucket in.

  4. Click Create Bucket.

  5. In the Create Bucket dialog, give the bucket a name and then click Create Bucket.

  6. After the bucket is created, click its name in the list of buckets to display its Bucket Details page.

  7. In the Objects box, click Upload Objects.

  8. In the Upload Objects dialog, click the select files link in the Choose Files From Your Computer box.

  9. In the file browser, navigate to and select your export files. Then, click Open.

  10. In the Upload Objects dialog, click Upload Objects to start uploading the files you selected.

  11. After the uploads complete, close the Upload Objects dialog.

Import Data Using Oracle Data Pump

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

Download the latest version of the Oracle Instant Client Basic Package and Tools Package (which includes Oracle Data Pump) for your platform from Oracle Instant Client Downloads. See the installation instructions on the platform install download page for the installation steps required after you download Oracle Instant Client.

In Oracle Data Pump version 18.3 and later, the credential argument authenticates Data Pump to the Cloud Object Storage service you are using for your source files. The dumpfile argument is a comma delimited list of URLs for your Data Pump files.

Data Pump Import versions 12.2.0.1 and earlier do not have the credential parameter. If you are using an older version of Data Pump Import you need to define a default credential property for Autonomous Transaction Processing and use the default_credential keyword in the dumpfile parameter.

In Oracle Data Pump, if your source files reside in Oracle Cloud Infrastructure Object Storage you can use Oracle Cloud Infrastructure native URIs or the Swift URIs. See Cloud Object Storage URI Formats for details on these URI formats.

  1. Store your Cloud Object Storage credential using the DBMS_CREDENTIAL.CREATE_CREDENTIAL procedure. For example:
    BEGIN
      DBMS_CREDENTIAL.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'atpc_user@oracle.com',
        password => 'password'
      );
    END;
    /

    The values you provide for username and password depend on the Cloud Object Storage service you are using:

    • Oracle Cloud Infrastructure Object Storage: username is your Oracle Cloud Infrastructure user name and password is your Oracle Cloud Infrastructure auth token. See Working with Auth Tokens.

    • Oracle Cloud Infrastructure Object Storage Classic: username is your Oracle Cloud Infrastructure Classic user name and password is your Oracle Cloud Infrastructure Classic password.

  2. If you are using Oracle Data Pump version 12.2.0.1 or earlier, set the credential as the default credential for your Autonomous Transaction Processing, as the ADMIN user. For example:
    alter database property set default_credential = 'ADMIN.DEF_CRED_NAME'
  3. Run Data Pump Import with the dumpfile parameter set to the list of file URLs on your Cloud Object Storage.

    Note:

    In the following examples, the nologfile=yes option is specified. This option is required for dedicated Autonomous Transaction Processing databases.
    • Oracle Data Pump version 18.3 or later: set the credential parameter to the name of the credential you created in Step 1. For example:

      impdp admin/password@ATPC1_high \
           credential=def_cred_name \
           dumpfile=https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/export%u.dmp \ 
           parallel=16 \
           transform=segment_attributes:n \
           transform=dwcs_cvt_iots:y \
           transform=constraint_use_default_index:y \
           exclude=cluster, db_link \
           nologfile=yes

      In this example, dumpfile is an Oracle Cloud Infrastructure Swift URI that specifies all files whose name matches export<number>.dmp in the mybucket bucket in the us-phoenix-1 region. (idthydc0kinr is the object storage namespace in which the bucket resides.)

    • Oracle Data Pump version 12.2.0.1 or earlier: start the value of the dumpfile parameter with the default_credential keyword and a colon. For example:

      impdp admin/password@ATPC1_high \
           dumpfile=default_credential:https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/export%u.dmp \
           parallel=16 \      
           transform=segment_attributes:n \      
           exclude=cluster, db_link \
           nologfile=yes

    For the best import performance use the high database service for your import connection and set the parallel parameter to the number of CPUs your database has.

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

    For information on disallowed objects in Autonomous Transaction Processing, see Limitations on the Use of SQL Commands.

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