How to Create a Database Link from Your Autonomous Database to a Database Cloud Service Instance

Update 12/16/2021: You can now create database links from your Autonomous Database to the databases that are on private endpoints. Check out my recent post for more details on this feature.

Autonomous Database on Shared Exadata Infrastructure (ADB-S) now supports outgoing database links to any database that is accessible from an ADB-S instance including Database Cloud Service (DBCS) and other ADB-Sinstances. To use database links in ADB-S, the target database must be configured to use TCP/IP with SSL (TCPS) authentication. Since ADB-S uses TCPS authentication by default, setting up a database link between two ADB-S instances is pretty easy and takes only a few steps. We covered the ADB-to-ADB linking process in the first of this two part series of blog posts about using database links, see Making Database Links from ADW to other Databases. That post explained the simplest use case to configure and use.

On the other hand, enabling TCPS authentication in a database that doesn't have it configured (e.g. in DBCS) requires some additional steps that need to be followed carefully. In this blog post, I will try to demonstrate how to create a database link from an ADB-S instance to a DBCS instance including the steps to enable TCPS authentication. Here is an outline of the steps that we are going to follow:

• Enable TCPS Authentication in DBCS
• Connect to DBCS Instance from Client via TCPS
• Create a DB Link from ADB-S to DBCS
• Create a DB Link from DBCS to ADB-S (Optional)

Enable TCPS Authentication in DBCS

A DBCS instance uses TCP/IP protocol by default. Configuring TCPS in DBCS involves several steps that need to be performed manually. Since we are going to modify the default listener to use TCPS and it's configured under the grid user, we will be using both oracle and grid users. Here are the steps needed to enable TCPS in DBCS:

• Create wallets with self signed certificates for server and client
• Exchange certificates between server and client wallets (Export/import certificates)
• Add wallet location in the server and the client network files
• Add TCPS endpoint to the database listener

Create wallets with self-signed certificates for server and client

As part of enabling TCPS authentication, we need to create individual wallets for the server and the client. Each of these wallets has to have its own certificates that they will exchange with one another. For the sake of this example, I will be using a self-signed certificate. The client wallet and certificate can be created on the client-side; however, I'll be creating my client wallet and certificate in the server and moving them to my local system later on. See Configuring Secure Sockets Layer Authentication for more information. Let's start...

Set up wallet directories with the root user

[root@dbcs0604 u01]$ mkdir -p /u01/server/wallet
[root@dbcs0604 u01]$ mkdir -p /u01/client/wallet
[root@dbcs0604 u01]$ mkdir /u01/certificate 
[root@dbcs0604 /]# chown -R oracle:oinstall /u01/server
[root@dbcs0604 /]# chown -R oracle:oinstall /u01/client
[root@dbcs0604 /]# chown -R oracle:oinstall /u01/certificate

Create a server wallet with the oracle user

[oracle@dbcs0604 ~]$ cd /u01/server/wallet/
[oracle@dbcs0604 wallet]$ orapki wallet create -wallet ./ -pwd Oracle123456 -auto_login
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Create a server certificate with the oracle user

[oracle@dbcs0604 wallet]$ orapki wallet add -wallet ./ -pwd Oracle123456 -dn "CN=dbcs" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Create a client wallet with the oracle user

[oracle@dbcs0604 wallet]$ cd /u01/client/wallet/
[oracle@dbcs0604 wallet]$ orapki wallet create -wallet ./ -pwd Oracle123456 -auto_login
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Create a client certificate with the oracle user

[oracle@dbcs0604 wallet]$ orapki wallet add -wallet ./ -pwd Oracle123456 -dn "CN=ctuzla-mac" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Exchange certificates between server and client wallets (Export/import certificates)

Export the server certificate with the oracle user

[oracle@dbcs0604 wallet]$ cd /u01/server/wallet/
[oracle@dbcs0604 wallet]$ orapki wallet export -wallet ./ -pwd Oracle123456 -dn "CN=dbcs" -cert /tmp/server.crt
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Export the client certificate with the oracle user

[oracle@dbcs0604 wallet]$ cd /u01/client/wallet/
[oracle@dbcs0604 wallet]$ orapki wallet export -wallet ./ -pwd Oracle123456 -dn "CN=ctuzla-mac" -cert /tmp/client.crt
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Import the client certificate into the server wallet with the oracle user

[oracle@dbcs0604 wallet]$ cd /u01/server/wallet/
[oracle@dbcs0604 wallet]$ orapki wallet add -wallet ./ -pwd Oracle123456 -trusted_cert -cert /tmp/client.crt
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Import the server certificate into the client wallet with the oracle user

