Twitter Linkedin RSS
Close
Oracle

Creating DB Link to Oracle Cloud ADB from an on-premises database

Sean D. StuberLeave a comment

Creating db links to an Oracle Cloud ADB database is mostly the same as you would between two on-premises databases. The key difference is the requirement to use a wallet for the SSL communication.

From within the Oracle Cloud interface select the database you want to link to, go to the DB connection tab and download the Client Credentials Instance wallet. This download is a zip file containing, among other contents, the cwallet.sso file.

For your on-premises database, copy the cwallet.sso file to a directory on your database server. Then edit the sqlnet.ora for your database to point to the directory where you placed the cwallet.sso file using the WALLET_LOCATION parameter. You will also need to as set the SSL_SERVER_DN_MATCH parameter to YES. 

WALLET_LOCATION = (
    SOURCE = (METHOD = file) 
    (METHOD_DATA = 
        (DIRECTORY="/path/to/your/wallet/")
    )
)

SSL_SERVER_DN_MATCH=yes

If you have multiple Oracle Homes on your server, make sure you edit the sqlnet.ora your database is using. Also note the WALLET_LOCATION is only the path to the directory, it does not include the file itself. Last, the directory must be one accessible by the database.

The client credentials zip file will also include a sample tnsnames.ora with entries for various services to your cloud db. The exact contents will vary for your DB hosting, but the general syntax will be something like this (formatted for ease of reading.)

YOUR_CONNECT_ALIAS=
    (description=
       (retry_count=20)
       (retry_delay=3)
       (address=
          (protocol=tcps)
          (port=1522)
          (host=YOUR_ADB_HOST.oraclecloud.com)
       )
       (connect_data=
          (service_name=YOUR_SERVICE.adb.oraclecloud.com)
       )
       (security=
          (ssl_server_cert_dn="CN=YOUR_DN.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
       )
    )

Copy the entry or entries you want to use to the tnsnames.ora file for your database, changing the connection alias if desired. If you use ldap for name resolution, you can load the alias and description into your ldap server instead.

Once you have the SSL infrastructure set up, the db link creation is the same as it would be with on-premises databases. 

create database link YOUR_LINK
  connect to YOUR_ADB_USER
  identified by YOUR_ADB_PASSWORD
  using 'YOUR_CONNECT_ALIAS';

And that’s all there is to it for connecting an on-premises db to an ADB cloud db. Once the link is created you can use it just as you would if it were connected to a local database.

Leave a Reply