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.
great man. you rocked ……
Thank you for visiting the site. I am happy you found the post useful.
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!
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.
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.
Bulls eye Eric! thanks
I did the changes as you mention in your post and it worked for me!
Thanks again for quick reply!:-)
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 ..
謝謝分享