Introduction

This blog covers the following topics:

• SqlNet connectivity challenges within Oracle Cloud
• How to enable SqlNet across VCNs within a region using Dynamic Routing Gateways
• How to enable SqlNet across VCNs within a region using Local Peering Gateways
• How to enable SqlNet across regions using Dynamic Routing Gateways

Part 2 of this blog covers the following:

• SqlNet latency across OCI regions
• SqlNet latency within a region
• Edge Caching for lower latency

Part 3 of this blogs covers the following:

• How the SQL resultset size affects SqlNet latency
• How the SQL statement complexity affects the SqlNet latency
• How the OCI compute shape [small VM vs bare metal] affects the SqlNet latency

SqlNet connectivity challenges within Oracle Cloud

SqlNet (Oracle Net Services) is a network protocol to enable Oracle SQL clients to communicate with the Oracle database server via the Oracle Net listener.  The Oracle ‘client’ may be thick or thin:

• Thin clients include JDBC thin, managed ODP.Net and python-oracledb
• Thick clients include any SQL driver that uses the Oracle Client or the Oracle Instant Client, eg
  • JDBC thick, OCI, thick mode python-oracledb, node-oracledb, Sqlplus, SqlLoader etc

SqlNet requires network connectivity to work. 

Network connectivity is enabled by default within a Virtual Cloud Network [VCN] in Oracle Cloud:

• You will need to allow SqlNet access [port 1521/TCP, or 2484/TCPS] via a VCN security list or security group
• A VCN may span OCI availability domains within a region

Cross VCN Connectivity

To enable fine grained management and increased security, you can have multiple VCNs within an OCI region.  By design, there is no default network connectivity between VCNs in a region. 

This means that by default, your Oracle client will not be able to communicate with your Oracle database if they are in different VCNs in a region.

Cross Region Connectivity

VCNs are local to an OCI region.  By design, there is no default network connectivity between regions.

This means that by default, your Oracle client will not be able to communicate with your Oracle database if they are in different regions.

To enable network connectivity between VCNs and between regions, you need to explicitly configure gateways.

Note

I used step-by-step screen shots for the first example [configuring a Dynamic Routing Gateway between VCNs within a region], but I only showed the new/different steps for the next two examples as most of it is repetition of exactly the same steps.

You need to remove some of the steps of each of the examples to get the next example to work. For example you cannot have duplicate rules in the routing table.

Software used in this blog

An Oracle 19c pluggable database created via the Oracle Base Database service was used for this blog in the Phoenix region:

• A VM.Standard.E4.Flex virtual machine with 64 OCPU, 1 TB of RAM and 40 Gbps network bandwidth was used
  • You do not need a large VM to test SqlNet connectivity for an Oracle database on Oracle Cloud.  I choose to use a large VM as:
    • I did not want to skimp on the network bandwidth
    • I wanted a large SGA for the more complex SQL used in part 3 of this blog
• This database uses Virtual Cloud Network vnc1 with a default CIDR of 10.0.0.0/16
• The DB system version was 19.18.0.0.0
• The database characterset was AL32UTF8 
• The SqlNet port was 1521
• Sqlnet used TCP rather than TCPS

The PDB long connect string from the OCI console was:

The TNS service name that I used for remote SqlNet latency tests was simpler:

pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.sub03160015150.vnc1.oraclevcn.com)
    )
  )

• Due to the long distances [eg nearly 9000 miles from Hyderabad to Phoenix], I assumed that non default SqlNet timeouts and retries would be needed
• For all of the 18 remote regions tested, the default Oracle 19c Net Services timeouts and retry counts worked without change
• This was a pleasant surprise as in the past I had needed increased connection timeouts and retry counts for less optimal networks than Oracle Cloud

The Oracle clients were small Virtual Machines:

• VM.Standard.E4.Flex
• 1 OCPU
• 16 GM RAM
• 1 Gbps network bandwidth

In part 3 of this blog, I used fast bare metal machines:

• BM.Standard.E4.128
• 128 OCPU
• 2048 GB RAM
• 100 Gbps network bandwidth

Non-overlapping CIDRs

Oracle Cloud uses Classes Inter-Domain Routing [CIDR] rather than the older class A, B or C mechanism for defining ranges of IP addresses.

To enable routing rules for gateways, OCI requires that the source and destination IP address ranges are distinct.

You can use various CIDR calculators to determine CIDR address ranges. For example

In the above picture:

• CIDR 10.0.0.0/16 covers the address range of 10.0.0.0 to 10.0.255.255
• CIDR 11.0.0.0/16 covers the address range of 11.0.0.0 to 11.0.255.255
• These are two distinct sets of IP address ranges as there are no IP address which can exist in both ranges
• This means that these two CIDRs are non-overlapping

