Looking for something? Try here..

Tuesday, February 13, 2024

Connection to Oracle Autonomous Database

This article explains how to connect to an Oracle Autonomous database using SQL developer, SQL plus or other tools.

When we create an Oracle ADB on OCI, we just can't connect directly to the database even though the Network ACL is disabled. The reason is mTLS authentication is set to Required by default. 

Let us see how to connect to the ADB using SQL developer first and then SQL plus.

If you have downloaded SQL developer for connecting to your database on cloud, it's super easy to connect.

Method 1: Using mTLS  

Mutual TLS (mTLS) is a 2-way handshake between a client and a server, and a more secure way of connection compared to TLS.

Step 1: Download the wallet (This is required for mTLS)

Go to your Autonomous database details page => Click on Database connection => Click on Download wallet



Provide the password and click on Download. This is your wallet password and not the password for the database Admin user or the database application user you are trying to connect. 

Step 2: Once you have this credentials wallet zip, Open SQL Developer and edit the connection properties to connect to the Autonomous database as below



Make sure we have the connection type selected as Cloud Wallet and provide the downloaded zip file under configuration file. 
While selecting Service, we have different default service to choose from which differs on how the connection works. I have selected atp01_high of the below 5 available.
  • atp01_high
  • atp01_low
  • atp01_medium
  • atp01_tp
  • atp01_tpurgent
Click on the Test button to check the connectivity and we now have a Successful connection. 

How do we connect to using SQL Plus? 

To make a SQL Plus or any Oracle Call Interface (OCI), JDBC connection to the Autonomous database, we need the client software to be installed in the server from where we are trying to connect. 
Since we need the wallet location to be specified, we should have sqlnet.ora file and tnsnames.ora file

I'll use my existing on-prem Linux server to attempt a connection without the wallet file location specified in the sqlnet.ora but with only the tnsnames.ora edited with the connection details of the ADB. 
[oracle@linux-8 db-sample-schemas-main]$ cd order_entry/
[oracle@linux-8 order_entry]$ sqlplus admin/xxxxx@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)
(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9751_atp01_high.adb.oraclecloud.com))
(security=(ssl_server_dn_match=yes)))'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 7 03:56:14 2024
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

ERROR:
ORA-12506: TNS:listener rejected connection based on service ACL filtering


Enter user-name:
 
So where do I get the connection description information from? 
When I click on the Database connection button on the Database details page, I'll get the connection strings for all the 5 services or the downloaded zip will include the tnsnames.ora file containing this information. 

As you see when we didn't provide the wallet location, we are not able to connect with error ORA-12506. 

There are 2 things you can do to fix this. 
Easy way is to unzip the contents of the downloaded zip file to a directory on the server and define the TNS_ADMIN environment variable value to this directory and attempt the connection. Make sure to make changes in the sqlnet.ora file to denote the wallet location
[oracle@linux-8 db-sample-schemas-main]$ ls -lrt
total 28
-rwxrwx---. 1 root vboxsf 21984 Jan  9 21:17 Wallet_ATP01.zip
drwxrwx---. 1 root vboxsf  4096 Feb  7 04:00 db-sample-schemas-main
[oracle@linux-8 db-sample-schemas-main]$ unzip Wallet_ATP01.zip -d ./Wallet_ATP01
Archive:  Wallet_ATP01.zip
  inflating: ./Wallet_ATP01/ewallet.pem
  inflating: ./Wallet_ATP01/README
  inflating: ./Wallet_ATP01/cwallet.sso
  inflating: ./Wallet_ATP01/tnsnames.ora
  inflating: ./Wallet_ATP01/truststore.jks
  inflating: ./Wallet_ATP01/ojdbc.properties
  inflating: ./Wallet_ATP01/sqlnet.ora
  inflating: ./Wallet_ATP01/ewallet.p12
  inflating: ./Wallet_ATP01/keystore.jks
[oracle@linux-8 db-sample-schemas-main]$ cd Wallet_ATP01/
[oracle@linux-8 Wallet_ATP01]$ vi sqlnet.ora
[oracle@linux-8 Wallet_ATP01]$ more sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes
[oracle@linux-8 Wallet_ATP01]$ vi sqlnet.ora
[oracle@linux-8 Wallet_ATP01]$ more sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/media/sf_Oracle/software/db-sample-schemas-main/Wallet_ATP01")))
SSL_SERVER_DN_MATCH=yes
[oracle@linux-8 Wallet_ATP01]$ export TNS_ADMIN=/media/sf_Oracle/software/db-sample-schemas-main/Wallet_ATP01
[oracle@linux-8 Wallet_ATP01]$ sqlplus admin/xxxxx@atp01_high

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 10 05:02:32 2024
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sat Feb 10 2024 04:11:47 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
feky1pod2

