Flashback database feature speed up migration script testing

Usually people use flashback database technique to recover fast from data loss or corruption. This is much faster approach than the traditional point-in-time recovery where redo and archive logs are being used.

We did find it useful that helped in testing migration scripts during big application release. We had to do this on real data. There were huge data changes/manipulations in several tables on different schemas. After migration, if anything discovered wrong later in testing phase, we had to start the whole thing with a fresh schema. The schema loading time was huge to start over where flashback technique just rewind the whole database in just 30 min. 

Here are few quick steps to configure flashback database feature -

1. Setting initialization parameters -

alter system set db_recovery_file_dest_size=100G;

alter system set db_recovery_file_dest='/oradata/flashback';
alter system set db_flashback_retention_target=4320;

2. DB in archive log mode -

select log_mode,flashback_on from v$database;
alter database archivelog;

3. Enabling flashback logging -

shutdown immediate
startup mount
alter database flashback on;
alter database open;

4. Checking status -

select flashback_on from v$database;
select * from v$flashback_database_log;
 
5. Creating restore point before running migration scripts -

create restore point before_migration_91;
select systimestamp from dual;

6. Performing migration/DML scripts, java programs etc.

When needed, we re-winded the database (to a fresh copy) using the following command -

We can lookup the already created restore points like below -

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

Putting database in mount state - 

flashback database to restore point before_migration_91;
alter database open resetlogs;
 

The commands below could be used as well - 

flashback database to timestamp to_timestamp('22-JUN-11 10:30:00','DD-MON-YY HH24: MI: SS');
flashback database to scn scn_no;

That made the whole migration testing a lot easy and fast if we had to start over from the begging with fresh schemas.

***