How to enable SqlNet across VCNs in a region using Dynamic Routing Gateways

To enable SqlNet connectivity between VCNs in a region, you can use a Dynamic Routing Gateway.

• The above picture shows the high level steps to enable network connectivity which is required for SqlNet connectivity within a region using a dynamic routing gateway
• Only the three steps highlghted in red in the above picture are specific to cross VCN DRGs
• The detail of those high level steps follows

Dynamic Routing Gateways [DRGs] are very flexible and can be used for many different scenarios.  Using a DRG to enable network connectivity within a region is the simplest use case for a DRG.  

In the OCI documentation, the steps to configure network connectivity between VCNs via a DRG is called Peering VCNs in the same region through a DRG.

In these examples I did not explicitly set the OCI IAM policies for dynamic routing gateways or local peering gateways as I am an administrator for my OCI tenancy.

Determine the VCN used by your Oracle database in Oracle Cloud

• My Oracle 19c database used a Virtual Cloud Network called vnc1 [yes, I really meant to call it vcn1 …]
• vnc1 uses the default CIDR 10.0.0.0/16

Determine the VCN used by your Oracle client [you need non overlapping CIDRs in each VCN]

• I needed to create a new Virtual Cloud Network which I called vcn2
• I choose to use 192.168.0.0/16 for the CIDR
• I used the VCN Wizard to create vcn2 with this specific CIDR

• Click on the Start VCN Wizard button in the OCI Networking page

• Click on the Start VCN Wizard button

• I used the following information:
  • vcn2 for the Virtual Cloud Network name
  • 192.168.0.0/16 for the CIDR for the VCN
  • 192.168.0.0/24 for the CIDR block for the public subnet
  • 192.168.1.0/24 for the CIDR block for the private subnet
• Click Next
• Click Create on the summary screen

• This should result in the above status

• This should result in two VCNs, each with different CIDRs

Determine the host IP address and TCP port used by the SqlNet listener for your Oracle database

The SqlNet listener for my Orace 19c database used :

• hostname 10.0.0.32 [the private IP address of the node]
• Port 1521

Determine the TNS servicename to connect to your Oracle database

The TNS service name that I used in my tnsnames.ora file was

pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.sub03160015150.vnc1.oraclevcn.com)
    )
  )

Configure the security list for the VCN used by your Oracle database

Oracle Cloud uses a secure by default policy for Virtual Cloud Networks.

I used OCI Network Security Lists to enable SqlNet traffic for the TCP protocol on port 1521 from within vnc1.

• I clicked on the Security Lists link

• I then clicked on the Default Security List for vnc1 link

• I clicked on Add Ingress Rules

• I added an ingress rule for SqlNet within vnc1
  • The CIDR was 10.0.0.0/16 [for traffic within vnc1] 
  • The IP protocol was TCP
  • The destination port was 1521
  • The comment was SqlNet from vnc1
• I then clicked Add Ingress Rule

• Now VCN vnc1 allows SSH traffic on port 22 from any network, and SqlNet traffic on port 1521 only from the vnc1 network

Use SqlPlus to verify that you can connect to your Oracle database within the same VCN

My OCI Virtual Machine [vm1] used Oracle Linux 8.7.  I got the following error when trying to use SqlPlus: 

By default the libnsl Linux networking library is not installed on Oracle Linux 8.7.  

I did the following to install the missing libnsl Linux library:

With the libnsl library installed, SqlPlus now worked:

There is no point trying SqlNet connectivity from different Virtual Cloud Networks or different regions if you cannot connect within your local OCI VCN:

• Now that the TCP port 1521 is open for connections from vnc1, you can test SqlNet connectivity via SqlPlus
• I had created a small Virtual Machine called vm1 which used the Virtual Cloud Network vnc1
• I connected to my Oracle 19c PDB as schema user OE which I had created earlier
• $TNS_ADMIN pointed to my tnsnames.ora file which defined the pdb1 TNS service name
• The same TNS service name [defined in my tnsnames.ora file] will be able to be used by Oracle clients on different VCNs and different regions

Create a shared Dynamic Routing Gateway [DRG]

• Next I created a Dynamic Routing Gateway which is shared by both VCNs

• From the OCI Networking page, click on the Customer connectivity link
• Dynamic Routing Gateways are independent of Virtual Cloud Networks

• Click on the Dynamic routing gateway link

• Click on the Create Dynamic Routing Gateway button

• Name the DRG, eg drg_phoenix
• Click on the Create Dynamic Routing Gateway button

Attach the first VCN to the DRG

• Click on the Create Virtual Cloud Network Attachment button
• This associates the drg_phoenix with the vnc1

