Skip to main content
FlashGrid.io

Connecting Database Clients (cloud only)

  • Updated
  • Products: FlashGrid Cluster on Azure
  • Products: FlashGrid Cluster on GCP
  • Products: FlashGrid Cluster on AWS
 

Note: The old version of this article describing how to connect clients using a single SCAN address is located here.

Overview

Clients (e.g. app servers) connecting to the database need connectivity to SCAN listeners and local listeners. However, in the FlashGrid cluster architecture the listeners are configured on the fg-pub CLAN virtual network, which is isolated from the rest of the VPC/VNet where the clients are located. FlashGrid SCAN Proxy service running on each database node of the cluster facilitates connectivity between the clients on the VPC/VNet and the SCAN listeners on the fg-pub subnet. Port forwarding facilitates connectivity between the clients on the VPC/VNet and the local listeners on the fg-pub subnet.

Because of the two separate IP address spaces, host name resolution must be configured with split-DNS. Within the cluster, DNSMASQ service running on each node of the cluster provides host name resolution to the 192.168.1.x address space. For the clients outside of the cluster, DNS servers must be configured with the database host names resolving to the corresponding VPC/VNet IP addresses.

The diagram below shows an example of the process of establishing a client connection. The initial connection requests are sent to the database nodes in round-robin fashion.

Configuring Alias for Local Listener

Use of an alias for the local listener simplifies use of a split horizon DNS resolution and allows setting same LOCAL_LISTENER parameter for all database instances.

In most cases the default configuration of the local listener alias is sufficient and does not require any changes. If automatic installation of the database software was selected in FlashGrid cluster Launcher then the alias entry is created in <database_home>/network/admin/tnsnames.ora

Example of a default alias entry on rac1 node:

DONOTDELETE,NodeFQDN = (ADDRESS = (PROTOCOL= TCP)(Host=rac1.example.com)(Port=1522))

In certain cases listed below these alias entries must be modified or added manually.

  • When default domain is configured in sqlnet.ora. If you configure default domain in sqlnet.ora then you also need to replace NodeFQDN with NodeFQDN.domainname in the <database_home>/network/admin/tnsnames.ora file.
  • When manually installing database software. If you did not select automatic installation of database software in the FlashGrid cluster Launcher tool then the <database_home>/network/admin/tnsnames.ora file with the corresponding entries must be created manually.
  • When same tnsnames.ora file must be used across all database nodes. By default the tnsnames.ora file is different on each node and contains same alias name NodeFQDN, but a different address corresponding to the host. This allows setting the LOCAL_LISTENER parameter to the same value on all database instances. If you need to use same tnsnames.ora file on all nodes then it must contain a separate alias entry for each node, e.g. NodeFQDN1 and NodeFQDN2 instead of a single NodeFQDN, and the LOCAL_LISTENER parameter of each database instance must be configured with the alias corresponding to the host where the database instance is running.

Note that in all cases a DNS address of the host must be used in the Host attribute of the alias. Do not replace the DNS address with an IP address. Also, the Port parameter must match the local listener port number selected in the FlashGrid cluster Launcher tool (1522 by default).

Setting LOCAL_LISTENER parameter for each database

Configuring the LOCAL_LISTENER parameter is required for accessing database services via the SCAN Listener. It must be set separately for each database.

Run the command below as the fg user to set the parameter for all databases registered with clusterware:

$ flashgrid-set-local-listener-parameter-for-all-dbs

Older FlashGrid Cluster versions before 21.08 do not have the above command. If the above command is not available on your system, then log in to each database and run the following command:

SQL> ALTER SYSTEM SET LOCAL_LISTENER="NodeFQDN" scope=both sid='*';

IMPORTANT! Do not replace the "NodeFQDN" parameter with a hostname. Run the command as is.

Make sure instance-specific values are not set by running SHOW SPPARAMETER command:

SQL> SHOW SPPARAMETER LOCAL_LISTENER

SID   NAME            TYPE    VALUE
----- --------------  ------- ---------
*     local_listener  string  NodeFQDN

Your output should be identical to the above, otherwise clear any additional values using ALTER SYSTEM RESET command:

