How to Resolve ORA-12505: TNS:listener does not currently know of SID given in connect

  • by

ORA-12505

Before migrating to a multitenant environment, some users used to use SID to connect to the source database. The connect identifier may look like this:

ERP2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ERP2)
      (SERVER = DEDICATED)
    )
  )

When they connect to the new database which is pluggable database (PDB), they're unable to connect to the database with the same connect identifier.

C:\Users\edchen>sqlplus hr/hr@erp2
...
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

We saw the connection failed with ORA-12505.

Solutions

ORA-12505 means that the SID you provided in the connect identifier does not match any SID registered with the listener. In fact, in the new multitenant environment, the name of SID has become a service name belonging to the migrated DB. That's why the listener refused to establish connections.

There're two types of solution that you can choose.

  1. Client Side Solution
  2. Server Side Solution

1. Client-Side

TNS Names

You can change the content of every client's connect identifier from SID into SERVICE_NAME.

ERP2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERP2)
      (SERVER = DEDICATED)
    )
  )

JDBC

For JDBC programmers, you can change the connect string from:

<IP Address>:<Port Number>:<SID>
Ex.
192.168.0.153:1521:ERP2

Into this:

//<IP Address>:<Port Number>/<Service_Name>
Ex.
//192.168.0.153:1521/ERP2

The differences are:

  • Leading by double slashes.
  • Delimited by a single slash between port number and service name.
  • The SID is replaced with the service name.

This should be able to prevent ORA-12505, and after this, I know you may see some other errors like ORA-28040 or ORA-01017 if you connect to the database from a plain old client version, e.g. Oracle 9i client.

2. Server-Side

If you prefer to solve this issue on the server side, the potential solution is to make the listener treat the SID as a service name and establish connections for users. Luckily, Oracle provides a parameter for listener to do this.

USE_SID_AS_SERVICE_<Listener Name> = ON

Single-instance

In our case, we add this feature to listener.ora.

[oracle@ora19c1 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
USE_SID_AS_SERVICE_LISTENER = ON

Then we restart the listener to make it work.

[oracle@ora19c1 ~]$ lsnrctl stop
[oracle@ora19c1 ~]$ lsnrctl start

RAC

For cluster databases, we should go for listener.ora at grid-level on all nodes.

[grid@primary01 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
USE_SID_AS_SERVICE_LISTENER = ON

Then restart listeners on all nodes.

[grid@primary01 ~]$ srvctl stop listener
[grid@primary01 ~]$ srvctl start listener
[grid@primary01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): primary02,primary01

3. Test Connection

Now we can test the connection again.

C:\Users\edchen>sqlplus hr/hr@erp2
...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show user
USER is "HR"

In practice, SID is usually used to register as a static service in the listener.

If you don't see any error pattern of your case in this post, please check: How to Resolve ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. There're more patterns of connection problems.

Leave a Reply

Your email address will not be published. Required fields are marked *