Backup - Flashback

• To put the session back in time
• Copy old data into a new table
• Export a database from a point in time
• Find the current SCN number
• Set the database recovery directory and size
• Set the flashback database retention target
• Turn on flashback database
• What is the earliest time the database can be flashed back to?
• Get a list of times and scn numbers
• Flash the database back

To put the 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.
Note. This can't be run as sys - system works though

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

To disable flashback run...

exec dbms_flashback.disable;

Copy old data into a new table

First, create an empty copy of the source table...

create table old_test_table
tablespace andy
as select *
from test_table
where rownum < 1
/

Now, insert the old data into it...

insert /*+ APPEND */ old_test_table
 (select * from test_table as of timestamp
 to_timestamp('24-AUG-06 12:00:00','DD-MON-YY HH24:MI:SS'))
/

Export a database from a point in time

exp / file=full_scr9.dmp log=full_scr9.log flashback_time=TO_TIMESTAMP('10-09-2006 09:00:00', 'DD-MM-YYYY HH24:MI:SS')

exp / parfile=full_scr9.par

parfile...

userid=system/******
file=full_scr9.dmp
log=full_scr9.log
flashback_time='2006-09-13 12:00:00'

Find the current SCN number

select dbms_flashback.get_system_change_number from dual
/

Set the database recovery directory and size

These settings are dynamic

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

Set the flashback database retention target

alter system set db_flashback_retention_target=<minutes> scope=both
/