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

Question

I'm trying to connect to Oracle 10.2.0 from NetBeans, using the following connection string:

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

The weirdest part is that everything worked fine, until the one of the reboots. I started getting this TNS-12505 errors all the time; look at the final 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. The fourth, the connection via SQL*Plus, worked just fine.

Here are 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 > lsnrctl services

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 are started and SQL*Plus connects fine.

Is there some workaround for this error?

Answer 1

You need to add the SID entry for XE in order to register the instance with the listener.

After installation of Oracle XE, everything looks good, but when you issue

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 register with the listener.

So please edit your listener.ora like this:

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 issue came up when I installed Oracle XE on Windows 7. I did not face this problem on Windows XP. In general, this entry should not be necessary, because the instance should register with the listener automatically. 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 then changed:

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

to:

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

Answer 3

After a change of ip in our oracle virtual machine, the listener never worked again. Finally these command solved the problem (where 192.168.10.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 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

System altered.

SQL> alter system register;

System altered.

SQL> exit

Answer 4

I ran into this problem after a firewall change to restrict access between our internal network and the database server reporting the error located in the DMZ. Communication was working fine until the change, and system and database restarts were of no help. In my case both Oracle XE11gR2 installations are on Windows.

After 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's ip])(PORT=1521))' scope=both;
alter system register;

it may be that this worked simply because of the 'alter system register' as suggested by ik_zelf. I did have the IP already set in relevant places in listener.ora and tnsnames.ora.

Answer 5

Step 1 – Check the DB listener status

   lsnrctl status

Notice that the listener you want (in our case “orcl”) is not showing.

Step 2 – Login via sqlplus

   sqlplus sys/oracle as sysdba

Sqlplus gave us this error message:

   Writing audit records to Windows Event Log failed

Step 3 – Go into the Windows Event Viewer (eventvwr.exe)

Under “Windows Logs”, right click on Application and select “Clear Log”. Do the same for System.

It may also be wise to right click on Application and select Properties. Then, under “Log Size” select the following option under “When maximum log size is reached”: “Overwrite events as needed”. This should prevent the log from maxing out and causing the DB not to start.

In Windows Vista and higher, you can execute the following command to clear the Application log:

   wevtutil cl Application

Step 4 – Login via sqlplus

   sqlplus sys/oracle as sysdba

You should now be able to login with no error messages.

Step 5 - Check the DB listener status

   lsnrctl status

You should now see your listener running.

Step 6 – Start UCM

UCM should now start up.

For a more in-depth answer to this question you can read my full blog post.

Answer 6

I'm running oracle xpress edition 11.2 on windows 8 and I had the same error when trying to connect to DB using sqldeveloper.

I've edited listener.ora as per Brandt answer above and even restarted my machine the issue wasn't fixed.

I've done the following: go to control panel -> administrative tools -> services you will find a service called "OracleServiceXE" not running.

I started it and tried to connect again, issue resolved.

Answer 7

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

I have had this issue before, too.

Answer 8

I had the same issue on Windows 7. The cause was, that I had been connected to VPN using Cisco AnyConnect Secure Mobility Client.

Answer 9

Check - LSNRCTL> stat I got result like -

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 above details - My port no is - 1522 and i am able to create connection using "orcl" instance - so i used port as 1522 and instance as "orcl" -- Now URL is -

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

It worked for me So please check LSNRCTL> stat in command prompt and configure your connection url accordingly.
Hope it will help Someone.

Answer 10

Your database, that apparently has the ORACLE_SID XE, is not defined in the listener.ora. That is no problem, since when the database normally opens, it will register itself to the default listener, being the one on port 1521 so that is ok.

1. is the database open?
2. what is the start order of listener/database?
3. is the error persistent?

If the database starts before the listener, the database has no listener to register to. It will do so every few minutes so after a while, I expect the error will go away because of the registration has taken place. You can issue alter system register; to speed this. If the database is in restricted mode, the connections using a service will fail. You are using ORACLE_SID so that is not your problem.

Also check the names in use. Is localhost resolving to the same address as Brodyaga-PC? In the jdbc string you use localhost and the listener listens om Brodyaga-PC. Is localhost 127.0.0.1 ?

Answer 11

Starting OracleServiceHari where 'HARI' is the SID, worked for me.

Others facing same problem may check status of the service.

Answer 12

In my case i just went through following steps in windows 10.

1. goto control panel
2. click administrative
3. click services
4. find OracelServeceXE, OracleXEClrAgeng, OracleXETNSListener
5. Right click and press Start/Restart
6. After Completing Process. Check it will work or it will work ;)
7. Done
8. All the Best.

Answer 13

Just for another possibility to check, I came up with exactly the same problem with an incorrect port number specified in connect URL. I created a new oracle11g instance and forgot to kill the former one occupying the same port 1521, so the new instance automatically started on port 1522. Editing port number solved my problem.

Answer 14

I've just faced the same problem just after installed Oracle XE 11.2. After reading and consulting 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 msec)

C:\>

As you can see, it takes long time to resolve, so I added an entry to hosts file as follows:

127.0.0.1       localhost

Once done, ran again 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 time response radically decreases, I tried my connection on sqldeveloper successfully.

Answer 15

in Windows in the search option Go to administrative tools>component services>OracleServiceXE(start this service)

Answer 16

Go to Task Manager and check below services are running or not (if not start the services):

OracleXETNSListener

OracleXEClrAgent

OracleServiceXE

Answer 17

This worked for me like a magic.

I logged into database and registered the listener.

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