Monday, June 4, 2012

Oracle 11gR2 RAC: How to Configure a Second Listener on a Separate Network




This example will be for a two node cluster.

During the Grid Infrastructure installation, the (default) node VIP listener is always created referencing the public network on Oracle 11gR2. 

However, some systems may be attached to more than one public network, so there may be a need to create a second listener for this second public network. 

The purpose of this note is to explain how to add/create/configure a second listener on a second network in an 11.2 Grid Infrastructure environment.
This note does not apply to the binding of two public network interfaces for failover purposes, like IPMP or bonding etc.

Solution:
Assume the following names are defined for two nodes cluster (DNS or /etc/hosts):
# public host name and associated VIP on eth1
prodserver1             192.18.20.195
prodserver1-vip         192.18.20.175
prodserver2             192.18.20.175
prodserver2-vip         192.18.20.176


# scan name
prod_cluster-scan                               192.18.20.186
                                                192.18.20.187
                                                192.18.20.188

# host name for the 2nd public network and associated VIP on eth0
prodserver1-m              192.168.10.36
prodserver1-m-vip          192.168.10.205
prodserver2-m              192.168.10.37
prodserver2-m-vip          192.168.10.206


A listener has already been created on 192.18.20.x network, we are going to create a 2nd listener on 192.168.10.x network.

1. Ensure a VIP address is defined for the 2nd public network

In this example it's prodserver1-m-vip and prodserver2-m-vip (see above).

2. Create the CRS resource for the 2nd network

A new network and new vip resources for the 2nd network are created by using the '-k' switch:
as root user:
# srvctl add vip -n  -k  -A //[if1[|if2...]] [-v]

eg:
# srvctl add vip -n prodserver1 -k 2 -A 192.168.10.205/255.255.255.0/eth0
# srvctl add vip -n prodserver2 -k 2 -A 192.168.10.206/255.255.255.0/eth0
This command will implicitly create the dependent network resource.

From 11.2.0.2+, network resource can be created explicitly:
as root user:
# srvctl add network [-k ] -S //[if1[|if2...]] [-w ] [-v]

eg:
# srvctl add network -k 2 -S 192.168.10.0/255.255.255.0/eth0

Then add vip resource for the 2nd network:
# srvctl add vip -n prodserver1 -k 2 -A 192.168.10.205/255.255.255.0/eth0
# srvctl add vip -n prodserver2 -k 2 -A 192.168.10.206/255.255.255.0/eth0

The CRS resource 'ora.net2.network' and 2nd network vip should now have been created, they are both required for the 2nd listener, run the following command to verify:
# crsctl stat res -t |grep -E 'net|vip'
ora.net1.network
ora.net2.network
ora.scan1.vip
ora.scan2.vip
ora.scan3.vip
ora.prodserver1-m-vip.vip
ora.prodserver1.vip
ora.prodserver2-m-vip.vip
ora.prodserver2.vip





3. Create a new RAC listener using 'netca'
As the grid user invoke "netca" from the 11.2 GRID_HOME, the select "Cluster configuration" -> "Listener configuration" -> "Add", enter Listener name as required.  For example: "LISTENER_MON". 
On the next page, "Select Subnet", the 2nd subnet will appear in the drop down list. For example:
2 192.168.10.0/255.255.255.0
1 192.18.20.0/255.255.255.128

grid@prodserver1:+ASM1:/grid/app/grid/product/11.2.0.3/bin >
$ ./netca

Oracle Net Services Configuration:
Oracle Net Configuration Assistant is launched from Grid Infrastructure home. Network configuration will be clusterwide.
Configuring Listener:LISTENER_MON
prodserver1...
prodserver2...
Listener configuration complete.
Oracle Net Listener Startup:
    Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0
grid@prodserver1:+ASM1:/grid/app/grid/product/11.2.0.3/bin >

Select subnet 2 , select the protocol and define the Listener port as desired to complete the listener creation.
Note: When adding 2nd listener with srvctl on the same port, it may fail with:

$ ./srvctl add listener -l LISTENER_MON -p 1523 -k 2
PRCN-2061 : Failed to add listener ora.LISTENER_MON.lsnr
PRCN-2065 : Port(s) 1523 are not available on the nodes given
PRCN-2067 : Port 1523 is not available across node(s) "prodserver1
"

The workaround is to use "-s" option:

$ ./srvctl add listener -l LISTENER_MON -s -p 1523 -k 2

4. Verify the new RAC listener
The new listener will be started at the end of netca. This listener will listen on the IP address 192.168.10.205 (or 192.168.10.206 for node 2) associated with the 2nd network.  To check:
$ lsnrctl status LISTENER_MON

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-MAY-2012 22:54:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MON)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_MON
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                30-MAY-2012 22:52:49
Uptime                    0 days 0 hr. 1 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /grid/app/grid/product/11.2.0.3/network/admin/listener.ora
Listener Log File         /grid/app/gridbase/diag/tnslsnr/prodserver1/listener_mon/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MON)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.205)(PORT=1523)))
The listener supports no services
The command completed successfully
grid@prodserver1:+ASM1:/grid/app/grid/product/11.2.0.3/bin >

Please note the output for 11.2.0.2+ only shows two ADDRESS lists:


(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MON)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.205)(PORT=1523)))

5. Prepare the database instance for the new listener

To ensure that connections to the remote listener are only redirected to the local listener on the same network, LISTENER_NETWORKS parameter needs to be set in the pfile or spfile for the database instance.

As the Oracle user, assuming the RDBMS $ORACLE_HOME/network/admin/tnsnames.ora (or the $TNS_ADMIN/tnsnames.ora) has the following local and remote listener defined,  prepare similar content for each node using node specific VIP name (or VIP IP adddress). For example, on node1:
#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
  )


proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1523)))
   )

On node 2:
#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
  )

proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523)))
   )

Make certain your remote_listener is set to the SCAN address:
alter system set remote_listener=’prod_cluster-scan.domain.com:1521’ scope=both sid=’*’;

Update your local listener for each database to point to the Description address as outlined in the Setting up SCAN notes from Oracle:

alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.18.20.175)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.205)(PORT=1523)))) scope=both sid=’proddb1’;


alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.18.20.176)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.206)(PORT=1523)))) scope=both sid=’proddb1’;

At this point,   I added the following to my tnsnames.ora file as the user oracle:
proddb_mon =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

The database parameter LISTENER_NETWORKS needs to be set to:
alter system set LISTENER_NETWORKS='((NAME=network1)(LOCAL_LISTENER=proddb_LOCAL)(REMOTE_LISTENER=prod_cluster-scan.domain.com))','((NAME=network2)(LOCAL_LISTENER=proddb_mon_LOCAL)(REMOTE_LISTENER=LISTENER_MON_PRODDB))';
Without using sid="', above change will be applied to all instances.
Please note:
 
1. Listeners specified by the LISTENER_NETWORKS parameter should not be used in the LOCAL_LISTENER and REMOTE_LISTENER parameters. Otherwise, cross registration will happen and connections will be redirected cross networks.

2. Due to unpublished bug 8678541 INCORRECT ENDPOINTS OF LISTENER ON THE SECOND NETWORK, the 2nd listener by default will also listen on the IP address associated with the host name of the node (in this example 192.18.20.195). Although it is invalid, it should not cause any issue. This has been fixed in 11.2.0.2.

3. Only 1 SCAN name is defined for a RAC cluster, the 2nd network can not use the same SCAN name.


Appendix:   Examples of the setup tnsnames, listener, and settings


AS ORACLE USER SQLPLUS ON DATABASE
Database Parameters:

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      proddb, proddb.domain.com
SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string      ((NAME=network1)(LOCAL_LISTENE
                                                 R=proddb_LOCAL)(REMOTE_LISTENER
                                                 =prod_cluster-scan.domain
                                                 .com)), ((NAME=network2)(LOCAL
                                                 _LISTENER=proddb_mon_LOCAL)(REM
                                                 OTE_LISTENER=LISTENER_MON_OP1V
                                                 C))
local_listener                       string      (DESCRIPTION=
                                                 (ADDRESS_LIST=(ADDRESS=(PROTOC
                                                 OL=TCP)(HOST=192.18.20.175)(PO
                                                 RT=1521))

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.10.205)(PORT=1523))))
remote_listener                      string      prod_cluster-scan.domain.
                                                 int:1521
SQL>




AS ORACLE USER

Node 1:

$ cat tnsnames.ora
proddb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb_mon =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb1)
     )
    )

proddb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb2)
     )
    )
#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
  )


proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1523)))
   )



Node 2:
$ cat tnsnames.ora
proddb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb_mon =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb1)
     )
    )

proddb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb2)
     )
    )

#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
  )

proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523)))
   )




GRID USER


$ cat listener.ora
LISTENER_PRODDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_PRODDB))))               # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PRODDB=ON                # line added by Agent
oracle@prodserver1:proddb1:/proddb/app/oracle/product/11.2.0.3/network/admin >


AS GRID USER:
cat listener.ora

# listener.ora.prodserver1 Network Configuration File: /grid/app/grid/product/11.2.0.3/network/admin/listener.ora.prodserver1
# Generated by Oracle configuration tools.
LISTENER_MON=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MON))))            # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN3 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))
    )
  )

LISTENER_SCAN2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))
    )
  )

ADR_BASE_LISTENER_SCAN3 = /grid/app/grid

LISTENER_SCAN1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
    )
  )

ADR_BASE_LISTENER_SCAN2 = /grid/app/grid

ADR_BASE_LISTENER_SCAN1 = /grid/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_MON=ON          # line added by Agent



AS GRID USER:

cat sqlnet.ora

# sqlnet.ora.prodserver1 Network Configuration File: /grid/app/grid/product/11.2.0/network/admin/sqlnet.ora.prodserver1
# Generated by Oracle configuration tools.



NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /grid/app/gridbase

No comments: