Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, September 16, 2015

Configure the network environment to allow connections to multiple databases

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
------------------------------------ ----------- ------------------------------
db_name                 string     MYDB


Documentation:
Oracle Database Net Services Reference --> Chapter-1
 

No comments: