dbazone

Recover database using Flashback Tables

Posted on January 1st, 2010 in Database backup and Recovery, database recovery scenarios Different between Flashback Table and Flashback Drop, Flashback Table, Flashback Table recovery, table point-in-time recovery using flashback table

In some scenarios, if a user accidentally deleted  rows of a table  and as a DBA you have to recover the table to point in time . How you can recover the rows without using more  time -consuming tablespace point-in-time recovery operation? Flashback Table allows us to recover one or more tables to specific point in time . But  make sure that there is enough undo information to support a Flashback Table operation.Flashback Table uses concept of rolling back only the changes made  to the table or tables and their dependent objects, such as indexes.

Different between Flashback Table and Flashback Drop

Flashback Table undoes recent transactions to an existing table, where as Flashback Drop recovers a dropped table.

Flashback Table uses undo information to recover transaction, where as Flashback Drop uses recycle bin.

Note  to use Flashback Table ,we have to enable row movement of the table . Because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. So, Flashback Table is not a valid option for the applications that depend on the table’s ROWIDs to remain constant.

Examples of Flashback Table recovery

SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
40 MARKETING      NEW DELHI
10 ACCOUNTING     NEW YORK
20 RESEARCH           DALLAS
30 SALES                    CHICAGO

SQL>
SQL> alter table scott.dept enable row movement;

Table altered.

No I will delete all the rows of the dept table
SQL> delete from scott.dept
2  ;

4 rows deleted.

SQL>
SQL>
SQL>
SQL> commit;

Commit complete.

All rows from dept table is deleted .Following select query is not selected any rows form dept table .

For example:

SQL> select * from scott.dept;

no rows selected

Following flashback table command recovers all the deleted rows from the table .
SQL> FLASHBACK TABLE scott.dept TO TIMESTAMP SYSTIMESTAMP – INTERVAL ’10′ MINUTE;

Flashback complete.

All rows of dept table is recovered.

For example :
SQL> select * from scott.dept;

DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

conclusion:

Flashback table is used to recover a table to point-in-time  without using time consuming point -in time tablespace recovery

Related posts:

1. Recover database with Flashback Query Flashback Query features introduces in Oracle 9i. This features allows...
2. Flashback drop and the recycle Bin Flashback Drop is an new features of Oracle 10g features....
3. Oracle Constraints Oracle Constraints are some business rule enforce to oracle database...
4. Recover database when all control file and Redo log files are lost As an Oracle DBA , Recover database and maintain high...
5. Database failure scenarios As an Oracle DBA ,understanding of the database failure types...

Related posts brought to you by Yet Another Related Posts Plugin.