post tail end of listener.log file
12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor" // *Cause: The listener received a request to establish a connection to a // database or other service. The connect descriptor received by the listener // specified a SID for an instance (usually a database instance) that either // has not yet dynamically registered with the listener or has not been // statically configured for the listener. This may be a temporary condition // such as after the listener has started, but before the database instance // has registered with the listener. // *Action: // - Wait a moment and try to connect a second time. // - Check which instances are currently known by the listener by executing: // lsnrctl services <listener name> // - Check that the SID parameter in the connect descriptor specifies // an instance known by the listener. // - Check for an event in the listener.log file.
Where as I am very much able to connect to DB through sqlplus but not through either TOAD or Java program.from DB Server system or remote client?
Uptime 0 days 0 hr. 0 min. 8 secshows listener was started a mere 8 seconds before STATUS was issued.
(HOST=127.0.0.1)can result is errors because it is a non-route-able IP#
832294 wrote:localhost is non-routeable
I am using Oracle 10g. Facing the same kind of problem. Also I am not able to connect isqlplus as well
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:
Now, when you issue a connect, say like this:
larry = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = curley) ) )
tns will look in your tnsnames.ora for an entry called 'larry'. Finding it, tns sends a request through the normal network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).
$> sqlplus scott/tiger@larry
tns will attempt to establish an IPC connection to the db instance. How does it know the name of the instance? It uses the current value of the enviornment variable ORACLE_SID. So...
$> sqlplus scott/tiger
It will attempt to connect to the instance known as "fred". If there is no such instance, it will, of course, fail. Also, if there is no value set for ORACLE_SID, the connect will fail.
$> export ORACLE_SID=fred $> sqlplus scott/tiger
set ORACLE_SID appropriately, and connect
[oracle@vmlnx01 ~]$ ps -ef|grep pmon|grep -v grep oracle 4236 1 0 10:30 ? 00:00:00 ora_pmon_vlnxora1
Now set ORACLE_SID to a bogus value, and try to connect
[oracle@vmlnx01 ~]$ export ORACLE_SID='vlnxora1 [oracle@vmlnx01 ~]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:37 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now set ORACLE_SID to null, and try to connect
SQL> exit [oracle@vmlnx01 ~]$ export ORACLE_SID=FUBAR [oracle@vmlnx01 ~]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:57 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory Enter user-name:
Ok, that is how we get from the client connection request to the listener. What about the listener's part of all this?
[oracle@vmlnx01 ~]$ export ORACLE_SID= [oracle@vmlnx01 ~]$ sqlplus /scott/tiger SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:43:24 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-12162: TNS:net service name is incorrectly specified
Where as I am very much able to connect to DB through sqlplus but not through either TOAD or Java program.
I was very much able to connect earlier. I have made no changes to oracle.
output of "tnsping orcl" :
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localho
st) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)
output of "LSNRCTL SERVICES"
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2011 00:04
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
"DEDICATED" established:0 refused:0
The command completed successfully