Oracle database guaranteed restore point

As DBAs, we often come across a requirement when business want all the changes on a database to be restored, so they can retest everything from scratch.  Database clone or RMAN restore is an answer to that. But  both options are time consuming for small amount of change in database has to be reverted. 

So to deal with this kind of situation, you can use  Normal Restore point or Guaranteed restore point  offered by Oracle databases. 

1. standard Restore Point  : It assigns a restore point name to an SCN or specific point in time.The control file stores the name of the restore point and the SCN.

2. Guaranteed Restore Point : It is same as normal restore point  but only difference is that the guaranteed restore points never age out of the control file and must be explicitly dropped.

Ensure to shutdown entire MT & CM tier in EBS before doing this in DB tier. The database should have archive log enabled. 

Check recovery parameters 

show parameter recovery;  (recovery_file_dest & recovery_file_dest_size should be set correctly)

Convert DB to archive log mode 

select name,database_role,open_mode,flashback_on,log_mode from v$database;

shutdown immediate

startup mount

alter database archivelog  à DEV was in NOARCHIVELOG mode

alter database open

 archive log list  à Verify if archive log is enabled

Create Guaranteed restore point (If flashback is not enabled then it enables it on its own)

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

CREATE RESTORE POINT TEST_BEFORE GUARANTEE FLASHBACK DATABASE;  à creates restore point

Flashback database to restore point

shutdown immediate;

startup mount;

FLASHBACK DATABASE TO RESTORE POINT TEST_BEFORE;

alter database open resetlogs; ( This may take few mins based on amount of changes in database)

select log_mode,flashback_on from v$database;

Verify flashback logs

select * from V$FLASHBACK_DATABASE_LOG;

select * from V$FLASHBACK_DATABASE_STAT;

select * from V$FLASHBACK_DATABASE_LOGFILE;