Friday, August 15, 2008

Enable Oracle Flashback

# adjust db_recovery_file_dest_size depending on space and expected archive / flashback volume
alter system set db_recovery_file_dest_size=300G;

# change db_recovery_file_dest depeding on which ASM disk group will be used
alter system set db_recovery_file_dest='+ASM_ORADATA12';

# keep 1 hour of flashback logs
alter system set db_flashback_retention_target = 60;

# enable flashback
shutdown immediate;
startup mount exclusive;
alter database flashback on;
alter database open;

# and some basic diagnostics scripts
SELECT object_type, message_type, message_level,reason, suggested_action
FROM dba_outstanding_alerts;

select * from v$flash_recovery_area_usage;

select * FROM V$RECOVERY_FILE_DEST;