How to configure Transparent Application Continuity (TAC) in a FlashGrid Cluster

Note: Please make sure you have followed the steps from this article before configuring Transparent Application Continuity (hereinafter TAC).

This article demonstrates how to setup TAC in a FlashGrid Cluster environment. TAC, used with an Oracle RAC database cluster powered by FlashGrid, enables uninterrupted transaction processing in case of a DB instance goes down for any reason (planned or unplanned events).

The configuration of TAC in FlashGrid Cluster environment is essentially the same as in an on-prem Oracle RAC environment. The main and only difference is the connect string, which will be based on a FlashGrid Cluster connect string.

Follow the steps below to configure and test TAC.

Create database service

1. Create a TAC-enabled database service.

   $ srvctl add service -db racdb \
                        -service fg_tac \
                        -pdb PDB \
                        -preferred racdb1,racdb2 \
                        -failovertype AUTO \
                        -failover_restore AUTO \
                        -failoverdelay 5 \
                        -failoverretry 30 \
                        -commit_outcome TRUE \
                        -replay_init_time 600

2. Start the created service

   $ srvctl start service -db racdb -service fg_tac

3. Check the configuration of the service.

   $ srvctl config service -db racdb -service fg_tac
   Service name: fg_tac
   Server pool:
   Cardinality: 2
   Service role: PRIMARY
   Management policy: AUTOMATIC
   DTP transaction: false
   AQ HA notifications: false
   Global: false
   Commit Outcome: true
   Failover type: AUTO
   Failover method:
   Failover retries: 30
   Failover delay: 5
   Failover restore: AUTO
   Connection Load Balancing Goal: LONG
   Runtime Load Balancing Goal: NONE
   TAF policy specification: NONE
   Edition:
   Pluggable database name: PDB
   Hub service:
   Maximum lag time: ANY
   SQL Translation Profile:
   Retention: 86400 seconds
   Replay Initiation Time: 600 seconds
   Drain timeout:
   Stop option:
   Session State Consistency: AUTO
   GSM Flags: 0
   Service is enabled
   Preferred instances: racdb1,racdb2
   Available instances:
   CSS critical: no
   Service uses Java: false
   

4. Check that the service is running.

   $ srvctl status service -db racdb -service fg_tac
   Service fg_tac is running on instance(s) racdb1,racdb2
   

Test TAC via SQL*Plus

It is recommended to test TAC via SQL*Plus since SQL*Plus 19c supports TAC without additional configuration. As of time of writing, SQL Developer cannot be used to test TAC due to Transparent Application Continuity(TAC) Is Not Successful For SQL Developer Connections On Node Failover (Doc ID 2859802.1). Oracle Instant Client 19.18 with SQL*Plus connected to Oracle Database 19.18 is used in this test.

1. Create the connection string for the service

   FG_TAC=
        (DESCRIPTION=
              (TRANSPORT_CONNECT_TIMEOUT=3) (RETRY_COUNT=6)
              (ADDRESS= (PROTOCOL=tcp) (HOST=rac1.mycompany.mydomain) (PORT=1521))
              (ADDRESS= (PROTOCOL=tcp) (HOST=rac2.mycompany.mydomain) (PORT=1521))
              (CONNECT_DATA =
                 (SERVICE_NAME = FG_TAC)
              )
         )

2. Connect through the service and create a test table

   SQL> create table tac_test(msg varchar2(10));
   
   Table created.
     

3. Confirm that failover parameters are set

   SQL> select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid');
   
   FAILOVER_TYPE FAILOVER_M FAI
   ------------- ---------- ---
   AUTO          BASIC      NO
     

4. Determine a DB instance to which the session is connected

   SQL> select host_name,instance_name from v$instance;
   
   HOST_NAME                      INSTANCE_NAME
   ------------------------------ ----------------
   rac1.mycompany.mydomain        racdb1
     

5. Start a new transaction without committing it

   SQL> insert into tac_test values ('TAC test');
   
   1 row created.
     

6. Shutdown the DB instance that the session is connected to

   $ srvctl stop instance -db racdb -instance racdb1 -force

7. Return to the session and commit the transaction

   SQL> commit;
   
   Commit complete.
     

8. Confirm that the failover happened

   SQL> select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid');
   
   FAILOVER_TYPE FAILOVER_M FAI
   ------------- ---------- ---
   AUTO          BASIC      YES
     

9. Confirm that the session failed over to another cluster node

   SQL> select host_name,instance_name from v$instance;
   
   HOST_NAME                      INSTANCE_NAME
   ------------------------------ ----------------
   rac2.mycompany.mydomain        racdb2
     

10. Confirm that the data is saved to the table

    SQL> select * from tac_test;
    
    MSG
    ----------
    TAC test