Create Flashback RESTORE POINT before any activity in Oracle

In Flashback recovery, you can create restore point before any upgrade, patching and application testing activity.

We can create restore point in two ways:

Norman Restore Point: Its age out with retention policy defined while configuring flashback for database.

Guaranteed Restore Point: guaranteed restore point is not age out as retention policy defined, It must be explicitly dropped.

Click for Enable or Disable Flashback at database level:

1. Normal Restore Point

CREATE RESTORE POINT before_application_upgrade;

2. Guaranteed Restore Point

CREATE RESTORE POINT before_application_upgrade GUARANTEE FLASHBACK DATABASE;

List the Restore point:

RMAN>LIST RESTORE POINT restore_point_name;
RMAN>LIST RESTORE POINT ALL;
-- Check the restore point from SQL
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

Reverse or Rollback the database to a restore points

Flashback database to restore point 'before_application_upgrade';
Flashbackup database to scn 3123381;

Following are the steps for reverse or rollback the changes to a restore point as follows:

1. Check the Restore Point.

RMAN>LIST RESTORE POINT restore_point_name;
RMAN>LIST RESTORE POINT ALL;
-- Check restore point
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

2. Flashback to a restore point:

Shutdown immediate;
startup mount;
Flashback database to restore point 'before_application_upgrade';
alter database open resetlogs

Drop the restore point If no need in future

SQL> DROP RESTORE POINT before_app_upgrade;

Detail of flashback from following view:

SQL>Select * from V$FLASHBACK_DATABASE_STAT;
-- Estimate size
SQL>select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

Disable the Flashback

ALTER DATABASE FLASHBACK OFF;