Tns listener does not currently know of sid given
Show
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 Ask your I.T. department's Oracle DBA to reconfigure the Oracle server's listener correctly.
ADR_BASE_LISTENER = E:\app\Administrator\product\11.2.0\dbhome_2\log
(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
[{"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.ORAIn 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-12505In 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.
SERVICE_NAME in TNSNAMES.ORAYou 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 JDBCFor JDBC programmers, you can change the connect string from: 192.168.0.153:1521:ERP2 Into this: // //192.168.0.153:1521/ERP2 The differences are: 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_ In our case, we add this feature to listener.ora. USE_SID_AS_SERVICE_LISTENER = ON Then we restart the listener to make it work. For cluster databases, we should go for listener.ora at grid-level on ALL nodes. USE_SID_AS_SERVICE_LISTENER = ON Then restart listeners on all nodes. Listener 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. |