Configure Static Service Information for a Database

The PMON process registers service information with the local listener by default. For most uses this is all that is needed to successfully connect to the database for use. If you are using a database release earlier than 8i or you need to remotely start the database from a tool other than Enterprise Manager, you will need to configure static service registration.

Below is the output from starting the listener. Note that currently the listener supports no services.

[oracle@ora1 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-APR-2010 11:02:50

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

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                06-APR-2010 11:02:50
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/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@ora1 ~]$

At this time the listener has no services registered because there are no databases running and there are no statically registered services. Below we start the database and check the status again.

oracle@ora1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 6 11:32:20 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             515903056 bytes
Database Buffers          327155712 bytes
Redo Buffers                5132288 bytes
Database mounted.
Database opened.
SQL> host 
[oracle@ora1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-APR-2010 11:33:11

Copyright (c) 1991, 2009, 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.1.0 - Production
Start Date                06-APR-2010 11:02:50
Uptime                    0 days 0 hr. 30 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1.localdomain)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora1 ~]$

After starting the database we can see that the database has been registered with the listener. We will use Oracle Net Manager to statically register the database with the listener. If your ORALCE_HOME and PATH are set properly you can start Oracle Net Manager with netmgr.

Under the Oracle Net Configuration node do the following.

1. Expand the Local node
2. Expand the Listeners node
3. Select the listener name in which you wish to statically register the database
4. Click the drop down and select Database Services

On the Database Services screen click the Add Database button.


Set appropriate values for Global Database Name (DB_NAME.DB_DOMAIN), Oracle Home Directory and SID (INSTANCE_NAME). Note if you are using Transparent Application Failover (TAF) or connect-time failover do not set the Global Database Name.

To save the changes select File and then Save Network Configuration.

Once you save the changes you can close Oracle Net Manager.

If your listener is running you will need to issue the reload command for the listener to see the changes made.

[oracle@ora1 ~]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-APR-2010 15:04:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@ora1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-APR-2010 15:04:22

Copyright (c) 1991, 2009, 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.1.0 - Production
Start Date                06-APR-2010 11:02:50
Uptime                    0 days 4 hr. 1 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1.localdomain)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora1 ~]$

You should now see that a service is registered. We will now restart the database to see the changes in the services.

[oracle@ora1 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-APR-2010 15:33:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ora1.localdomain, pid: 15259>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ora1.localdomain)(PORT=34804))
The command completed successfully
[oracle@ora1 ~]$

Even though the database is statically registered, it still registers dynamically with listener. In the case of both static and dynamic registration, the listener uses the dynamic registration information before using static service registration information.

Below are listing of the listener.ora file before and after changes were made using Oracle Net Manager.

Contents of the listener.ora file before changes.

[oracle@ora1 ~]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora1)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@ora1 ~]$

Contents of the listener.ora file after the changes.

[oracle@ora1 ~]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora1)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@ora1 ~]$

Keep in mind that for most uses, dynamic registration should be all that you need.

6 thoughts on “Configure Static Service Information for a Database”

  1. Thanks ERIC for the wonderful demonstration of static registration:-)

    But when we add more than one database to static registration it is giving error in netmgr as “please add listening locations” & in the listening window it is not allowing default port number 1521, is there any special reason for this?
    Can we do the static registration of more than one database on the same host with default listener?
    Kindly reply for the question, i have googled a lot for finding out this !

    Thanks a bundle!

  2. Hello Texas,

    The error you mentioned “please add listening location” has to do with the Listener itself and not a statically registered database. Is the listener set up correctly?

    To answer your question, the default listener or non default listener can have multiple statically registered databases. Below is the section from the listerner.ora in the post.

    SID_LIST_LISTENER = 
      (SID_LIST = 
        (SID_DESC = 
          (GLOBAL_DBNAME = orcl) 
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 
          (SID_NAME = orcl) 
        ) 
      ) 
    

    The SID_LIST section is for static registration and in the case above only one databas is statically registered. To statically register adidional database to this listener you can follow the steps outlined above or add the following after the (SID_NAME=) parameter.

    (SID_DESC = 
      (GLOBAL_DBNAME = <db_name>) 
      (ORACLE_HOME = <ORACLE_HOME_PATH) 
      (SID_NAME = <db_sid>) 
    

    Replace < db_name >, < ORACLE_HOME_PATH >, < db_sid > and with values for your environment.

    If you are still having problems you can either post your listener.ora file or send it to me at eric.jenkinson@oracledistilled.com and I will take a look to see what the problem.

    Thank you for visiting Oracle Distilled.

  3. Bulls eye Eric! thanks

    I did the changes as you mention in your post and it worked for me!
    Thanks again for quick reply!:-)

  4. Hi ;
    First my Thanks and really Well Explained.

    LISTENER.ORA having following entry

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME =orcltest)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME= myorcltest)
    )
    (SID_DESC =
    (SID_NAME = orclprod)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME= orclprod)
    when checking linrctl status i got

    Service “myorcltest” has 1 instance(s).
    Instance “orcltest”, status UNKNOWN, has 1 handler(s) for this service..
    Service “orcltest” has 1 instance(s).
    Instance “orcltest”, status READY, has 1 handler(s) for this service…
    Service “orcltestXDB” has 1 instance(s).
    Instance “orcltest”, status READY, has 1 handler(s) for this service…
    Service “orcltest_XPT” has 1 instance(s).
    Instance “orcltest”, status READY, has 1 handler(s) for this service…

    Why i am getting two different status ( what’s is the logic behind) here ..
    I know basically unknown means = static ready= dynamic
    In my case , please provide clear explanation ..

Leave a Reply

Your email address will not be published. Required fields are marked *