SSL setup for oracle RAC database.
Step by Step Guide: How to Configure SSL/TLS on ORACLE RAC (with SCAN) (Doc ID 1448841.1)
1. configure TCPS protocal endpoints
crsctl stat res -p | grep -i endpoints
srvctl modify listener -p "TCP:1521/TCPS:35050";
srvctl config listener
srvctl modify scan_listener -p "TCP:1521/TCPS:35050"
srvctl config scan_listener
2. create SSL certificataes and wallets for cluster and for client.
2.1 create self-signed CA (NO need if exsited CA is used. )
mkdir -p /u01/app/oracle/CA
cd /u01/app/oracle/CA
export CA_HOME=/u01/app/oracle/CA
orapki wallet create -wallet $CA_HOME
orapki wallet remove -trusted_cert_all -wallet $CA_HOME
orapki wallet add -wallet $CA_HOME -self_signed -dn "CN=DEV-CA,O=MYORG,C=MYOFFICE" -keysize 2048 -validity 3650 -sign_alg sha256 -pwd <password>
orapki wallet export -wallet $CA_HOME -dn "CN=DEV-CA,O=MYORG,C=MYOFFICE" -cert devCA.cer -pwd <password>
orapki wallet display -wallet $CA_HOME -summary
2.2 create cluster wallet and cert
mkdir -p /u01/app/oracle/wallet/myrac
export W_HOME=/u01/app/oracle/wallet/myrac
orapki wallet create -wallet $W_HOME
orapki wallet remove -trusted_cert_all -wallet $W_HOME -pwd <password>
orapki wallet add -wallet $W_HOME -dn "CN=myrac" -keysize 2048 -pwd <password>
orapki wallet export -wallet $W_HOME -dn "CN=myrac" -request $W_HOME/myrac.req -pwd <password>
orapki cert create -wallet $CA_HOME -request $W_HOME/myrac.req -cert $W_HOME/myrac.cer -validity 3650 -sign_alg sha256 -pwd <password>
orapki wallet add -wallet $W_HOME -trusted_cert -cert $CA_HOME/devCA.cer -pwd <password>
orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/myrac.cer -pwd <password>
orapki wallet display -wallet $W_HOME -summary -pwd <password>
orapki wallet create -wallet $W_HOME -auto_login
## the wallet with cert need be copy to all nodes.
2.3 create client wallet and cert
## unique ssl cert is required for client with the same CA for successful SSL connection.
mkdir -p /home/oracle/ben/wallet
export W_HOME=/home/oracle/ben/wallet
orapki wallet create -wallet $W_HOME -pwd <password>
orapki wallet remove -trusted_cert_all -wallet $W_HOME -pwd <password>
orapki wallet add -wallet $W_HOME -trusted_cert -cert $W_HOME/devCA.cer -pwd <password>
orapki wallet create -wallet $W_HOME -auto_login
orapki wallet add -wallet $W_HOME -dn "CN=devUser" -keysize 2048 -pwd <password>
orapki wallet export -wallet $W_HOME -dn "CN=devUser" -request $W_HOME/devUser.req -pwd <password>
orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/devUser.cer -pwd <password>
3. configure listener.ora and sqlnet.ora for cluster
# both PMON and the listener processes of each node must be able to access the wallets.
# This is in oracle home.
3.1 in sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/wallet/myrac)
)
)
3.2 in listener.ora ## this is in Grid home.
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/wallet/myrac)
)
)
4.Restart instances and listeners.
srvctl stop listener
srvctl start listener
srvctl stop scan_listener
srvctl start scan_listener
srvctl stop database -d testdb
srvctl start database -d testdb
/** OR shut instance one by one
srvctl stop instance -db testdb -instance testdb1 -stopoption immediate -force
srvctl start instance -db testdb -instance testdb1
srvctl stop instance -db testdb -instance testdb2 -stopoption immediate -force
srvctl start instance -db testdb -instance testdb2
*/
5. configure sqlnet.ora for client
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/wallet/devUser)
)
)
6. test connection
6.1 create tns entry
testdbSSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = myrac-scan)(PORT = 35050))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
6.2 ping ssl entry
tnsping testdbSSL
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-FEB-2018 16:17:03
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = myrac-scan)(PORT = 35050)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb)))
OK (130 msec)
6.3 connect with sqlplus
[oracle@sp-ud-oitord01 ben]$ sqlplus system@testdbssl
SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 7 16:17:51 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Feb 07 2018 16:00:09 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL>
6. verify connection protocol
SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
SQL>
###################
# troubelshooting
####################
###ORA-28865: SSL connection closed or ORA-28864: SSL CONNECTION CLOSED GRACEFULLY
for rac both grid_home and oracle_home need have below setup on sqlnet.ora
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/wallet/myrac)
)
)
#### ORA-28860: Fatal SSL error
this is because client side sqlnet.ora has
SL_CLIENT_AUTHENTICATION=FALSE
comment it out.
