How do I troubleshoot common connection errors for my Amazon RDS for Oracle instance?

Short description

Before troubleshooting the connection error, do the following:

• Check the state of your Amazon RDS for Oracle DB instance. If the instance is in any state other than available, storage optimization, or backing-up, then you can't connect to the instance.
• Verify that you can connect to the database instance over the database port. By default, Oracle uses port 1521.

telnet example-endpoint 1521

If you can't establish a connection using telnet, then troubleshoot the issue based on the instructions provided in How do I resolve problems when connecting to my Amazon RDS DB instance?

Resolution

Check your connection

Verify that your connection string is accurate. A typical connect descriptor looks similar to the following:

$ sqlplus admin/xxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myexampledb.xxxx.us-east-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))

With Easy Connect, the connection string looks similar to the following:

$ sqlplus 'admin@myexampledb.xxxx.us-east-1.rds.amazonaws.com:1521/ORCL'

Use the following troubleshooting options based on the error message that you get:

ORA-01017: invalid username/password; logon denied

Check the user credentials provided in the connection string. Be sure that these credentials are accurate.

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

Use the following NLSLOOKUP command to verify that the hostname (RDS endpoint) is accurate:

nslookup example-database.xxxx.us-east-1.rds.amazonaws.com 
Server: xx.xx.xx.xx 
Address: xx.xx.xx.xx#53

ORA-12170: TNS:Connect timeout occurred

-or-

ERROR: Connection reset by peer

The most common reasons for these errors include the following:

• There are issues with the network, connection establishment, or firewall.
• Communication with the client failed to complete within the allotted time interval.
• The database is down.
• The sqlnet.ora parameter is invalid.

Run the TNSPING command on the client side to verify the syntax of the connection string:

$ tnsping example-connection-string

If the syntax of the connection string is accurate, then the output looks similar to the following:

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
Output :
ORCL=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= example-database.xxxx.us-east-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
$ tnsping ORCL
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2021 09:16:04
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=example-database.xxxx.us-east-1.rds.amazonaws.com)(PORT=1521))
CONNECT_DATA=(SERVICE_NAME=ORCL)))
OK (20 msec)

If there is an error with the syntax, then the output looks similar to the following:

$ tnsping ORCL
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2021 09:23:47
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name

You can also run the TRCROUTE command to check the syntax of the connection string:

$ trcroute example-connection-string

If the syntax of the connection string is accurate, then the output looks similar to the following:

Trace Route Utility for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2021 09:16:25
Copyright (c) 1995, 2014, Oracle. All rights reserved.
Route of TrcRoute:
------------------
Node: Client            Time and address of entry into node:
-------------------------------------------------------------
16-NOV-2021 09:16:25 ADDRESS= PROTOCOL=TCP  HOST=example-database.xxxx.us-east-1.rds.amazonaws.com  PORT=1521
Node: Server            Time and address of entry into node:
-------------------------------------------------------------
16-NOV-2021 14:16:25 ADDRESS= PROTOCOL=TCP  HOST=example-database.xxxx.us-east-1.rds.amazonaws.com PORT=1521

If there is an error with the syntax, then the output looks similar to the following:

$ trcroute ORCL
Trace Route Utility for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2021 09:25:06
Copyright (c) 1995, 2014, Oracle.  All rights reserved.
TNS-03505: Failed to resolve name

To further troubleshoot these errors, do the following:

• Confirm that the security group for your RDS DB instance allows the appropriate incoming traffic to your database.

• If the application connects from an on-premises network, then confirm that the firewall rules allow connections to and from the instance.

• The JDBC Thin driver connection doesn't go through the Oracle Net layer. So if you use the Oracle JDBC Thin driver, perform the equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver. For more information, see How to perform the equivalent of SQL*Net client tracing with Oracle JDBC thin driver releases prior to 11.2 (Doc ID 793415.1) on the Oracle website.

• If you use the JDBC Thick or OCI client, then turn on sqlnet tracing on the server side. To turn on tracing on the server side, modify the custom parameter group attached to the instance. In this case, a reboot isn't required. If you use the default parameter group, then create a custom parameter group and modify the following parameters. Then, modify the instance to use the newly created parameter group. In this case, a reboot is required.

  sqlnetora.trace_level_server=16

  sqlnetora.diag_adr_enabled=OFF

  Important: When you turn on server-level tracing, a large number of trace files can be generated quickly. In rare cases, the listener or even the database might need to be restarted to stop tracing after the troubleshooting is completed. The process also requires close monitoring. It's a best practice to turn on tracing during non-peak business hours or under isolated conditions when no other user is connecting to the database. Also, be sure that the server has sufficient disk space available. The parameter values mentioned in this article are examples. You can modify these values according to your use case. For more information, see Connecting to your RDS for Oracle DB instance.

