How to create a DB Link in Autonomous Database?

[admin]

Hi,
If you want to create a DB Link from Autonomous database instance to your local on-premises database 11g.

Note: Target database must have SSL certificate installed and configured. In my case I have digicert installed.

1-

$ cd <location of wallet>
$ orapki wallet display -wallet .

Subject: CN=*********,O=*******\,,L=*******,C=**
Trusted Certificates:
Subject: CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Subject: CN=DigiCert SHA2 Secure Server CA,O=DigiCert Inc,C=US

2- I added the following entry in my listener.ora on my target database,

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=*******)(PORT=1522))) << this host is having public IP so access over the internet and port is allowed on my firewall.

3. Copy cwallet.sso file from wallet folder and upload to [url http://www.erpstuff.com/viewtopic.php?f ... f5657edd66]Object Store[/url].

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OBJ_STORE_CRED',
username => 'admin@erpstuff.com',
password => '*************;
END;

BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => 'OBJ_STORE_CRED',
object_uri => 'https://objectstorage.us-ashburn-1.orac ... wallet.sso',
directory_name => 'DATA_PUMP_DIR');
END;

4- Check if your file upload.
SELECT * FROM table(dbms_cloud.list_files('DATA_PUMP_DIR'));

5- Now create a credential of your local user to which you will access using DB Link,

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DB_LINK_CRED',
username => 'XXXXXX',
password => '********'
);
END;

Note: to drop credentials use the following command,

BEGIN
DBMS_CLOUD.DROP_CREDENTIAL(
credential_name => 'DB_LINK_CRED' );
END;

BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'ERPSTUFF',
hostname => 'XXX.XXX.XXX.XXX', << Public ip or name of your server
port => '1522',
service_name => 'my11gdb.erpstuff.com',
ssl_server_cert_dn => CN=*********,O=*******\,,L=*******,C=**, << as per step 1 above.
credential_name => 'DB_LINK_CRED',
directory_name => 'DATA_PUMP_DIR');
END;

BEGIN
DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(db_link_name => 'ERPSTUFF' );
END;

to test db link,

select sysdate from dual@ERPSTUFF;