|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Flashback Database [Oracle]
Oracle Flashback Database is an alternative to a point in time recovery (PITR). datafiles are brought to their contents they had at a past time.
Old database block images are stored in a flash recovery area which allow fast rollbacks of database (as no online redo logs are required.)
exec dbms_flashback.enable_at_time(...); select * from my_important_table; exec dbms_flashback.disable; select * from my_important_table AS OF TIMESTAMP '...'
Flashback Versions Query
select versions_xid, versions_startscn, versions_endscn, versions_operation, foo_1, bar_2 from t_baz VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE where qqux = 334;
The query makes use of the following pseudo columns:
Flashback Transaction History
select xid, start_scn, commit_scn, operation, undo_sql, table_name from flashback_transaction_query where xid = '0004000400000003D';
Correcting human errors
A table is easily dropped but hard to be recovered. Unless FlashBack DB is turned on, in which case the table is not dropped physically, but renamed. (See also dba_recyclebin).
flashback table RB$$4902$TABLE$1 to before drop rename to rene.my_important_table
ASM simplifies the optimal layout of flashback recovery area files.