How to configure Transparent Application Failover (TAF) in single instance oracle dataguard 11g R2
Q: Is it possible to configure TAF in single instance dataguard?.Ans: Yes, it is possible to configure TAF in single instance dataguard.
Q: Why do we need TAF in single instance dataguard?.
Ans: When we perform switchover/switchback activity, database will be down for a short period of time and an application or any client connection will be lost and will receive below error messages about database shutdown:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4166
Session ID: 43 Serial number: 11
Business must be up and running.
Hence we need TAF to failover user connections to standby database(new primary after switchover).
Q: What are the different types of Failover methods?.
Ans: There are two types of Failover methods:
1)Basic: In this method, an application connects to secondary node in case of failure of primary node but it will take time to connect the secondary node till the Failover time.
2) Preconnect: In this method, an application connects to both primary and secondary node at the same time which is faster than the basic method. Because as it already connects to the secondary node, it reduces the Failover time. But it will consume more resources due to extra connections.
Q: What are the different types of Failover types?.
Ans: There are two type of Failover:
1)Select: In this type, if the connection is lost then Oracle establishes connection to the secondary node and re-executes the select statements by re-positioning the cursor without knowing the client's application.During the time, any uncommitted transactions will be rolled back.
2) Session: In this type, if the connection is lost then Oracle establishes connection to the secondary node but does not re-execute the select statements and you will receive below error message.
*
ERROR at line 1:
ORA-25408: can not safely replay call
During the time, any uncommitted transactions will be rolled back.
Step 1: Create a service to access the database remotely.
Step 2: Modify the newly created service and modify it.
BEGIN
dbms_service.modify_service('TEST',
failover_method =>'BASIC',
failover_type =>'SESSION',
failover_retries =>200,
failover_delay =>1);
END;
/
BEGIN
DBMS_SERVICE.START_SERVICE('TEST');
END;
/
commit;
Step 3: Create trigger to start and stop the above service automatically whenever satisfy the condition.
(DESCRIPTION_LIST =
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
(DESCRIPTION =
(SDU = 32767)
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.91)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.92)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
-bash-4.1$ sqlplus system@test
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 7
16:26:48 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> @rd
NAME
INSTANCE_NAME
OPEN_MODE DATABASE_ROLE CONTROL SWITCHOVER_STATUS
--------- ---------------- --------------------
---------------- ------- --------------------
DC
DC READ WRITE PRIMARY CURRENT SESSIONS ACTIVE
SQL>
col username for a14
col
machine for a16
select
sid,serial#,username,status,machine,failover_type, failover_method from
v$session where username not in(' ');
SQL>
SQL> SQL> SQL>
SID
SERIAL# USERNAME STATUS
MACHINE FAILOVER_TYPE FAILOVER_M
----------
---------- -------------- -------- ---------------- ------------- ----------
17 3 SYS ACTIVE
dc.oracle.com NONE NONE
42 1 PUBLIC INACTIVE dr.oracle.com NONE NONE
48 17 SYSTEM INACTIVE dc.oracle.com SESSION BASIC
SQL> @rd
NAME
INSTANCE_NAME
OPEN_MODE DATABASE_ROLE CONTROL SWITCHOVER_STATUS
--------- ---------------- --------------------
---------------- ------- --------------------
DC
DC READ WRITE PRIMARY CURRENT SESSIONS ACTIVE
MAX(SEQUENCE#)
--------------
273
SID | SERIAL# | USERNAME | STATUS | MACHINE | FAILOVER_TYPE | FAILOVER_M
---------- | ---------- | -------------- | -------- | ---------------- | ------------- | ----------
17 | 3 | SYS | ACTIVE | dc.oracle.com | NONE | NONE
34 | 9 | SYSTEM | INACTIVE | dr.oracle.com | SESSION | BASIC
44 | 1 | PUBLIC | INACTIVE | dr.oracle.com | NONE | NONE
SQL>
startup mount;
ORACLE
instance started.
Total
System Global Area 784998400 bytes
Fixed
Size 2257352 bytes
Variable
Size 515903032 bytes
Database
Buffers 264241152 bytes
Redo
Buffers 2596864 bytes
Database
mounted.
Database altered.
SQL> alter database commit to switchover to primary with session shutdown nowait;
Database altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL>
Thanks for reading this post ! Please comment if you like this post.
Very nice Rupesh.. 👍
ReplyDeletegreat post
ReplyDeleteHello, this is a great post. Do you please have configuration for SELECT type (not SESSION)?
ReplyDeletePlease follow the similar steps. Just execute any select statement and try it out.
ReplyDeleteThanks Rupesh. I am trying to combine SELECT and PRECONNECT. I am reading for PRECONNECT some backup service needs to be created and the tns entries will be different. Can you please help with a sample configuration for the trigger, tns entries and how the service will have to be created?
ReplyDeleteFollow the same steps mentioned in this blog. Just change failover method and type.
ReplyDeleteVery useful post for every one needs single Instance Data Gaurd TAF.
ReplyDeleteVery nice Rupesh sir
ReplyDeleteceliexne Jessica Wright NetBalancer
ReplyDeleteEset NOD 32
Driver Genius
workpulinthumb
Mfractadia_shi Thomas Newman click
ReplyDeletesoftware
maesuncopa