Skip to main content
FlashGrid.io

Troubleshooting: Client connection failing

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

For information on configuring client connections in cloud environments, see Connecting Database Clients

General troubleshooting recommendations

Clarifying the following information can help with troubleshooting client connections:

  • Is any other client system able to connect? If yes, what are the differences in configuration between the working and the failing clients? (e.g., client type, client version, connect string, network location, etc.)
  • Did the affected client system ever connect successfully? If yes, what were the changes at the client application, connect string, client system, database cluster, or network level?
  • Did you follow all connection testing steps as described in Connecting Database Clients?
  • What is the error code? If the error code matches any of the codes listed in this article, please check the corresponding troubleshooting steps.

Client connection fails with ORA-12541

Error message:

ORA-12541: TNS : no listener

Error details:

This happens if the listener is either down or improperly configured.

Possible causes:

  1. Local or SCAN listeners are not started up properly or have been stopped
  2. SCAN name, VIP name, and node hostname are resolved into the wrong IP addresses
  3. Client-side DNS server does not resolve SCAN name or DB node hostnames into correct IP addresses
  4. The port number is not valid in the client-side connection string

Troubleshooting:

  1. Check listener statuses on each database node:
# srvctl status listener
 Listener LISTENER is running on node(s): rac1,rac2

# srvctl status scan_listener
 SCAN listener LISTENER_SCAN1 is running on node rac1
 SCAN listener LISTENER_SCAN2 is running on node rac2
 SCAN listener LISTENER_SCAN3 is running on node rac2

LISTENER must be running on each DB node. At least one SCAN listener must be running on each node. If LISTENER or SCAN listener is not running on any DB node, start using srvctl command.

  1. On each database node, verify that /etc/hosts file does not contain entries other than localhost and hostname-ext. On each database node, check that the SCAN address and db node hostnames are resolved into the correct IP addresses from fg-pub subnet (192.168.1.x by default):
# nslookup myrac-scan.example.com
# nslookup rac1.example.com
# nslookup rac2.example.com

  1. Verify that the client-side DNS server resolves the SCAN address and db node hostnames into the correct private IP addresses on the VPC/VNet.

    To verify the resolution of database host names on the client run:

# nslookup rac1.example.com
# nslookup rac2.example.com

The SCAN address must resolve to the private IP addresses of all database nodes. To verify, run the following on the client:

# nslookup myrac-scan.example.com
  1. Verify that the port number in the connection string is correct.

If connecting through SCAN address, indicate the SCAN listener port (1521 by default). For connecting directly to a specific database node, indicate the local listener port number (1522 by default).

    5. On all database nodes, check that iptables rule for forwarding of the local listener port is loaded (replace 1522 if you have a different local listener port number)

# iptables -L -n -t nat | grep 1522

 

Client connection fails with ORA-12514

Error message:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Error details:

The error means the listener is up and running (otherwise, it would return ORA-12541), but the database service is not registered.

Possible causes:

  1. The wrong SERVICE_NAME is indicated in the connection string
  2. The database or the service is down
  3. The database has not registered dynamically or statically due to the wrong config

Troubleshooting:

In the following examples, we assume that three SCAN listeners are configured.

  1. Check that the SERVICE_NAME is correctly indicated in TNSNAMES.ORA or JDBC connection string.

  2. Check database or service status using crsctl status res -t as root. If the database or the service is down, start them using srvctl command.

  3. Check which listener returns the error by investigating listener_listener_name.log files in $CRS_BASE/diag/tnslsnr/$HOSNAME/listener name/trace/ on all nodes.

To find which logfile is active on which node, first check how listeners are distributed between the nodes:

ps -ef|grep tns

3.1 If the error is returned by the SCAN listener, then check remote_listener parameter on each db node.

SQL> show parameter remote_listener

NAME           TYPE  VALUE
---------------- -------- ------------------------------
remote_listener   string     myrac-scan.example.com:1521

myrac-scan.example.com is the SCAN address that must be resolved by DNS server into three correct IP addresses on all database nodes:

# nslookup myrac-scan.example.com
  ...
  Address: 192.168.1.23
  ...  
  Address: 192.168.1.21
  ...
  Address: 192.168.1.22

If during database restart SCAN address was returning the wrong IPs (this can happen if someone incorrectly changed entries in /etc/racdns or modified /etc/resolv.conf with wrong information on database hosts) then the database will not be registered with SCAN listeners.

Ensure that the SCAN address is correctly resolved into three IP addresses on each database node. Unlike clients, the correct IP addresses for the database nodes are the CLAN IPs (192.168.1.x).
Note that all database servers have local DNS servers installed and configured, known as dnsmasq. The local DNS server reads entries from the /etc/racdns file, which has an immutable flag set on it. After correcting entries, force the database to register with SCAN listeners by running the following command:

SQL> alter system set remote_listener='myrac-scan.example.com:1521';
SQL> alter system register;

Check that databases are registered with all SCAN listeners:

$ lsnrctl services listener_scan1
$ lsnrctl services listener_scan2
$ lsnrctl services listener_scan3

Note that not all SCAN listeners are started on all nodes, but they are distributed between the nodes, so you need to identify which SCAN is running on which node and run lsnrctl services with a specific SCAN listener name.

3.2 If the error is returned by the local listener, then check the parameter local_listener on each database node, it must return NodeFQDN:

SQL> show parameter local_listener
NAME            TYPE     VALUE
-------------- ------- -------
local_listener string    NodeFQDN

On each database node, check if NODEFQDN resolves to the correct address:

$ tnsping NodeFQDN
…
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(Host=rac1.example.com)(Port=1522))
OK (40 msec)

rac1.example.com is the node hostname where the command is executed, which must be resolved into nodes’s VPC/VNet IP address.

If the parameter is not correct, set it dynamically.

Run the command below as the fg user to set the local_listener 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. Please execute the command exactly as it is written.

Client connection fails with ORA-12545

Error message:

ORA-12545: Connect failed because target host or object does not exist

Error details:

The error means that the client was unable to resolve the database hostname indicated in the connection string.

Possible causes:

  1. DNS does not contain entries about the hostname that is used in the connection string.

Troubleshooting:

  1. On the DNS server(s) used by clients, for each database node, add two records resolving to the VPC/VNet private IP address of the node VM: 1) Hostname of the database node 2) SCAN address

Example of DNS entries 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

rac1.example.com, rac2.example.com are database node hostnames.
myrac-scan.example.com is the SCAN address. 10.100.0.1, 10.100.0.2 are VPC/VNet private IPs of each db node.

Client connection fails with ORA-12543

Error message:

ORA-12543: TNS:destination host unreachable

Error details:

The error means that the client was able to resolve the hostname that was used in the connection string, but the server with that IP does not exist in the network.

Possible causes:

  1. DNS server resolved hostname into the wrong IP address that does not exist
  2. Route to the host does not exist

Troubleshooting:

  1. Verify that client side DNS server resolves SCAN name and db node hostnames into correct IP addresses.

Example of DNS entries 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

rac1.example.com, rac2.example.com are database node hostnames.
myrac-scan.example.com is the scan hostname. 10.100.0.1, 10.100.0.2 are VPC/VNet  IPs of each db node.

  1. Ask your network administrators about possible problems with routing between the client and the database nodes. For example, they may be on different VPC/VNet networks.

Client connection fails with ORA-12521

Error message:

ORA-12521: TNS: listener does not currently know of instance requested in connect descriptor

Error details:

Cannot connect to the database. The instance string for the service string is not registered with the listener at the specified location.

Possible causes:

Client-side DNS server does not resolve SCAN name or DB node hostnames into correct IP addresses

Troubleshooting:

Verify that the client-side DNS server resolves the SCAN address and db node hostnames into the correct private IP addresses on the VPC/VNet.

Example for a 2-node cluster:

# nslookup rac1.example.com
# nslookup rac2.example.com
# nslookup myrac-scan.example.com

Client connection fails with ORA-12170

Error message:

ORA-12170: TNS:Connect timeout occurred

Error details:

The error happens after a long wait when the client is not able to establish a network connection in a specific time.

Possible causes:

  1. Local or SCAN listener ports are not open in the Network Security Group
  2. LOCAL_LISTENER parameter is not set to NodeFQDN
  3. Database server is down
  4. flashgrid-clan service is not running
  5. flashgrid-scan-proxy service is not running

Troubleshooting:

  1. Make sure that both local listener and SCAN listener ports (1521 and 1522 by default) are open in the corresponding Network Security Group settings for inbound connections from the clients.

  2. Check parameter local_listener on each database node, it must return NodeFQDN:
SQL> show parameter local_listener

NAME            TYPE     VALUE
-------------- ------- -------
local_listener string    NodeFQDN

If the parameter is not correct, set it dynamically.

Run the command below as the fg user to set the local_listener 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. Please execute the command exactly as it is written.

  1. Check that database servers are running.

  2. Check that flashgrid-clan service is running:
systemctl status flashgrid-clan

If it is stopped then start it and check the status again:

systemctl start flashgrid-clan
systemctl status flashgrid-clan
  1. Check that flashgrid-scan-proxy service is running:
systemctl status flashgrid-scan-proxy

If it is stopped then start it and check the status again:

systemctl start flashgrid-scan-proxy
systemctl status flashgrid-scan-proxy

Long wait during the initial connection

Error message(s):

N/A

Details:

The initial connection to the database hangs for a while and then succeeds. The problem happens sometimes.

Possible causes:

The same as for ORA-12170, but the minimum number of nodes is healthy and accepts the connections.

Troubleshooting:

The same as for ORA-12170. To avoid long waits during initial connection when only several nodes are down, add the following TNS parameters in database connection strings:

  • TRANSPORT_CONNECT_TIMEOUT=3
  • RETRY_COUNT=6

Example of a TNSNAMES.ORA entry:

SALESservice=(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=6)(ADDRESS=(PROTOCOL=tcp)(HOST=myrac-scan.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=salesservice)))

Example of a JDBC thin connect string:

jdbc:oracle:thin:@(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=6)(ADDRESS=(PROTOCOL=tcp)(HOST=myrac-scan.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=salesservice)))

JDBC 11g client connection fails with ORA-12520/ORA-12516

Error message(s):

ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-12516: TNS:listener could not find an instance with matching protocol stack

Error details:

The SCAN listener responds to the JDBC client with a refuse packet. JDBC 11g and below use a lower version of NS, which does not keep a redirect count.

JDBC OCI and SQLPLUS connections work fine. JDBC connections to local listener work fine, only connections to SCAN listener fail.

Possible causes:

Oracle bug 17284368: JDBC Connections Using SCAN Fail With ORA-12516 Or ORA-12520 (Doc ID 1555793.1) https://support.oracle.com/epmos/faces/DocumentDisplay?id=1555793.1

Solution:

  • Apply patch 17284368 to JDBC 11g

    or

  • Use JDBC 12C

    or

  • Connect to the local listener port instead of the SCAN listener port (note that dynamic load balancing and database services will not be available, only round-robin load balancing will be available)

To apply patch 17284368 to JDBC 11g:

  1. Apply the patch to the database home using opatch apply command

  2. Copy the patched $ORACLE_HOME/jdbc/lib/ojdbc5.jar file to the client systems

JDBC client connections experience long delays or time-out

Symptoms:

  • JDBC client takes too much time to establish a connection to the database
  • JDBC client connections may time-out and throw Connection reset error
  • Database alert log contains WARNING: inbound connection timed out (ORA-3136)

Possible cause:

Insufficient amount of entropy available via /dev/random device used by JDBC client. The client is waiting for the /dev/random device response.

Note: the problem is purely client-system-related and does not depend on any server-side settings.

Solution:

  • Use client VM of a larger size and have some load running on the VM
  • As a workaround, add Java parameter -Djava.security.egd=file:/dev/../dev/urandom on the client. This will prevent blocking even when no sufficient entropy is available on the client system. Note that this workaround may have security implications.