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

Last updated: 2021-11-18

I'm unable to connect to my Amazon Relational Database Service (Amazon RDS) for Oracle instance. I want to troubleshoot the error message that I'm getting.

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

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

Verify that the host name (RDS endpoint) is accurate using the NLSLOOKUP command:

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

To troubleshoot these errors, verify the syntax of the connection string by running the TNSPING command on the client side:

$ 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:

  • Be sure 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 make sure that the firewall rules are updated to allow connections to and from the RDS instance.
  • The JDBC Thin driver connection doesn't go through the Oracle Net layer. Therefore, if you're using the Oracle JDBC Thin driver, perform the equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver. For more information, see Oracle documentation for Doc ID 793415.1.
  • If you're using 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 is not required. If you're using the default parameter group, then create a custom parameter group, modify the following parameters, and 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: Turning on server-level tracing might have a few potential impacts. A large number of trace files might 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. Therefore, 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 Modifying connection properties using sqlnet.ora parameters.
  • 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. Even after the tracing parameters are removed, 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

Be sure 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 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 that you're trying to connect to the wrong SID or a database that's not running. 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 terminated:

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;

For more information, see Terminating a session.

3.    To increase the value of the sessions and processes parameters, edit the custom parameter group, and modify these 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 Modifying parameters in a DB parameter group.

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


Did this article help?


Do you need billing or technical support?