Flashback Table, available since Oracle 10g, allows user to recover a table to a previous point in time/scn. This feature depends entirely on undo information in the undo segments to perform the recovery and no archivelog will be applied during recovery.
According to Oracle documents, Flashback Table is recommended when 5-10% data of the table being changed. For a large number of changed rows, table ,tablespace or database level recovery would be better.
Here is some points to note when using Flashback Table...
1. To use Flashback Table, a user needs to following privileges
i) FLASHBACK ANY TABLE or FLASHBACK object privilege
ii) SELECT, INSERT, DELETE, and ALTER privileges on the table.
2. You have to enable row movement in the tables you are recovering.
3. You must be using automatic undo management to use the flashback table feature.
4. How far a table can be flashed back relies on the amount of undo data in the database. Therefore, undo tablespace size and undo retention time are critical in your successfulness of recovery.
5. Oracle hold exclusive DML locks on the table or tables that it is being recovered but the tables continue to remain online.
6. You can’t flash back any of the SYS user’s objects, recovered objects, or a remote table.
8. ROWID will not be preserved for restored data blocks of the table. That's why you have to enable row movement on the tables being recovered.
9. You cannot recover a table to a time before any DDL statements on that table, including any changes on table storage attributes.
10. You cannot flashback on tables with virtual columns.
11. Whenever possible, perform flashback table to scn instead of to timestamp. Since Flashback Table works on scn internally, and timestamp to scn mapping is not always accurate.
12. If you have constraints on the table, you need to disable those constraints before the Flashback Table and enable it with validate after the Flashback.
13. To flashback a table with materialized view, drop the materialized view with preserve clause. Then flashback the table and rebuild the materialized view.
Filed under:
Oracle
Post a Comment