Configuring TCPS connection, Jan 2021 DBRU (Oracle 19.10) and resolving ORA-28864, TNS-01194

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.

?

Listener config file

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

[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]$

?

sqlnet config file

1 2 3 4 5 6 7 8 9 10 11 12

[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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[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. 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

[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. 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

[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. 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

[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: 

Oracle Net Listener Parameters in the listener.ora File

How to Restrict the Lsnrctl Utility to Communicate with the Listener Using ONLY the TCPS Protocol [2280787.1]

Listener Fails to Start Using Only TCPS Address - ORA-28864: SSL Connection Closed Gracefully [2732618.1]

How to Check Validity of Server Certificate from ORAPKI Wallet in Command Line [2674842.1]

Understanding and Investigating SSL Issues Support Pattern [1078957.1]

Database 19 Release Updates and Revisions Bugs Fixed Lists [2523220.1]

Happy Troubleshoooting...!!!