Create Flashback RESTORE POINT before any activity in Oracle

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: Enable or Disable the Flashback of Oracle Database

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.