ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
OracleJdbcOracle Problem Overview
I have installed Oracle 11g Express Edition Release 2 in my windows 7 64 bit OS and tried to execute JDBC program, then I got the following error:
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
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.jlcindia.jdbc.JDBCUtil.geOracleConnection(JDBCUtil.java:28)
at Lab3O.main(Lab3O.java:15)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:385)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301)
... 8 more
Oracle Solutions
Solution 1 - Oracle
I fixed this issue by correcting my jdbc string.
For example, the correct jdbc string should be...
jdbc:oracle:thin:@myserver:1521/XE
But the jdbs string I was using is ...
jdbc:oracle:thin:@myserver:1521:XE
(Note: between 1521
and XE
should be a /
)
This bad jdbc string give me a ORA-12505 error too.
Solution 2 - Oracle
There are a few things that can cause this problem, but before you get started with JDBC, you need to be sure that you can connect to the database using SQLPlus. If you're not familiar with SQLPlus, it's a command-line tool for connecting to Oracle databases that has been a standard part of Oracle for a long time and it is included with Oracle XE.
When connecting to an Oracle database using JDBC, you don't connect to the database directly. Instead, you connect to a TNS listener, which then connects you to the database. The error ORA-12505
means that the listener was up and you could connect to it, but it couldn't connect you to the database because it doesn't know that that database is up. There are two reasons for this:
- the database has not been started up,
- the database has not registered with the listener, e.g. because the database was started before the listener. (When the database starts, it registers itself with a listener if it is already running. If the listener isn't running, the database doesn't register itself, and if the listener starts, it doesn't go looking for databases that might register with it.)
ORA-12505 means that the listener knows about that database, but the listener hasn't received a notification from the database that the database is up. (If you were trying to connect to the wrong database, using the wrong SID, you would get an ORA-12154 error "TNS: could not resolve the connect identifier specified".)
What Oracle services are running in the Services snap-in? (Open this from Control Panel > Administrative Tools > Services, or just Start > Run > services.msc
.) You need the services OracleServiceXE and OracleXETNSListener to be running.
If the services have both been started, can you connect to the database in SQL*Plus using any of the following at a command prompt? (I'm assuming you're running these on the machine you've installed Oracle XE on.)
sqlplus system/system-password@XE
sqlplus system/system-password
sqlplus / as sysdba
(Replace system-password
with the password you set for the SYS and SYSTEM users during the Oracle XE installation.)
The first of these three connect via the TNS listener, but the second two connect directly to the database without going via the listener, and only work if you're on the same machine as the database. If the first one fails but the other two succeed, then JDBC connections will also fail. If so, connect to the database using either of the other two and run ALTER SYSTEM REGISTER
. Then exit from SQL*Plus and try the first form again.
If the third one fails but the second one works, add your user account to the ora_dba group. Do this in Control Panel > Computer Management > Local Users and Groups.
Once you can get connections of the form
sqlplus system/system-password@XE
to work, you ought to be able to connect to Oracle XE via JDBC. (Incidentally, you haven't shown us the JDBC code you're using to connect to the database, but I would suspect that it is quite probably correct; there would be various other errors if parts of the connection string were wrong.)
Solution 3 - Oracle
I too got the same error but when tried all the three of them failed. If the above three fails.Try LSNRCTL status if you find the service (XE in my case)missing try this
sqlplus /nolog
conn system
alter system register;
exit
lsnrctl status
Now you can see the service
Even if don't see try this one out
sqlplus /nolog
conn system
alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;
alter system register;
exit
lsnrctl status
This should probably work ...
Solution 4 - Oracle
When your getting this error "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"
Solution: Open Services, and start OracleServiceXE
, after that try to connect...
Solution 5 - Oracle
If you have a working connection in Oracle SQL Developer, use the information on the connection menu to build your url, as described in the following image:
In the above example, the url would be : jdbc:oracle:thin:@ORADEV.myserver.com:1521/myservice
Note that if your are using a SID, then there is a colon (":") instead of a slash ("/") after the host name.
Solution 6 - Oracle
I found some reasons for this exception.they are
1)The name of the database XE by default.so the url will be"jdbc:oracle:thin:@localhost:1521:XE".
2)Make sure that OracleServiceXE,OracleXETNSListener is running.it will be in Control Panel\All Control Panel Items\Administrative Tools\Services
Solution 7 - Oracle
I solved this issue by correcting my JDBC code.
the correct JDBC string should be...
conection = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:xe","system","ishantyagi");
But the JDBC string I was using was ...
conection = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl","system","ishantyagi");
So, the mistake of specifying orcl instead of xe showed this error as the SID name was wrong.
Solution 8 - Oracle
My issue is resolved when I use the below code:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:1521/servicename","userName","Password");
Solution 9 - Oracle
Faced similar error, any of the above solutions didn't help.
There was a problem in the listner.ora file. By mistake I had added SID
out of the SID_LIST
see below(section between the stars *).
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
*(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
)*
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
Corrected this error as below:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
Stopped the listeners OracleServiceXE and OracleXETNSListener manually as it did not stop automatically by going to Control Panel\All Control Panel Items\Administrative Tools\Services. Restarted the database and it worked like a charm.
Solution 10 - Oracle
i initially came here with the same problem. I had jus installed Oracle 12c on Windows 8 (64-bit),but i have since resolved it by 'TNSPING xe' on the command line... If the connection isn't established or name not found,try the database name,in my case it was 'orcl'... 'TNSPING orcl' again and if it pings successfully then u need to change the SID to 'orcl' in this case (or whatever database name u used)...
Solution 11 - Oracle
One possibility that I haven't seen widely discussed is that there may be a problem resolving the hostname on the host machine itself. If there is no entry for $(hostname) in /etc/hosts, the Oracle listener gets confused and wont come up.
That turned out to be my problem, and adding the hostname and ip address in /etc/hosts resolved the problem.
Solution 12 - Oracle
Oracle: Thin-style Service Name Syntax
Thin-style service names are supported only by the JDBC Thin driver. The syntax is:
@//host_name:port_number/service_name
http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIDHCBA
Solution 13 - Oracle
I have faced the same issue and solved by restart the OracleServiceXE service. Goto Services.msc and then verify the 'OracleServiceXE' service is UP and running
Solution 14 - Oracle
I fixed this issue by changing "SID" to "SERVICE_NAME" in my TNSNAMES.ora file.
Please see if your DB asks for SID or SERVICE_NAME.
Cheers
Solution 15 - Oracle
I too faced the same issue. I had installed Oracle Express edition 10g in Windows XP OS using VMware and it was working fine. Since it was very awkward typing SQL queries in the SQL utility provided by 10g and since I was used to working with SQL developer, I installed 32 bit SQL developer in XP and tried connecting to my DB SID "XE". But the connection failed with error-ORA-12505 TNS listener doesn't currently know of SID given in connect descriptor. I was at sea as to how this problem occurred since it was working fine with the SQL utility and I had also created few Informatica mappings using the same. I did browse a lot on this stuff hither thither and applied the suggestions offered to me after pinging the status of "lsnrctl" on public forums but to no avail. However, this morning I tried creating a new connection again, and Voila, it worked with no issues. I am guessing after reading in few posts that sometimes listener listens before the DB connects or something(pardon me for my crude reference as I am a newbie here) but I suggest to just restart the machine and check again.
Solution 16 - Oracle
I had the same problem so to resolve this problem I first reconfigure my listener using netca
after that I deleted my old database which was ORCL by using dbca
and then i created the new database again using dbca
Solution 17 - Oracle
Please check both OracleServiceXE
and OracleXETNSListener
having the status started when you navigate through start->run->services.msc
.
For my case only OracleXETNSListener
was started but OracleServiceXE
was not started, when I started by right clicking -> start
and checked the connection its working for me
Solution 18 - Oracle
If you use Oracle Express Edition, you should have this url > jdbc:oracle:thin:@localhost:1521:xe or jdbc:oracle:thin:@localhost:1521/XE
I had similar problem with liquibase config plugin in pom.xml. And I changed my configuration:
`<configuration>
<driver>oracle.jdbc.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:xe</url>
<defaultSchemaName></defaultSchemaName>
<username>****</username>
<password>****</password>
</configuration>`
Solution 19 - Oracle
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","scott","tiger");
Error I got:
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
How I solved it:
Connection con=DriverManager.getConnection("jdbc:oracle:thin:localhost:1521:xe","scott","tiger");
(Remove @)
Don't know why, but its working now...
Solution 20 - Oracle
Check by doing tnsping and instance name in host machine. It will give u the tns decription and all most of the time host name is different which is not matching.
I resolve my issue likewise
In Unix machine
$ tnsping
It gives me full tns description where I found that host name is different.. :)
Solution 21 - Oracle
I had similar problem in SQL Workbench.
URL:
jdbc:oracle:thin:@111.111.111.111:1111:xe
doesn't work.
URL:
jdbc:oracle:thin:@111.111.111.111:1111:asdb
works.
This help me in my concrete situation. I afraid, that could exists many other reasons with different solutions.
Solution 22 - Oracle
I got this error ORA-12505, TNS:listener does not currently know of SID given in connect descriptor when I tried to connect to oracle DB using SQL developer.
The JDBC string used was jdbc:oracle:thin:@myserver:1521/XE, obviously the correct one and the two mandatory oracle services OracleServiceXE, OracleXETNSListener were up and running.
The way I solved this issue (In Windows 10)
1. Open run command.
2. Type services.msc
3. Find services with name OracleServiceXE and OracleXETNSListener in the list.
4. Restart OracleServiceXE service first. After completing the restart try restarting OracleXETNSListener service.
Solution 23 - Oracle
Had a similar issue. The issue started occurring suddenly - we are having load balanced database connection URL, but in jdbc connections I was pointing to a single db directly.
Changed to load balanced db url and it worked.
Solution 24 - Oracle
In my case not was working out, finally i restarted my oracle and TNS listener and everything worked. Was struggling for 2 days.
Solution 25 - Oracle
Apart from running services(OracleServiceXE,OracleXETNSListener) on there is a chance your Anti-virus software/firewall may still block them. Just make sure they are not blocked.
Solution 26 - Oracle
I just fixed it by restarting / starting oracleService in services
Solution 27 - Oracle
My oracle stopped working and I was getting this error. I restarted my machine and also tried above solutions. Ultimately, I opened component services and restarted the oracle services and it all started working. Hope this helps someone.
Solution 28 - Oracle
I was just creating the database link incorrectly.
Simple fix for me was to simply change 'SID' to SERVICE_NAME
CREATE DATABASE LINK my_db_link
CONNECT TO myUser IDENTIFIED BY myPassword
USING
'
(
DESCRIPTION=
(
ADDRESS=
(PROTOCOL=TCP)
(HOST=host-name-heren)
(PORT=1521)
)
(CONNECT_DATA=(SID=theNameOfTheDatabase))
)';
Changing
SID=theNameOfTheDatabase
to
SERVICE_NAME=theNameOfTheDatabase
resolved my issue.
Solution 29 - Oracle
I encounter this problem because I did kill task to "Oracle" task in the Task Manager.
To fix it you need to open the cmd -> type: services.msc
-> the window with all services will open -> find service "OracleServiceXE
" -> right click: start.
Solution 30 - Oracle
@Luke Woodward's answer helped to identify my XE wasn't connecting.
I had an issue when connecting to a VPN. And I am using a fresh local Oracle 21c on Windows 11.
The files listener.ora
and tnsnames.ora
were using my PC name "ERIK-PC" like this:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ERIK-PC)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
I just needed to change ERIK-PC
to localhost
.
More information in this thread.
By the way, I am using Oracle 21c, and since 18c, the *.ora files are under installationfolder\homes\OraDB21Home1\network\admin\*.ora
Save your changes and restart the listener using a privileged windows shell:
lsnrctl stop
lsnrctl start