Import Schema into Oracle Autonomous Database
Follow the steps below to import the Data Pump file
(export.dmp
) into your Oracle Autonomous Database.
See To create an auth token in Managing User Credentials in Oracle Cloud Infrastructure Documentation. Copy the auth token as it will not be displayed again.
Note:
Oracle recommends to use the Oracle Instant Client downloaded from Autonomous Database service console (Development page, Download Oracle Instant Client link).- Sign in to your Oracle Autonomous Transaction Processing or Oracle Autonomous Data Warehouse service console and click Development.
- Click Download Oracle Instant Client and save the zip file.
- Unzip the file and set the
$ORACLE_HOME
environment variable to point to this directory. - On the Administration page, click Download Client Credentials (Wallet) and save the zip file.
- Extract the file into
$ORACLE_HOME/network/admin
directory. - Inspect the
tnsnames.ora
file included in the zip to see the list of service names to connect to. Identify the "high" service name of the database into which you are migrating. - Use SQL*Plus to connect to your Autonomous Database as the ADMIN user using the service name you identified in the previous step.
- Run the following to create a credential using the
DBMS_CLOUD
package. ReplaceOCI_USERNAME
with your Oracle Cloud Infrastructure username andOCI_AUTH_TOKEN
with your Oracle Cloud Infrastructure auth token you created in a previous step.BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'def_cred_name', username => 'OCI_USERNAME', password => 'OCI_AUTH_TOKEN' ); END; /
- Use
impdp
Data Pump utility to import your Exadata Express export file from Oracle Cloud Infrastructure Object Storage to your Autonomous Database. ReplaceSERVICE_NAME
with the "high" service name of your Autonomous Database. ReplaceAPEX_TABLESPACE
with the Oracle Application Express (APEX) tablespace name used in your Exadata Express database. ReplaceEXPORT_FILE_URL
with the URL to theexport.dmp
file uploaded in Object Storage. Enter the password for the ADMIN database user when prompted. Make note of the database user that is created by the import operation.impdp admin@SERVICE_NAME credential=def_cred_name directory=data_pump_dir remap_tablespace=APEX_TABLESPACE1:data,APEX_TABLESPACE2:data,... \ dumpfile='EXPORT_FILE_URL' \ parallel=2 transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \ exclude=cluster,db_link
For example:impdp admin@demo_high credential=def_cred_name directory=data_pump_dir remap_tablespace=APEX_1234567890:data,APEX_12223333444:data \ dumpfile='https://objectstorage.us-phoenix-1.oraclecloud.com/n/.../b/.../o/export.dmp' \ parallel=2 transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \ exclude=cluster,db_link
Note:
If you encountered the following role grant error, please ignore it:Processing object type SCHEMA_EXPORT/ROLE_GRANT ORA-39083: Object type ROLE_GRANT failed to create with error: ORA-01924: role 'C##DBPOD_BUILTIN_USERS_ROLE' not granted or does not exist Failing sql is: GRANT "C##DBPOD_BUILTIN_USERS_ROLE" TO "DEMO"
- Connect to your Autonomous Database as ADMIN using SQL*Plus and reset the
password for the database user that was created as a result of the Data Pump
import. The password must conform to the Autonomous Database password complexity
rules.
alter user USERNAME identified by "PASSWORD" account unlock;
For additional information, see:
- Import with Oracle Data Pump Version 18.3 or Later in Using Oracle Autonomous Transaction Processing
- Import with Oracle Data Pump Version 18.3 or Later in Using Oracle Autonomous Data Warehouse
- Creating Users with Autonomous Database in Using Oracle Autonomous Transaction Processing
- Creating Users with Autonomous Database in Using Oracle Autonomous Data Warehouse