TNS-12505: TNS: the listener is currently unaware of the SID specified in the connection descriptor

Question

I am trying to connect to Oracle 10.2.0 from NetBeans using the following connection string:

jdbc:oracle:thin:@localhost:1521:XE 

The strangest part is that everything works fine while one of the reboots. I kept getting these TNS-12505 errors; look at the latest entries in my listener.log :

 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 14-APR-2011 13:46:48 Copyright (c) 1991, 2005, Oracle. All rights reserved. System parameter file is D:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora Log messages written to D:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log Trace information written to D:\oraclexe\app\oracle\product\10.2.0\server\network\trace\listener.trc Trace level is currently 0 Started with pid=3460 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Brodyaga-PC)(PORT=1521))) Listener completed notification to CRS on start TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE 14-APR-2011 13:48:54 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=Brodyaga))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58458)) * establish * XE * 12505 TNS-12505: TNS:listener does not currently know of SID given in connect descriptor 14-APR-2011 13:49:00 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=Brodyaga))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58481)) * establish * XE * 12505 TNS-12505: TNS:listener does not currently know of SID given in connect descriptor 14-APR-2011 13:49:02 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=Brodyaga))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58487)) * establish * XE * 12505 TNS-12505: TNS:listener does not currently know of SID given in connect descriptor 14-APR-2011 13:50:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Brodyaga))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * services * 0 

The first three entries are my attempts to connect from NetBeans. Fourth, connecting through SQL * Plus worked just fine.

Here is the contents of my listener.ora :

 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = Brodyaga-PC)(PORT = 1521)) ) ) DEFAULT_SERVICE_LISTENER = (XE) 

And tnsnames.ora :

 XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Brodyaga-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) 

And output from services < lsnrctl

 C:\Users\Brodyaga>lsnrctl services LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 14-APR-2011 13:59 :45 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER The command completed successfully 

Both Oracle services start and SQL * Plus connects perfectly.

Is there a way around this error?

Answer 1

You need to add an SID entry for XE to register the instance with a listener.

Everything looks fine after installing Oracle XE, but when you release

 C:\>sqlplus / as sysdba SQL>shutdown immediate SQL>startup TNS-12505: TNS:listener does not currently know of SID given in connect descriptor 

the instance will not be registered by the listener.

Therefore, please edit your listener.ora as follows:

 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = XE) (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server) (PROGRAM = extproc) ) ) 

This problem occurred when I installed Oracle XE on Windows 7. I did not encounter this problem on Windows XP. In general, this entry is not needed because the instance must be automatically registered by the listener. Running Oracle XE on Linux (Fedora), there is no need to add XE to the sid-list.

Answer 2

this worked for me - I did all of the above and then changed:

 jdbc.databaseurl=jdbc:oracle:thin:@localhost:1521:xe 

at

 jdbc.databaseurl=jdbc:oracle:thin:@localhost:1521/xe 

Answer 3

I encountered this problem after changing the firewall to restrict access between our internal network and the database server reporting an error in the DMZ. Communication worked fine until the changes and rebooting the system and database helped. In my case, both Oracle XE11gR2 installations are on Windows.

After a day of struggle, I found http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/ and solved the problem with:

 alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=[my server ip])(PORT=1521))' scope=both; alter system register; 

it is possible that this worked simply because of a “system registry change”, as suggested by ik_zelf. I really had the IP address set in the appropriate places in listener.ora and tnsnames.ora.

Answer 4

After changing ip in our oracle virtual machine, the listener no longer worked. Finally, this command solved the problem (where 192.168.10.200.200 is the new ip)

[oracle @oracle admin] $ sqlplus / as sysdba

SQL * Plus: Release 11.2.0.1.0 Production on Tue May 10 12:57:37 PM

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64-bit production With partition parameters, OLAP, Data Mining, and Real Application Testing

SQL> change the system set local_listener = '(ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.10.200) (PORT = 1521))' scope = both;

Changed the system.

SQL> change the system register;

Changed the system.

SQL> exit

Answer 5

Step 1 - Check the status of the database listener

  lsnrctl status 

Please note that the listener you want (in our case "orcl") is not displayed.

Step 2 - Log in via sqlplus

  sqlplus sys/oracle as sysdba 

Sqlplus gave us this error message:

  Writing audit records to Windows Event Log failed 

Step 3 - Go to the Windows Event Viewer (eventvwr.exe)

In the "Windows Logs" section, right-click the application and select "Clear Log." Do the same for the system.

