File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes ORA-12505, TNS:listener does not currently know of SID given in connect descriptor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor" Watch "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor" New topic
Author

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

anitao loboo
Greenhorn

Joined: Apr 25, 2013
Posts: 1

Hi,

We had database issue today and we observed below Error in out jboss logs. Once the db is started i executed "Select count(1) from tname whre col1='INITIAL', from TOAD session but query did not give any output. But same query gives output when col1='FAILED'. This happened once db is restarted. Even my java application hangs when the above query is executed. I am not able to trace why it happened .


Errors from jboss logs

2013-04-25 13:48:25,104 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (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 org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:179)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:577)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:262)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:500)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:315)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
at sun.reflect.GeneratedMethodAccessor205.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService.doDataSourceMethod(WrapperDataSourceService.java:344)
at org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService.invoke(WrapperDataSourceService.java:201)
at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.invocation.jrmp.server.JRMPInvoker$MBeanServerAction.invoke(JRMPInvoker.java:818)
at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:419)
at sun.reflect.GeneratedMethodAccessor188.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:595)
Caused by: 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 org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:171)
... 33 more
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)
... 38 more


Regards,
Anita Lobo
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Welcome to the ranch!

The ORA-12505, TNS:listener does not currently know of SID given in connect descriptor error may occur after the database gets restarted. It might take even a minute before everything warms up in Oracle. Just retry the connection (or restart JBoss) after a minute or two, the error might disappear.

Problems with your query are not necessarily connected to the ORA-12505 error. How long did you wait for the query to finish? Perhaps it is just taking very long time. The count() function essentially needs to read all rows where the value is INITIAL, and there could be a lot of them. If there were very few rows with the 'FAILED' value, Oracle might use and index and return the value much quicker.

You might try the EXPLAIN PLAN command to see whether the query is expensive (this assumes your statistics are up to date). SQL Developer can create and display execution plans easily. Interpreting execution plans (and catching everything that may go wrong, such as statistics, bind variables and so on) can be sometimes pretty complicated, though.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor