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

Question

I am using Oracle database. I've written a small JDBC connection program in Java but I am facing an issue with the listener.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcConnection {
    public static void main(String[] args) throws SQLException,ClassNotFoundException {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "system";
        String password = "password";
        Connection connection = null;

        Class.forName("oracle.jdbc.driver.OracleDriver");
        connection = DriverManager.getConnection(url, user, password);
        if(connection!=null){
            System.out.println("Success in connnection");
        } else {
            System.out.println("failure in connection ");
        }
    }
}

I am getting the following exception:

C:\Users\Administrator\Desktop>java JdbcConnection
Exception in thread "main" java.sql.SQLException: Listener refused the connectio
n with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
localhost:1521:orcl

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:261)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:
441)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
n.java:35)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at JdbcConnection.main(JdbcConnection.java:18)

This is the output of lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 16-JUN-2015 13:43
:41

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Produ
ction
Start Date                16-JUN-2015 12:02:52
Uptime                    0 days 1 hr. 40 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\orauser\product\12.1.0\dbhome_1\network\admin\l
istener.ora
Listener Log File         C:\app\orauser\diag\tnslsnr\hydwemvm\listener\alert\lo
g.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hydwemvm)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Ploch

Could you show us the code? – Grigson

Did you start the Windows services for Oracle and the listener on your computer? – Halophyte

Yes, services are running and I've restarting the services also. – Ploch

Could you please add the output of lsnrctl status to your question? – Embellishment

Can you connect using sqlplus system/password@orcl? Btw: you should never use the system account for application stuff. Create a regular user and use that. The system and sys accounts should only be used to administer the database. Never, ever create your own objects with those users. – Halophyte

Since the listener doesn't show the orcl service, the database doesn't appear to be up; or is up and was unable to register. Trying to connect locally through SQL*Plus (without the @orcl) will show you which; or see which processes are running. An initial thought is that the listener is using hydwemvm, the DB may be trying to register using localhost or a fixed (stale) IP if you're using DHCP. Not enough information to tell though. Start by seeing what is actually running. – Expellant

Answer 1

If you know your oracle database SID, then use

jdbc:oracle:thin:@localhost:1521:orcl

otherwise use below in case you have service name

jdbc:oracle:thin:@localhost:1521/orcl

Also, make sure service name with the name ORCL should be up and running. If still doesn't work, then you need to restart your machine and try again above.

Still, not working ? Then, try following :

Login with SYSTEM user and register LOCAL_LISTENER by running below SQLs.

alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;
alter system register;

---

How to check oracle SID and service name :

SELECT sys_context('USERENV', 'SID') FROM DUAL; -- It will return your oracle database SID

SELECT sys_context('USERENV', 'SERVICE_NAME') FROM DUAL; -- It will return your oracle database service name

Colossian

I've tried with both the above formats still its no luck – Ploch

Then, as I mentioned, restart your machine and try. – Colossian

Answer 2

If you want to know the default SID of your database use this query in sqlplus:

SELECT sys_context('USERENV', 'SID') FROM DUAL;

Use this value in the JDBC URL instead of "orcl".

Berke

Answer 3

Can you use the below URL?
Note the difference, this is to use the SERVICENAME instead of a SID.

jdbc:oracle:thin:@localhost:1521/orclservice

Hord

Answer 4

I am facing the same problem.

Try removing the LAN cable or disconnect your net connectivity and restart the services of Listener and run the code.

It worked for me.

Lindquist