In one of our DB environment, we use TCPS only connection defined in the listener configuration
which used to run properly without any issues. The listener.ora and sqlnet.ora files are as below. [oracle@abc]$ more listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 10.195.60.243)(PORT = 2484))
#(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.195.60.243)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ABC)
(ORACLE_HOME = /oracle/product/base/19.3.0.0)
(SID_NAME = ABC)
)
)
LOGGING_LISTENER = OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
SECURE_CONTROL_LISTENER = TCPS
SECURE_REGISTER_LISTENER = TCPS
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /oracle/product/base/server_wallet)
)
)
admin_restrictions_LISTENER = ON
SSL_CLIENT_AUTHENTICATION = FALSE
ADR_BASE=/home/oracle/trace
TRACE_LEVEL_LISTENER=10
TRACE_DIRECTORY_LISTENER=/home/oracle/trace
TRACE_FILE_LISTENER=listener.log
[oracle@abc]$ [oracle@abc]$ more sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=0
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /oracle/product/base/server_wallet)
)
)
SSL_CLIENT_AUTHENTICATION = FALSE
[oracle@abc]$ You can take a look here to understand what the 2 highlighted parameters would do. Basically, we are trying to secure the listener connections and also restrict client connections to TCPS protocol and this configuration was working fine until we patch the database from Oracle version 19.9 to 19.10. Upon starting the listener post 19.10 (Jan 21 RU) patching, we are getting the below error
[oracle@abc]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-APR-2021 11:43:36 Copyright (c) 1991, 2020, Oracle. All rights reserved. Starting /oracle/product/base/19.3.0.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /oracle/product/base/19.3.0.0/network/admin/listener.ora Trace information written to /oracle/product/base/diag/tnslsnr/server1/listener/trace/ora_3647_140266612784320.trc Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.195.60.243)(PORT=2484))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=10.195.60.243)(PORT=2484))) ORA-28864: SSL connection closed gracefully TNS-12560: TNS:protocol adapter error TNS-00542: SSL Handshake failed Linux Error: 28864: Unknown error 28864 [oracle@abc]$Oracle support note Doc ID 2280787.1 provides the steps to properly configure and restrict the Lsnrctl Utility to Communicate with the Listener Using ONLY the TCPS Protocol which was exactly followed in our environment.
We even recreated the wallet as below so as to be sure that there are no mistakes in the process followed.
[oracle@abc]$ orapki wallet create -wallet /oracle/product/base/server_wallet -auto_login -pwd ******** Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved. Operation is successfully completed. [oracle@abc]$ orapki wallet add -wallet /oracle/product/base/server_wallet -dn "CN=server" -keysize 512 -self_signed -validity 3650 -pwd ******** Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved. Operation is successfully completed. [oracle@abc]$ orapki wallet add -wallet /oracle/product/base/server_wallet -trusted_cert -cert /temp/devid_abc_com.crt Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved. Cannot modify auto-login (sso) wallet Enter wallet password: Operation is successfully completed. [oracle@abc]$ [oracle@abc]$ cd server_wallet [oracle@abc]$ ls -lrt total 12 -rw-------. 1 oracle oinstall 0 Apr 9 11:12 ewallet.p12.lck -rw-------. 1 oracle oinstall 0 Apr 9 11:12 cwallet.sso.lck -rwxr-x---. 1 oracle oinstall 2326 Apr 9 11:12 devid_abc_com.crt -rw-------. 1 oracle oinstall 3440 Apr 9 11:13 ewallet.p12 -rw-------. 1 oracle oinstall 3485 Apr 9 11:13 cwallet.sso [oracle@abc]$ orapki wallet display -wallet /oracle/product/base/server_wallet Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=server Trusted Certificates: Subject: CN=server Subject: CN=devid.abc.com,O=xxxx International Inc.,L=Morris Plains,ST=New Jersey,C=US [oracle@abc]$Even after recreating the wallet and listener.ora and sqlnet.ora files are properly configured, we still hit the same error while starting the listener.
Things got interesting when the patches were rolled back to 19.9, the listener started without issues and application was able to connect via TCPS connectivity.
So we raised an SR suspecting the issue to be a bug with Jan 21 RU and the ticket is work in progress. Oracle support works on their own pace and our investigation continues.
I came across Oracle support note Doc ID 2732618.1 when I was trying to keep only the TCPS connection in the listener and start to drill down the issue. The note suggested to check the validity of the certificate in the wallet which might have expired. You might have seen in our wallet configuration, we also added 3rd party certificate from client for their secure connection. We can't suspect the client certificate here as the listener itself is not starting in the first place and the server wallet certificate is also self signed just now with validity of 3650 days. So what could actually be wrong?
Oracle support note Doc ID 2674842.1 explains the procedure on how to check the validity of the certificate which is as show below.
[oracle@abc]$ orapki wallet export -wallet /oracle/product/base/server_wallet -dn 'CN=server' -cert wallet.cert
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
[oracle@abc]$
[oracle@abc]$ $ORACLE_HOME/jdk/jre/bin/keytool -printcert -file wallet.cert
Owner: CN=server
Issuer: CN=server
Serial number: aa005d7cd120d6588e8540f95914c3f
Valid from: Fri Apr 09 11:12:22 UTC 2021 until: Mon Apr 07 11:12:22 UTC 2031
Certificate fingerprints:
SHA1: 25:AB:2D:6D:39:A6:84:2E:8D:14:9A:83:66:F5:E6:5E:11:FC:B2:8F
SHA256: 83:19:46:95:7D:EE:35:08:0F:86:A8:77:AE:F7:F8:E1:27:F4:75:33:5D:F0:26:9A:3C:D4:36:BE:51:49:E4:D7
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 512-bit RSA key (disabled)
Version: 1
Warning:
The certificate uses a 512-bit RSA key which is considered a security risk and is disabled.
[oracle@abc]$
Now, we are able to find the root cause of the issue. The certificate key is disabled and also a warning is printed stating the same. The solution now is easy, change the keysize to 1024 or 2048 while signing the certificate if you don't want to get into this trouble again.
[oracle@abc]$ orapki wallet remove -wallet /oracle/product/base/server_wallet -dn 'CN=server' Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved. Cannot modify auto-login (sso) wallet Enter wallet password: Operation is successfully completed. [oracle@abc]$ [oracle@abc]$ orapki wallet add -wallet /oracle/product/base/server_wallet -dn "CN=server" -keysize 1024 -self_signed -validity 3650 -pwd ******** Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved. Operation is successfully completed. [oracle@abc]$ [oracle@abc]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-APR-2021 12:06:51 Copyright (c) 1991, 2020, Oracle. All rights reserved. Starting /oracle/product/base/19.3.0.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /oracle/product/base/19.3.0.0/network/admin/listener.ora Trace information written to /oracle/product/base/diag/tnslsnr/server1/listener/trace/ora_10336_140143471860928.trc Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.195.60.243)(PORT=2484))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.195.60.243)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=10.195.60.243)(PORT=2484))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 09-APR-2021 12:06:51 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level admin Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/product/base/19.3.0.0/network/admin/listener.ora Listener Trace File /oracle/product/base/diag/tnslsnr/server1/listener/trace/ora_10336_140143471860928.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.195.60.243)(PORT=2484))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.195.60.243)(PORT=1521))) Services Summary... Service "ABC" has 1 instance(s). Instance "ABC", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@abc]$I have checked in support notes and also in the known issues, bugs fixed or read me file of the Jan 21 DBRU in which I couldn't find any mention of this restriction that keysize with 512 bits are not supported. All the documents that I have referred in the Oracle support site mentions the keysize as 512 bit which needs to be updated if it is not supported w.r.to a specific version and above patching.
So the take away from this issue and resolution is that when you are planning for Oracle database 19.10 patching, check whether the certificates are signed with 1024 bits or higher.
Update:
Oracle support resolved the ticket after we shared with them that we used a keysize 1024 instead of 512. Still don't know where is this limitation documented :)
References:
Happy Troubleshoooting...!!!
Very useful note sir.
ReplyDeleteThanks Pradeep. Glad it helped!!
DeleteInteresting, what Oracle Support still doing within "work" or SR which you have opened with them regarding described in that blog post issue (if you have not already asked to close SR as "not a bug"). Do no "help" them!! Let's see, how long it will take for them to solve such issue, which they normally should resolve almost *automatically*, just via validating logs and config files, which you have uploaded for them during creation of SR. Because as far as I understand, their internal SR ticketing system contains lots of automatic validation mechanisms, they check customer's configs and settings for all most obvious misconfigurations without human intervention, just upon upload archives during SR creation, various rules applied and validated. Of course, maybe there are some delay with adding new rules, like in your case - when new version 19.10 introduced new restriction for SSL Certificate key length, appropriate checks should be also added by Oracle Support teams too. Maybe we have here "broken communication" between Dev and Support Teams..
ReplyDeleteAnd second - I think, if you would enable Listener's tracing - you should see something more explainable nor just "ORA-28864: SSL connection closed gracefully" during start of listener process.
Hi,
DeleteI have provided what I have done with the issue to resolve it and they took it as resolution step. They also referred me Doc ID 1226654.1 before closing the SR which again states we can use keysize of 512 bit which triggered issue (don't know why they provided it).
The ticket being rotated with multiple engineers, sometime we feel exhausted working with Oracle support as each and every person will ask the same question/logs and the ticket will never move forward.
I don't want to waste my time on such tickets and this is just one like that :)
Yes, listener tracing would have helped but I figured out by checking validity of certificate and so my investigation didn't needed tracing effort.
Thanks for reading and providing inputs :)