Flashback using Restore Point

PITR (Point In Time Recovery) was never so easy!!!

Just follow some simple steps & use the power of flashback

SQL> SHUTDOWN DATABASE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE SET DB_RECOVERY_FILE_DEST_SIZE=200G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’+FLASH1′;
SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

To verify the database settings

SQL> SELECT FLASHBACK_ON, LOG_MODE, CURRENT_SCN FROM V$DATABASE;

Now instead of remembering the SCN’s, we can create meaningful mark for restore

SQL> CREATE RESTORE POINT UAT_1;

Some DML statement
Some DML statement
Some DML statement
Some DML statement
Some DML statement

SQL> CREATE RESTORE POINT UAT_2;

These restore points details are available in V$RESTORE_POINT

SQL> SELECT SCN, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE, TO_CHAR (TIME), NAME FROM V$RESTORE_POINT;

If we want to go back to specific restore point

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT ;
SQL> ALTER DATABASE OPEN RESETLOGS;

Parameter db_flashback_retention_target defines the lifetime for the flashback logs.

In RAC environment, all the instances accessing the database should be stopped first. Then mount the database from one instance in exclusive mode

SQL> STARTUP MOUNT EXCLUSIVE
SQL> FLASHBACK DATABASE TO RESTORE POINT ;
SQL> ALTER DATABASE OPEN RESETLOGS;

Advertisements
This entry was posted in Oracle Database Architecture and tagged . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s