• Turn on sqlnet tracing on the client side. Then, reproduce the error and capture the details in the trace files. Create a backup of the sqlnet.ora file, and then modify the following parameters in the file:

  TRACE_LEVEL_CLIENT = 16

  TRACE_FILE_CLIENT = client.trc

  TRACE_DIRECTORY_CLIENT = /var/log/sqlnet

  TRACE_TIMESTAMP_CLIENT = ON

  TRACE_UNIQUE_CLIENT = ON

  DIAG_ADR_ENABLED= OFF

  To turn off tracing, remove the tracing parameters from the sqlnet.ora file. The tracing for the already connected sessions on the server side is stopped only after the client session is closed.

• If you get the error because of a slow network, then configure the following parameters on the client side and server side. This allows more time to establish the connection:

  Client side:

  SQLNET.INBOUND_CONNECT_TIMEOUT

  SQLNET.SEND_TIMEOUT

  SQLNET.RECV_TIMEOUT

  Server side:

  sqlnetora.sqlnet.inbound_connect_timeout

  sqlnetora.sqlnet.send_timeout

  sqlnetora.sqlnet.recv_timeout

  For example, if you use the value 600 for all parameters, then the connection time is set to 10 minutes.

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Confirm that the SID provided in the connection string matches the value of DBNAME that's under the Configuration tab on the Amazon RDS console. By default, the SID and DBNAME for an RDS for Oracle instance is ORCL.

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Be sure to use either the SID or SERVICE_NAME clause under the CONNECT_DATA section in the connection string. The value of SERVICE_NAME provided in the connection string is the same as the value of DBNAME that's under the Configuration tab on the Amazon RDS console. By default, the SERVICE_NAME value for an RDS for Oracle instance is ORCL.

ORA-12538: TNS:no such protocol adapter

Be sure that the PROTOCOL section of your connect descriptor is accurate. By default, the value of PROTOCOL is TCP.

ORA-12560: TNS:protocol adaptor error

This error indicates a connection to the wrong SID or a database that's not active. Check the status of the RDS DB instance. Run the tnsping command to test the syntax and connectivity of the connection string:

$ tnsping example-connection-string

ORA-00018: maximum number of sessions exceeded

This error indicates that too many sessions are connected to the database. To troubleshoot this error, do the following:

1. If you are connected to the database, then run the following query to find the number of sessions connected:

   SELECT COUNT(*) TOTAL FROM v$session;

   Run the following query to view the current utilization, maximum utilization, and the configured limits for the sessions and processes parameters in the RDS instance:

   SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
   FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ( 'sessions', 'processes');

2. End the unwanted sessions so that the other sessions can connect to the database.

   Run the following query to view the SID and serial number of individual sessions to be stopped:

   col username format a15
   col osuser format a15
   col program format a40
   col machine format a40
   SELECT s.osuser,
   s.sid,
   s.serial#,
   p.spid "RDS OS PID",
   s.program,
   s.machine,
   s.process "CLIENT OS PID",
   s.STATUS,
   s.SQL_ID,
   s.CURRENT_QUEUE_DURATION
   FROM v$session s, v$process p
   WHERE s.paddr = p.addr AND s.machine LIKE '%<client machine>%'
   AND s.STATUS='ACTIVE';

   Note: Modify the WHERE clause according to your use case.

   Run the following command to end unwanted sessions in your RDS for Oracle instance:

   begin
       rdsadmin.rdsadmin_util.disconnect(
           sid => example-sid,
           serial => example-serial_number);
   end;

3. To increase the value of the sessions and processes parameters, edit the custom parameter group and modify the parameters. The sessions parameter is not defined in the parameter group and uses the default values for the engine.

   The default number of sessions in Oracle 19c is calculated as follows:

   Number of sessions = (1.5 * number of processes) + 22.

   The default value for the processes parameter is defined as LEAST({DBInstanceClassMemory/9868951}, 20000). The number of processes is calculated as the total memory (MB) in host /10 or 20,000, whichever is less.

   For more information on modifying a parameter group, see Working with parameter groups.

   Because these parameters are static, you must reboot your instance after you update these parameters.

Related information

Why can't I connect to my Amazon RDS for Oracle database instance?