Monday, February 18, 2013

"The Listener Supports No Services" and Using "Alter System Register" Command

We have started oracle database, which is followed by starting the listener.  Then we checked listener's status.  As you can see below, at beginning, it reports that "The listener supports no services."  However, after exactly 60 seconds, it reports that two services were supported (i.e., ps6stg7 and ps6stg7XDB).

In this article, we explain what happened and will show you how to force service be registered with the listener.  So, your application can find the services sooner.

-bash-3.2$ $ORACLE_HOME/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-FEB-2013 21:07:57

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-FEB-2013 21:06:58
Uptime                    0 days 0 hr. 0 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /scratch/aime1/app/oracle11.2.0.3.0/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /scratch/aime1/app/oracle11.2.0.3.0/diag/tnslsnr/myserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.us.oracle.com)(PORT=1521)))
The listener supports no services
The command completed successfully
-bash-3.2$ $ORACLE_HOME/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-FEB-2013 21:07:59

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-FEB-2013 21:06:58
Uptime                    0 days 0 hr. 1 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /scratch/aime1/app/oracle11.2.0.3.0/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /scratch/aime1/app/oracle11.2.0.3.0/diag/tnslsnr/myserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.us.oracle.com)(PORT=1521)))
Services Summary...
Service "ps6stg7" has 1 instance(s).
  Instance "ps6stg7", status READY, has 1 handler(s) for this service...
Service "ps6stg7XDB" has 1 instance(s).
  Instance "ps6stg7", status READY, has 1 handler(s) for this service...
The command completed successfully

PMON


In our case, we have executed the following shell commands in a script named startDB.sh:

#!/bin/bash
. /scratch/aime1/scripts/db/setEnvPs6stg7.sh

echo "### Restart Oracle databse instance $ORACLE_SID ###"
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
startup;
EOF
echo "Done"

echo "### Starting tns listener ###"
$ORACLE_HOME/bin/lsnrctl start
sleep 20;
$ORACLE_HOME/bin/lsnrctl stat

However, when the last listener status command was executed, it showed:
"The listener supports no services."

Then I did some research and found out that what happened behind the scene is that:  PMON process wakes up at every 60 seconds and provide (or register) information to the listener.  So, not until 60 seconds later, did listener's status show it supported two services.

Alter System Register


Without waiting for 60 seconds, you can use 
  • alter system register command
This command forces the registration of database information to the listener.  So, I decided to give it a try and I added the new sql command in my script as follows:

$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
startup;
alter system register;
EOF

However, this didn't work.  After some detective works, finally I have figured out that I need to start the listener first before starting the database.  In other words, when the command tried to manually register server information with the listener.  The listener needs to be up and running.  So, after I have moved "lsnrctl start" command before the database start command, it then worked.  Also, you don't need to sleep for 20 seconds before checking the listener's status.

References

No comments: