Skip to main content
FlashGrid.io

Troubleshooting: Client connection failing (cloud only)

  • 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 (cloud only)

This troubleshooting article is for cloud environments only.

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 (cloud only)?
  • 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. Node, scan, or VIP hostnames are resolved into wrong IP addresses.
  3. Client-side DNS server does not resolve scan or DB node hostnames into correct IP addresses.
  4. The Port number is not correct 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 of the DB nodes then start it 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 tab node check that the SCAN address and db node hostnames are resolved into correct ip addresses on 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 client side DNS server resolves SCAN address and db node hostnames into correct Private IP addresses on the VPC/VNet.

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

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

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

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

If connecting through SCAN address then 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 different local listener port number)

$ sudo 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 that the listener is up and running (otherwise it would return ORA-12541), but database service is not registered.

Possible causes:

  1. Wrong SERVICE_NAME is indicated in the connection string.
  2. The database or the service is down.
  3. Database has not registered dynamically or statically, because of 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 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 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 wrong IPs (this can happen if someone accidentally changed entries in DNS or modified /etc/resolv.conf with wrong information) then database will not be registered with SCAN listeners.
In that case, correct the DNS entries to make the SCAN address resolve into three correct IP addresses on each db node. Force 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 local listener then check parameter local_listener on each database node, it must return NodeFQDN:

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

Check that NODEFQDN is resolved into 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, which must be resolved into nodes’s VPC ip address.

If parameter is not correct, set it dynamically:

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

Do the same steps on all other database nodes.

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 not able to resolve the database hostname that was 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 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 connection string, but server with that ip does not exist in the network.

Possible causes:

  1. DNS server resolved hostname into 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 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 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-12170

Error message:

ORA-12170: TNS:Connect timeout occurred

Error details:

Error happens after a long wait, when client was not able to establish a network connection in a specific time.

Possible causes:

  1. Local or SCAN listener ports are not open in 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 parameter is not correct, set it dynamically:

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

  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 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.