SQL> ALTER SYSTEM RESET LOCAL_LISTENER SID='orcl1';
SQL> ALTER SYSTEM RESET LOCAL_LISTENER SID='orcl2';

In case of multitenant architecture, make sure PDB-specific values are not set by running the command below:

SQL> SELECT CON_ID, NAME, VALUE FROM GV$SYSTEM_PARAMETER WHERE CON_ID <> 0 AND NAME='local_listener';

no rows selected

Your output should be identical to the above, otherwise clear any additional values using ALTER SYSTEM RESET command logging into each PDB that has the parameter set (do not run this command in the root):

SQL> ALTER SYSTEM RESET LOCAL_LISTENER SCOPE=BOTH SID='*';

Adding DNS Records

On the DNS server(s) used by clients, for each database node add a record resolving to the VPC / VNet Private IP address of the node VM. In a test environment with no DNS server the entries can be added to /etc/hosts on the clients instead of the DNS server.

To see exact host names and IP addresses for your cluster, run cat /etc/motd.

Example for a 2-node cluster:

rac1.example.com 10.100.0.1
rac2.example.com 10.100.0.2

IMPORTANT! The hostnames and domain names in the DNS records must exactly match the hostnames as reported by the hostname command on the database servers.

IMPORTANT! Do not modify the /etc/resolv.conf files on the database servers. Within the cluster the DNS names are resolved to IP addresses on the fg-pub CLAN subnet 192.168.1.x via local DNSMASQ service.

Opening listener ports

The FlashGrid architecture requires separate port numbers for the SCAN Listener (default: 1521) and for the Local Listener (default: 1522). The default ports can be changed when creating a cluster configuration. Make sure that both ports are open in the corresponding Network Security Group settings for inbound connections from the clients.

Testing client connectivity

Perform the following steps on a client system:

  1. Confirm that Local Listeners are reachable via the hostnames (testing with IP address is not enough)

    $ tnsping rac1.example.com:1522
$ tnsping rac2.example.com:1522

  2. Confirm that SCAN Listeners are reachable via the hostnames (testing with IP address is not enough)

    $ tnsping rac1.example.com:1521
$ tnsping rac2.example.com:1521

  3. Connect to each database instance through its Local Listener (replace orcl with the corresponding database name)

    sqlplus "sys/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.example.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=orcl)))" as sysdba
sqlplus "sys/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.example.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=orcl)))" as sysdba
  4. Connect through the SCAN Listeners on each node (replace orcl with the corresponding database name) 
    sqlplus "sys/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))" as sysdba
sqlplus "sys/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))" as sysdba

    Note that when connecting through a SCAN Listener the connection may be established to a database instance on a different node.

Configuring client connect string

A client connect string must have addresses of all database nodes listed. This ensures that the client attempts to connect to all database nodes (using round-robin) even if one node is down.

In most cases using the port number of the SCAN Listener is recommended.

Additionally, the following parameters must be specified:

  • TRANSPORT_CONNECT_TIMEOUT=3: The time, in seconds, for a client to establish a TCP connection to the database server. The default value is 60 seconds. It must be changed to avoid a long wait in case one of the database servers is down.
  • RETRY_COUNT=6: The number of connection attempts before the connection is terminated.

Example of a TNSNAMES.ORA entry:

EXAMPLEservice=
     (DESCRIPTION=
           (TRANSPORT_CONNECT_TIMEOUT=3) (RETRY_COUNT=6)
           (ADDRESS= (PROTOCOL=tcp) (HOST=rac1.example.com) (PORT=1521))
           (ADDRESS= (PROTOCOL=tcp) (HOST=rac2.example.com) (PORT=1521))
           (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = exampleservice)
           )
      )

Example of a JDBC thin connect string:

jdbc:oracle:thin:@(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=6)(ADDRESS= (PROTOCOL=tcp)(HOST=rac1.example.com) (PORT=1521))(ADDRESS= (PROTOCOL=tcp) (HOST=rac2.example.com) (PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=exampleservice)))

For more details about the connect string parameters see https://docs.oracle.com/database/121/NETRF/tnsnames.htm

Troubleshooting

For additional information on troubleshooting client connections see Troubleshooting: Client connection failing (cloud only)