Configuring Oracle NET Listener

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!