How to configure the services with the listener dynamically and statically.
It is possible to start and use a listener with no configuration. This default listener has a name of LISTENER, supports no services on startup, and listens on the following TCP/IP protocol address:
[oracle@collabn1 admin]$ ps -aef| grep tns
root 15 2 0 Sep13 ? 00:00:00 [netns]
oracle 25719 15337 0 21:20 pts/4 00:00:00 grep tns
[oracle@collabn1 admin]$ netstat -an | grep 1521
unix 3 [ ] STREAM CONNECTED 160152191
unix 3 [ ] STREAM CONNECTED 160152192
unix 3 [ ] STREAM CONNECTED 160152193
unix 3 [ ] STREAM CONNECTED 160152194
No listener.ora or sqlnet.ora
[oracle@collabn1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 21:20:12
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.2/SHAIKPROD/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 16-SEP-2015 21:20:12
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/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@collabn1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 21:22:43
Copyright (c) 1991, 2009, 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.1.0 - Production
Start Date 16-SEP-2015 21:20:12
Uptime 0 days 0 hr. 2 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))
The listener supports no services
The command completed successfully
These services can be configured statically in the listener.ora file or they can be dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the PMON process, an instance background process of each database instance that is configured in the database initialization parameter file. Dynamic service registration does not require any manual configuration in the listener.ora file.
When services are configured statically, a listener starts a dedicated server when it receives a client request. If the instance is not up, then the server returns an Oracle not available error message.
If you expect the listener to handle large volumes of concurrent connection requests, then you can specify a listener queue size for its TCP/IP or IPC listening endpoints.
Setting Initialization Parameters for Dynamic Service Registration
To ensure service registration works properly, the initialization parameter file should contain the following parameters:
- SERVICE_NAMES for the database service name
- INSTANCE_NAME for the instance name
- LOCAL_LISTENER for the local listener
- REMOTE_LISTENER for the remote listener, if any
Demo for dynamic registration:
Without any listener.ora or sqlnet.ora files in the $TNS_ADMIN:
SHAIKDB>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))';
System altered.
SHAIKDB>!
[oracle@collabn1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 21:35:30
Copyright (c) 1991, 2009, 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.1.0 - Production
Start Date 16-SEP-2015 21:20:12
Uptime 0 days 0 hr. 15 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))
Services Summary...
Service "SHAIKDB.SHAIKSAMEER" has 1 instance(s).
Instance "SHAIKDB", status READY, has 1 handler(s) for this service...
The command completed successfully
Below are the parameters on my instance:
SHAIKDB>show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))
remote_listener string
SHAIKDB>show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string SHAIKDB
SHAIKDB>show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string shaikdb
SHAIKDB>
Let’s create another database MYDB and register it with the listener dynamically.
[oracle@collabn1 ~]$ dbca -createdatabase -silent -sid MYDB -gdbname MYDB -templatename General_Purpose.dbc -datafiledestination /u01/app/oracle/mydb
Enter SYS user password:
Enter SYSTEM user password:
Copying database files
1% complete
3% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/MYDB/MYDB.log" for further details.
After the database creation listener automatically registered the database dynamically.
[oracle@collabn1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 21:55:10
Copyright (c) 1991, 2009, 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.1.0 - Production
Start Date 16-SEP-2015 21:20:12
Uptime 0 days 0 hr. 34 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1521)))
Services Summary...
Service "MYDB" has 1 instance(s).
Instance "MYDB", status READY, has 2 handler(s) for this service...
Service "MYDBXDB" has 1 instance(s).
Instance "MYDB", status READY, has 1 handler(s) for this service...
Service "SHAIKDB.SHAIKSAMEER" has 1 instance(s).
Instance "SHAIKDB", status READY, has 1 handler(s) for this service...
The command completed successfully
Instances parameters for MYDB:
SHAIKDB>show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string MYDB
SHAIKDB>show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string MYDB
SHAIKDB>show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=
192.168.78.61)(PORT=1521))
SHAIKDB>show parameter remote_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string collabn-cluster-scan.shaiksam
eer:1521
Now let’s do the static registration:
Stop the default listener:
[oracle@collabn1 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 22:09:36
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
Let’s create the listener.ora file and configure MYDB to listen on 1621 and SHAIKDB on 1522
[oracle@collabn1 admin]$ vi listener.ora
SID_LIST_SHAIKDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SHAIKDB.shaiksameer)
(SID_NAME = SHAIKDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.2/SHAIKPROD)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_dgmgrl.shaiksameer)
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.2/SHAIKPROD)
)
(SID_DESC =
(GLOBAL_DBNAME = MYDB)
(SID_NAME = MYDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.2/SHAIKPROD)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1621))
)
)
SHAIKDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522))
)
)
[oracle@collabn1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 22:13:46
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.2/SHAIKPROD/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.2/SHAIKPROD/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1621)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 16-SEP-2015 22:13:46
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.2/SHAIKPROD/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1621)))
Services Summary...
Service "MYDB" has 1 instance(s).
Instance "MYDB", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl_dgmgrl.shaiksameer" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@collabn1 admin]$ lsnrctl start shaikdb
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 22:13:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.2/SHAIKPROD/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.2/SHAIKPROD/network/admin/listener.ora
Log messages written to /u02/app/oracle/listener/trace/shaikdb.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias shaikdb
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 16-SEP-2015 22:13:55
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.2/SHAIKPROD/network/admin/listener.ora
Listener Log File /u02/app/oracle/listener/trace/shaikdb.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 1 instance(s).
Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@collabn1 admin]$ tnsping mydb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 22:15:34
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
OK (0 msec)
[oracle@collabn1 admin]$ tnsping shaikdb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 22:15:39
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SHAIKDB.shaiksameer)))
OK (0 msec)
[oracle@collabn1 admin]$ sqlplus test/test@shaikdb
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 16 22:15:50 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SHAIKDB>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@collabn1 admin]$ sqlplus system@mydb
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 16 22:16:10 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SHAIKDB>show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
No comments:
Post a Comment