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. 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 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.
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]$ 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]$ 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:
Posts
0 comments:
Post a Comment