Oracle DBMS_FLASHBACK package

Oracle Flashback technology

Logical Flashback (do not depend on RMAN, rely on undo data) Oracle Flashback Query (Oracle 10g) Oracle Flashback Version Query Oracle Flashback Transaction Query Oracle DBMS_FLASHBACK (Oracle 9i) Oracle Flashback Transaction (New 11g) Oracle Flashback Table - Rewind table to a point back in time Oracle Flashback Drop - Reverse the effects of DROP TABLE Physical flashback Oracle Flashback Database Oracle Flashback Data Archive (Oracle Total Recall)(11g) New on Oracle 11g: Oracle Flashback Transaction Oracle Flashback Data Archive (Oracle Total Recall)

Using DBMS_FLASHBACK package

• Released with Oracle 9i
• Provides the same functionality as Oracle Flashback Query (Oracle 10g)
• Acts as a time machine:
• 1. set time to a previous point in time.
• 2. perform queries without the "AS OF" and "VERSIONS BETWEEN" clauses
• 3 return time to the "present"

Requirements:

• You need EXECUTE privilege on DBMS_FLASHBACK package
• The UNDO_RETENTION parameter defines how far back in time an object can be flashbacked.
• UNDO_RETENTION can be set in the init.ora or with:
• ALTER SYSTEM SET UNDO_RETENTION = num_seconds;

Using the DBMS_FLASHBACK package:

1. Specify a past time with DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.
2. Perform regular queries (queries without flashback-feature syntax such as AS OF).
   Do not perform DDL or DML operations.
3. Return to the present by invoking DBMS_FLASHBACK.DISABLE.

(a) Check current data in the emp table

SQL> select * from emp;

 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900      

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432709                12-08-10 10:58:45  

(b) modify data in emp table

SQL> Insert into emp values (3, 'Mark', 5000);
SQL> commit;

SQL> Update emp set salary = 0 where empno=1;
SQL> commit;

SQL> Delete from emp where empno=2;
SQL> update emp set empname = 'Lenny' where empno=100;
SQL> commit;

SQL> select * from emp;

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       0                      
100                    Lenny      900                    
3                      Mark       5000   

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432739                12-08-10 10:59:30 

To view and restore the original data, you can
(1) create a cursor to hold the original data
(2) go back in time with dbms_flashback.enable_at_system_change_number(n);
(3) Open and fetch the data in the cursor.
(4) return to the present with dbms_flashback.enable;
(5) Insert the data in the cursor in the restore table

(c) Create a table to restore original data

SQL> create table emp_restore
     as select * from emp where 1 = 0;


set serveroutput on
DECLARE
  cursor c_emp is 
     select * from emp;
  v_row c_emp%rowtype;
BEGIN
  dbms_output.put_line('enabling dbms_flashback..');
  dbms_flashback.enable_at_system_change_number(1432709);
  Open c_emp;
  dbms_output.put_line('disabling dbms_flashback..');  
  dbms_flashback.disable;
  
  loop 
    fetch c_emp into v_row;
    exit when c_emp%notfound;
    insert into emp_restore values
      (v_row.empno, 
       v_row.empname,
       v_row.salary);
  end loop;
  close c_emp;
  commit;
END;
/
enabling dbms_flashback..
disabling dbms_flashback..

SQL> select * from emp_restore; 

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900