Friday, December 24, 2010

Creating listeners on the private network interfaces of RAC nodes or use that Exadata Infiniband network

In general usage, everyone configures tns listeners on the public interfaces of RAC nodes so that clients can connect through the public network. Conventionally the private network interfaces are used for the interconnect traffic so most people do not open them to the application because the application traffic may interfere with the interconnect messaging.

But what if we have a high-speed and high-bandwidth interconnect network that some applications can also use for fast communication to the nodes? Can we create tns listeners on the private interfaces, if we can how? This high-speed interconnect network is especially true for Exadata where we have an Infiniband private network that is used for RAC interconnect and for the communication between the storage nodes and the database nodes. Since it is a high-speed low-latency network the application servers or ETL servers can connect to that network and use it for fast data transfer.

To do this we need to create tns listeners on the private interfaces so that clients can connect through sqlnet. The following steps will show how I did this. I did this configuration on a half rack Database Machine but since I do not have access to that now the example here is based on a test Virtualbox system (which runs 11.2.0.2 RAC) so Infiniband is not here but the steps are the same for Exadata. Also DNS is not used, all addresses are defined in the /etc/hosts files of the nodes.

Here is my /etc/hosts file for this setup.


127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6


# Public
192.168.56.2   rac1.localdomain        rac1
192.168.56.3   rac2.localdomain        rac2
# Private
192.168.0.101   rac1-priv.localdomain   rac1-priv
192.168.0.102   rac2-priv.localdomain   rac2-priv
# Virtual
192.168.56.4   rac1-vip.localdomain    rac1-vip
192.168.56.5   rac2-vip.localdomain    rac2-vip
# SCAN
192.168.56.6   rac-scan.localdomain rac-scan

I have two nodes, rac1 and rac2. The private network is 192.168.0 and the public network is 192.168.56. After the default installation I have the default listeners created on the public interfaces, there is only one SCAN address as this is a test setup.

Assume I have an ETL server that is connected to the private network which needs to connect to the database through the private interface. What I need is a listener per node that is listening on the private IP addresses.

If you start with netca ( from the grid home because that is where the listeners run on 11.2) and try to create the listeners you will see that you will not be able to select the private network.


It will show only the public network because this selection is based on your virtual IP definitions. Since I do not have any VIPs on the private network I do not see it.

So the first step is to create VIPs on the private interfaces. I start by adding the new VIPs to the /etc/hosts files. These lines should be added to both nodes' /etc/hosts file.


# Private VIPs
192.168.0.103   rac1-ib.localdomain rac1-ib
192.168.0.104   rac2-ib.localdomain rac2-ib


With root I run "srvctl add vip" from the grid home to create the VIPs.


[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@rac1 bin]# ./srvctl add vip -n rac1 -A 192.168.0.103/255.255.255.0/eth1 -k 2
[root@rac1 bin]# ./srvctl add vip -n rac2 -A 192.168.0.104/255.255.255.0/eth1 -k 2

I created a VIP for each node on the eth1 interface which is the private interface and I have specified "-k 2" to indicate that the network number is 2. You can use "srvctl add vip -h" to see what options you have.

At this step if you look at the "ifconfig -a" output you will not see the new VIPs up because we have not started them up yet. Let's start them now.

[root@rac1 bin]# ./srvctl start vip -i rac1-ib

Now you will see the new IP up in the "ifconfig -a" output. This is the related line.


eth1:2    Link encap:Ethernet  HWaddr 08:00:27:8B:69:FB
          inet addr:192.168.0.103  Bcast:192.168.0.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

We need to start the new VIP for the second node also. Go to the second node and repeat the same for that.

[root@rac2 bin]# ./srvctl start vip -i rac2-ib

We can now start netca and we see the new subnet there. You can create the listener by selecting the new subnet. I named mine as LISTENER_IB and chose 1522 as the port number.


After netca completes here is what I see on rac1.


[oracle@rac1 ~]$ lsnrctl status listener_ib


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-DEC-2010 13:36:20


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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_IB)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_IB
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-DEC-2010 13:34:46
Uptime                    0 days 0 hr. 1 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_ib/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_IB)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.103)(PORT=1522)))
The listener supports no services
The command completed successfully

The listener is up and running but it does support any services yet. Here is my init.ora parameters related to the listener.

SQL> show parameter listen


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=192.
                                                 168.56.4)(PORT=1521))))
remote_listener                      string      rac-scan:1521

I can change the local_listener parameter and add this listener so that my database registers with it. After that as the default installation sets the SCAN listener as the remote_listener SCAN will be able to direct connections to this listener as well. But, there is a problem with this. What happens if SCAN directs the connection from the ETL server to the public interface instead of the private one? Or vice-versa, what happens if it directs connections from the public network clients to the private interface? Users will get errors because they cannot reach the private network and the ETL server will get errors because it cannot reach the public network.

The correct way to register my database to the listeners is to use the listener_networks parameter. listener_networks is a new 11.2 parameter and serves the purpose of cross-registration when you have listeners on multiple networks. Basically with it you can say, "register my local listeners on the public interfaces to the SCAN listener, register my local listeners on the private interface to each other".

This way clients using SCAN will connect to the public interfaces and clients using the private network will connect to the private interfaces. Let's do it now.

Not to clutter the listener parameters with long tns descriptions let's add the definitions to the tnsnames.ora file and use the names instead. On both nodes's tnsnames.ora file residing in the database home I add these lines. Remember to change the hostnames to rac2 for the ones other ORCL_IB when editing the file on rac2.


ORCL_IB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-ib)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-ib)(PORT = 1522))
  )


ORCL_PUBLIC_LOCAL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
  )


ORCL_PRIVATE_LOCAL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-ib)(PORT = 1522))
  )

ORCL_PUBLIC_LOCAL will be used as the local listener for the public network, ORCL_PRIVATE_LOCAL will be used as the local listener for the private network, ORCL_IB will be used as the remote listener for the private network so that the local private listeners can register to each other and the SCAN address will be used as the remote listener for the public network.

Now time to change the parameters.

SQL> alter system set listener_networks='((name=public_network)(local_listener=orcl_public_local)(remote_listener=rac-scan:1521))','((name=priv_network)(local_listener=orcl_private_local)(remote_listener=orcl_ib))';

System altered.

SQL> alter system set remote_listener='';

System altered.

SQL> show parameter listen

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string      ((name=public_network)(local_l
                                                 istener=orcl_public_local)(rem
                                                 ote_listener=rac-scan:1521)),
                                                 ((name=priv_network)(local_lis
                                                 tener=orcl_private_local)(remo
                                                 te_listener=rac-scan:1521))
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=192.
                                                 168.56.4)(PORT=1521))))
remote_listener                      string

Now let's see what is registered to the new listener listener_ib.

[oracle@rac1 ~]$ lsnrctl status listener_ib

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-DEC-2010 15:03:17

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_IB)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_IB
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-DEC-2010 13:34:46
Uptime                    0 days 1 hr. 28 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_ib/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_IB)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.103)(PORT=1522)))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl1", status READY, has 2 handler(s) for this service...
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

Both instances are registered and can be served by this listener. Also have a look at the scan listener to make sure we did not make a mess.

[oracle@rac1 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-DEC-2010 15:04:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-DEC-2010 12:06:02
Uptime                    0 days 2 hr. 58 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.6)(PORT=1521)))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

It is OK too.

Now we can use this tnsnames entry to connect through the private network.

ORCL_ETL =
  (DESCRIPTION =
    (LOAD_BALANCE=on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-ib)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-ib)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

It took quite a while for me to get this in writing, if you use this procedure and see problems or if you think there are points not explained clearly, let me know so I can edit the post, this way it will be more useful to others who may need it.

0 comments: