Re: dynamic service registration

"Ed Stevens" <nospam_at_noway.nohow> wrote in message news:6n8m505961qjtaqk45g436r6vr9n1jfsul_at_4ax.com...
> Platform: Oracle 9.2 EE on Solaris 8
>
> The last couple of days I've been playing around with the tns config
> on a new 'sandbox' server we just put up. It's all working, but I've
> been tweaking a few things to see what I can learn. What I've learned
> is that I didn't understand as well as I thought.
>
> For instance, given the following:
>
> -----------------
> listener.ora (host name masked for public viewing)
> -----------------
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = VITSB01))
> )
> )
> )
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = VITSB01)
> (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
> (GLOBAL_DBNAME = VITSB01)
> )
> )
>
> -----------------
> initVITSB01.ora
> -----------------
> db_domain=""
> db_name="VITSB01"
> instance_name=VITSB01
> service_names=VITSB01
>
> listener status give the following. Notice that there is one instance
> of service VITSB01 listed, and in the Listening Endpoints
> Summary...the host is listed by IP address. :
>
> LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 19-MAR-2004
> 09:52:41
>
> Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
> Connecting to
> (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxx)(PORT=1521)))
> STATUS of the LISTENER
> ------------------------
> Alias LISTENER
> Version TNSLSNR for Solaris: Version 9.2.0.1.0 -
> Production
> Start Date 19-MAR-2004 09:41:08
> Uptime 0 days 0 hr. 11 min. 33 sec
> Trace Level off
> Security OFF
> SNMP OFF
> Listener Parameter File
> /u01/app/oracle/product/9.2.0.1.0/network/admin/listener.ora
> Listener Log File
> /u01/app/oracle/product/9.2.0.1.0/network/log/listener.log
> Listening Endpoints Summary...
>
> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nn.nnn.nnn.nnn)(PORT=1521)))
> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=VITSB01)))
> Services Summary...
> Service "VITSB01" has 1 instance(s).
> Instance "VITSB01", status UNKNOWN, has 1 handler(s) for this
> service...
> The command completed successfully
>
>
>
> And yet, on a system that this was modeled after, I have the
> following
>
> -----------------
> listener.ora (host name masked)
> -----------------
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
> )
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = CMPM1240))
> )
> )
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
> (SID_NAME = CMPM1240)
> )
> )
>
> -----------------
> initVITSB01.ora
> -----------------
> db_name = "CMPM1240"
> service_names = CMPM1240
> instance_name = CMPM1240
> db_domain=""
>
> And lsnrctl status gives this. Notice that there are two instances of
> service CMPM1240 listed, and in the Listening Endpoints Summary...the
> host is listed by server name. :
>
> LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 19-MAR-2004
> 09:58:36
>
> Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
> Connecting to
> (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxx)(PORT=1521)))
> STATUS of the LISTENER
> ------------------------
> Alias LISTENER
> Version TNSLSNR for Solaris: Version 9.2.0.1.0 -
> Production
> Start Date 04-MAR-2004 21:00:02
> Uptime 14 days 12 hr. 58 min. 34 sec
> Trace Level off
> Security OFF
> SNMP OFF
> Listener Parameter File
> /u01/app/oracle/product/9.2.0/network/admin/listener.ora
> Listener Log File
> /u01/app/oracle/product/9.2.0/network/log/listener.log
> Listening Endpoints Summary...
> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxx)(PORT=1521)))
> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=CMPM1240)))
> Services Summary...
> Service "CMPM1240" has 2 instance(s).
> Instance "CMPM1240", status UNKNOWN, has 1 handler(s) for this
> service...
> Instance "CMPM1240", status READY, has 1 handler(s) for this
> service...
> The command completed successfully

The status "UNKNOWN" is what you get as a result of declaring the SID in the SID_LIST section of the listener.ora (a "static declaration"). You've told the listener to handle requests for the instance, but it has no idea of whether the instance is actually running or not.

The status "READY" is what you get as a result of automatic instance registration: the listener knows to handle requests for the instance, because the instance itself tells it to. And it knows full well that the instance is up and running, because if it weren't it wouldn't have heard from PMON in the first place.

Since you are running on 9i Release 2, try logging in to your first instance and issuing the command:

'alter system register'.

Then check lsnrctl services.

The command forces PMON to register with the listener at your request. Without it (ie, anything earlier than 9i), you have to wait for PMON to re-register every 5 minutes.

For example, if your instance is running, and you stop your listener and then re-start it, you will not see any 'READY' statuses, because the bounce of the listener wiped the registration information, and PMON is still counting down the seconds to a re-register.

HTH
Regards
HJR Received on Fri Mar 19 2004 - 14:02:07 CST