Transparent Application Failover (TAF) on oracle 9i rac and 10g rac

October 7, 2009 at 10:17 am (Oracle) (TAF, TAF for 9i rac and 10g rac, Transparent Application Failover)

sunny1pub and sunny2pub are 2 nodes of a 9i real application cluster setup. orcl1 and orcl2 are instance names respectively accessing database “orcl”

LISTENER_ORCL1 resides on sunny1pub(node 1)

LISTENER_ORCL2 resides on sunny2pub(node 2)

Create 2 non default listeners LISTENER_ORCL1 and LISTENER_ORCL2 respectively on both nodes on port 1521. Make sure the listener you have started is LISTENER_ORCL1(look at alias in lsnrctl status, if not use set current_listener listener name and then start). Lets say, you want the value of remote_listener to be LISTENER_ORCL . So you need to have a tns entry on both servers/nodes with LISTENER_ORCL .

my configuration files are as follows:

node 1 tnsnames.ora (node 2 tnsnames.ora is just the same. The only difference is the second net service name orcl.ezhome.com where the SID is orcl2)

LISTENER_ORCL.EZHOME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sunny1pub.ezhome.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = sunny2pub.ezhome.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.ezhome.com)
)
)
ORCL.EZHOME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sunny1pub.ezhome.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl1)
)
)

node 1 Listener.ora (node 2 listener.ora is just the same. The difference is the LISTENER_ORCL2, hostname, SID_LIST_LISTENER_ORCL2, sid_name)

LISTENER_ORCL1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sunny1pub.ezhome.com)(PORT = 1521))
)
)
)

SID_LIST_LISTENER_ORCL1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl1)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
)
)

node 1 SQLNET.ORA (node 2 sqlnet.ora is just the same as node 1)

NAMES.DIRECTORY_PATH= (TNSNAMES)

NAMES.DEFAULT_DOMAIN = ezhome.com

Test if your tnsentry for LISTENER_ORCL works.
Do a tnsping or connect scott/tiger@LISTENER_ORCL

Once your done with this,
conn sys/*********@orcl as sydba
show parameters listener
alter system set remote_listener=’LISTENER_ORCL’ scope=both;
show parameters listener
Now remote_listener should be updated.(No need to use option sid=)
Now to update local_listener,

alter system set LOCAL_LISTENER=”(address=(protocol=tcp)(host=)(port=1521))” scope=both;
show parameters listener

You do not have to input host, the server automatically takes the hostname on next startup.(No need to use option sid=)

Now my client(a remote system other than these 2 nodes) tnsnames.ora is as follows:

ORCL.EZHOME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(FAILOVER = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.3)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL.ezhome.com)
(FAILOVER_MODE=(TYPE=select)(METHOD=basic)(RETRIES=10)(DELAY=1))
)
)

my sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES)

NAMES.DEFAULT_DOMAIN= ezhome.com

set lines 1000
set feed on
SELECT username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION
GROUP BY username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

To test TAF, do not use disconnect or conn after you are connected to a session as it will not work. Once you connect, exit from the session and reconnect to test TAF configuration. It is better explained below:

I’m already connected as HR
SQL> show user
USER is “HR”
SQL> conn hr/hr@orcl
Connected.
SQL> set lines 1000
SQL> set feed on
SQL> SELECT username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION
2 GROUP BY username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

USERNAME TERMINAL MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(
)

---

——————————

---

————-

---

—

---

UNKNOWN sunny1pub.ezhome.com NONE NONE NO
2
HR sun WORKGROUP\sun NONE NONE NO
1
SYS sunny1pub.ezhome.com NONE NONE NO
1

3 rows selected.
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
SQL> conn hr/hr@orcl
Connected.
SQL> set lines 1000
SQL> set feed on
SQL> SELECT username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION
2 GROUP BY username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

USERNAME TERMINAL MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*
)

---

——————————

---

————-

---

— -

---

UNKNOWN sunny1pub.ezhome.com NONE NONE NO 1
2
HR sun WORKGROUP\sun NONE NONE NO
1
SYS sunny1pub.ezhome.com NONE NONE NO
1

3 rows selected.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production

C:\Documents and Settings\nilaya>sqlplus

SQL*Plus: Release 9.2.0.1.0 – Production on Fri Jul 3 11:56:42 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: hr/hr@orcl

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production

SQL> set lines 1000
SQL> set feed on
SQL> SELECT username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION
2 GROUP BY username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

USERNAME TERMINAL MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*
)

---

——————————

---

————-

---

— -

---

UNKNOWN sunny1pub.ezhome.com NONE NONE NO 1
2
HR sun WORKGROUP\sun SELECT BASIC NO
1
SYS sunny1pub.ezhome.com NONE NONE NO
1

3 rows selected.

SQL>

And to test TAF, look at FAILED_OVER column in the above query. To test this, srvctl stop instance -d orcl -i orcl1 . The instance resulting from the query below is the instance you are connected to and is instance that you need to bring down. Note: Change the username in the query as required.

set lines 1000
set feed on
select instance_name, host_name,
NULL AS failover_type,
NULL AS failover_method,
NULL AS failed_over
FROM v$instance
UNION
SELECT NULL, NULL, failover_type, failover_method, failed_over
FROM v$session
WHERE username = ‘HR’;
SQL> SELECT username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION
2 GROUP BY username, terminal, MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

USERNAME TERMINAL MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*
)

---

——————————

---

————-

---

— -

---

UNKNOWN sunny1pub.ezhome.com NONE NONE NO 1
2
HR sun WORKGROUP\sun SELECT BASIC YES
1

2 rows selected.

Now, after you stopped the instance, if you execute the above query, column failed_over has changed to “yes” meaning that the instance has failed over. Thats all about transparent application failover.

PS: Other than virtual ips to be used in client tnsnames.ora instead of 192.168.2.3 and 192.168.2.4, the same setup could be used for 10g rac also.