[oracle@dbcs0604 wallet]$ cd /u01/client/wallet/
[oracle@dbcs0604 wallet]$ orapki wallet add -wallet ./ -pwd Oracle123456 -trusted_cert -cert /tmp/server.crt 
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Change permissions for the server wallet with the oracle user

We need to set the permissions for the server wallet so that it can be accessed when we restart the listener after enabling the TCPS endpoint.

[oracle@dbcs0604 wallet]$ cd /u01/server/wallet
[oracle@dbcs0604 wallet]$ chmod 640 cwallet.sso

Add wallet location in the server and the client network files

Creating server and client wallets with self-signed certificates and exchanging certificates were the initial steps towards the TCPS configuration. We now need to modify both the server and client network files so that they point to their corresponding wallet location and they are ready to use the TCPS protocol. Here's how those files look in my case:

Server-side $ORACLE_HOME/network/admin/sqlnet.ora under the grid user

# sqlnet.ora Network Configuration File: /u01/app/18.0.0.0/grid/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/u01/server/wallet)))

SSL_SERVER_DN_MATCH=(ON)

Server-side $ORACLE_HOME/network/admin/listener.ora under the grid user

wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/u01/server/wallet)))

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Server-side $ORACLE_HOME/network/admin/tnsnames.ora under the oracle user

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/18.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_CDB1 =
  (ADDRESS = (PROTOCOL = TCPS)(HOST = dbcs0604)(PORT = 1521))


CDB1_IAD1W9 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = dbcs0604)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1_iad1w9.sub05282047220.vcnctuzla.oraclevcn.com)
    )
    (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
  )

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = dbcs0604)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.sub05282047220.vcnctuzla.oraclevcn.com)
    )
    (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
  )

Add TCPS endpoint to the database listener

Now that we are done with configuring our wallets and network files, we can move onto the next step, which is configuring the TCPS endpoint for the database listener. Since our listener is configured under grid, we will be using srvctl command to modify and restart it. Here are the steps:

[grid@dbcs0604 ~]$ srvctl modify listener -p "TCPS:1521/TCP:1522"
[grid@dbcs0604 ~]$ srvctl stop listener
[grid@dbcs0604 ~]$ srvctl start listener
[grid@dbcs0604 ~]$ srvctl stop database -database cdb1_iad1w9
[grid@dbcs0604 ~]$ srvctl start database -database cdb1_iad1w9
[grid@dbcs0604 ~]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 05-JUN-2019 16:07:24

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                05-JUN-2019 16:05:50
Uptime                    0 days 0 hr. 1 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/18.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/dbcs0604/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.0.0.4)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.4)(PORT=1522)))
Services Summary...
Service "867e3020a52702dee053050011acf8c0.sub05282047220.vcnctuzla.oraclevcn.com" has 1 instance(s).
  Instance "cdb1", status READY, has 2 handler(s) for this service...
Service "8a8e0ea41ac27e2de0530400000a486a.sub05282047220.vcnctuzla.oraclevcn.com" has 1 instance(s).
  Instance "cdb1", status READY, has 2 handler(s) for this service...
Service "cdb1XDB.sub05282047220.vcnctuzla.oraclevcn.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1_iad1w9.sub05282047220.vcnctuzla.oraclevcn.com" has 1 instance(s).
  Instance "cdb1", status READY, has 2 handler(s) for this service...
Service "pdb1.sub05282047220.vcnctuzla.oraclevcn.com" has 1 instance(s).
  Instance "cdb1", status READY, has 2 handler(s) for this service...
The command completed successfully

Please note that in the first step we added the TCPS endpoint to port 1521 and the TCP endpoint to port 1522 of the default listener. It's also possible to keep the port 1521 as is and add the TCPS endpoint to a different port (e.g. 1523).

Connect to DBCS Instance from Client via TCPS

We should have TCPS authentication configured now. Before we move onto testing, let's take a look at the client-side network files (Please note the public IP address of the DBCS instance in tnsnames.ora):

Client-side tnsnames.ora

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 132.145.151.208)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1_iad1w9.sub05282047220.vcnctuzla.oraclevcn.com)
    )
    (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
  )
    
PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 132.145.151.208)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.sub05282047220.vcnctuzla.oraclevcn.com)
    )
    (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
   )

Client-side sqlnet.ora

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /Users/cantuzla/Desktop/wallet)
     )
   )

SSL_SERVER_DN_MATCH=(ON)

