Using Database Links with Autonomous Databases

In this blog post, I will describe how to create database links from Other Databases to Autonomous Database and from Autonomous Database to Other Databases. Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from an Autonomous Database to another database. To use database links with Autonomous Database the target database must be configured to use TCP/IP with SSL (TCPS) authentication. Autonomous Databases use TCP/IP with SSL (TCPS) authentication by default, so you do not need to do any configuration in your target database for an Autonomous database. Autonomous Database supports creating database links only if the target database is accessible through a public IP. Only one wallet file is valid per directory for use with database links. To use multiple cwallet.sso files with database links you need to create additional directories and put each cwallet.sso in a different directory. When you create database links with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK, specify the directory that contains the wallet with the directory_name parameter.

Let me show the creation a database link to a target database.

1- Copy your target database wallet, cwallet.sso, containing the certificates for the target database to Object Store.

2- Upload the target database wallet to the data_pump_dir directory.

BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => ‘ADMIN’,
object_uri => ‘https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frskjd0nbatp/b/wallets/o/cwallet.sso’,
directory_name => ‘DATA_PUMP_DIR’);
END;
/

In this example;
my bucket name: wallets
my namespace: frskjd0nbatp
my cloud server: eu-frankfurt-1
my credential_name : ADMIN

3- On Autonomous Database create credentials to access the target database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target database that you use to create the database link.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => ‘DB_LINK_USER’,
username => ‘ADMIN’,
password => ‘*********’
);
END;
/

This operation stores the credentials in the database in an encrypted format. If the password of the target user changes you can update the credential that contains the target user’s credentials as follows:

BEGIN
DBMS_CLOUD.UPDATE_CREDENTIAL (
credential_name => ‘DB_LINK_USER’,
attribute => ‘PASSWORD’,
value => ‘*******’);
END;
/

4- Create the database link to the target database.

BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => ‘MYLINK’,
hostname => ‘adb.eu-frankfurt-1.oraclecloud.com’,
port => ‘1522’,
service_name => ‘vjdijq7k4au1w9x_myatp_medium.atp.oraclecloud.com’,
ssl_server_cert_dn => ‘CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US’,
credential_name => ‘DB_LINK_USER’,
directory_name => ‘DATA_PUMP_DIR’);
END;
/

If you need drop the created database link, you can drop as follows:

BEGIN
DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
db_link_name => ‘MYLINK’ );
END;
/

5- Test the database link you created to access data on the target database.

select * from dba_db_links;

select * from dual@MYLINK;

Now, let’s create database link from another Oracle Database to an Autonomous Database.

1- Download your Autonomous Database wallet. I had explained in my previous post.

2- Upload the wallet to the database instance where you want to create the link to the Autonomous database.

3- Set GLOBAL_NAMES to FALSE

ALTER SYSTEM SET GLOBAL_NAMES = FALSE;

4- Create the database link to the target Autonomous Database.the security path includes my_wallet_directory; the path where you upload the Autonomous Database wallet.

CREATE DATABASE LINK AUTONOM_DBLINK
CONNECT TO ADMIN IDENTIFIED BY “*********”
USING
‘(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=vjdijq7k4au1w9x_myatp_high.atp.oraclecloud.com))(security=(my_wallet_directory=/home/oracle/wallet)
(ssl_server_dn_match=true) (ssl_server_cert_dn=”CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))’;

Test the database link you created to access data on your Autonomous Database instance.

Now, I want to do some data test. I will create DB_INVENTORY table copy to my local 12.1.0.2 database over database link. And then re insert from this copy table to my 19c autonomous database.

let me check the data from my autonomous database using SQL Developer Web.

Yes! My data is here.