Server produces or throws following error message when starting: The connect descriptor received by the Oracle Database listener specified an Oracle System ID SID for an instance [usually a database instance] that either has not yet dynamically registered with the listener or has not been statically configured for the listener.
This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.
I.T. administrator launches "Controller Configuration" and creates new database connection to Oracle database schema. Administrator clicks 'Test Connection' but receives an error message.
There are many possible causes for the "ORA-12505: TNS:listener ..." error, such as:
- Scenario #1 - Incorrect configuration of Oracle database server
- Specifically, the LISTENER on the Oracle database server was not configured correctly.
- Scenario #2 - Incorrect configuration of Oracle database client [installed on Controller application server]
- For more details, see separate IBM Technote #1447103.
Scenario #1 - Incorrect configuration of Oracle database server Ask your I.T. department's Oracle DBA to reconfigure the Oracle server's listener correctly.
- Real-life example:
In one real-life example, the customer wanted to connect to a database called "CCR11G". In this case the solution was to modify the "listener.ora" file from:
- lsnrctl stop
- lsnrctl start
- LISTENER = [DESCRIPTION_LIST = [DESCRIPTION = [ADDRESS = [PROTOCOL = TCP][HOST = MYSERVERNAME][PORT = 1521]] ] [DESCRIPTION = [ADDRESS = [PROTOCOL = IPC][KEY = EXTPROC1521]] ] ]
ADR_BASE_LISTENER = E:\app\Administrator\product\11.2.0\dbhome_2\log
- SID_LIST_LISTENER =
[SID_LIST =
[SID_DESC =
[SID_NAME = PLSExtProc]
[ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_2]
[PROGRAM = extproc]
]
[SID_DESC=
[GLOBAL_DBNAME=ccr11g]
[ORACLE_HOME=E:\app\Administrator\product\11.2.0\dbhome_2]
[SID_NAME=ccr11g]
]
] LISTENER = [DESCRIPTION_LIST = [DESCRIPTION = [ADDRESS = [PROTOCOL = IPC][KEY = EXTPROC1522]] [ADDRESS = [PROTOCOL = TCP][HOST = MYSERVERNAME][PORT = 1521]] ] ]
ADR_BASE_LISTENER = E:\app\Administrator\product\11.2.0\dbhome_2\log
Afterwards, restart the listener by typing:
Scenario #2 - Incorrect configuration of Oracle database client
- See separate IBM Technote #1447103.
[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
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:
[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\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 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.
- Use SERVICE_NAME on Client Side
- Use SID as SERVICE_NAME on Server-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] ]]
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.
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.
USE_SID_AS_SERVICE_ = ON
For Single-instance DB
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
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 enabledListener LISTENER is running on node[s]: primary02,primary01
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 we don't need to restart SCAN listeners above, because we know it's useless.
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\edchen>sqlplus hr/hr@erp2 ... Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> show userUSER 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.