Use Data Pump on OCI Cloud Console to Move Data Between Autonomous Databases

Kurt Liu

Published Sep 26, 2022

There are multiple ways of move data between Oracle Autonomous Databases (ADB), one of which is through Oracle Data Pump. It is especially useful when moving large amount of data across multiple tables, schemas etc. for initial load that is required when replicating data from one ADB flavour to another using OCI GoldenGate. Other than calling Data Pump API from within the ADB (examples on oracle-base.com), we can always use data pump client utility (expdp/impdp) installed either on the on-prem database server or a client machine where Oracle client (e.g. InstantClient) is installed. Given Oracle Autonomous Database runs on OCI, it may save us some time to get the client ready by using the OCI CloudShell console, which has all client tools installed by default for you.

# OCI Cloud Console has lots of Oracle client tools installed by default, such as OCI CLI, SQL*PLus and Data Pump Export/Import utilities, and so on.

To start with, we connect to OCI Cloud Shell first from OCI web console. After signing in OCI web console, it's really a mouse click away to connect to OCI CloudShell.

Download Client Wallet and Prepare For TNS_ADMIN directories

To connect from Cloud Console to source/target ADBs using SQL*Plus and/or expdp/impdp, we can download the ADB Client Wallet from the relevant ADB instance using OCI CLI which has been pre-configured for us.

user01@cloudshell:~ (ap-sydney-1)$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.ap-sydney-1.anzxsljrjdjgirqap37gkazhnjjylbdqcqkimxxxxxxxxxe6g5g2qbfrq --file wallet_srcadb.zip --password My_Encryption_Pass12

user01@cloudshell:~ (ap-sydney-1)$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.ap-sydney-1.abzxsljrp675q2ujas7nk5qpoig65zdlegxxxxxxxy5o3dd5sauao3lsn3a --file wallet_tgtadb.zip --password My_Encryption_Pass123

user01@cloudshell:~ (ap-sydney-1)$ ls

oradiag_user01 wallet_srcadb.zip wallet_tgtadb.zip3

We will always need a password to protect the client wallet when generating ADB client wallet, similar to download that from OCI web console.

# Decompress the wallet zip files to wallet directories.

user01@cloudshell:~ (ap-sydney-1)$ unzip wallet_srcadb.zip -d wallet_srcad

Archive: wallet_srcadb.zip

 inflating: wallet_srcadb/ewallet.pem  

 inflating: wallet_srcadb/README   

 inflating: wallet_srcadb/cwallet.sso  

 inflating: wallet_srcadb/tnsnames.ora  

 inflating: wallet_srcadb/truststore.jks  

 inflating: wallet_srcadb/ojdbc.properties  

 inflating: wallet_srcadb/sqlnet.ora  

 inflating: wallet_srcadb/ewallet.p12  

 inflating: wallet_srcadb/keystore.jks  

user01@cloudshell:~ (ap-sydney-1)$ unzip wallet_tgtadb.zip -d wallet_tgtadb

Archive: wallet_tgtadb.zip

 inflating: wallet_tgtadb/ewallet.pem  

 inflating: wallet_tgtadb/README  

 inflating: wallet_tgtadb/cwallet.sso  

 inflating: wallet_tgtadb/tnsnames.ora  

 inflating: wallet_tgtadb/truststore.jks  

 inflating: wallet_tgtadb/ojdbc.properties  

 inflating: wallet_tgtadb/sqlnet.ora  

 inflating: wallet_tgtadb/ewallet.p12  

 inflating: wallet_tgtadb/keystore.jks 

user01@cloudshell:~ (ap-sydney-1)$ b

Check and Configure TNS_ADMIN files from the relevant Wallet Directories

# WALLET_LOCATION parameter should be updated to reflect the correct directory where the wallet file is located.

user01@cloudshell:~ (ap-sydney-1)$ cat wallet_srcadb/sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))

SSL_SERVER_DN_MATCH=yes

user01@cloudshell:~ (ap-sydney-1)$ 

user01@cloudshell:~ (ap-sydney-1)$ sed -i 's/\?\/network\/admin/\/home\/user01\/wallet_srcadb/g' wallet_srcadb/sqlnet.ora 

user01@cloudshell:~ (ap-sydney-1)$ cat wallet_srcadb/sqlnet.ora 

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/user01/wallet_srcadb")))

SSL_SERVER_DN_MATCH=yes

user01@cloudshell:~ (ap-sydney-1)$ 

user01@cloudshell:~ (ap-sydney-1)$ sed -i 's/\?\/network\/admin/\/home\/user01\/wallet_tgtadb/g' wallet_tgtadb/sqlnet.ora 

user01@cloudshell:~ (ap-sydney-1)$ cat wallet_tgtadb/sqlnet.ora 

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/user01/wallet_tgtadb")))

SSL_SERVER_DN_MATCH=yes

user01@cloudshell:~ (ap-sydney-1)$

# ADB hostname may be replaced with its private IP address if the ADB uses private endpoint as private hostname may not be resolvable from the Cloud Console

user01@cloudshell:~ (ap-sydney-1)$ cat wallet_tgtadb/tnsnames.ora

tgtadb_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=firbj1xn.adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=yj6gtaxxxx4zvoj_tgtadb_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com, OU=Oracle ADB SYDNEY, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

tgtadb_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=firbj1xn.adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=yj6gtaxxxx4zvoj_tgtadb_low.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com, OU=Oracle ADB SYDNEY, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

tgtadb_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=firbj1xn.adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=yj6gtaxxxx4zvoj_tgtadb_medium.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com, OU=Oracle ADB SYDNEY, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

# Test host/port connectivity from CloudShell using Ncat

user01@cloudshell:~ (ap-sydney-1)$ nc -tv firbj1xn.adb.ap-sydney-1.oraclecloud.com 152

Ncat: Version 7.50 ( https://nmap.org/ncat )

Ncat: Could not resolve hostname "firbj1xn.adb.ap-sydney-1.oraclecloud.com": Name or service not known. QUITTING.

user01@cloudshell:~ (ap-sydney-1)$ nc -tv 10.100.0.100 1522

Ncat: Version 7.50 ( https://nmap.org/ncat )

Ncat: Connected to 10.100.0.100:1522.2

# As we can see the private hostname was not resolved but its private IP could be reached.

# Replace the private hostname (host=) with its associated with private IP address in the tnsnames.ora file.

Create Credential Objects in source/target DB

For Data Pump to access dump files on OCI Object Storage, an OCI auth token needs to be created for the user that has read/write to the Object Storage bucket. The auth token and the relevant OCI tenancy username should be stored in the credential object in the ADB schema by calling dbms_cloud.create_credential. Obviously, the ADB user for Data Pump command will need the privilege to use this credential object. For the sake of simplicity, I created the credential objects with ADMIN user.

user01@cloudshell:~ (ap-sydney-1)$ export TNS_ADMIN=~/wallet_srcadb

user01@cloudshell:~ (ap-sydney-1)$ sqlplus admin@srcadb_low


SQL*Plus: Release 21.0.0.0.0 - Production on Mon Sep 26 01:18:12 2022

Version 21.7.0.0.0


Copyright (c) 1982, 2022, Oracle. All rights reserved.


Enter password: 

Last Successful login time: Mon Sep 26 2022 01:13:52 +00:00


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.1.0


SQL> 

SQL> BEGIN

 DBMS_CLOUD.CREATE_CREDENTIAL(
  credential_name => 'DP_CRED_NAME',
  username => 'oracleidentitycloudservice/idcs_user01',
  password => 'YkSFyTl>F(mmO<3_GX9k'
 );
END;

/ 2  3  4  5  6  7  8  


PL/SQL procedure successfully completed.


SQL> exit

/ 2  3  4  5  6  7  8  


PL/SQL procedure successfully completed.


SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.1.0

user01@cloudshell:~ (ap-sydney-1)$ export TNS_ADMIN=~/wallet_tgtadb

user01@cloudshell:~ (ap-sydney-1)$ sqlplus admin@tgtadb_low


SQL*Plus: Release 21.0.0.0.0 - Production on Mon Sep 26 01:14:16 2022

Version 21.7.0.0.0


Copyright (c) 1982, 2022, Oracle. All rights reserved.


Enter password: 

Last Successful login time: Mon Sep 26 2022 00:57:06 +00:00


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.1.0


SQL> BEGIN

 DBMS_CLOUD.CREATE_CREDENTIAL(
  credential_name => 'DP_CRED_NAME',
  username => 'oracleidentitycloudservice/idcs_user01',
  password => 'YkSFyTl>F(mmO<3_GX9k'
 );
END;

/ 2  3  4  5  6  7  8  


PL/SQL procedure successfully completed.


SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.1.0

user01@cloudshell:~ (ap-sydney-1)$

Prepare for the object storage bucket to stage the dump files

An OCI Object Storage bucket has been created to stage the dump files. It is a private bucket hence why we needed to credential object created above to ADB instance to be able to access the bucket.

Export Schemas From Source ADB

Again, security is important, the data pump dump files can be encrypted with encryption_password or through command line prompt.

user01@cloudshell:~ (ap-sydney-1)$ export TNS_ADMIN=~/wallet_srcad

user01@cloudshell:~ (ap-sydney-1)$ expdp admin@srcadb_low schemas=CPAT filesize=5GB dumpfile=https://objectstorage.ap-sydney-1.oraclecloud.com/n/sdc90vkxb5rj/b/kli-datapump-files/o/ajdb_%U.dmp encryption_pwd_prompt=yes credential=DP_CRED_NAME directory=data_pump_dir logfile=srcadb_export_01.log parallel=2


Export: Release 21.0.0.0.0 - Production on Mon Sep 26 02:03:49 2022

Version 21.7.0.0.0


Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.

Password: 


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production


Encryption Password: 

Starting "ADMIN"."SYS_EXPORT_SCHEMA_01": admin/********@srcadb_low schemas=CPAT filesize=5GB dumpfile=https://objectstorage.ap-sydney-1.oraclecloud.com/n/sdc90vkxb5rj/b/kli-datapump-files/o/srcadb_%U.dmp encryption_pwd_prompt=yes credential=DP_CRED_NAME directory=data_pump_dir logfile=srcadb_export_01.log parallel=2 

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "CPAT"."DEMO"                  0 KB    0 rows

. . exported "CPAT"."TESTCOLLECTION"             0 KB    0 rows
...
...
...
...
...

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Master table "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ADMIN.SYS_EXPORT_SCHEMA_01 is:

 https://swiftobjectstorage.ap-sydney-1.oraclecloud.com/v1/sdc90vkxb5rj/kli-datapump-files/srcadb_01.dmp

 https://swiftobjectstorage.ap-sydney-1.oraclecloud.com/v1/sdc90vkxb5rj/kli-datapump-files/srcadb_02.dmp

Job "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Sep 26 02:05:49 2022 elapsed 0 00:01:42


user01@cloudshell:~ (ap-sydney-1)$

# Note the dump files can be staged directly to the object storage bucket. It’s also recommended to set filesize parameter and use parallelism when exporting the data so the import can run in parallel as well.

Import the Dump File Into Target ADB

When calling impdp, we also need provide the encryption password to be able to decrypt the data.

user01@cloudshell:~ (ap-sydney-1)$ export TNS_ADMIN=~/wallet_tgtad

user01@cloudshell:~ (ap-sydney-1)$ impdp admin@tgtadb_medium dumpfile=https://objectstorage.ap-sydney-1.oraclecloud.com/n/sdc90vkxb5rj/b/kli-datapump-files/o/srcadb_%U.dmp encryption_pwd_prompt=yes credential=DP_CRED_NAME directory=data_pump_dir logfile=cpat_import_01.log parallel=2


Import: Release 21.0.0.0.0 - Production on Mon Sep 26 02:42:38 2022

Version 21.7.0.0.0


Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.

Password: 


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production


Encryption Password: 

Master table "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@tgtadb_medium dumpfile=https://objectstorage.ap-sydney-1.oraclecloud.com/n/sdc90vkxb5rj/b/kli-datapump-files/o/srcadb_%U.dmp encryption_pwd_prompt=yes credential=DP_CRED_NAME directory=data_pump_dir logfile=cpat_import_01.log parallel=2 

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "CPAT"."DEMO"                  0 KB    0 rows

. . imported "CPAT"."TESTCOLLECTION"             0 KB    0 rows
...
...
...
...
...

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "ADMIN"."SYS_IMPORT_FULL_01" successfully completed at Mon Sep 26 02:43:40 2022 elapsed 0 00:00:49


user01@cloudshell:~ (ap-sydney-1)$

Task accomplished!

Footnotes:

It does require some basic configuration of the TNS files to make it work with ADB instance with private endpoint. That's why it makes sense to call Data Pump API from Database Actions (formerly knowns SQL Developer Web) for the ADB instance.
It can take longer time to complete the export/import jobs than the TTL of the cloud shell session. However, as data pump jobs run on the database server, you can always connect back and use attach=job_name parameter of expdp/impdp to check the status of the relevant jobs.

venkateswaran ramanathan 3y

Kurt Liu, Is this functionality currently restricted to ADB databases . Will these work in Oracle Enterprise Databases 19c and 21c if we install the dbms_cloud packages and use 21c client to use datapump for export

To view or add a comment, sign in

Use Data Pump on OCI Cloud Console to Move Data Between Autonomous Databases

Kurt Liu

Download Client Wallet and Prepare For TNS_ADMIN directories

Check and Configure TNS_ADMIN files from the relevant Wallet Directories

Recommended by LinkedIn

Create Credential Objects in source/target DB

Prepare for the object storage bucket to stage the dump files

Export Schemas From Source ADB

Import the Dump File Into Target ADB

More articles by Kurt Liu

Others also viewed

Masterclass on Oracle DataPump Utility [Part-III]: expdp/impdp

Table Partitioning: A 101/201 Level Introduction

[IBM DB2] 4. Table Partitioning

Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature

🚀 Migrate from Oracle to Databricks: Supercharge Your Data Strategy! 🔄🧠

How DBMS organizes the data inside database pages ?

The Journey to Modernization – Part 4 – Final steps of technical modernization preparation for your legacy application

DataBase Partitioning Techniques

why only one thread need to be apply in RAC DR on stand by

From ___DBA to Data Administrator

Explore content categories

Download Client Wallet and Prepare For TNS_ADMIN directories

Check and Configure TNS_ADMIN files from the relevant Wallet Directories

Recommended by LinkedIn

Create Credential Objects in source/target DB

Prepare for the object storage bucket to stage the dump files

Export Schemas From Source ADB

Import the Dump File Into Target ADB

More articles by Kurt Liu

Network Data Flow for APEX on ADB-S

Run Your Oracle APEX Apps on Autonomous Database with Your Own Domain Name

Moving Your PDBs Between Oracle DBCS Instances

Setting up Data Guard Between OCI-C and OCI DBCS

Connect to Oracle Autonomous Database over Private Network from On-Premises

Three Steps to Install Oracle 19c on Oracle Linux 7

Connect to Oracle Autonomous Database Using SQL Developer Through SSH Tunnel

Migrate Your Dev/Test Databases To Oracle Database Cloud

Storage Performance In the Cloud

Others also viewed

Masterclass on Oracle DataPump Utility [Part-III]: expdp/impdp

Table Partitioning: A 101/201 Level Introduction

[IBM DB2] 4. Table Partitioning

Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature

🚀 Migrate from Oracle to Databricks: Supercharge Your Data Strategy! 🔄🧠

How DBMS organizes the data inside database pages ?

The Journey to Modernization – Part 4 – Final steps of technical modernization preparation for your legacy application

DataBase Partitioning Techniques

why only one thread need to be apply in RAC DR on stand by

From ___DBA to Data Administrator

Explore content categories