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 SQL*Plus. If you’re not familiar with SQL*Plus, 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.)
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 SQL*Plus. If you’re not familiar with SQL*Plus, 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.)
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:oraclexeapporacleproduct10.2.0servernetworkadminlistener.ora
Log messages written to D:oraclexeapporacleproduct10.2.0servernetworkloglistener.log
Trace information written to D:oraclexeapporacleproduct10.2.0servernetworktracelistener.trc
Trace level is currently 0
Started with pid=3460
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC_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:oraclexeapporacleproduct10.2.0server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:oraclexeapporacleproduct10.2.0server)
(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:UsersBrodyaga>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?
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:oraclexeapporacleproduct10.2.0servernetworkadminlistener.ora
Log messages written to D:oraclexeapporacleproduct10.2.0servernetworkloglistener.log
Trace information written to D:oraclexeapporacleproduct10.2.0servernetworktracelistener.trc
Trace level is currently 0
Started with pid=3460
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC_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:oraclexeapporacleproduct10.2.0server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:oraclexeapporacleproduct10.2.0server)
(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:UsersBrodyaga>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?
Problem
I.T. administrator launches «Controller Configuration» and creates new database connection to Oracle database schema. Administrator clicks ‘Test Connection’ but receives an error message.
Symptom
Connection failed
Error Details
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Cause
There are many possible causes for the «ORA-12505: TNS:listener …» error, such as:
- Scenario #1 — Incorrect configuration of Oracle database server
- Specifically, the LISTENER on the Oracle database server was not configured correctly.
- Scenario #2 — Incorrect configuration of Oracle database client (installed on Controller application server)
- For more details, see separate IBM Technote #1447103.
Diagnosing The Problem
Scenario #1
Logon to the Oracle database server and launch a command prompt. Type the following command:
- lsnrctl status
In one real-life example, the message included the following text:
- The listener supports no services
Resolving The Problem
Scenario #1 — Incorrect configuration of Oracle database server
Ask your I.T. department’s Oracle DBA to reconfigure the Oracle server’s listener correctly.
- Real-life example:
- lsnrctl stop
- lsnrctl start
In one real-life example, the customer wanted to connect to a database called «CCR11G». In this case the solution was to modify the «listener.ora» file from:
- LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVERNAME)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = E:appAdministratorproduct11.2.0dbhome_2log
to:
- SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:appAdministratorproduct11.2.0dbhome_2)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=ccr11g)
(ORACLE_HOME=E:appAdministratorproduct11.2.0dbhome_2)
(SID_NAME=ccr11g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVERNAME)(PORT = 1521))
)
)
ADR_BASE_LISTENER = E:appAdministratorproduct11.2.0dbhome_2log
Afterwards, restart the listener by typing:
Scenario #2 — Incorrect configuration of Oracle database client
- See separate IBM Technote #1447103.
Related Information
[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.1.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor error occurs when the listener cannot find any matched SID with the provided SID, preventing it from connecting to the database.The database connection Sid you used is either unavailable or wrongly configured. Oracle Listener discovers a discrepancy between your Sid and the connection descriptor settings. The error ORA-12505, TNS:listener does not currently know of SID given in connect descriptor is thrown when oracle fails to create a connection due to invalid SID provided in the configuration.
Please wait a moment while the Oracle service starts up before attempting to connect again. Run the command lsnrctl services to see what services the listener already has. Verify that the service specified by the SID parameter in the connect descriptor of the net SID used is one that the listener is familiar with. If you used a connect identifier, ensure sure the SID is one that the listener recognises. To resolve the error ORA-12505, TNS:listener does not currently know of SID given in connect descriptor, look for any events in the listener.log file.
The Problem
You’ll be provided the host name, port, user name, password, and service name or Sid when you try to connect to a database. Oracle will wait for connections on the same host name and port when it starts up. The SID is the name of the presently running Oracle instance. The alias for the instance that lets you to connect to it is the service name. You will be unable to connect to the running Oracle database instance if the SID setting is incorrect.The error message ORA-12505, TNS:listener does not currently know of SID given in connect descriptor will be displayed.
Host name : localhost
port : 1521
sid. : orclcdb
username : hr
password : hrError
Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
(CONNECTION_ID=glCwDQzBSiScXNzmRhbuQg==)Solution 1
It’s possible that the Oracle server is starting up. Retry the connection after a brief pause. It’s possible that a network outage will occur. This will prevent the connection to the Oracle database from being established. Make that the database is up and operating, and that there are no network issues with it. If you’re having trouble connecting to an Oracle database using the host name, consider using the IP address instead. Ensure that the port configuration and the operating port are same. If you’re connecting using an application, make sure the connection url follows the format.
jdbc:oracle:thin:@localhost:1521/sidSolution 2
Check to see if the listen is working properly. You can pause and resume listening if there is a problem. The connection to the specified host and port will be refreshed as a result of this action. Multiple connections may cause the listener to hang. After restarting the listener, try connecting it again. The issue ORA-12514, TNS:listener does not presently know of service requested in connect descriptor, will be resolved as a result of this.
lsnrctl status
lsnrctl stop
lsnrctl startSolution 3
Check to see if the specified SID corresponds to a valid SID in the Oracle database. The SQL statement below will query the Oracle database for the specified SID. Use the database’s SID if the SID specified in listener.ora varies from the database configuration. This will resolve the issue. ORA-12505: TNS:listener is unaware of the SID specified in the connect descriptor.
select value from v$parameter where name='service_names'
value
------
orclcdbSolution 4
Check the listener.ora file for errors. This is how the configuration should appear. The SID LIST LISTENER will hold the Oracle instance settings. This setup will map GLOBAL DBNAME, SID NAME, and ORACLE HOME in the database. The service names from the preceding query should be the same as the SID NAME. The LISTENER setup determines the PROTOCAL, HOST, and PORT. This is how the LISTENER configuration waits for the database connection to establish.
/u01/app/oracle/product/version/db_1/network/admin/listener.ora
OR
[ORACLE_HOME]/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclcdb)
(SID_NAME = orclcdb)
(ORACLE_HOME = /u01/app/oracle/product/version/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
)
#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orclcdb)
Solution 5
The SID settings, as well as the listener host and port, are all stored in the tnsnames.ora file. Below is an example tnsnames.ora file that demonstrates SID configuration. Check the SID in tnsnames.ora. Make the modifications specified below if the SID is not configured or is incorrectly configured.
/u01/app/oracle/product/version/db_1/network/admin/tnsnames.ora
OR
[ORACLE_HOME]/network/admin/tnsnames.ora
ORCLCDB=localhost:1521/orclcdb
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orclcdb)
)
)
Solution 6
Check the oracle database’s system environment configuration. The proper database that is operating will be displayed in the Oracle database settings. It’s possible that numerous Oracle database versions are installed on the server, causing conflicts. Then /.bash profile, /.bashrc, or /etc/..bashrc will be used to set the environment.
export ORACLE_UNQNAME=orclcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/version/db_1
export ORACLE_SID=orclcdb
export PATH=/home/oracle/bin:/home/oracle/LDLIB:$ORACLE_HOME/bin:/usr/sbin:$PATH
Solution 7
Finally, while attempting to connect to the database, double-check the settings you gave. If you misspelt the configuration, an error message will appear. Check the configurations below to see whether they match your Oracle database configurations.
Host name : localhost
port : 1521
sid : orclcdb
username : hr
password : hr