Introduction
Oracle Autonomous Database is based on Oracle RAC and Exadata and hence, highly available by design. But how about your application? What happens to sessions getting interrupted by a maintenance event (e.g., patching), a local failure (e.g., instance crash), or (accidentally) terminated by a KILL SESSION command?
Let’s give it a try! You can reproduce all steps easily, also on Autonomous Always Free.
The Environment
- Oracle Autonomous Database on Shared Exadata Infrastructure (Always Free resource).
- SQL*Plus: Oracle Instant Client version 19.18.
Test #1
Connect to the Autonomous Database using one of the predefined services, e.g., the high service, and query your session details as follows:
set lines 300
col mysid for a10
SQL> select sys_context('USERENV','SID') mysid from dual;
SQL> select sid, serial#, inst_id, service_name from gv$session where sid=32459;
Start a transaction:
SQL> insert into tactab values ('Transparent Application Continuity is cool');
From another session, someone terminates your session while you are in the middle of your transaction:
SQL> alter system kill session '32459,31143,@2';
Now, you commit:
SQL> commit;
This is what is usually expected. But it doesn’t need to be that way!
Enable Transparent Application Continuity (TAC)
You can survive this. With Transparent Application Continuity (TAC).
Connect to the database again and check if TAC is enabled for the high service:
set lines 300
col name for a70
col failover_type for a15
SQL> select name, failover_type from dba_services where name = 'UKBIMZ1QJPGSSBJ_ADBFRA_high.adb.oraclecloud.com';
FAILOVER_TYPE is not set. This means TAC is not enabled. So, let’s enable it:
SQL> execute DBMS_APP_CONT_ADMIN.ENABLE_TAC('UKBIMZ1QJPGSSBJ_ADBFRA_high.adb.oraclecloud.com', 'AUTO', 1200);
The ENABLE_TAC procedure takes three parameters:
- SERVICE NAME: name of the service to be modified
- FAILOVER_RESTORE: set to AUTO to enable TAC
- REPLAY_INITIATION_TIMEOUT: specify how many seconds after a request is submitted to allow that request to replay.
Query again to verify:
Test #1, again
Let’s reconnect and repeat the first test and see what happens:
As we see, the commit was completed successfully. The session got automatically and transparently reconnected (see the different session id) and the in-flight transaction successfully replayed.
Conclusion
Transparent Application Continuity (TAC) hides planned maintenance and failure events from applications and end users. Sessions automatically get reconnected and any in-flight work is replayed, completely transparent to the application.
TAC doesn’t require any application code changes. Only configuration changes are required.
Usually, you can test TAC by logging in to the database server and relocating the service or causing the instance to crash by terminating the pmon or smon process, for example.
On Oracle Autonomous Database, you can use the KILL SESSION command to initiate a failure event. TAC will hide that failure from the end user and those provide high availability for your application.
Database Heartbeat 