Oracle RAC handson Part 4 TAF Configuration

TAF (Transparent Application Failover) Configuration

Listener file on node:

SID_LIST_LISTENER_BLADE1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/10.2/db)
(PROGRAM = extproc)
)
)

LISTENER_BLADE1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = blade1-vip.wyeth.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.166.81)(PORT = 1521)(IP = FIRST))
)
)

Restart listener by
lsnrctl stop LISTENER_BLADE1
lsnrctl start LISTENER_BLADE1
Or
svrctl stop listener –n blade1
svrctl start listener –n blade1

Tnsnames.ora on client

RAC =
(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.166.91)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.166.92)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.166.93)(PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.wyeth.com)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
)
)
)

#Host IP Address is the VIP (virtual IP) of cluster node

#type=select, session

#Method=basic, preconnect

TAF session information

SQL> connect test/password@rac_serv1;
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac3


SQL> select failover_type,failover_method,failed_over
2 from v$session where username='TEST';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT BASIC YES

--OS process ID

SQL> SELECT p.spid FROM v$session s, v$process p
2 WHERE s.paddr = p.addr AND s.username='TEST';

SPID
------------
22179

Create service on RAC

Service can be created by OEM and DBCA. Here are screenshot for OEM. Service is used by application to connect one or more instances on RAC and need to set at tnsname entry. By default, service name is rac.wyeth.com which is createde during database creation.

OEM 10g cluster database

@rac_modserv
connect / as sysdba
execute dbms_service.modify_service( -
service_name => 'RAC_SERV1' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries => 180 -
, failover_delay => 5 -
, clb_goal => dbms_service.clb_goal_long);
exit;

On client side tnsnames.ora

RAC_SERV1 =
(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.166.91)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.166.92)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.166.93)(PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_SERV1.wyeth.com)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
)
)
)

Issues:

1. Get ORA-12545 (Connect failed because target host or object does not exist) sometimes when connect to RAC. The public host name is not in Domain Name Server (DNS) and gets ORA-12545 when redirect. (Subject: RAC Connection Redirected To Wrong Host/IP ORA-12545 Doc ID: Note:364855.1) See reference 3. Solution is to set LOCAL_LISTENER init parameter with VIP address

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.166.91) (PORT = 1521))

Reference:

1. Oracle® Database 2 Day + Real Application Clusters Guide 10g Release 2 (10.2), Part Number B28759-01, “7 Managing Database Workload Using Services”, “Creating Services” http://download-east.oracle.com/docs/cd/B19306_01/rac.102/b28759/configwlm.htm#CACFEADG

2. Using Transparent Application Failover in Oracle Real Applications Clusters http://www.oracle.com/technology/obe/10gr2_db_vmware/ha/rac/rac.htm

3. Metalink Note: 364855.1: RAC Connection Redirected To Wrong Host/IP ORA-12545