Hardik's DBA Blog: Renew SSL certificate for TCPS connection in Oracle wallet

1) Set Oracle Home and Oracle SID

ORACLE_SID=TESTDBA1

ORACLE_HOME=/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_6

2) Take backup of existing wallet directory

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/

cp -R wallet wallet_DDMMYYYY

Note: make sure you are always keeping copy of wallet just in case revert is needed.

3) Add user certificate in existing wallet (Later, we need to renew this certificate.)

INDODAPDB01:TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet> orapki wallet add -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -keysize 2048 -pwd tempwallet1

** Check added certificate in existing wallet, requested entry should be added in display output. **

Output:

INDODAPDB01:TESTDBA1:/u01/app/oracle/admin/TESTDBA1/wallet> orapki wallet display -wallet .

Oracle PKI Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Requested Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com <<< Newly requested certificate for renew

User Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

Subject: CN=TEST Root CA 02,DC=TEST,DC=com

Subject: CN=TEST ICA 12,DC=NA,DC=TEST,DC=com

4) Remove existing user certificate

Here certificate name of existing user certificate and requested user cert is exactly same (above we can see that)

so orapki cannot distinguish them so as a workaround copy wallet directory and remove existing user certificate from copied wallet directory and after that export it.

** Make a copy of existing wallet directory as wallet_edit directory **

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/

cp -R wallet wallet_edit

** Remove user certificate from copied wallet directory(wallet_edit) **

Note: It is very important to work on only copied wallet directory

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit

orapki wallet remove -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -user_cert -pwd ******

Output:

INDODAPDB01:TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet_edit>orapki wallet remove -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -user_cert -pwd ******

Oracle PKI Tool Release 23.0.0.0.0 - Production

Version 23.0.0.0.0

Operation is successfully completed.

** Check wallet contents **

INDODAPDB01:TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet_edit> orapki wallet display -wallet .

Oracle PKI Tool Release 23.0.0.0.0 - Production

Version 23.0.0.0.0

Requested Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

Subject: CN=TEST Root CA 02,DC=TEST,DC=com

Subject: CN=TEST ICA 12,DC=NA,DC=TEST,DC=com

5) Export certificate from wallet_edit (copied directory) and send it to team who is taking care to provide SSL/TLS certificate.

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit

orapki wallet export -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -request ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit/`hostname|cut -d"." -f 1`_${ORACLE_SID}_AD_2048.csr -pwd ******

Output:

INDODAPDB01:TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet_edit>orapki wallet export -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -request ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_edit/`hostname|cut -d"." -f 1`_${ORACLE_SID}_AD_2048.csr -pwd ******

Oracle PKI Tool Release 23.0.0.0.0 - Production

Version 23.0.0.0.0

Operation is successfully completed.

INDODAPDB01: TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet_edit> ls -ltr

total 28

-rw-r--r-- 1 oracle oinstall 0 Sep 9 04:20 ewallet.p12.lck

-rw-r--r-- 1 oracle oinstall 0 Sep 9 04:20 cwallet.sso.lck

-rw------- 1 oracle oinstall 8320 Sep 9 04:22 ewallet.p12

-rw------- 1 oracle oinstall 8365 Sep 9 04:22 cwallet.sso

-rw------- 1 oracle oinstall 1016 Sep 9 04:24 indodapdb01_TESTDBA1_AD_2048.csr

6) Copy csr file (indodapdb01_TESTDBA1_AD_2048.csr) to local machine and raise a request to renew it from concerned authority.

7) Concerned authority will send us signed certificate (p7b extention file)

** Rename file with servername_DATABASENAME_AD_2048.p7b and change permission to 755 **

$ mv OracleContext.p7b indodapdb01_TESTDBA1_AD_2048.p7b

$ chmod 777 indodapdb01_TESTDBA1_AD_2048.p7b

** copy .p7b file to wallet location **

8) Install new certificate in new wallet directory (wallet_`date +%Y%m%d`)

** Take a fresh Backup existing wallet directory **

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/

tar -cvf wallet_backup_`date +%Y%m%d`.tar ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet

** Make a copy of existing wallet directory as wallet_`date +%Y%m%d` directory **

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/

cp -pr wallet wallet_`date +%Y%m%d`

** go to wallet_`date +%Y%m%d` directory **

cd wallet_`date +%Y%m%d`

** Rename old certificate file to an old year **

mv `hostname|cut -d"." -f 1`_${ORACLE_SID}_AD_2048.p7b `hostname|cut -d"." -f 1`_${ORACLE_SID}_AD_2048.p7b.YYYY <<< Replace with year when certificate is expiring

