ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Many a times, When user try connecting to the database though an application, user gets the error as below.

ORA-12505: TNS: listener does not currently know of SID given in connect descriptor

Error: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

This is very comman issue, and there are many reasons for this error.


Cause: User was unable to connect to database through connect descriptor that we use to connect to database from remote server or from application.

Solution: 

Since listener is running,try to connect the database manually through connect descriptor that is usually stored in tnsnames.ora file.  Always make sure that connect descriptor has correct database name, hostname and port name.

Example:

sqlplus usrname/pwd@PRODDB

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 20 04:39:03 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
Descriptor

Ø  Then check your database name in connect descriptor available at tnsnames.ora file.

Ø  If that is correct then check your services in listener to find whether requested database is being listened by the listener. You can use below command to check the same.

$lsnrctl services listener_name

Ø  If database is found, then wait for few seconds and check the connection.

Ø  If database is not found in the services, then manually register your database to the listener by using below command.

SQL> alter system register;

Check after few seconds, you will be able to connect to the database using connect descriptor.

If still not working, check the connection string of the database a provide the connection string to the user, to make sure the same string is used by users.

Also, check the mount point of the host. Sometimes the iNodes get occupied on the host, which make the listener state as hung and will not allow users to connect. If so, perform necessary housekeeping on the respective mount-points and check the listener Status again.

Happy Learning  :) 

Keep Sharing . . .

See Also :   

Slowness in Database 

Reading AWR Report

-

Interview Tips  

-

Houekeeping Tasks