• I named the attachment attach_vnc1 [there wlll be two attachment for this use case]
• Select the vnc1 Virtual Cloud Network from the drop down
• Click on the Create Virtual Cloud Network Attachment button

• The status should now be attached

Attach the second VCN to the DRG

Repeat the process to attach the second VCN to the DRG.

• Now both vnc1 and vnc2 are attached to the Dynamic Routing Gateway in the Phoenix region

Configure the first VCN’s routing rule to send traffic to the second VCNs CIDR 

From the vnc1 Virtual Cloud Network page:

• Click on the Route Tables link

• Click on the default route table for vnc1 link

• Click on the Add Route Rules button

• The target type should be Dynamic Routing Gateway
• The destination type should be CIDR block
• The CIDR block should be 192.168.0.0/16 [ie vcn2]
• The comment should state what the routing rules does

Configure the second VCN’s routing rule to send traffic to the first VCNs CIDR 

To enable SqlNet requests to be routed to my Oracle database in vnc1: 

• Use a target type of Dynamic Routing Gateway
• Use a destination type of CIDR block
• Use a destinatin CIDR block of 10.0.0.0/16 [ie vnc1] 
• Describe the routing rule in the description

Configure the security list of the first VCN to accept SqlNet traffic for ingress from the CIDR of the second VCN

From the networking page of vnc1

• Click on the Security Lists link

• Click on the Default Security List for vnc1 link

• Click on the Add Ingress Rules button to enable access from vcn2
• Note how we had previously enabled Sqlnet access from within vnc1

• Enable SqlNet access from vcn2
• Click on the Add Ingress Rules button

• The result should be that SqlNet access from both vnc1 [10.0.0.0/16] and vcn2 [192.168.0.0] is enabled

Configure the security list of the second VCN to accept SqlNet traffic for ingress from the CIDR of the first VCN

In vcn2, you now need to enable SqlNet responses from vnc1.

• After adding the Ingress Rule for SqlNet from vnc1 your default secrity list for the second VCN should look like the above picture

Now test SqlPlus connectivity from vcn2 to your Oracle database in vnc1

• I had created a Virtual Machine called vm2 which uses vcn2
• $TNS_ADMIN pointed to my tnsnames.ora file which defined the pdb1 TNS service name

