Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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?
1 2 3 4 5 | SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG |
Is Supplemental Logging active?
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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.
1 2 3 4 5 | SQL> select dbms_flashback.get_system_change_number beging_SCN from dual; BEGING_SCN ---------- 2116288 |
5. Now let’s delete our connected data.
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 | 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”.
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.
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.
1 | SQL> select * from dba_FLASHBACK_TXN_REPORT; |
