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.)

Relevant parameters:

Relevant Commands

alter database flashback on | off
alter tablespace flashback on | off

Flashback Query

exec dbms_flashback.enable_at_time(...);
select * from my_important_table;
exec dbms_flashback.disable;
select * from my_important_table AS OF TIMESTAMP '...'

Flashback Table

flashback table to scn.
flashback table to 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:

versions_xid: The transaction id that created this version of the row
versions_operation: The action that created this version of the row (one of delete, insert, update)
versions_startscn: The SCN in which this row version first occured
versions_endscn: The SCN in which this row version was changed.

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).

Recoverying:

flashback table RB$$4902$TABLE$1 to before drop rename to rene.my_important_table