Multicloud

How to connect to a VM DB System via Oracle Database Service for Azure (ODSA)

Introduction

After creating a VM DB System via Oracle Database Service for Azure (ODSA) in the previous blog post, we are going to see how to connect to the database host via SSH and to the CDB and PDB via SQL*Net from an Azure virtual machine, where usually your application will be running.

The Environment

  • VM DB System created in OCI via ODSA with private IP 10.33.1.162.
  • VM running in Azure with private IP 10.22.0.4 created in the same VNet connected to OCI via the ODSA Multicloud Link. Operating System is Oracle Linux 8.5.

Inbound and Outbound Traffic

In the Azure portal (not the ODSA portal), from the Networking setting page of your application VM:

Add inbound and outbound security rules to allow traffic to flow to and from the OCI VCN with CIDR 10.33.0.0/16:

In the OCI Console, if you navigate to the VCN created automatically by ODSA, you will see that there is a Network Security Group (NSG) created:

and already contains the Ingress and Egress rules for TCP port 22 (SSH) and TCP port 1521 (SQL*Net):

Connecting to the Database Host

From your Azure application VM, you can connect via SSH to the database host and then to the database:

[azureuser@app01 ~]$ ssh opc@10.33.0.162
Last login: Fri Feb  3 12:50:23 2023 from 10.22.0.4
[opc@dbhost01 ~]$ sudo su - oracle
Last login: Fri Feb  3 16:22:24 UTC 2023
[oracle@dbhost01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 3 16:22:54 2023
Version 19.17.0.0.0

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


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
SQL>

This could be useful for administrative purposes. However, the application on the Azure VM will need to connect directly to the database in OCI via SQL*Net.

Oracle Client

Download the Oracle Instant Client for Linux version 21.9. You will need the Basic Package (ZIP) and the SQL*Plus Package (ZIP). Upload both zip files to the Azure VM and unzip those in the same folder, e.g., /home/azureuser/instantclient_21_9/. This folder will contain the sqlplus binary and all libraries needed.

Change the mode of the sqlplus binary to be executable:

[azureuser@app01 ~]$ chmod 744 /home/azureuser/instantclient_21_9/sqlplus

Include the following variables in your .bashrc file, so they are set every time you log in to Azure VM:

[azureuser@app01 ~]$ vi .bashrc

export PATH="$PATH:/home/azureuser/instantclient_21_9/"
export LD_LIBRARY_PATH=/home/azureuser/instantclient_21_9/
export TNS_ADMIN=/home/azureuser/instantclient_21_9/network/admin/

Connection String

From the Base Database overview page in the ODSA portal, click on the name of your database:

From the Connect settings page, click on the three dots icon on the right and copy the connection string.

For the CDB:

For the PDB:

Add both connection strings to the tnsnames.ora file on the Azure VM:

[azureuser@app01 ~]$ vi instantclient_21_9/network/admin/tnsnames.ora

cdb01 = (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.33.0.162)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=cdb01_ams1hc.odsp099742.cvcn.oraclevcn.com)))

pdb01 = (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.33.0.162)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdb01.odsp099742.cvcn.oraclevcn.com)))

Connect to the Database

Now everything is ready to connect from the Azure VM to the OCI database directly via SQL*Net over the ODSA Multicloud link while using the private IP of the database host.

Connect to the CDB:

[azureuser@app01 ~]$  sqlplus SYS@cdb01 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 3 16:40:42 2023
Version 21.9.0.0.0

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

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
SQL>

Connect to the PDB:

[azureuser@app01 ~]$  sqlplus SYS@pdb01 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 3 16:41:53 2023
Version 21.9.0.0.0

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

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB01                          READ WRITE NO
SQL>

Conclusion

Oracle Database Service for Azure (ODSA) provides a private multicloud network link between your Azure VNet and OCI VCN. The ingress and egress rules in OCI are already set up to allow SQL*Net and SSH traffic to connect to the database and the database host. Make sure you have the corresponding inbound and outbound security rules on the Azure site and you are ready to go.

Further Reading

Would you like to get notified when the next post is published?