SQL>

 
I just used atp01_high as my connect identifier as they are already defined in the tnsnames.ora file thats bundled in the zip. 

If you already have a working TNS_ADMIN (which defaults to $ORACLE_HOME/network/admin), all you have to do is to only mention the below 2 parameters in the existing sqlnet.ora file and add the connection string for the ADB connection in the tnsnames.ora file. 
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/media/sf_Oracle/software/db-sample-schemas-main/Wallet_ATP01")))
SSL_SERVER_DN_MATCH=yes

Method 2: Using TLS connection.

Now let's see how to connect to the Autonomous database without the wallet files using a TLS connection. 

We can connect to the autonomous database without the wallet files as well. If we wan't to allow TLS connection to connect to Autonomous database, we need to enable Access Control List (ACL). To do that, we can click on edit adjacent to the Disabled status of Access Control List

We can configure different type of ACLs such as with specific IPs, CIDR block, VCNs or VCN OCID and the combination of these can be used in real environments. 

For our testing purpose, I'm just going to add my ip address to show the ACL and TLS connection. 

Just click on Add my IP address and click Save. This will add our IP address to the ACL and the status of the ACL will turn as Enabled. 
Now click on Edit adjacent to mTLS: Required

Uncheck the Require mTLS authentication check box to disable mTLS


Now, we have enabled TLS authentication to the Autonomous database but only the connections from my IP is allowed since we have also enabled ACLs. 

Now, as we changed the configuration, lets try to connect to the ADB without the wallet files to the database using SQL Developer first and then using SQL Plus. 

The configuration will look like the below. 

We have now selected Custom JDBC instead of Cloud wallet since we are not using TLS connection for the database. 
For the JDBC URL, the format should be 
jdbc:oracle:thin:@<connection string for the service intended> 

In my example, I'm trying to connect to atp01_high service and hence the JDBC URL will be as below. 
jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)
(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
(connect_data=(service_name=g34efa58d1e9750_atp01_high.adb.oraclecloud.com))
(security=(ssl_server_dn_match=yes)))
Click on Test to see the connection status is Success. 

If we need to connect using SQL Plus, we can save this connect string in the tnsnames.ora file and connect directly. 
[oracle@linux-8 admin]$ pwd
/oracle/db/db19/network/admin
[oracle@linux-8 admin]$ ls -lrt sqlnet.ora tnsnames.ora
-rw-r-----. 1 oracle oinstall 1629 Feb  7 04:33 tnsnames.ora
-rw-r--r--. 1 oracle oinstall  619 Feb 10 05:16 sqlnet.ora
[oracle@linux-8 admin]$ rm sqlnet.ora
[oracle@linux-8 admin]$ tail tnsnames.ora
rmancat =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux-8.selvapc.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rmancat.selvapc.com)
    )
  )

atp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9751_atp01_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
[oracle@linux-8 admin]$
[oracle@linux-8 admin]$ tnsping atp

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 10:18:22

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9750_atp01_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
OK (380 msec)
[oracle@linux-8 admin]$
[oracle@linux-8 admin]$ sqlplus admin/xxxxx@atp

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 10:18:37 2024
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 12 2024 11:49:25 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0

SQL>
  
In the above demo, I just removed my tnsnames.ora file meaning we don't pass any location for the wallet files which is confirmed by blank o/p for Used parameter files of the tnsping. 
We are now able to connect to the autonomous database using TLS but only from my specific IP address. 
Any connection attempt from other addresses will not be allowed as per the ACL. 

Requirements of TLS connection: 

SQL Developer - version 18.2 or later
SQL Plus - Oracle instant client/database client 19.14 or later and 21.5 or later (if you need it on Windows)

References: 

Connect to Autonomous Database Using Oracle Database Tools

Happy Connecting...!!!

Sunday, January 28, 2024

ASMFD - Troubleshooting ASMCMD-9513

 I was converting my Oracle ASM disk management from udev rules to ASM Filter Driver (ASMFD) and encountered the below errors which might happen during the conversion operation and this post might help you fix the issue as quickly as possible. 


You can read more on Oracle ASMFD, configuration and migrating to ASMFD using this 19c documentation

1. Wrong/incorrect afd_diskstring value

[root@ol819-rac1 ~]#
[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_label asmdisk1 /dev/sdb1 --migrate
No devices to be labeled.
ASMCMD-9513: ASM disk label set operation failed.
Since my disks are already pointing to /dev/oracleasm due to the udev rules, ASMFD config file picked up the afd_diskstring parameter picked up this value by default and hence the issue. Edit the /etc/oracleafd.conf config file and retry the operation

[root@ol819-rac1 ~]# cat /etc/oracleafd.conf
afd_diskstring='/dev/oracleasm/*'
[root@ol819-rac1 ~]# vi /etc/oracleafd.conf
[root@ol819-rac1 ~]# cat /etc/oracleafd.conf
afd_diskstring='/dev/sd*'
[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_label asm-disk1 /dev/sdb1 --migrate
Invalid label string : asm-disk1
ASMCMD-9513: ASM disk label set operation failed.

[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_label asmdisk1 /dev/sdb1 --migrate
[root@ol819-rac1 ~]#
[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
ASMDISK1                    ENABLED   /dev/sdb1
[root@ol819-rac1 ~]# ls -al /dev/oracleafd/disks
total 4
drwxrwxr-x. 2 oracle dba      60 Jan 18 10:28 .
drwxrwx---. 3 oracle dba      80 Jan 18 09:45 ..
-rw-rw-r--. 1 oracle oinstall 10 Jan 18 10:28 ASMDISK1
[root@ol819-rac1 ~]# 
Note: You can see we get the Invalid label string when provided the disk name as asm-disk1. hyphen (-) is not allowed in the disk name. If needed we can use underscore (_). I left it without any special characters. 

2. Retry label after wrong path

I used a wrong path value during label operation by providing the pointer instead of actual disk path and tried to relabel again causing ASM-9513 
We can use the kfod command to check the label details of the ASM disk. 

[root@ol819-rac1 ~]# asmcmd afd_label asmdisk1 /dev/sdb1 --migrate
label name ASMDISK1 is already in use
ASMCMD-9513: ASM disk label set operation failed.

[root@ol819-rac1 ~]# kfod OP=DISKS status=TRUE disks=ASM label=TRUE dscvgroup=TRUE
--------------------------------------------------------------------------------
 Disk          Size Header    Path                                    Disk Group         Site      Failgroup           Disk-Label User     Group
================================================================================
   1:      25599 MB MEMBER    /dev/oracleasm/asm-disk1                 DATA                 #              #             ASMDISK1 oracle   dba
[root@ol819-rac1 ~]# 
In the above you can see the path is /dev/oacleasm/asm-disk1 with the Disk-label set to ASMDISK1. This might work now as I still have udev rules in place so the pointer path would work. Once I remove the udev rules, this path will not exist and would cause issues in identifying disks and my cluster won't come up as the disk contains the voting disk as well
[root@ol819-rac1 ~]# asmcmd afd_unlabel asmdisk1
disk AFD:ASMDISK1 is already provisioned for ASM
No devices to be unlabeled.
ASMCMD-9514: ASM disk label clear operation failed.

[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# afdtool -delete '/dev/oracleasm/asm-disk1'
disk /dev/oracleasm/asm-disk1 is already provisioned for ASM
No devices to be unlabeled.
[root@ol819-rac1 ~]# afdtool -delete -f '/dev/oracleasm/asm-disk1'
Unlabeled device ASMDISK1
[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# kfod OP=DISKS status=TRUE disks=ASM label=TRUE dscvgroup=TRUE
--------------------------------------------------------------------------------
 Disk          Size Header    Path                                    Disk Group         Site      Failgroup           Disk-Label User     Group
================================================================================
   1:      25599 MB MEMBER    /dev/oracleasm/asm-disk1                 DATA                 #              #                    # oracle   dba
[root@ol819-rac1 ~]# 
afd_unlabel fails with ASMCMD-9514 stating disk already provisioned for ASM. This means afd_label command not only labels the disk but also includes it in the list of disks managed by AFD. So AFD does not allow altering the label for a disk that is currently being managed by AFD.

We can use afdtool command to delete the label in this case. 
After unlabeling the disk, we can see the kfod command has null value for Disk-label for the disk. 

We can then use afd_label command to label the disk using the proper path as shown in this post above.

References:
 
ASMCMD AFD_LABEL --RENAME FAILS (Doc ID 2884565.1)