- BASIC METHOD
ADD THE ENTRIES TO THE RAC DATABASE NODE1 tnsnames.ora file
CDBS_TAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdbs)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=BASIC)
)
)
)
Make sure all the resources are up and running.
[email protected] bin]# ./crs_stat -t
Name Type Target State Host
ora….s1.inst application ONLINE ONLINE racnode1
ora….s2.inst application ONLINE ONLINE racnode2
ora….bs1.srv application ONLINE ONLINE racnode1
ora….bs2.srv application ONLINE ONLINE racnode2
ora…._srv.cs application ONLINE ONLINE racnode2
ora.cdbs.db application ONLINE ONLINE racnode2
ora….SM1.asm application ONLINE ONLINE racnode1
ora….E1.lsnr application ONLINE ONLINE racnode1
ora….de1.gsd application ONLINE ONLINE racnode1
ora….de1.ons application ONLINE ONLINE racnode1
ora….de1.vip application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….E2.lsnr application ONLINE ONLINE racnode2
ora….de2.gsd application ONLINE ONLINE racnode2
ora….de2.ons application ONLINE ONLINE racnode2
ora….de2.vip application ONLINE ONLINE racnode2
[[email protected] admin]$ sqlplus system/[email protected]_TAF
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Aug 26 17:36:45 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> show user;
USER is “SYSTEM”
SQL> col osuser format a10
SQL> col username format a10
SQL> col failover_type format a15
SQL> col failover_method format a18
SQL> select username,osuser,failover_method,failover_type from v$session
2 where username=’SYSTEM’;
USERNAME OSUSER FAILOVER_METHOD FAILOVER_TYPE
SYSTEM oracle BASIC SELECT
SQL> select instance_name from v$instance;
INSTANCE_NAME
cdbs1
SQL> select count(*) from demotb;
COUNT(*)
954SQL> select * from demotb;
run the long running query
simultaneously open another terminal and give shutdown abort instance cdbs1
SQL> shu abort
ORACLE instance shut down.
but the query runs smoothly without any interruption after the completion of the query, verify the instance name
SQL>select instance_name from v$instance;
INSTANCE_NAME
cdbs2
automatically it switch over to the instance cdbs2 , because of this user can access the database and no failure in queries. users can access without interruption.
USER will not receive any error message while running query.
- PRECONNECT METHOD
Add the entries to the tnsnames.ora file on both nodes.
CDBS1_TAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdbs)
(INSTANCE_NAME = cdbs1)
(FAILOVER_MODE=
(BACKUP=CDBS2_TAF)
(TYPE=SELECT)
(METHOD=PRECONNECT)
)
)
)
CDBS2_TAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdbs)
(INSTANCE_NAME = cdbs2)
(FAILOVER_MODE=
(BACKUP=CDBS1_TAF)
(TYPE=SELECT)
(METHOD=PRECONNECT)
)
)
)
connect to the database on node1
[[email protected] ~]$ sqlplus system/[email protected]_TAF
SQL*Plus: Release 10.2.0.1.0 – Production on Fri Aug 27 12:23:11 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
cdbs1
SQL> COL USERNAME FOR a10
SQL> col osuser for a10
SQL> col failover_type format a15
SQL> col failover_method for a18
SQL> select username,failover_type,failover_method from v$session
2 where username=’SYSTEM’;
USERNAME FAILOVER_TYPE FAILOVER_METHOD
SYSTEM SELECT PRECONNECT
SQL> select count(*) from demo;
COUNT(*)
49783run the long running query.
SQL> select * from demo;
simultaneously issue shutdown abort in new terminal
SQL> shu abort;
ORACLE instance shut down.
SQL>
the query runs smoothly, user will not receive any kind of error messages.
then check the instance name in node1 machine.
SQL> select instance_name from v$instance;
INSTANCE_NAME
cdbs2
SQL> select username,osuser,failover_type,failover_method from v$session
2 where username=’SYSTEM’;
USERNAME OSUSER FAILOVER_TYPE FAILOVER_METHOD
SYSTEM oracle SELECT PRECONNECT
SQL>
checking node2 machine before and after TAF.
Before TAF
SQL> select username,osuser,failover_type,failover_method from v$session
2 where username=’SYSTEM’;
USERNAME OSUSER FAILOVER_TYPE
FAILOVER_M
SYSTEM oracle NONE
NONE
SQL> select instance_name from v$instance;
INSTANCE_NAME
cdbs2
SQL> select instance_name from v$instance;
INSTANCE_NAME
cdbs2
After TAF.
SQL> select username,osuser,failover_type,failover_method from v$session
2 where username=’SYSTEM’;
USERNAME OSUSER FAILOVER_TYPE
FAILOVER_M
SYSTEM oracle SELECT
PRECONNECT
SQL>
Hope, this will helps you to understand the TAF.
