Wednesday, February 7, 2018

SSL setup for oracle RAC database.

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.