Note: This article describes an alternative method of connecting clients. The recommedned method is described here.
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.
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 Cloud Provisioning then the alias entry is created in
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
- When manually installing database software. If you did not select automatic installation of database software in the FlashGrid Cloud Provisioning tool then the
<database_home>/network/admin/tnsnames.orafile with the corresponding entries must be created manually.
- When same tnsnames.ora file must be used across all database nodes. By default the
tnsnames.orafile 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 Cloud Provisioning tool (1522 by default).
Configuring the LOCAL_LISTENER parameter is required for accessing database services via the SCAN Listener. It must be set separately for each database.
To set the parameter, 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.
On the DNS server(s) used by clients, for each database node add two records resolving to the VPC Private IP address of the node VM:
- Hostname of the database node
- SCAN address
To see exact host names and IP addresses for your cluster, run
Example for a 2-node cluster:
rac1.example.com 10.100.0.1 rac2.example.com 10.100.0.2 myrac-scan.example.com 10.100.0.1 myrac-scan.example.com 10.100.0.2
IMPORTANT! The hostnames and domain names in the DNS records for the database servers 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.
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.
To test client connectivity via the SCAN address
- 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
- Connect to all database instances via Local Listeners (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
Confirm that SCAN Listeners are reachable via the SCAN address (run three times to cycle through all nodes)
$ tnsping myrac-scan.example.com:1521
Connect to a database service via SCAN Listener (replace exampleservice with the corresponding service name)
The following parameters must be specified in the client connect string:
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:
Example of a JDBC thin connect string:
For more details about the connect string parameters see https://docs.oracle.com/database/121/NETRF/tnsnames.htm
For additional information on troubleshooting client connections see Troubleshooting: Client connection failing (cloud only)