Migrating Data to Autonomous Database Using Database Links

Introduction

Autonomous Database supports outgoing Database Links to other Oracle databases. These links provide a secure bridge between your source Oracle Database and Autonomous Database, enabling direct data transfer without intermediate dump files or object storage.

This guide walks through the wallet setup, SQLNet configuration, DNS resolution, VCN connectivity, and database link creation* required to migrate data into Autonomous Database using Data Pump over a network link.

Migration with Oracle Data Pump

Oracle Data Pump provides multiple ways to move data. One efficient method is using Network Links or Database Links.

Advantages:

No need to generate dump files.
No Object Storage or NFS transfer required.
Ideal for small to medium databases — migration happens directly over the link.

Step 1: Configure Wallet and SQL*Net

Download the Autonomous Database wallet from the OCI Console and place it in your $ORACLE_HOME/network/admin directory (or another secure location).

sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin"))). --This path can change based on wallet file
SSL_SERVER_DN_MATCH = no NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11 SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 11

Enforce Case‑Sensitive Logon (On database from where data is pulled) -In My case Standalone database

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

This ensures that usernames and passwords are validated exactly as defined.

Step 2: Configure TNS Names

Update your tnsnames.ora to include both Autonomous Database service aliases and your on‑premises database entries.

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 Zone Setup

Autonomous Database private endpoints require DNS resolution for hostnames.

Create a Private DNS Zone in OCI.
Add an A record mapping the hostname of your on‑premises database (e.g., myonpremdb.localdomain) to its private IP address.
Ensure the Autonomous Database VCN is associated with this DNS zone so it can resolve the hostname.

Step 4: VCN Connectivity Checks

For the database link to work, the VCN of Autonomous Database and the VCN of the source database must be connected.

Use VCN peering (local or remote) or a Dynamic Routing Gateway (DRG).

Update Route Tables:

ATP subnet → route to source DB subnet.

Source DB subnet → route back to ATP subnet.

Update Security Lists/NSGs:

Allow TCP 1521 ingress on the source DB subnet.

Allow TCP 1521 egress on the ATP subnet.

Verify connectivity with a test VM in the ATP subnet:
telnet myonpremdb.localdomain 1521

Step 5: Create User and Credential in Autonomous Database

BEGIN

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

Step 6: Create Database Link in Autonomous Database

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 the Database Link

SELECT * FROM dual@ADB_TO_XE_LINK;

Step 8: Migrate Data Using Data Pump

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 Checklist

Wallet downloaded and placed in $ORACLE_HOME/network/admin.
sqlnet.ora and tnsnames.ora configured.
SEC_CASE_SENSITIVE_LOGON enabled.
Private DNS zone created with A record for source DB hostname.
VCN peering and route/security rules configured.
Database link created and tested.
Data Pump import executed successfully.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Migrating Data to Autonomous Database Using Database Links