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

2
votes

When i try to connect to oracle through SQL Developer or my java application i get below error, though i am able to connect from SQL PLUS (command line tool)

    Listener refused the connection with the following error :

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

I face this problem whenever i do system restart. Though 1 out of 10 time after system restart , i am able to connect successfully.

I can see all of the oracle services including OracleOraDb11g_home1TNSListener are up under services section. Just for information my SID name is orcl

After going thru Luke answer at ORA-12505, TNS:listener does not currently know of SID given in connect descriptor looks like at the time of restart oracle database gets started earlier than OracleOraDb11g_home1TNSListener. so its not able to register with listener.

I think solution should be make listener up first then start the database. How can i configure to start database after some time lag after reboot ?

1
javaoracle
The database should attempt to register periodically; I'd expect it to do that within about a minute of the listener coming up, if the DB started first. Does it appear eventually, or if you explicitly do alter system register as SYS from SQL*Plus? If not does your IP address change on reboot, and is the local_listener init parameter set? (Erm, except the listener is on localhost from another comment, so it probably isn't an IP issue...) - Alex Poole
Almost it happens every time when i restart the system. Also i don't explicitly register the db with listenet(it should happen automatically on reboot. And yes sometimes it happens but sometime it does not). I pasted the result of "lsnrctl status" under comments ofLalit answer. Can you make out something out of it ? - emilly
You asked "does your IP address change on reboot ?" Probably yes. But how does it impact? You also asked "is the local_listener init parameter set ?" i am not sure where to check this and whats the importance of it ? - emilly
@emily - it probably doesn't matter, since your listener is on localhost. show parameters listener would tell you if that is set, but there would have to be a discrepancy amd something odd for it to matter. If it isn't set it defaults to localhost:1521, which is what your listener is on anyway. - Alex Poole

1 Answers

0
votes

I face this problem whenever i do system restart. Though 1 out of 10 time after system restart , i am able to connect successfully.

The possible reason would be that the SERVICES are not up after the system restart. The way to check it, depends on the operating system.

For example, in Windows OS you could go to services.msc and see whether the TNS service is up and running.

To check whether listener is up or not, and whether the DB is registered, check lsnrctl status. Also, remember, the listener needs sometime after your system restart. The same applies after any changes to the listener, you must wait for sometime.