Migrating to the Autonomous Database - Dedicated using Database Links

Autonomous Database on Dedicated Exadata Infrastructure and Cloud@Customer (ADB-D and ADB-CC) includes support for outgoing Database Links to various Oracle databases, including its Serverless variant (ADB-S). These links offer a bridge between your source Oracle Database and an Autonomous Database, facilitating data reading or transfer between the two.

Migration using Oracle Data Pump
Oracle's Data Pump empowers you with diverse techniques to transition data between your source and target databases. Among the numerous methods, one efficient route involves using Network or Database Links.

Advantages? Utilizing Database Links for data migration via the Data Pump utility (impdp) eliminates the necessity of writing dump files or moving these files from the source database to intermediary storage areas like Object Storage or Network File System. This is particularly beneficial for databases of a smaller size since the entire migration can take place over a network link.

Step-by-Step Migration Guide Using Database Links

Prerequisites:

Source Oracle Database:
Your database could be on-premises, on Oracle Cloud Infrastructure (OCI), or with another cloud provider.

CPAT:
The Cloud Premigration Advisory Tool (CPAT) helps you evaluate an existing Oracle database for compatibility with the Autonomous Database (ADB). Using CPAT before migrating to ADB makes assessing your source database easier and faster!  CPAT removes much of the legwork of identifying potential user actions, prioritizing their importance, and suggesting resolutions. Refer here to get more details on CPAT.

Network Connectivity:
Ensure seamless network connectivity between your source Oracle Database and the target ADB-D.  Remember, your ADB-D sits in a private subnet, making it essential to have a reliable network configuration in place.

Target Autonomous Database:
Gear up by provisioning the ADB-D on Oracle Cloud Infrastructure or Cloud@Customer. Need guidance on this?  Dive into our detailed guide.

Setting Up the Database Link:

Preparation:
Before creating the Database Link, ensure you have connectivity to your ADB-D, either copying Wallet into your source Oracle Database or using a Walletless connection.  Here's a quick walkthrough (Lab 2: Configure a Development System) to help you with the download and transfer process.

Configuration:
Adjust your tnsadmin and sqlnet.ora settings on your source Oracle Database and make certain you can seamlessly SQLPLUS into the Autonomous Database - Dedicated.

Database Link Creation:
Execute the following command:

CREATE DATABASE LINK <Source_Database_global_unique_name> CONNECT TO system IDENTIFIED BY <SourceDB_password> USING '<Connect_string_Source_DB>';

Testing:
Confirm the functionality of the newly created Database Link with this simple test:

select * from dual@<Database_Link>;

Data Migration:

Preparation:
Before migrating, ensure you've set up the necessary profiles, roles, and Tablespaces in the ADB-D instance.

Data Import:

You can remap to DATA Tablespace in ADB-D or you can create Tablespaces as you have them in your source Database. For Autonomous Data Warehouse, user-created Tablespace will not be enabled with compression. You can alter the created Tablespace or include it while creating Tablespce to enable compression.

alter tablespace test default compress for QUERY HIGH ROW LEVEL LOCKING;

Also, make sure to create your existing Profiles and Roles in ADB-D before starting the import datapump process. 
Determine which schemas you'd like to migrate. Once decided, initiate the Import Data Pump from your source Oracle Database with the following command:

impdp admin/<ADB_Password>@adb2_high SCHEMAS=<schemas> network_link=<Database_Link> parallel=1 transform=segment_attributes:n exclude=cluster nologfile=yes remap_tablespace=USERS:DATA

Note: Post-migration, you may occasionally encounter 'Role grant failed' errors.  This is because ADB-D restricts access to SYS, SYSTEM, or DBA.  For in-depth information and best practices to avoid such pitfalls, refer to our comprehensive guide.

Verification:
After the migration, SQLPLUS into your ADB-D to cross-check the migrated data. Simply select values from the table in question to confirm its presence. 

And Voila!  It works 

Bonus: Stay tuned for an immersive, step-by-step hands-on LiveLab about this topic.  Watch this blog!