• After a bunch of configuration, SqlPlus can now use SqlNet across VCNs to talk you the Oracle database
• Now your applications (eg JDBC, Python, Node.js, C#, Go and Rust) can use this SqlNet connectivity across VCNs

How to enable SqlNet across VCNs in a region using Local Peering Gateways

The steps to use Local Peering Gatways are very similar to using a Dynamic Routing Gateway for routing between VCNs within a region.

The only differences are:

• Creating Local Peering Gateways [LPG] for each VCN
• Establishing a connection between the LPGs

The other steps are exactly the same as for using Dynamic Routing Gateway between two VCNs within a region.

Add a Local Peering Gateway to the first VCN

In the Networking page for vnc1, add a local peering gateway.

• Click on the Local Peering Gateway link
• Unlike Dynamic Routing Gateways, Local Peering Gateways are an artifact of a Virtual Cloud Network

• Click on the Create Local Peering Gateway button

• Name the local peering gateway, eg lpg_vnc1
• Click the Create Local Peering Gateway button

• The Local Peering Gateway for vnc1 now exists, but it is not yet connected 

Add a Local Peering Gateway to the second VCN

In the Networking page for vnc2, add a local peering gateway.

• The Local Peering Gateway for vcn2 now exists, but it is not yet connected 

Establish a connection between the two Local Peering Gateways

Connections between Local Peering Gateways are bi-directional, so you can establish the connection from either VCN.

From the Network page for vcn2, establish the connection by clicking on the ‘three dots’ for the lpg_vcn2.

• Click the Establish Peering Connection option

• Choose Virtual Cloud Network vnc1 to connect to 
• Choose lpg_vnc1 as the Unpeered Gateway to connect to
• Click the Establish Peering Connection button

• The state for the Local Peering Gateways on vcn2 should now be peered
• The state for the Local Peering Gateways on vnc1 should also be peered 

• Now that the local peering connection has been established, the routing rules and security rules still need to be configured

• The routing rule for vnc1 needs to have a target type of Local Peering Gateway

• The routing rule for vcn2 needs to have a target type of Local Peering Gateway

The configuration for the security lists in vnc1 and vcn2 are the same as for using Dynamic Routing Gateways.

How to enable SqlNet across OCI Regions

The goal is to have a SqlPlus client in the San Jose region be able to communicate with my Oracle database in the Phoenix region.

Cross region connectivity is similar to using Local Peering Gateways:

• Cross region connectivity requires a dynamic routing gateway for each region
• The dynamic routing gateways need to be attached to a VCN in their region
• You need to establish a remote peering connection between the DRGs in each region
• You need to create routing rules to steer traffic between regions
• You need to create security lists to allow traffic from remote regions

The OCI documentation for configuring cross region dynamic routing gateways is here.

Create a VCN in the San Jose region with non overlapping CIDRs

My Oracle database on the Phoenix region uses the default CIDR of 10.0.0.0/16 in vnc1.

The VCN in San Jose needs a CIDR block for vcn3 which does not clash with 10.0.0.0/16.

I created a new VCN [vcn3] with a CIDR block of 11.0.0.0/16 for the San Jose region.

Create a Dynamic Routing Gateway for the San Jose Region

Create a new dynamic routing gateway in the San Jose region.

Once the DRG is created in that region, you need to attach it your VCN, eg vcn3.

• Click on the Create Virtual Cloud Network Attachment button

In the San Jose Region, attach the vcn3 VCN to the DRG drg_sanjose

• Name the DRG attachment, eg attach_vcn3
• Choose the VCN to attach to, eg vcn3 
• Click on Create Virtual Cloud Network Attachment

• The state of the attachment for drg_sanjose should now be attached

Create a Remote Peering Connection for drg_sanjose

• Click on the Remote Peering Connections Attachments link

• Clink on the Create Remote Peering Connection button

• Name the remote peering connection, eg rpc_phoenix as a DRG can be connected to many different regions
• Clink on the Create Remote Peering Connection button

• This should result in a state of the remote peering connection rpc_phoneix as Attached + New (not peeered)

Create a Dynamic Routing Gateway for Phoenix

Create a dynamic routing gateway called drg_phoenix.

• Click on the Create Virtual Cloud Network Attachment for drg_phoenix

• Name the attachment, eg attach_phoenix

• Choose the network where your Oracle database exists, eg vnc1

• Click on the Create Virtual Cloud Network Attachment button

Create a Remote Peering Connection for drg_phoenix

• Click on the Create Remote Peering Connection button

• Name the remote peering connection, eg rpc_sanjose
  • There can be many remote peering connections, so refering to the destination is useful
• Click on the Create Remote Peering Connection button

• The state of the remote peering connection should Attached + New (not peered)
• Click on the rpc_sanjose link

Establish the Remote Peering Connection

The Oracle Cloud unique identifier [OCID] for the target remote peering connection is needed by the requesting remote peering connection.

You need to copy the OCID of rpc_sanjose from the phoenix region for the rpc_phoenix in the sanjose region.

• Click on the OCID Copy link for the rpc_sanjose remote peering connection
  • You soon need to use this OCID to establish the remote peering connection

Now, from the San Jose region, establish the remote peering connection.

• Click on the Establish Connection button for rcp_phoenix in the San Jose Region

• Choose the target region, eg Phoenix
• Paste the OCID for the phoenix remote peering connection [that you earlier copied]
• Click on the Establish Connection button

• The state of the rcp_phoenix should now be peered
• Remote peering connections are bi-directional, so the state of rpc_sanjose should also be peered
• You now have a network connection between the Phoenix and SanJose regions

Configure routing for the San Jose Region

From the San Jose Networking page for vcn3

• Click on the Route Tables link

• Click on the link for default route table for vnc3

• Choose a Target Type of Dynamic Routing Gateway
• Use a destination CIDR block of 10.0.0.0/16 [ie vnc1 on Phoenix]
• Describe the routing rule, eg route to Phoenix vnc1

• The routing rule from San Jose to Phoenix is highlighted

Configure routing for the Phoenix Region

• The route from Phoenix to San Jose [vcn3] is highlighted

Confgure Security Lists for Phoenix

• SqlNet on port 1521 is now enabled from 11.0.0.0/16 [San Jose]
• Sqlnet was already enabled from 10.0.0.0/16 [Phoenix, vnc1]

Confgure Security Lists for San Jose

• SqlNet on port 1521 is now enabled from 10.0.0.0/16 [ie Phoenix, vnc1]

Part 2 of this blog investigates the SqlNet latency both with regions and across regions.

Summary

• Oracle SqlNet needs network connectivity between clients and the Oracle database
• Network connectivity is enabled by default within a Virtual Cloud Network
• Network connectivity is not enabled by default between Virtual Cloud Networks
• Network connectivity is not enabled by default between regions
• Oracle Local Peering Gateways and Dynamic Routing Gateways enable network connectivity between Virtual Cloud Networks
• Oracle Dynamic Routing Gateways enable network connectivity between regions
• Local Peering Gateways and Dynamical Routing Gateways enable SqlNet connectivity within Oracle Cloud

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.