Java Oracle localhost connection error (ORA-12505)

Question

I am trying to currently connect to a database on my current computer.

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

public class Main {
    public static void main(String[] argv) throws Exception {

        Connection connection = null;
        try {
            // Load the JDBC driver
            String driverName = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverName);

            // Create a connection to the database
            String serverName = "localhost";
            String portNumber = "1521";
            String sid = "xe";
            String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
            String username = "scott";
            String password = "tiger";
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("Success");
        } catch (ClassNotFoundException e) {
            System.out.println("Class Not Found Error");
        } 
    }
}

I keep getting this error and I do not know why...

Exception in thread "main" java.sql.SQLException: Listener refused the connection 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:xe

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:496)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:202)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:465)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at Main.main(Main.java:21)

Within my server I have used the command (logged on as sys) SQL> select instance from v$thread; (it returns) Instance--> xe

What could I be doing incorrect?

Thanks!

P.S. I have also tried 127.0.0.1 instead of localhost

What does `lsnrctl status` show when run with the user that owns the database? — Mat, Jul 24 '11 at 08:21
It is either your connection string or database configuration files. — dkroy, Jul 24 '11 at 08:21
Are you using Oracle Express Edition (XE) or the Standard/Enterprise editions? — Vineet Reynolds, Jul 24 '11 at 08:35
@Luke, not necessary. He's running an example, going by his previous question, so the `xe` SID might be from the example he's copied. — Vineet Reynolds, Jul 24 '11 at 10:54
@Vineet: I was unaware of his/her previous question, thanks for pointing that out. However, the SQL query he/she runs to fetch the instance name (first paragraph under the stacktrace), which returns `xe`, *does* suggest he/she's using XE. — Luke Woodward, Jul 24 '11 at 11:31
I am using 10g express edition, the xe was there by default, I just used it — k9b, Jul 24 '11 at 16:50

score 4 · Answer 1 · answered Jun 05 '14 at 19:40

4

Instead of String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;

use this:

String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + "/" + sid;

answered Jun 05 '14 at 19:40

Sergio

41
1

score 4 · Accepted Answer · answered Jul 24 '11 at 08:26

4

Check if listener.ora file under the <ORACLE_HOME>\admin\NETWORK directory has the following value:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

answered Jul 24 '11 at 08:26

Jugal Shah

3,621
1
24
35

1

I dont have that, but I do have `LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = LENOVO-E04EFE5E)(PORT = 1522)) ) ) DEFAULT_SERVICE_LISTENER = (XE)` – k9b Jul 24 '11 at 17:05
Thanks for this, I had a problem with the port was 1522 instead of 1521, it works now. THANKS! – k9b Jul 24 '11 at 17:16
@k9b same here! What's the solution then? – Satej Dec 05 '19 at 19:45
It seems my port was off (was 1522 instead of 1521) @Satej - question is rather old sorry don't remember exactly – k9b Dec 12 '19 at 18:50

score 2 · Answer 3 · answered Aug 26 '13 at 12:35

2

replace xe with database name which you have set during installation, you will surely get success

if you forgot the dbname it could be retreived from file tnsnames.ora in your oracle directory

answered Aug 26 '13 at 12:35

tarun hasija

21
1

score 1 · Answer 4 · edited Jul 25 '14 at 17:50

1

I faced same problem while connecting to oracle rac. I changed the url from port:servicename to port/servicename and it worked for me.

edited Jul 25 '14 at 17:50

Stuart Sierra

10,837
2
29
35

answered Jul 25 '14 at 17:13

user3877876

11
1

score 0 · Answer 5 · answered Jul 24 '11 at 10:42

I'm going to guess that the TNS listener has started, but the database instance started up before the listener did.

When the database instance starts up, it will register itself with the TNS listener. However, if there's no listener to register with, it can't do this. When the listener starts up, it doesn't check to see whether the instances it knows about have started up.

I can provide a demonstration. I'm using Oracle 11g XE Beta on Windows 7. Initially, the OracleServiceXE service is running but OracleXETNSListener service is not.

I ran your database connection code and I got the following error:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

If you're getting a ORA-12505 error, then clearly your TNS listener is running.

I then started the TNS listener and re-ran your database connection code. I got the following output this time: (I've renamed your class and changed the username and password within it, but other than that, the code within it is the same):

C:\Users\Luke\stuff>java DbConnTest
Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
[stacktrace snipped]

(This error isn't identical to yours: I didn't get a The Connection descriptor used by the client was: section in it. I'm not 100% sure why.)

In the case above, the fix is connect to SQL*Plus as SYS and run ALTER SYSTEM REGISTER. This registers the instance with the listener:

C:\Users\Luke\stuff>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Beta on Sun Jul 24 11:13:57 2011

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

After doing this, I was able to connect to the database:

C:\Users\Luke\stuff>java DbConnTest
Success

Java Oracle localhost connection error (ORA-12505)

5 Answers5

Linked

Related