About this Document
This document covers
overview of SCAN and troubleshooting listener issues. It also covers relation
implications of running autoconfig on SCAN listeners.
This document is primarily
meant for database administrators, system administrators, Team Leaders,
Technical Managers, etc.
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)
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.
