Migrating Data to Oracle Autonomous Database Using Database Links (Step-by-Step Guide)

Learn how to migrate data to Oracle Autonomous Database (ATP/ADW) using Database Links and Oracle Data Pump Network Link. This method eliminates dump files and enables secure, direct data transfer from on-premises or cloud databases.

Why Use Database Links for Migration?

  • No dump files required
  • No Object Storage dependency
  • Secure direct database-to-database transfer
  • Ideal for small and medium database migrations

Step 1: Configure Wallet and SQL*Net

Download Autonomous Database wallet and place it in:

$ORACLE_HOME/network/admin

sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin"))) SSL_SERVER_DN_MATCH = no NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT) SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11 SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 11

Enable Case-Sensitive Logon

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;
---

Step 2: Configure tnsnames.ora

myadb_tp = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(PORT = 1522) (HOST = myadb.adb.region.oraclecloud.com)) (CONNECT_DATA = (SERVICE_NAME = myadb_tp.adb.oraclecloud.com)) (SECURITY = (ssl_server_dn_match = no)) ) XEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = myonpremdb.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1)) )
---

Step 3: Private DNS Configuration (OCI)

  • Create Private DNS Zone
  • Add A record mapping on-prem hostname to private IP
  • Associate DNS zone with Autonomous Database VCN
---

Step 4: VCN Connectivity Requirements

  • VCN Peering or DRG setup
  • Route table updates (bidirectional)
  • Security List/NSG rules allowing TCP 1521

Connectivity Test

telnet myonpremdb.localdomain 1521
---

Step 5: Create Credential in Autonomous Database

BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'ONPREM_XE_CRED', username => 'IMS', password => 'Oracle123!' ); END; /
---

Step 6: Create Database Link

BEGIN DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK( db_link_name => 'ADB_TO_XE_LINK', hostname => 'myonpremdb.localdomain', port => '1521', service_name => 'xepdb1', ssl_server_cert_dn => NULL, credential_name => 'ONPREM_XE_CRED', directory_name => NULL, private_target => TRUE ); END; /
---

Step 7: Test Database Link

SELECT * FROM dual@ADB_TO_XE_LINK;
---

Step 8: Migrate Using Data Pump Network Link

impdp "ims/Oracle#12345@myadb_tp" \ SCHEMAS=IMS \ NETWORK_LINK=ADB_TO_XE_LINK \ PARALLEL=1 \ TRANSFORM=SEGMENT_ATTRIBUTES:N \ EXCLUDE=CLUSTER \ NOLOGFILE=YES \ REMAP_TABLESPACE=USERS:DATA,TEMP_TABLESPACE:TEMP
---

Step 9: Verify Migration

SELECT COUNT(*) FROM ims.my_table;
---

Final Migration Checklist

  • Wallet configured
  • sqlnet.ora & tnsnames.ora updated
  • DNS resolution working
  • VCN peering configured
  • Database link tested
  • Data Pump executed successfully
---