Backup - Flashback

Backup - Flashback

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

/

Turn on flashback database

shutdown immediate

startup mount

alter database flashback on;

alter database open;

What is the earliest time the database can be flashed back to?

select oldest_flashback_scn scn

, to_char(oldest_flashback_time, 'hh24:mi:ss dd/mm/yyyy') oldest_time

from v$flashback_database_log

/

Get a list of times and scn numbers

set pages 999

select scn

, to_char(time_dp, 'hh24:mi:ss dd/mm/yyyy') time

from smon_scn_time

order by scn

/

Flash the database back

shutdown immediate

startup mount exclusive

flashback database to scn <scn>;

or...**

flashback database to timestamp to_timestamp('22/04/2007 19:30:00','dd/mm/yyyy hh24:mi:ss');

alter database open resetlogs;