Flashback Database in Oracle

Flashback Database in Oracle

Flashback database is related to protect data from loss and overcome the problems like user error, corruption etc. Flashback has faster point in time recovery also called Database point in time recovery(DBPITR).
By configure flashback, we can directly apply the application patch on database,
if something wrong then roll-back it.
 
Check Flashback Database is Enabled

select flashback_on from v$database;

Configure the Flashback Database

1. Set the db_recovery_file_dest parameter

ALTER SYSTEM SET db_recovery_file_dest='C:\flashback' scope=spfile;

2. Set the db_recovery_file_dest_size parameter for size allocated for flashback recovery

ALTER SYSTEM SET db_recovery_file_dest_size=20G scope=spfile;

3. Configure the DB_FLASHBACK_RETENTION_TARGET Parameter in minutes.

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 days

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880; # 2 days

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

4. Enable the flashback database

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

3. Disable the flashback for tablespace if not required

ALTER TABLESPACE users FLASHBACK OFF;

ALTER TABLESPACE users FLASHBACK ON;

Create RESTORE POINT before any activity
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.

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;

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;

Step for reverse or roll back 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;

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

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;

Advertisements

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.