X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

How to Create a Database Link from an Autonomous Data Warehouse to a Database Cloud Service Instance

Can Tuzla
Senior Product Manager

Autonomous Data Warehouse (ADW) now supports outgoing database links to any database that is accessible from an ADW instance including Database Cloud Service (DBCS) and other ADW/ATP instances. To use database links with ADW, the target database must be configured to use TCP/IP with SSL (TCPS) authentication. Since both ADW and ATP use TCPS authentication by default, setting up a database link between these services 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 ADW 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 ADW to DBCS

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 their 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 in 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 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"
[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)))
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 the port 1521 of the default listener. It's also possible to keep the port 1521 as is and add 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 ADW 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 ADW 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.
  • Upload the target database wallet to the data_pump_dir directory on ADW 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 => 'DATA_PUMP_DIR'); 
END;
/    

PL/SQL procedure successfully completed.
  • On ADW 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');
END;
/   

PL/SQL procedure successfully completed.
  • Use the database link you created to access data on the target database:
SQL> select * from dual@DBCSLINK;

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 ADW to our DBCS instance. Even though we focused on the DBCS configuration, these steps can be applied when setting up a database link between ADW and any other Oracle database.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.