ORA-12505
ORA-12505 means that the SID you provided in the connect identifier does not match any SID registered with the listener. Basically, SID means $ORACLE_SID, which is the instance name by default.
SID in TNSNAMES.ORA
In some upgrade or migration cases, some users used to use SID to connect to the old database. The connect identifier in TNSNAMES.ORA 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 may be a pluggable database (PDB), they're unable to connect to the database with the same connect identifier. This is because the new database adopts SERVICE_NAME instead of SID as the entry point of connection.
C:\Users\scott>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 to ORA-12505
In fact, in a multitenant environment, the used name of SID has become a service name belonging to the migrated DB or PDB. SID is no longer used. That's why the listener refused to establish connections, because there's no such SID registered with the listener.
There're two types of solution that you can choose.
1. Use SERVICE_NAME on Client Side
SERVICE_NAME in TNSNAMES.ORA
You can change the content of every client's connect identifier from SID into SERVICE_NAME in TNSNAMES.ORA.
ERP2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ERP2)
(SERVER = DEDICATED)
)
)
But where we can find TNSNAMES.ORA file? You may take a look.
Without TNSNAMES.ORA
At times, you may not able to modify TNSNAMES.ORA. In such moment, you can still connect to the database without TNSNAMES.ORA.
SERVICE_NAME in JDBC
For JDBC programmers, you can change the connect string from:
Ex.
192.168.0.153:1521:ERP2
Into this:
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. Use SID as SERVICE_NAME on 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 SERVICE_NAME and establish connections for users. Luckily, Oracle provides a parameter USE_SID_AS_SERVICE_LISTENER for listener to do this.
For Single-instance DB
In our case, we add this feature to listener.ora.
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
USE_SID_AS_SERVICE_LISTENER = ON
Then we restart the listener to make it work.
[oracle@test ~]$ lsnrctl stop
[oracle@test ~]$ lsnrctl start
For RAC DB
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
Restrictions
Please note that, such backward compatibility has some restrictions. For a cluster system, USE_SID_AS_SERVICE_LISTENER is only used for connecting databases directly through local listeners.
Which means, SCAN listener won't translate SID into SERVICE_NAME, so don't use SCAN IP, use public or virtual IP of specific node.
In other words, if you insist on using SCAN IP to connect to the database, you should change SID into SERVICE_NAME in your connection string.
3. Test Connection
Now we can test the connection again.
C:\Users\scott>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.