Troubleshooting: Client connection failing (cloud only)

For information on configuring client connections in cloud environments see https://kb.flashgrid.io/connect-clients

This troubleshooting article is for cloud environments only.


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. 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 db 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 Privet 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).


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. Database server is down.
  3. flashgrid-clan service is not running
  4. 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 that database servers are running.

  3. 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 minimum number of nodes are healthy and accept 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 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 11853850 - JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516

Solution:

  • Apply patch for bug 11853850 on JDBC 11g.

    or

  • Use JDBC 12C, which uses a later version of NS like OCI clients and will not encounter this problem.

Alternatively, as a workaround, connect to Local listener port instead of the SCAN listener port.