Difference between revisions of "Flashback"

Revision as of 23:37, 4 January 2013

Enable flashback

exec dbms_flashback.enable;

Disable flashback

exec dbms_flashback.disable;

Put a session back in time

Note: undo_management must be auto
Note: Take a look at undo_retention to get an idea of how far back you might be able to go.

exec dbms_flashback.enable_at_time(to_date('24-AUG-2012 12:00:00', 'DD-MON-YYYY HH24:MI:SS'));

Set the database recovery directory and size

alter system set db_recovery_file_dest='<path>' scope=both;
alter system set db_recovery_file_dest_size=<size> scope=both;

Copy old data into a new table

• Create an empty copy of the source table

create table old_table_data
tablespace ts_data
as select *
from ruined_table
where rownum < 1;

• Insert the flashback data into it

insert into old_table_data
select * from ruined_table
as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' );

Enable flashback on Standby database

select flashback_on from v$database;
alter system set db_recovery_file_dest_size=50G scope=both;
alter system set db_recovery_file_dest=’/<directory>/’
shutdown immediate;
startup mount;
alter database flashback on;
show parameter db_flashback_retention_target;
alter system set db_flashback_retention_target=2880 scope=both;
alter database recover managed standby database cancel;

How far back can we flashback the database?

select	oldest_flashback_scn scn
,	to_char(oldest_flashback_time, 'hh24:mi:ss dd/mm/yyyy') oldest_time
from	v$flashback_database_log
/