Oracle Cloud Infrastructure Documentation

Export Your Existing Oracle Database to Import into Autonomous Database

Use Oracle Data Pump to export your existing Oracle Database as the first step to migrate to Autonomous Database.

Oracle recommends using Oracle Data Pump schema mode to migrate your database to Autonomous Database. 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 database.

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.

You have two options to export data with Data Pump, depending on the Data Pump version and whether you include the CREDENTIAL parameter:

Parent topic: Import Data Using Oracle Data Pump on Autonomous Database

Export Your Existing Oracle Database to a Local Database File (Export without Credential Parameter)

Use Oracle Data Pump to export your existing Oracle Database and specify an optional directory object and file name in directory-object-name:file-name format, as the first step to migrate to 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%l.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%l.dmp \
encryption_pwd_prompt=yes

After you create the dumpfile(s), upload the dumpfile(s) to Cloud Object Storage. For example use DBMS_CLOUD.PUT_OBJECT to upload the dumpfiles.

See PUT_OBJECT Procedure for more information.

Notes for Data Pump parameters:

  • If during the export with expdp you use the encryption_pwd_prompt=yes parameter, also use encryption_pwd_prompt=yes with your import and input the same password at the impdp prompt to decrypt the dump files (remember the password you supply during export). The maximum length of the encryption password is 128 bytes.

  • 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.

Export Your Existing Oracle Database Directly to Cloud Object Storage (Export with Credential Parameter)

Use Oracle Data Pump to export your existing Oracle Database directly to Cloud Object Storage, as the first step to migrate to Autonomous Database.

Note

This option is supported for Oracle Data Pump Export 19.9 or later versions.

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=https://objectstorage.example.com/schema_name.dmp
credential=credential_name

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

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=https://objectstorage.example.com/%l.dmp \
credential=sales-dept

Notes for Data Pump parameters:

  • If during the export with expdp you use the encryption_pwd_prompt=yes parameter, also use encryption_pwd_prompt=yes with your import and input the same password at the impdp prompt to decrypt the dump files (remember the password you supply during export). The maximum length of the encryption password is 128 bytes.

  • 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.

  • Data Pump Export does not support using an Oracle Cloud Infrastructure Object Storage pre-authenticated URL (PAR URL) for the dumpfile parameter.

  • The credential parameter can be one of the following supported credential types:

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

    • Data Pump export supports using a resource principal credential with expdp.

    See CREATE_CREDENTIAL Procedure for more information on the credential types for Oracle Cloud Infrastructure Cloud Object Storage.