If you check the status of the listener, some times we will see as instance status is UNKNOWN or instance is READYÖ. So what are these messages?ÖWhen do they appearÖ?
Lets explore it now,
There are basically 2 ways of getting a database registered with listener
- Static Registration
- Dynamic Registration
Static Registration:
This is the first and old method of registering a database with the listener.
In this scenario instance is listed in the SID_LIST section of listener.ora file like below,
LISTENER_bharatdb =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1522))
)
)
SID_LIST_LISTENER_bharatdb =
(SID_LIST =
(SID_DESC =
(SID_NAME = bharatdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
Start the listener
[[email protected] admin]$ lsnrctl start LISTENER_bharatdb
LSNRCTL for Linux: Version 11.2.0.3.0 ñ Production on 22-AUG-2013 06:12:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please waitÖ
TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener_bharatdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
óóóóóóóó
Alias LISTENER_bharatdb
Version TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
Start Date 22-AUG-2013 06:12:07
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.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener_bharatdb/alert/log.xml
Listening Endpoints SummaryÖ
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Services SummaryÖ
Service ìbharatdbî has 1 instance(s).
Instance ìbharatdbî, status UNKNOWN, has 1 handler(s) for this serviceÖ
The command completed successfully
If you can see, listener started stating that it knows there is a services by name bharatdb but its status is unknown.
Check connection to database:
[[email protected] admin]$ sqlplus [email protected]
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 22 06:15:47 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
We got an error stating that database isnít available.
Check for pmon process to confirm it,
[[email protected] admin]$ ps -ef | grep pmon
oracle 3830 2218 0 06:16 pts/1 00:00:00 grep pmon
DATABASE IS DOWN !!!!!!
Now we understood why the listener reported that database knows a service but its status is UNKNOWN.Itís because we have hardcoded the instance name in the listener file.
Dynamic Registration:
Dynamically registering the instance with the listener helps us to solve the above issue.
By default instance automatically registers with the default listener named LISTENER which will be listening on port 1521.
With this, it is no longer necessary to list the database instance in the listener.ora file. Instead, the database instance will contact the listener directly and register itself. PMON process is responsible for dynamic registration.
Let check this out,
I have removed my listner.ora by renaming it.
[[email protected] admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@ rac1.localdomain admin]$ ls -lrt
total 24
-rw-rñrñ. 1 oracle oinstall 205 May 11 2011 shrept.lst
drwxr-xr-x. 2 oracle oinstall 1024 Aug 21 06:37 samples
-rwóó-. 1 oracle oinstall 310 Aug 22 04:12 listener.ora_orig
NOTE: Default listener is able to run without listener.ora file.
Start the listener
[[email protected] admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 ñ Production on 22-AUG-2013 05:30:47
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please waitÖ
TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
Log messages written to /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.31)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
óóóóóóóó
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
Start Date 22-AUG-2013 05:30:47
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/10.0.0.31/listener/alert/log.xml
Listening Endpoints SummaryÖ
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.31)(PORT=1521)))
The listener supports no services
The command completed successfully
Observe that the listener has started but doesnít know about any services i.e instances here
Check the availability of the database.
[[email protected]] $ps ñef | grep pmon
Database is down.
Start the database now
[[email protected]] sqlplus / as sysdba
Sql> startup
Now check the listener status again
[[email protected] admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 ñ Production on 22-AUG-2013 05:34:48
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
óóóóóóóó
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
Start Date 22-AUG-2013 05:30:47
Uptime 0 days 0 hr. 4 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints SummaryÖ
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Services SummaryÖ
Service ìbharatdbî has 1 instance(s).
Instance ìbharatdbî, status READY, has 1 handler(s) for this serviceÖ
The command completed successfully
Now you can see that listener knows about the service bharatdb and the status is shown as Ready. This didnít come from listener file because I have removed it.
Also notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready.
This works only if the listener is running on default port 1521.
Lets change the port number and verify dynamic registration
$cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1522))
)
)
Start the listener,
[[email protected] admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 ñ Production on 22-AUG-2013 05:46:13
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please waitÖ
TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
óóóóóóóó
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
Start Date 22-AUG-2013 05:46:13
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.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints SummaryÖ
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
The listener supports no services
The command completed successfully Start the database
Start the database
SQL> startup
ORACLE instance started.
Total System Global Area 492781568 bytes
Fixed Size 2229504 bytes
Variable Size 343935744 bytes
Database Buffers 134217728 bytes
Redo Buffers 12398592 bytes
Database mounted.
Database opened.
Check the listener status,
[[email protected] admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 ñ Production on 22-AUG-2013 05:46:35
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
óóóóóóóó
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 ñ Production
Start Date 22-AUG-2013 05:46:13
Uptime 0 days 0 hr. 0 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints SummaryÖ
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
The listener supports no services
The command completed successfully
If you can see, database is unable to locate the listener and couldnít register itself.
So how to solve this now !!!!!
We have 2 options,
Go with static registration process or
Set LOCAL_LISTENER initialization parameter
The LOCAL_LISTENER parameter:
Setting this parameter will allow the database to register itself with a listener running on non-default port.
This parameter specifies a network name that should be resolved through tnsnames.ora
Checking for this parameter in my database,
SQL> sho parameter db_name
NAME TYPE VALUE
óóóóóóóóóóóó óóóñ óóóóóóóóóó
db_name string bharatdb
SQL> sho parameter local_listener
NAME TYPE VALUE
óóóóóóóóóóóó óóóñ óóóóóóóóóó
local_listener string
Here I donít have this parameter set. So lets set it,
Option 1:
Provide the network address details directly
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=rac1)(PORT=1522))’ scope=both;
System altered.
Option 2:
Set a value and resolve it through tnsentry
SQL> alter system set local_listener=listener_bharatdb scope=both;
System altered.
Now resolve listener_bharatdb in tnsnames.ora
In tnsnames.ora:
[[email protected] admin]$ cat tnsnames.ora
listener_bharatdb=(ADDRESS = (PROTOCOL=TCP)(HOST=rac1)(PORT=1522))
Start the database & Check the listener status now,
[[email protected] admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 22-AUG-2013 05:59:43
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date 22-AUG-2013 05:46:13
Uptime 0 days 0 hr. 13 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Services Summary…
Service “bharatdb” has 1 instance(s).
Instance “bharatdb”, status READY, has 1 handler(s) for this service…
The command completed successfully
Here also you can see listener status as READY because, database itself is registering with the listener.
Another case,
If database is started before listener, database couldnít find any listener to register with. So we need to force a registration of the instance as below
Ofcourse, LOCAL_LISTENER param should be set for this to work.
Sql> alter system register.
System altered.
So now when you see an instance with status ìREADYî, you know that PMON communicates with the listener. An instance with status UNKNOWN is statically registered.
Hope this post cleared few of your doubts 🙂
