Thursday, November 25, 2010

Integrate Oracle 10g/11gr2 client or RAC RDBMS with 11gR2 Clusterware

The Single Client Access Name is a new feature that simplifies acces to a cluster database.
In versions proir to Oracle RDBMS 11.2, an entry in the tnsnames.ora file for a n-npde RAC database always referenced all nodes in the address_list section.
From Oracle RDBMS 11.2 the RAC databases can be covered with the SCAN method. A SCAN method is a hostname based method on 1 - 3 virtual IP-addresses based in the DNS of the organisation. The SCAN method solved an TNS ENTRY editing issue in the TNSNAMES.ORA, on actions of adding and removing nodes from the cluster.
SCAN method makes my life easier in managing the Oracle sqlnet configurations for the Oracle cluster databases.



The new Oracle client connect string in the TNSNAMES.ora

Fisrt the cluster database setup prior 11gR2;
DB_JOORDS =
  (DESCRIPTION =
     (ADDRESS_LIST =
  (LOAD_BALANCE = ON)
  (FAILOVER = ON)
  (
          (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.rac.joords.nl )(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.rac.joords.nl )(PORT = 1521))
         ) 
    (CONNECT_DATA = 
      (SERVICE_NAME = DB11GR2)
    )
  )
Now conform the setupof the SCAN method;
DB_JOORDS =
  (DESCRIPTION =
     (ADDRESS_LIST =
  ((ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan.rac.joords.nl )(PORT = 1521))) 
    (CONNECT_DATA = 
      (SERVICE_NAME = DB11GR2)
    )
  )

How to intergrate a 10g/11gR1 RAC database or clients to a 11gR2 clusterware with SCAN.
Oracle 10g/11gR1 sqlnet does not support multiple IP adresses associated with the same host name (SCAN method). There is no need to use the SCAN VIPs when running a 10g/11gR1 database under 11gR2 Grid, it is perfectly fine to continue use the node listeners, both for instance registration as well as client connect strings. The solution outlined in this document requires that the SCAN VIPs are fixed, hence it cannot be used if the SCAN VIPs are dynamically allocated via GNS (using DHCP).

The Solution

Pre-11gR2 clients/database instances do not support multiple IP addresses associated with the same host name (SCAN), consequently the 11gR2 syntax should not be used for the remote_listener parameter or the client connect string as it probably would only register with/connect to the first VIP of the SCAN only.

grid@racnode1 $ nslookup cluster_scan.rac.joords.nl 
Server: dns1
Address: 10.1.10.183#53

Name: cluster-scan.rac.joords.nl
Address: 10.1.10.200
Name: cluster-scan.rac.joords.nl
Address: 10.1.10.201
Name: cluster-scan.rac.joords.nl
Address: 10.1.10.202

Using above information, TNS aliases for instance parameters local_listener / remote_listener can be created. Please note that the alias for 'local_listener' uses the node VIPs, while the alias for 'remote_listener' uses the SCAN VIPs. This matches the setup for 11gR2 databases

LISTENER_DB10G1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))

LISTENER_DB10G2 =
   (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))


LISTENERS_GRID =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.200)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.201)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.202)(PORT = 1521))
   )

Set the local_listener/remote_listener parameters for the 10g database instances:

alter system set local_listener='LISTENER_DB10G1' scope=spfile sid='DB10G1';
alter system set local_listener='LISTENER_DB10G2' scope=spfile sid='DB10G2';
alter system set remote_listener='LISTENERS_GRID' scope=spfile sid='*';

There should be a CRS service configured in the OCR for clients to connect this database - e.g.:

srvctl status service -d DB10G 
Service DB10G_SCAN is running on instance(s) DB10G1, DB10G2
The corresponding client connect string would be:
DB10G_VIA_SCAN =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.200)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.201)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.202)(PORT = 1521))
    ) 
    (LOAD_BALANCE = ON)
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB10G_SCAN)
    )
  )

To check if the 10g/11gR1 database instances have registered their services names with the SCAN listeners the command 'lsnrctl services scan_listener' can be used, however since the SCAN can run on any node of the cluster, it first needs to be established where they are currently running:

srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node racnode2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node racnode1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node racnode1

All SCAN listeners should show the service names of the 10g/11gR1 database instances - e.g.:


lsnrctl service LISTENER_SCAN1
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-FEB-2010 14:26:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "DB10G" has 2 instance(s).
   Instance "DB10G1", status READY, has 1 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521))
   Instance "DB10G2", status READY, has 1 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=racnode2-vip)(PORT=1521))
...
Service "DB10G_SCAN" has 2 instance(s).
   Instance "DB10G1", status READY, has 1 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521))
   Instance "DB10G2", status READY, has 1 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=racnode2-vip)(PORT=1521))

Multiple ports SCAN LISTENER

srvctl config scan_listener

 SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
 SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
 SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

$ srvctl remove scan_listener -l LISTENER_SCAN1 -f
$ srvctl remove scan_listener -l LISTENER_SCAN2 -f
$ srvctl remove scan_listener -l LISTENER_SCAN3 -f
$ srvctl add scan_listener -l LISTENER_SCAN1 -p TCP:1521,1523
$ srvctl add scan_listener -l LISTENER_SCAN2 -p TCP:1521,523
$ srvctl add scan_listener -l LISTENER_SCAN3 -p TCP:1521,1523

For more information see:

  • How to integrate a 10g/11gR1 RAC database with 11gR2 clusterware (SCAN) [ID 1058646.1]
  • Using Oracle 11g Release 2 Real Application Clusters with Oracle E-Business Suite Release 11i [ID 823586.1]
  • Using Oracle 11g Release 2 Real Application Clusters with Oracle E-Business Suite Release 12i  [ID 823587.1]