| Oracle Flashback technology | ||
|---|---|---|
Logical Flashback (do not depend on RMAN, rely on undo data)
|
Oracle Flashback Transaction
- Allow you to rollback changes made by one or more transactions.
- Flashback Transaction generates and executes the DMLs necessary to undo a given transactions and (optionally) all its dependent transactions. Requisites:
ARCHIVELOGmode- At least one archive log already generated
- Minimal and PK (plus FK)
supplemental loggingenabled FLASHBACKandSELECTon the object orFLASHBACK ANY TABLE.
 | Configuring the database to use Flashback Transaction |
(1) Check the database log mode
SQL> Select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
(2) Change Log mode to archivelog:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> Select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
(3) Switch logfile
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
(4) Check log files
SQL> select group#, thread#, bytes/1024/1024 Mb, members, archived, status
from v$log;
GROUP# THREAD# MB MEMBERS ARCHIVED STATUS
---------------------- ---------------------- ---------------------- ---------------------- -------- ----------------
1 1 50 1 YES ACTIVE
2 1 50 1 NO CURRENT
3 1 50 1 YES INACTIVE
SQL> select name, archived, applied, status, completion_time, compressed
from v$archived_log;
NAME ARCHIVED APPLIED STATUS COMPLETION_TIME COMPRESSED
--------------------------------------------------------- -------- --------- ------ ------------------------- ----------
/u01/.../flash_recovery_area/SID/archivelog/date/name.arc YES NO A 08-DEC-10 NO
(5) Enable Minimal and PK (and FK) supplemental logging
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
(3) Check the current setting for supplemental logging
(V$DATABASE, DBA_LOG_GROUPS, DBA_LOG_GROUP_COLUMNS)
SQL> Select supplemental_log_data_min, supplemental_log_data_all,
supplemental_log_data_UI, supplemental_log_data_FK
from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK
------------------------- ------------------------- ------------------------ ------------------------
YES NO NO YES
| Using Flashback Transaction |
- Use
DBMS_FLASHBACK.TRANSACTION_BACKOUTprocedure to rollback a transaction and its dependent transactions. - Can be done with the database online.
- DDL operations cannot be flashed back
- Transactions that use LOB datatypes (BFILE, BLOB, CLOB, NCLOB) cannot be flashed back
- Transactions cannot use features or datatypes not supported by the Log Miner
- Ensure that undo segments are large enough to maintain undo information for the period you may need to flashback.
 | Example 1: Flashback transactions that have no dependent transactions.