It may also be appropriate to right-click on the application and select “Properties”. Then, in the "Log Size" section, select "When the maximum log size is reached": "Overwrite events as necessary." This should prevent the log from being exceeded and not start the database.

In Windows Vista and above, you can run the following command to clear the application log:

  wevtutil cl Application 

Step 4 - Login through sqlplus

  sqlplus sys/oracle as sysdba 

Now you can log in without error messages.

Step 5 - Check the status of the database listener

  lsnrctl status 

You should now see that your listener is working.

Step 6 - Starting UCM

UCM should now start.

For a more detailed answer to this question, you can read my full blog post .

Answer 6

As mentioned, removing the colon : and replacing it with a slash / before sid worked for me.

I also had this question.

Answer 7

I am running oracle xpress edition 11.2 on Windows 8, and I had the same error when trying to connect to the database using sqldeveloper.

I edited listener.ora according to Brandt's answer above and even restarted my machine, the problem was not fixed.

I did the following: go to the control panel -> administrative tools -> services you will find that a service called "OracleServiceXE" is not working.

I started it and tried to connect again, the problem is resolved.

Answer 8

I had the same problem in Windows 7. The reason was that I was connected to the VPN using the Cisco AnyConnect Secure Mobility Client.

Answer 9

Your database, which obviously has an ORACLE_SID XE, is not defined in listener.ora. This is not a problem, because when the database usually opens, it will register with the default listener, being one on port 1521, so this is normal.

• Is the database open?
• What is the start order of listening / database?
• - a constant mistake?

If the database starts before the listener, there is no listener in the database for registration. He will do this every few minutes, so after a while I expect the error to disappear due to registration. You can issue alter system register; to speed it up. If the database is in restricted mode, connections using the service will fail. You are using ORACLE_SID, so this is not your problem.

Also check the names used. Does the local host resolve the same address as Brodyaga-PC? In the jdbc line, you use localhost, and the listener listens to om Brodyaga-PC. Is localhost 127.0.0.1?

Answer 10

Running OracleServiceHari, where "HARI" is the SID, worked for me.

Others who encounter one problem may check the status of the service.

Answer 11

Check - LSNRCTL> stat I got the result as -

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production Start Date 17-APR-2016 10:12:38 Uptime 0 days 10 hr. 6 min. 16 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\product\11.2.0\dbhome_1\network\admin\listener.ora Listener Log File c:\app\admin\diag\tnslsnr\admin-PC\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully. 

From the details above - My port no is - 1522, and I can create a connection using an instance of "orcl" - so I used the port as 1522 and the instance as "orcl" - now the URL is

 DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:orcl", "SYS as SYSDBA","password"); 

It worked for me. Therefore, please check the command line LSNRCTL> stat and configure the connection URL accordingly.
Hope this helps someone.

Answer 12

In my case, I just went through the following steps in Windows 10.

1. go to control panel
2. click administrative
3. click service
4. find OracelServeceXE , OracleXEClrAgeng , OracleXETNSListener
5. Right click and click Start / Restart
6. After the completion of the process. Check if this will work or will work;)
7. Done
8. All the best.

Answer 13

Just for another way to check, I ran into the same problem with the wrong port number provided in the connection url. I created a new instance of oracle11g and forgot to kill the first one occupying the same port 1521, so the new instance automatically started on port 1522. Editing the port number solved my problem.

Answer 14

I encountered the same problem right after installing Oracle XE 11.2. After reading and advising a DBA friend, I ran the following command:

 C:\>tnsping xe TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 11-ENE-2017 14:27:44 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myLaptop)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) 

OK (30 ms)

 C:\> 

As you can see, resolution takes a lot of time, so I added an entry to the hosts file as follows:

 127.0.0.1 localhost 

After execution, repeat the same command:

 C:\>tnsping xe TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 11-ENE-2 017 14:40:29 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myLaptop)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SER VICE_NAME = XE))) OK (30 msec) C:\> 

As the response time drastically decreases, I have successfully tried connecting to sqldeveloper.

Answer 15

on Windows in the search parameter. Go to Administration Tools> Component Services> OracleServiceXE (start this service).

Answer 16

Go to the task manager and check if the services are running or not (if the services are not starting):

 OracleXETNSListener OracleXEClrAgent OracleServiceXE 

Answer 17

It worked for me like magic.

I went into the database and registered the listener.

 alter system set local_listener='(...)'; alter system register;