Features of Oracle Flashback

1. Flashback query

Introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select id from hasan.test where id in (100,101);
SQL>delete from hasan.test where id in (100,101);
SQL>commit;

SQL>select id from hasan.test as of timestamp systimestamp - interval '20' minute where id not in (select id from hasan.test);

SQL>insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '10' minute
where id not in (select id from hasan.test);

SQL>commit;


2. Flashback Table

When a human or application error occurs, you want to be able to restore the state of one or more tables to a point in time before the problem occurred. Flashback Table provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. Flashback Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select count(*) from hasan.test;
SQL>delete from hasan.test;
SQL>commit;


SQL>alter table hasan.test enable row movement;
SQL>Flashback table hasan.test to SCN ;
SQL>Flashback table hasan.test to timestamp systimestamp - interval '5' minute ;

SQL>select count(*) from hasan.test;


3. Flashback Version Query

Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> select password from hasan.test where id in (100,101);

SQL> update hasan.test set password='Bang'where id in (101);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> update hasan.test set password='ladesh'where id in (100);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> delete from hasan.test where id in (100,101);
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '20' minute
where id not in (select id from hasan.test);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> SELECT versions_startscn start_scn
, versions_endscn end_scn
, versions_xid transection_id
, DECODE(versions_operation,'I','Insert','U','Update','D','Delete',NULL) oper
, id, password
FROM hasan.test versions between scn 85174629 AND 85186130
WHERE id in (100,101) Order by start_scn;


4. Flashback Transaction Query

You may discover that somehow data in a table has been inappropriately changed. To research this change, you can use multiple flashback queries to view row data at specific points in time. More efficiently, you can use Flashback Versions Query feature to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values. Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes.

SQL> sqlplus / as sysdba

SQL> SELECT * FROM flashback_transaction_query x
where x.table_name='LOGIN' AND x.table_owner='HASAN'
AND x.start_timestamp >(systimestamp - interval '10' minute);


5. Flashback Drop

Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

SQL> create table table1 (a number(1));
SQL> INSERT INTO table1 values(1);
SQL> INSERT INTO table1 values(2);
SQL> INSERT INTO table1 values(3);
SQL> INSERT INTO table1 values(4);
SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> DROP TABLE hasan.table1;

SQL> SELECT ur.base_object, ur.object_name,ur.original_name FROM user_recyclebin ur;

SQL> FASHBACK TABLE table1 TO BEFORE DROP;


6.Flashback Database

Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast

Related Topics:

1. Flashback Part #1 Basics
2. Flashback Part #2 Setup and Maintenance