|
(1) Drop and recreate dept and emp tables.
SQL> drop table emp;
SQL> drop table dept;
-- Check the current database SCN
SQL> select current_scn scn_before_tblcreation
from v$database;
SCN_BEFORE_TBLCREATION
----------------------
1473663
SQL> create table dept
(deptno number primary key,
deptname varchar2(20));
SQL> create table emp
(empno number primary key,
empname varchar2(10),
deptno number,
salary number,
constraint fk_dept foreign key(deptno)
references dept(deptno));
-- Check the current database SCN
SQL> select current_scn scn_before_transactions
from v$database;
SCN_BEFORE_TRANSACTIONS
-----------------------
1473743
(2) Insert data into emp and dept -- Transaction 1: Insert new row (id=1)
insert into dept values (1, 'accounting');
insert into dept values (2, 'sales');
insert into emp values (1, 'John', 1, 2000);
commit;
-- Transaction 2: Insert new rows (ids=2,3)
insert into emp values (2, 'Mary', 1, 2500);
insert into emp values (3, 'Jack', 2, 2000);
insert into dept values (3, 'IT');
commit;
-- Check the current database SCN
SQL> select current_scn scn_after_transactions
from v$database;
SCN_AFTER_TRANSACTIONS
----------------------
1473781
-- Check inserted data
SQL> select * from dept;
DEPTNO DEPTNAME
---------------------- --------------------
1 accounting
2 sales
3 IT
SQL> select * from emp;
EMPNO EMPNAME DEPTNO SALARY
---------------------- ---------- ---------------------- ----------------------
1 John 1 2000
2 Mary 1 2500
3 Jack 2 2000
(3) Now use the flashback transaction query to find information about all transactions in a time or SCN interval SQL> select versions_xid xid, versions_startscn start_scn,
versions_endscn end_scn, versions_operation operation,
empno, empname, deptno, salary
from emp
versions between scn 1473743 and 1473781;
XID START_SCN END_SCN OPERATION EMPNO EMPNAME DEPTNO SALARY
---------------- ----------- -------- --------- ------- ---------- -------- --------
05000D0099040000 1473777 I 3 Jack 2 2000
05000D0099040000 1473777 I 2 Mary 1 2500 -- Transaction 2
07000A006A030000 1473747 I 1 John 1 2000
Identify the transaction you need to flashback. For example, if you choose to flashback transaction 2 only its three insert statements will be flashed back. Also, transaction 2 has no dependents, since:
- No other subsequent transaction modified any data touched by transaction 2
- No other subsequent transaction modified data linked through referential integrity with the rows touched by transaction 2.
To flashback a transaction, use
DBMS_FLASHBACK.TRANSACTION_BACKOUT (numtxns, xids, options).The procedure accepts one or more transaction IDs (XIDs).
It has also an
OPTIONS parameter to indicate how dependent transactions should be treated. The possible values are: NOCASCADE- The transaction should have no dependencies. If a dependency is found, this raises an error.NOCASCADE_FORCE- The transaction is backed out without affecting dependent transactions. This option succeeds only if no constraints are violated in the process.CASCADE- The transaction and all it's dependencies will be removed.
NONCONFLICT_ONLY - This option backout only the changes to the non-conflicting rows of the given transactions.
SQL> conn / as sysdba;
SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('05000D0099040000');
dbms_flashback.transaction_backout( numtxns => 1,
xids => v_xid,
options => dbms_flashback.cascade);
end;
/
anonymous block completed
TRANSACTION_BACKOUTanalyzes the transactional dependencies, performs DML operations, and generates reports.- It also DOES NOT commit the actions performed.
- To make the transaction backout permanent, you need to explicitly commit the transaction.
SQL> select * from dept; DEPTNO DEPTNAME ---------------------- -------------------- 1 accounting 2 sales SQL> select * from emp; EMPNO EMPNAME DEPTNO SALARY ---------------------- ---------- ---------------------- ---------------------- 1 John 1 2000 SQL> commit;
TRANSACTION_BACKOUT ReportsThe backout procedure populates two static data dictionary views.
DBA_FLASHBACK_TXN_STATE- Shows information about the transactions backedout by the procedure.
COMPENSATING_XID- Transaction of ID of the compensating transaction. You will need to explictly commit this transaction in order to make the backout permanent.XID- Backedout transactionDEPENDENT_XID- Dependent transaction
(a) Query DBA_FLASHBACK_TXN_STATE view
SQL> select *
from dba_flashback_txn_state
where xid = '05000D0099040000';
COMPENSATING_XID XID DEPENDENT_XID BACKOUT_MODE USERNAME
---------------- ---------------- ---------------- ---------------- ------------------------------
0800160056050000 05000D0099040000 CASCADE SYSTEM
(b) Use the compensating_xid returned to query the DBA_FLASHBACK_TXN_REPORT view:
sql> select xid_report
from dba_flashback_txn_report
where compensating_xid = '0800160056050000';
XID_REPORT
------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="0800160056050000" NAME="_SYS_COMP_TXN_1114255_TIM_1319522919">
<TRANSACTION XID="05000D0099040000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL\="yes">
delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT'
</USQL>
<USQL exec="yes">
delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000'
</USQL>
<USQL exec="yes">
delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT'
</EXEC_USQL>
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000'
</EXEC_USQL>
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
