Configuring TAF in Oracle 11g Single Instance database

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

But some clients want these sessions and the requirement may be like connections should not be lost.
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.

Note : This TAF configuration is for session only not select query.

Step 1: Create a service to access the database remotely.

BEGIN

DBMS_SERVICE.CREATE_SERVICE(service_name=>'TEST',

network_name=>'TEST');

END;

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.

create or replace trigger TRIGGER_TEST after startup on database

declare

v_role varchar(30);

begin

select database_role into v_role from v$database;

if v_role = 'PRIMARY' then

DBMS_SERVICE.START_SERVICE('TEST');

else

DBMS_SERVICE.STOP_SERVICE('TEST');

end if;

end;

commit;

The above trigger will first select the database_role column of the v$database view and then it will check if the database_role is PRIMARY if so then it will start the service 'TEST' otherwise it will stop the service.

Step 4: Create tns service entry in tnsnames.ora file in source database only(primary).

TEST =
(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)
    )
)

The tns service name will be your created service name and you have to add both IP addresses of the both primary and standby servers. Also turn Failover option ON.

Step 5: Access the primary database using the service 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

By sys user you can check your session details:

SQL> select sid,serial#,username,status,machine,failover_type, failover_method from v$session where username not in(' ');

       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

Step 6: Initiate switchover task.

On Primary:

SQL> alter database commit to switchover to standby with session shutdown nowait;

Database altered.

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.

On Standby:

SQL> alter database recover managed standby database cancel;

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.

SQL> startup;
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>

Step 7: Check the above connected session whether you receive any error.

SQL> @rd

NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE CONTROL SWITCHOVER_STATUS

--------- ---------------- -------------------- ---------------- ------- --------------------

DC DC READ WRITE PRIMARY CURRENT SESSIONS ACTIVE

MAX(SEQUENCE#)

--------------

273

SQL> / => This will be in hang for some time till you convert standby into primary.Once converted then you will get below output.

select max(sequence#) from v$archived_log

ERROR at line 1:

ORA-25408: can not safely replay call