Sunday, April 18, 2021

Adding additional disk to guest OS on VirtualBox

 If you are running out of space on the guest OS that you have created under the VirtualBox, the following steps can be followed to add a new disk. I have provided the steps in GUI which can also be done using VBoxManage createmedium command. 

Step 1: Go to File - Click on Virtual Media Manager


Step 2: Click on Create


Step 3: Choose VMDK and click on Next


To learn about different Hard disk file type, click here

Step 4: Choose Dynamically allocated and click Next


Step 5: Provide the image file name, size and click on Create


Since we have provided option to Dynamically allocated, the disk image will be created and the initial size will be very low. I have provided 30 GB of size and the initial disk image created is of size 3.81 MB which will grow dynamically as we start using/writing on it. 

Click on close once the disk is created successfully. 

Once the disk image is created, we can then attach the storage to the OS that we are in need of space as below. The same operation can also be done using command line with VBoxManage storageattach commands

Step 1: Select the VM you want to add the disk and click on Storage


Step 2: Click on Adds Hard disk button under Controller: SATA



Step 3: Choose the hard disk you would like to add and click on Choose


You can now see that the hard disk is added to the VM. Click on OK to close the dialog box and start the VM to use. 



Happy learning...!!!

Sunday, April 11, 2021

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.

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