** Set environment to add new certificate file

export Ymd=`date +%Y%m%d`

echo $Ymd

** Display wallet contents from new wallet directory (wallet_`date +%Y%m%d`)

orapki wallet display -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}

Output:

INDODAPDB01:TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet_20230713> orapki wallet display -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}

Oracle PKI Tool Release 23.0.0.0.0 - Production

Version 23.0.0.0.0

Requested Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

User Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

Subject: CN=TEST Root CA 02,DC=TEST,DC=com

Subject: CN=TEST ICA 12,DC=NA,DC=TEST,DC=com

** Remove existing/old user certicate record from wallet_${Ymd} **

orapki wallet remove -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd} -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -user_cert -pwd ******

** Remove existing/old Intermediate certificate record from wallet_${Ymd} **

orapki wallet remove -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd} -dn "CN=TEST ICA 12,DC=NA,DC=TEST,DC=com" -trusted_cert

** Remove existing/old root certificate record from wallet_${Ymd} **

orapki wallet remove -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd} -dn "CN=TEST Root CA 02,DC=TEST,DC=com" -trusted_cert

** Display wallet contents from new wallet directory (wallet_`date +%Y%m%d`)

orapki wallet display -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}

Output:

INDODAPDB01:TESTDBA1:/u01/app/odaorabase/oracle/admin/TESTDBA1/wallet_20230713> orapki wallet display -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}

Oracle PKI Tool Release 19.0.0.0.0 - Production

Version 19.0.0.0.0

Requested Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

** Add newly signed certificate using ${ORACLE_SID}_AD_2048.p7b file **

orapki wallet add -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd} -user_cert -cert ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}/`hostname|cut -d"." -f 1`_${ORACLE_SID}_AD_2048.p7b

** Display wallet contents from new wallet directory (wallet_`date +%Y%m%d`)

orapki wallet display -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}

Output:

INDODAPDB01:TESTDBA1:/u01/app/oracle/admin/TESTDBA1/wallet_${Ymd> orapki wallet display -wallet .

Oracle PKI Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Requested Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

User Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

Subject: CN=TEST ICA 12,DC=NA,DC=TEST,DC=com

Subject: CN=TEST Root CA 02,DC=TEST,DC=com

** Remove requested certificate from wallet_${Ymd} directory **

orapki wallet remove -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd} -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -cert_req -pwd ******

** Display wallet contents from new wallet directory (wallet_`date +%Y%m%d`)

orapki wallet display -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet_${Ymd}

Output:

INDODAPDB01:TESTDBA1:/u01/app/oracle/admin/TESTDBA1/wallet_${Ymd> orapki wallet display -wallet .

Oracle PKI Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

User Certificates:

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

Subject: CN=TEST ICA 12,DC=NA,DC=TEST,DC=com

Subject: CN=TEST Root CA 02,DC=TEST,DC=com

** Rename existing/current wallet directory by adding year when certificate is going to expire **

cd ${ORACLE_BASE}/admin/${ORACLE_SID}

mv wallet wallet_2021

** Replace new wallet directory (wallet_`date +%Y%m%d`) to actual wallet directory **

This step will make newly signed certificate in use.

cp -pr wallet_${Ymd} wallet

** export crt file to check certificate expiry **

cd ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet

orapki wallet export -wallet ewallet.p12 -dn "CN=${ORACLE_SID},CN=OracleContext,DC=db,DC=test,DC=com" -cert certificate_expiry.crt -pwd ******

orapki cert display -cert /u01/app/oracle/admin/TESTDBA1/wallet/certificate_expiry.crt -summary

Output:

INDODAPDB01:TESTDBA1:/u01/app/oracle/admin/TESTDBA1/wallet/> orapki cert display -cert /u01/app/oracle/admin/TESTDBA1/wallet/certificate_expiry.crt -summary

Oracle PKI Tool Release 19.0.0.0.0 - Production

Version 19.0.0.0.0

Subject: CN=TESTDBA1,CN=OracleContext,DC=db,DC=test,DC=com

Issuer: CN=TEST ICA 12,DC=NA,DC=TEST,DC=com

Valid Until: Mon Jul 10 19:15:38 IST 2027

9) Restart Listener (This is required to take effect of certificate renewal)

lsnrctl stop listener

lsnrctl start listener

lsnrctl status listener

Hardik's DBA Blog

Thursday, 4 January 2024

Renew SSL certificate for TCPS connection in Oracle wallet

No comments:

Post a Comment

ASM Administration