In order to connect to the DBCS instance from the client, you need to add an ingress rule for the port that you want to use (e.g. 1521) in the security list of your virtual cloud network (VCN) in OCI as shown below:

We can now try to establish a client connection to PDB1 in our DBCS instance (CDB1):

ctuzla-mac:~ cantuzla$ cd Desktop/InstantClient/instantclient_18_1/
ctuzla-mac:instantclient_18_1 cantuzla$ ./sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 Production on Wed Jun 5 09:39:56 2019
Version 18.1.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

SQL> connect c##dbcs/DBcs123_#@PDB1
Connected.
SQL> select * from dual;

D
-
X

Create a DB Link from ADB-S to DBCS

We now have a working TCPS authentication in our DBCS instance. Here are the steps from the documentation that we will follow to create a database link from ADB-S to DBCS:

• Copy your target database wallet (the client wallet cwallet.sso that we created in /u01/client/wallet) for the target database to Object Store.
• Create credentials to access your Object Store where you store the cwallet.sso. See CREATE_CREDENTIAL Procedure for details.
• Create a directory to store the target database wallet:

SQL> create directory wallet_dir as 'walletdir';

Directory WALLET_DIR created.

• Upload the target database wallet to the wallet_dir directory on ADB-S using DBMS_CLOUD.GET_OBJECT:

SQL> BEGIN 
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'OBJ_STORE_CRED',
    object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adwctraining8/b/target-wallet/o/cwallet.sso',
    directory_name => 'WALLET_DIR'); 
END;
/    

PL/SQL procedure successfully completed.

• On ADB-S, 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. Make sure the username consists of all uppercase letters. For this example, I will be using the C##DBCS common user that I created in my DBCS instance:

SQL> BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DBCS_LINK_CRED',
    username => 'C##DBCS',
    password => 'DBcs123_#');
END;
/    

PL/SQL procedure successfully completed.

• Create the database link to the target database using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK:

SQL> BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'DBCSLINK', 
    hostname => '132.145.151.208', 
    port => '1521',
    service_name => 'pdb1.sub05282047220.vcnctuzla.oraclevcn.com',
    ssl_server_cert_dn => 'CN=dbcs',
    credential_name => 'DBCS_LINK_CRED',
    directory_name => 'WALLET_DIR');
END;
/   

PL/SQL procedure successfully completed.

• Use the database link you created to access the data on the target database:

SQL> select * from dual@DBCSLINK;

D
-
X

Create a DB Link from DBCS to ADW (Optional)

Although the previous section concludes the purpose of this blog post, here's something extra for those who are interested. In just a couple additional steps, we can also create a DB link from DBCS to ADB-S:

• Download your ADB-S wallet.
• Upload the wallet to your DBCS instance using sftp or an FTP client.
• Unzip the wallet:

    [oracle@dbcs0604 ~]$ cd /u01/targetwallet
    [oracle@dbcs0604 targetwallet]$ unzip Wallet_adwtuzla.zip 
    Archive:  Wallet_adwtuzla.zip
      inflating: cwallet.sso             
      inflating: tnsnames.ora            
      inflating: truststore.jks          
      inflating: ojdbc.properties        
      inflating: sqlnet.ora              
      inflating: ewallet.p12             
      inflating: keystore.jks 
    

• Set GLOBAL_NAMES parameter to FALSE. This step is very important. If you skip this, your DB link will not work.

SQL> alter system set global_names=FALSE;

System altered.

SQL> sho parameter global

NAME                                                   TYPE        VALUE
----------------------   ----------- -----------
allow_global_dblinks     boolean	 FALSE
global_names			 boolean	 FALSE
global_txn_processes	 integer            1

• Create a DB link as follows (notice the my_wallet_directory clause pointing to where we unzipped the ADB-S wallet):

create database link ADBLINK connect to ADMIN identified by ************ using 
  '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
     (connect_data=(service_name=ctwoqpkdfcuwpsd_adwtuzla_high.adwc.oraclecloud.com))
     (security=(my_wallet_directory=/u01/targetwallet)(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))';

Database link created.

• Use the database link you created to access the data on the target database (your ADB-S instance in this case):

SQL> select * from dual@ADBLINK;

D
-
X

That's it! In this blog post, we covered how to enable TCPS authentication in DBCS and create an outgoing database link from ADB-S to our DBCS instance. As bonus content, we also explored how to create a DB link in the opposite direction, that is from DBCS to ADB-S. Even though we focused on the DBCS configuration, these steps can be applied when setting up a database link between ADB-S and any other Oracle database.