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:
- Local or scan listeners are not started up properly or have been stopped.
- Node, scan, or VIP hostnames are resolved into wrong IP addresses.
- Client-side DNS server does not resolve scan or DB node hostnames into correct IP addresses.
- The Port number is not correct in the client-side connection string.
Troubleshooting:
- 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.
- 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
-
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
- 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:
- Wrong SERVICE_NAME is indicated in the connection string.
- The database or the service is down.
- Database has not registered dynamically or statically, because of wrong config.
Troubleshooting:
In the following examples, we assume that three SCAN listeners are configured.
-
Check that the SERVICE_NAME is correctly indicated in TNSNAMES.ORA or JDBC connection string.
-
Check database or service status using
crsctl status res -t
as root. If database or the service is down, start them usingsrvctl
command. - 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:
- DNS does not contain entries about the hostname that is used in the connection string.
Troubleshooting:
- 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:
- DNS server resolved hostname into wrong ip address that does not exist.
- Route to the host does not exist.
Troubleshooting:
- 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.
- 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:
- Local or SCAN listener ports are not open in Network Security Group.
- LOCAL_LISTENER parameter is not set to NodeFQDN.
- Database server is down.
- flashgrid-clan service is not running
- flashgrid-scan-proxy service is not running
Troubleshooting:
-
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.
- Check parameter
local_listener
on each database node, it must returnNodeFQDN
:
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;
-
Check that database servers are running.
- 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
- 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:
- Apply the patch to database home using
opatch apply
command -
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.