How does an instance registers with the listener !!!

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

1. Static Registration
2. 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

[oracle@rac1 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:

[oracle@rac1 admin]$ sqlplus dbaoper@bharatdb

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,

[oracle@rac1 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.

[oracle@rac1.localdomain 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

[oracle@rac1 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.

[oracle@rac1] $ps –ef | grep pmon

Database is down.

Start the database now

[oracle@rac1] sqlplus / as sysdba
 Sql> startup

Now check the listener status again

[oracle@rac1 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,

[oracle@rac1 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,

[oracle@rac1 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,

1. Go with static registration process or
2. 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:

[oracle@rac1 admin]$ cat tnsnames.ora
 listener_bharatdb=(ADDRESS = (PROTOCOL=TCP)(HOST=rac1)(PORT=1522))

Start the database & Check the listener status now,

[oracle@rac1 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 🙂