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

 

I face this issue “listener does not currently know of SID” while I was installing a new database. I think is due to some  mix of old oracle database installation (without a proper uninstall).

So most of the resources on the net  say that there is an issue in the configuration of listener, a kind of proxy/hub service for Oracle. It’s right and it’s good to check that configuration of those files are rights : tnsnames.ora and the listener.ora. But my configuration was good.

Configuration trough system variable

So I took sometimes to figure out that oracle was looking inside the wrong old home folder.

It thought first that the system variable “path” was not correct and it was the case… The bin folder path should point to your new  oracle installation fodler (e.g. C:\oraclexe\app\oracle\product\11.2.0\server\bin). But It doesn’t fix my issue. I found that the path to the tns configuration was not correct by typping : lsnrctl status.

listener_status

In green the oracle configuration path, source image : http://www.in-oracle.com/Oracle-DBA/architecture/oracle-listener.php

To fix the path there is a windows system variable called TNS_ADMIN  (that is not refer inside the path variable). This TNS_ADMIN should refer to the good oracle home where you have the last tnsnames.ora and the listener.ora configuration files (e.g. C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN)

Configuration via the oracle system

You could also configure the TNS directly via  sqlplus sys as sysdba

alter system set local_listener='TEST'; //name of the listener
//or
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))';

But then if you save the configuration, for instance via a pfile (parameter settings into a text initialization parameter file) then you need to start the database with this file. More information in this article : ORA-00119: invalid specification for system parameter LOCAL_LISTENER

References

Image source : https://coderanch.com/t/585051/databases/Listener-refused-connection-error-ORA


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s