With FlashGrid Cluster, connecting clients through TCPS requires certain configuration steps that are different from standard steps recommended by Oracle for TCPS configuration.
In this document we assume the following port assignments (all four ports must be different):
- TCP SCAN Listener: 1521
- TCP Local Listener: 1522
- TCPS SCAN Listener: 1523
- TCPS Local Listener: 1524
1. Add TCPS port to SCAN Proxy configuration
Do the following on all database nodes.
1.1 Modify /etc/flashgrid-scan.cfg
#scan_port: 1521 scan_port_list: [1521, 1523]
1.2 Restart SCAN Proxy service
# systemctl restart flashgrid-scan-proxy.service
2. Add iptables forwarding rule for Local Listener TCPS port
Do the following on all database nodes.
2.1 Remove immutable flag from /etc/sysconfig/iptables
# chattr -i /etc/sysconfig/iptables
2.2 In /etc/sysconfig/iptables add the following forwarding rule under similar rule for TCP port (replace X with address corresponding to the node):
-A PREROUTING -i eth0 -p tcp -m tcp --dport 1524 -j DNAT --to-destination 192.168.1.X
2.3 Add immutable flag to /etc/sysconfig/iptables
# chattr +i /etc/sysconfig/iptables
2.4 Restart iptables service
# systemctl restart iptables.service
3. Add TCPS port to Local Listener configuration
On each database node, modify LISTENER entry in listener.ora file under GI home (replace rac1.example.com with corresponding host name):
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) (ADDRESS = (PROTOCOL = TCPS)(HOST =rac1.example.com)(PORT = 1524)) ) ) SECURE_REGISTER_LISTENER_SCAN1 = (IPC,TCPS) SECURE_REGISTER_LISTENER_SCAN2 = (IPC,TCPS) SECURE_REGISTER_LISTENER_SCAN3 = (IPC,TCPS) SECURE_REGISTER_LISTENER = (IPC,TCPS)
From the first node:
[grid@rac1 ~]$ srvctl stop listener [grid@rac1 ~]$ srvctl start listener [grid@rac1 ~]$ srvctl config listener Name: LISTENER Type: Database Listener Network: 1, Owner: grid Home: <CRS home> End points: TCP:1522 Listener is enabled. Listener is individually enabled on nodes: Listener is individually disabled on nodes: [grid@rac1 ~]$ lsnrctl status Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1.example.com)(PORT=1524))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.1)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1522)))
From the second node:
[grid@rac2 ~]$ lsnrctl status Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac2.example.com)(PORT=1524))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1522)))
4. Add TCPS port to SCAN Listener configuration
On the first database node:
[grid@rac1 ~]$ srvctl stop scan_listener [grid@rac1 ~]$ srvctl stop scan [grid@rac1 ~]$ srvctl modify scan_listener -p TCP:1521/TCPS:1523 [grid@rac1 ~]$ srvctl start scan [grid@rac1 ~]$ srvctl start scan_listener [grid@rac1 ~]$ srvctl config scan_listener SCAN Listeners for network 1: Registration invited nodes: Registration invited subnets: Endpoints: TCP:1521/TCPS:1523 SCAN Listener LISTENER_SCAN1 exists SCAN Listener is enabled. SCAN Listener LISTENER_SCAN2 exists SCAN Listener is enabled. SCAN Listener LISTENER_SCAN3 exists SCAN Listener is enabled. [grid@rac1 ~]$ lsnrctl status listener_scan3 Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.1.23)(PORT=1523))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.23)(PORT=1521))) The listener supports no services The command completed successfully [grid@rac1 ~]$ crsctl stat res -p |grep ENDPOINTS ENDPOINTS=TCP:1525 ENDPOINTS=TCP:1522 ENDPOINTS=TCP:1521 TCPS:1523 ENDPOINTS=TCP:1521 TCPS:1523 ENDPOINTS=TCP:1521 TCPS:1523
5. Create SSL Certificates and wallets for DB nodes and client
5.1 Create a self-signed CA (You can use your own CA and safely skip this step)
On the first database node:
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/CA [oracle@rac1 ~]$ export CA_HOME=/u01/app/oracle/CA [oracle@rac1 ~]$ orapki wallet create -wallet $CA_HOME -auto_login -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet remove -trusted_cert_all -wallet $CA_HOME -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet add -wallet $CA_HOME -self_signed -dn "CN=TEST-CA" -keysize 1024 -validity 3650 -sign_alg sha256 -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet export -wallet $CA_HOME -dn "CN=TEST-CA" -cert /u01/app/oracle/CA/testCA.cer -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet display -wallet $CA_HOME -summary Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=TEST-CA Trusted Certificates: Subject: CN=TEST-CA
5.2 Create Cluster Wallet and Certificate
On the first database node:
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/wallet [oracle@rac1 ~]$ export W_HOME=/u01/app/oracle/wallet [oracle@rac1 ~]$ orapki wallet create -wallet $W_HOME -pwd Oracle123 -auto_login -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -trusted_cert -cert /u01/app/oracle/CA/testCA.cer -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet display -wallet $W_HOME -summary Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Trusted Certificates: Subject: CN=TEST-CA
Find out what is your scan name (from oracle or root user):
[oracle@rac1 ~]$ srvctl config scan|grep "SCAN name" SCAN name: marihcheck-scan.example.com, Network: 1
Instead of marihcheck-scan
indicate scan name returned from the previous command:
[oracle@rac1 ~]$ export W_HOME=/u01/app/oracle/wallet [oracle@rac1 ~]$ export CA_HOME=/u01/app/oracle/CA [oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -dn "CN=marihcheck-scan" -keysize 1024 -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet export -wallet $W_HOME -dn "CN=marihcheck-scan" -request $W_HOME/marihcheck.req -pwd Oracle123 [oracle@rac1 ~]$ orapki cert create -wallet $CA_HOME -request $W_HOME/marihcheck.req -cert $W_HOME/marihcheck.cer -validity 3650 -sign_alg sha256 -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/marihcheck.cer -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet display -wallet $W_HOME -summary Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=marihcheck-scan Trusted Certificates: Subject: CN=TEST-CA
5.3 Create Client Wallet and Certificate
From the first database node:
Instead of clientvm
indicate hostname of the client:
[oracle@rac1 ~]$ export W_HOME=/u01/app/oracle/wallet/client [oracle@rac1 ~]$ orapki wallet create -wallet $W_HOME -pwd Oracle123 -auto_login -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -trusted_cert -cert /u01/app/oracle/CA/testCA.cer -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -dn "CN=clientvm" -keysize 1024 -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet export -wallet $W_HOME -dn "CN=clientvm" -request $W_HOME/clientvm.req -pwd Oracle123 [oracle@rac1 ~]$ orapki cert create -wallet $CA_HOME -request $W_HOME/clientvm.req -cert $W_HOME/clientvm.cer -validity 3650 -sign_alg sha256 -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/clientvm.cer -pwd Oracle123 [oracle@rac1 ~]$ orapki wallet display -wallet $W_HOME -summary Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=clientvm Trusted Certificates: Subject: CN=TEST-CA
Change permission on server and client cwallet.sso
file:
[oracle@rac1 wallet]$ chmod 640 /u01/app/oracle/wallet/cwallet.sso [oracle@rac1 wallet]$ chmod 640 /u01/app/oracle/wallet/client/cwallet.sso
Create wallet location on the clientvm and copy generated client wallet from rac1 to the client:
[root@clientvm ~]# mkdir /usr/lib/oracle/19.5/client64/wallet [root@clientvm ~]# cd /usr/lib/oracle/19.5/client64/wallet [root@clientvm ~]# scp oracle@rac1:/u01/app/oracle/wallet/client/* .
Delete client folder from rac1:
[oracle@rac1 ~]$ rm -rf /u01/app/oracle/wallet/client/
Copy generated server wallet from rac1 to rac2:
[oracle@rac1 ~]$ scp -r /u01/app/oracle/wallet oracle@rac2:/u01/app/oracle/ cwallet.sso 100% 2445 638.3KB/s 00:00 cwallet.sso.lck 100% 0 0.0KB/s 00:00 ewallet.p12 100% 2400 540.6KB/s 00:00 ewallet.p12.lck 100% 0 0.0KB/s 00:00
6. Configure Oracle network files
6.1 Add the following lines to the server side sqlnet.ora file in RDBMS and GI homes accordingly (on all database nodes)
[oracle@rac1 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallet) ) ) SSL_VERSION = 0 SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ) SSL_CLIENT_AUTHENTICATION = FALSE
[grid@rac1 ~]$ cat /u01/app/19.3.0/grid/network/admin/sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallet) ) ) SSL_CLIENT_AUTHENTICATION = FALSE
6.2 Add the following entries to the client side sqlnet.ora file:
[root@clientvm admin]# cat sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /usr/lib/oracle/19.5/client64/wallet) ) )
6.3 Add the following lines to the listener.ora in GI home (/u01/app/19.3.0/grid/network/admin/listener.ora) on all database nodes
SSL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallet) ) )
6.4 Update NODEFQDN entry to TCPS/1524 in tnsnames.ora in RDBMS home on all database nodes
[oracle@rac1 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora DONOTDELETE,NODEFQDN = (ADDRESS = (PROTOCOL = TCPS)(Host = rac1.example.com)(Port = 1524))
[oracle@rac2 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora DONOTDELETE,NODEFQDN = (ADDRESS = (PROTOCOL = TCPS)(Host = rac2.example.com)(Port = 1524))
6.5 Reset local_listener parameter to NODEFQDN
[oracle@rac1 ~]$ export ORACLE_SID=orclcdb1 [oracle@rac1 ~]$ sqlplus / as sysdba SQL> alter system set local_listener='NODEFQDN';
6.6 Restart listeners:
[root@rac1 ~]# srvctl stop listener; srvctl start listener [root@rac1 ~]# srvctl stop scan_listener; srvctl start scan_listener
6.7 Add the following entries to the client side tnsnames.ora
[root@clientvm admin]# cat tnsnames.ora TEST_SSL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = marihcheck-scan.example.com)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclcdb) ) ) TEST_SSL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = rac1.example.com)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclcdb) ) ) TEST_SSL2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = rac2.example.com)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclcdb) ) )
7. Test connections
[root@clientvm admin]# export ORACLE_HOME=/usr/lib/oracle/19.5/client64 [root@clientvm admin]# export PATH=$ORACLE_HOME/bin:$PATH [root@clientvm admin]# export TNS_ADMIN=$ORACLE_HOME/network/admin [root@clientvm admin]# sqlplus system/oracle@TEST_SSL SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 19 11:52:33 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sat Mar 14 2020 19:54:53 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL; SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') -------------------------------------------------------------------------------- tcps
[root@clientvm admin]# sqlplus system/oracle@TEST_SSL1 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 19 11:53:23 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Thu Mar 19 2020 11:53:20 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 SQL>
[root@clientvm admin]# sqlplus system/oracle@TEST_SSL2 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 19 11:53:23 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Thu Mar 19 2020 11:53:20 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 SQL>