During the preparation for a tutorial, I stumbled on the following error after attempting to connect to Oracle from the hosting XP machine using SQLTools:
TNS-12505: TNS:
listener does not currently know of SID given in connect descriptor
Bear in mind that the VM had been restarted for the first time after its installation.
Detailed messages for listeners can be found in the log file
/u01/app/oracle/diag/tnslsnr/localhost/listener/trace/listener.log
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
01-AUG-2010 19:57:54 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\Program?Files\SQLTools?1.5\SQLTools.exe)(HOST=CHUCKIE)(USER=Ray))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.1)(PORT=3725)) * establish * orcl
Below is the original listener file before I applied changes
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
The listener file was left unconfigured after the installation. No changes were made to it.
The first item to be removed is the IPC protocol (highlighted) as we are not connecting to Oracle via
IPC.
Secondly, because I we have no DNS server and limited to the host and VM we will
change the HOST (highlighted) to a hardcoded IP of the VM..
In addition to this, we remove the
ADR_BASE line to stop the listener from writing message logs to xml.
Lastly, we add the
SID_LIST_LISTENER list to statically register the instance and allow for remote configuration of the database using Oracle Enterprise Manager.
The listner configuration now looks like this:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.129)(PORT = 1521))
)
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME = LISTENER)
(SID_NAME = ORCL)
)
)
All that needs to be done is to stop and restart the listener
[oracle@localhost bin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-AUG-2010 20:39:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))
The command completed successfully
[oracle@localhost bin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-AUG-2010 20:39:53
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 01-AUG-2010 20:39:53
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))
Services Summary...
Service "LISTENER" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
*status UNKNOWN means the listener is statically registered.
Now to connect remotely
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
Not good. Something is wrong. First thing to check is if its possible to ping the listener.
[oracle@localhost admin]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-AUG-2010 21:43:46
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.129)(PORT = 1521)) (CONNECT_DATA = (SID = orcl) (SERVER = DEDICATED)))
OK (10 msec)
No problem there. Next to check the logs.
Sun Aug 01 21:41:10 2010
01-AUG-2010 21:41:10 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Ray))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.1)(PORT=4842)) * establish * orcl * 0
Sun Aug 01 21:43:16 2010
01-AUG-2010 21:43:16 * ping * 0
Sun Aug 01 21:43:41 2010
01-AUG-2010 21:43:41 * ping * 0
01-AUG-2010 21:43:46 * ping * 0
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
After much searching on the web, I found out that the problem is the
SID in the listener is UPPER CASE.
(SID_NAME = ORCL)
Change it to:
(SID_NAME = orcl)
We restart and connect again
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.129)(PORT=1521)))
Services Summary...
Service "LISTENER" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost admin]$
SELECT * FROM global_name
GLOBAL_NAME
ORCL.LOCALDOMAIN
Connected Successfully!
