Implications of Autoconfig on RAC setup



About this Document
Background
This document covers overview of SCAN and troubleshooting listener issues. It also covers relation implications of running autoconfig on SCAN listeners.

Intended Audience
This document is primarily meant for database administrators, system administrators, Team Leaders, Technical Managers, etc.

References
SCAN Registration Issue: Services Fail to Register to SCAN Listeners (Doc ID 1448717.1
Bug 12946495: SERVICES FAIL TO REGISTER TO ALL SCAN LISTENERS
12.13 RAC Setup, Why Scan IPs Are Generated Instead of Scan Name In tnsnames.ora (Doc ID 2141287.1)

Assumptions
We are considering ebs environment with minimum base version of database as 11gr2 (11.2.0.1) and onwards.



Understanding SCAN and complications with ebs r12

SCAN was introduced by Oracle starting from 11gr2 and stands for Single Client Access Name. Oracle introduced this new feature in a RAC environment to overcome challenge of changing client-side connection string if there are any changes done on server side. We have set of 3 ips that are resolved by DNS/GNS for end-user connections. SCAN supports Load balancing and Failover, which was earlier handled by pmon process monitoring load on listeners at server side (Server side Load Balancing) and tnsnames.ora (load_balance and failover also known as Client side Load Balancing and failover).

Assuming below architecture

We have a 2 node RAC using set of 2 IPs for EBS environment -
DB Name - ERP_PROD
Instance Name - ERP_PROD1, ERP_PROD2
Listener Names - LISTENER_SCAN1, LISTENER_SCAN2, LISTENER_SCAN3



Troubleshooting Steps

1.         Identifying current SCAN Listener Status and services registered using below –

lsnrctl services LISTENER_SCAN1
(listener has registered both erp_prod1 and erp_prod2 with ready status)
lsnrctl services LISTENER_SCAN2
(listener has registered erp_prod2 only)
lsnrctl services LISTENER_SCAN3
(listener shows no services registered)
  

2.                  Checking parameter REMOTE_LISTENER

This initORA parameter is used to redirect connections from one db node to another. In RAC, this value is typically set to either –
a.  EZCONNECT style string i.e. scan-name:port
b.  REMOTE_LISTENER is set to a tnsnames entry SCAN_LISTENER

3.                  We will check below 2 files-

1.  Tnsnames.ora for SCAN_LISTENER tns-entry.
2.  Sqlnet.ora (our main line of concern will be - NAMES.DIRECTORY_PATH=(EZCONNECT,TNSNAMES))

4.                  Checking last run of Autoconfig-

We will further investigate last run of autoconfig on DB Node. If below files were backed-up before running autoconfig –
1.  Tnsnames.ora
2.  Sqlnet.ora

During startup when parameter remote_listener is read, it will try to figure out value assigned to it by first reading sqlnet.ora. Possible methods in NAMES.DIRECTORY_PATH are read from left to right which is standard Oracle Functionality.
When we have entry like as follows –
NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT))

Tnsnames being read first is given 1st preference because of which tnsname.ora file is read first. If it finds an entry matching “scan-name” given to remote_listener parameter, it will read that entry which may look like as follows after autoconfig –

SCANHOST:1521 =
    (DESCRIPTION=
       (ADDRESS_LIST=
         (ADDRESS=(PROTOCOL=tcp)(HOST=SCANHOST)(PORT=1521))
      )
    )
Whenever pmon will try to perform dynamic service registration, it will search for host name with ‘SCANHOST’ as there is no listener present with that name, it will fail to register instance to scan listeners. SCANHOST resolves 3 unique IP addresses.

Possible Solutions

We have 2 ways to resolve this –

1.                  Prefer EZCONNECT over TNSNAMES(requires DB bounce for pmon/lreg to read changes)-

When we have entry like as follows –
NAMES.DIRECTORY_PATH=(EZCONNECT, TNSNAMES))

EZCONNECT having higher preference than TNSNAMES here, Oracle will resolve SCANHOST with EZCONNECT method. We need to bounce database so that pmon will read these changes again in sqlnet.ora file.

4.                  Using 3 IPs in TNS Entry-

We would have to set 3 Ip addresses as follows in tnsnames.ora.


REMOTE_LISTENERS_SCAN=
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.12.13)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.12.14)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.12.15)(PORT = 1521))
    )

SQL>Alter system set REMOTE_LISTENER='REMOTE_LISTENERS_SCAN' scope=both sid='*';
SQL>Alter system register;


Conclusion

We must Always take backup of below files before running autoconfig to identify impact of autoconfig on the system. For both RAC/Non-RAC environments, if there are issues connecting to database over network, troubleshooting should start with –

1.  Reading listener log,
2.  understanding sqlnet.ora,
3.  connection string being used and its preference in NAMES.DIRECTORY_PATH,
4.  further tracing pmon process during dynamic service registration,
5.  For RAC, checking services properly registered to scan listeners.

Permanent Solution ( requires testing and patch analysis before migrating to production environment)

Found patch Patch 10427234: AUTOCONFIG TO OPTIONALLY GENERATE ADDITIONAL ALIASES FOR SCAN LISTENER.
It further requires below steps to be performed –
1.  Autoconfig after applying patch (apps node)
2.  Execute admkappsutil.pl utility to create the file appsutil.zip
3.  Copy appsutil.zip to db node and unzip under RDBMS_ORACLE_HOME
4.  Autoconfig on db node.


No comments:

Post a Comment