Flashback Transaction In Oracle

Onur ARDAHANLI

10 months ago

In today’s article, we will learn important information and process steps about Flashback Transaction in Oracle Database.

There are some prerequisites to use Flashback Transaction:

The database must be in archive mode,
Supplemental logging must be active.

Flashback transaction uses LOGMINER in the background, so anything that is not supported by LogMiner is not supported by Flashback Transaction.

Data types that Flashback Transaction does not support:

BFILE,
BLOB,
CLOB,
NCLOB,
XML

Recovery with Flashback Transaction is done with the TRANSACTION_BACKOUT procedure of the DBMS_FLASHBACK api package.

Parameters of this procedure;

NUMTXNS (Number Of Transaction) – Number of transactions to be recovered.

XIDS (Transaction List) – Transaction ID numbers to recover.

Back-Out Options – Used to specify dependent processes.
CASCADE – Used with transactions on which all transactions are dependent.
NOCASCADE – Used in operations that do not have dependencies. It is the default value.
NOCASCADE FORCE – The transaction is recovered regardless of dependent transactions.
NONCONFLICT ONLY – Recovers non-conflicting lines.
TIME & SCN Hint – Used to enable the LogMiner tool to find the desired transactions easily. If left as default, the UNDO_RETENTION parameter is considered the starting point.

Flashback Transaction process steps.

1. First, database checks should be done.

Is the database in archive mode?

		SQL> select log_mode from v$database;
		
		LOG_MODE
		------------
ARCHIVELOG

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

Is Supplemental Logging active?

		SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_FK from v$database;
		
		SUPPLEME SUP SUP
		-------- --- ---
		YES      YES YES

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_FK from v$database;

SUPPLEME SUP SUP

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

YES YES YES

2. Let’s create two tables with Foreign Key connections.

	SQL> create table PERSONEL(
	  2  id number(2),
	  3  name varchar2(20),
	  4  f_day date,
	  5  city number(2))
	  6  tablespace DENEME;
	
	Table created.
	
	SQL> create table CITY(
	  2  id number(2),
	  3  city varchar2(20))
	  4  tablespace DENEME;
	
	Table created.
	
	SQL> alter table PERSONEL add constraint pk_pers primary key (id)  using index tablespace INDEX_TBS;
	
	Table altered.
	
	SQL> alter table CITY add constraint pk_city primary key (id) using index tablespace INDEX_TBS;
	
	Table altered.
	
	SQL> alter table PERSONEL add constraint fk_pers foreign key(city) references city (id);
	
Table altered.

SQL> create table PERSONEL(

2 id number(2),

3 name varchar2(20),

4 f_day date,

5 city number(2))

6 tablespace DENEME;

Table created.

SQL> create table CITY(

2 id number(2),

3 city varchar2(20))

4 tablespace DENEME;

Table created.

SQL> alter table PERSONEL add constraint pk_pers primary key (id) using index tablespace INDEX_TBS;

Table altered.

SQL> alter table CITY add constraint pk_city primary key (id) using index tablespace INDEX_TBS;

Table altered.

SQL> alter table PERSONEL add constraint fk_pers foreign key(city) references city (id);

Table altered.

3. Let’s insert the data.

	SQL> insert into city values (39, 'Kirklareli');
	
	1 row created.
	
	SQL> insert into city values (2, 'Adiyaman');
	
	1 row created.
	
	SQL> insert into PERSONEL values (1,'Emrah',sysdate,39);
	
	1 row created.
	
	SQL> insert into PERSONEL values (2, 'Melih', sysdate, 2);
	
	1 row created.
	
	SQL> insert into PERSONEL values (3, 'Ozan', sysdate, 2);
	
1 row created.

SQL> insert into city values (39, 'Kirklareli');

1 row created.

SQL> insert into city values (2, 'Adiyaman');

1 row created.

SQL> insert into PERSONEL values (1,'Emrah',sysdate,39);

1 row created.

SQL> insert into PERSONEL values (2, 'Melih', sysdate, 2);

1 row created.

SQL> insert into PERSONEL values (3, 'Ozan', sysdate, 2);

1 row created.

4. Let’s learn the SCN number.

	SQL> select dbms_flashback.get_system_change_number beging_SCN from dual;
	
	BEGING_SCN
	----------
   2116288

SQL> select dbms_flashback.get_system_change_number beging_SCN from dual;

BEGING_SCN

----------

2116288

5. Now let’s delete our connected data.

	SQL> delete personel where city=2;
	
	2 rows deleted.
	
	SQL> delete city where id=2;
	
	1 row deleted.

SQL> delete personel where city=2;

2 rows deleted.

SQL> delete city where id=2;

1 row deleted.

6. Let’s learn our SCN number again.

	SQL> select dbms_flashback.get_system_change_number beging_SCN from dual;
	
	BEGING_SCN
	----------
   2116993

SQL> select dbms_flashback.get_system_change_number beging_SCN from dual;

BEGING_SCN

----------

2116993

7. Now, let’s find out the transaction ID number with the Flashback Transaction Query feature using the SCN number.

	SQL> select distinct xid, start_scn, start_timestamp
	from flashback_transaction_query
	where table_name='CITY'
	and
	start_scn>2116288 and commit_scn < 2118412;
	
	XID               START_SCN START_TIM
	---------------- ---------- ---------
	07000C007E050000    2118365 23-SEP-14

SQL> select distinct xid, start_scn, start_timestamp

from flashback_transaction_query

where table_name='CITY'

and

start_scn>2116288 and commit_scn < 2118412;

XID START_SCN START_TIM

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

07000C007E050000 2118365 23-SEP-14

8. Now let’s save all the dependent variables with the CASCADE option.

	SQL> declare
	v_xid_arr sys.xid_array;
	begin
	v_xid_arr := sys.xid_array('07000C007E050000');
	dbms_flashback.transaction_backout (
	numtxns => 1,
	xids=> v_xid_arr,
	options=> dbms_flashback.cascade,
	scnhint=>2116288
	);
	end;
	/

SQL> declare

v_xid_arr sys.xid_array;

begin

v_xid_arr := sys.xid_array('07000C007E050000');

dbms_flashback.transaction_backout (

numtxns => 1,

xids=> v_xid_arr,

options=> dbms_flashback.cascade,

scnhint=>2116288

);

end;

9. We confirm “After”. You should not forget to “Commit”.

	SQL> commit;

1	SQL> commit;

10. We see that data comes to our table.

11. When the recovery process is successful, we can find out by querying the DBA_FLASHBACK_TXN_STATE image.

	SQL> select * from DBA_FLASHBACK_TXN_STATE where xid=HEXTORAW('07000C007E050000');

1	SQL> select * from DBA_FLASHBACK_TXN_STATE where xid=HEXTORAW('07000C007E050000');

12. Operations taken to recover can be seen by querying the dba_FLASHBACK_TXN_REPORT image.

	SQL> select * from dba_FLASHBACK_TXN_REPORT;

1	SQL> select * from dba_FLASHBACK_TXN_REPORT;