Oracle Flashback Database , Restore point and GUARANTEE Point

Oracle Flashback Database , Restore point and GUARANTEE Point

Flashback Database is as name suggest allow you to revert or rewind database to a point in when restore point is created in database. Restore point is a pointer which point to a SCN or effectively a time in past in database. So if we flashback database to a restore point effectively , We are reverting it back to the a point in time. In other words, It a point in time revert backup of database like DB Ponit in time recovery. 

There are two type of restore point 1) Normal Restore Point & 2) Guaranteed Restore Point.  

The difference between these two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter. Using guaranteed restore points is always possible as long as you have enough space in the flash recovery area. Guaranteed restore point never age out like normal restore point. You must explicitly drop the guaranteed restore point. In addition to that guaranteed  restore point require all the archive logs in order to flashback database.

Below is procedure to create Restore Point in Database :

*****************************************************************
Verify and capture few things before creating restore point
*****************************************************************

sqlplus / as sysdba

spool flashback_database_RESTORE_point.lst 

colum current_scn format 9999999999999999999999999999
set lines 199 pages 1999

-- flashback_on status should be YES

select dbid, name dbname, open_mode, flashback_on, database_role, current_scn,
to_char(created,'dd-Mon-YYYY hh24:mi:ss') created,
to_char(resetlogs_time,'dd-Mon-YYYY hh24:mi:ss') resetlogs_time
from v$database;

-- If the flashback_on status is not YES - run the below command to turn it ON and verify

alter database flashback on;

-- Check the db_recovery_file_dest and db_flashback_retention_target

show parameter db_recover
show parameter flashback

-- Verify if there is any existing restore point

select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

*****************************************************************
Create restore point and verify
*****************************************************************

create restore point PRE_UPgrade_25DEC2020 guarantee flashback database; 

set linesize 121
col scn format 9999999999999
col time format a32

alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size
from v$restore_point where guarantee_flashback_database = 'YES';

select sysdate from dual;

set lines 200
col TIME for a40
col NAME for a40
select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

Below is procedure to Drop Restore Point in Database :

*****************************************************************
Drop restore point and verify
*****************************************************************

drop restore point restore_03142015 ;

set linesize 121
col scn format 99999999
col time format a32

select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size
from v$restore_point where guarantee_flashback_database = 'YES';

spool off
exit

*****************************************************************
Drop restore point and verify
*****************************************************************

Below is procedure to Flahsback database to Restore Point  :

*****************************************************************
Flashback database
*****************************************************************

srvctl status database -d MYTESTDB
srvctl stop database -d MYTESTDB
srvctl status database -d MYTESTDB
srvctl start instance -d MYTESTDB -i MYTESTDB1 -o mount

SQL> select instance_name,status from gv$instance;
SQL> flashback database to restore point REST_POINT;
SQL> alter database open resetlogs;

==>  Stop and start the database and make sure all instances are up, using srvctl.
srvctl stop database -d MYTESTDB
srvctl start database -d MYTESTDB
srvctl status database -d MYTESTDB