20.6.17

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


How to solve ORA-12505, Listener does not currently know of SID  


This is very common error and really it will suck your blood if you are not right path. I will try to explain you about this error.

As you can see this error is showing itself that listener is up and running but not serving  the SID that it is meant for. And yes 1st you need to check where you are facing this error. Is it on client side or on server side.

So what is the SID this listener looking for?

SID is name for your database or you can say service identifier, That is help to to identify the the service like we use PROD for production UAT for testing server. SID of Database should be identical to avoid the confusion. It's length is 8 character only that means you can not extend it more then 8 character.

Now how listener works ?

As we all know its like a car, That drop us from airport to hotel, hahah sorry I am not good in examples. So let me explain technically, whenever you try to make connection from a client machine to the DB server rather than connecting directly oracle have this utility called listener. As you can understand by name it self, yes yes it is going to listen your reqst and then pass that to DB. wow great right.?


Now what if you are taking a taxi to some hotel and that hotel does not exist? Same happens with this error. Taxi driver don't know where you are going so it will through you the error :-

"ORA-12505, TNS:Listener does not currently know of SID given in connect descriptor"


How to solve this "ORA-12505, TNS:listener does not currently" ?



It's very easy. Tell the taxi driver name of hotel that really exist. Fare enough right?

OK Tips for troubleshooting.

We all know that listner.ora and tnsnames.ora  are two files that are involved in this case. and location of these files are $ORACLE_HOME\network\admin

Now check your tnsnames.ora that SID is mentioned is correct or not. Like for my TEST DB is should be like below :-
TEST_TNS = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*******)(PORT=0000)))(CONNECT_DATA=(SERVICE_NAME=TEST)(INSTANCE_NAME=TEST)))


You can change the format also but i like this way. and yes PORT can't be 0000. ;)

Now you know SID service name TEST is listening at 0000 port number.

If I have set accurate entries than you shouldn't face any issue. So Please check these entries. Also check listner.ora file too. If there is mismatch in entries. Than you going to face same issue

That will solve your problem.
Also check this link :- ora-12154-error-in-oracle-11g-and-12c

Keep sharing. Keep smile ;)