Oracle Flashback database using guaranteed restore point

Flashback database using guaranteed restore point↓♚

FlashBack Restore Point in oracle

1.  Requirements for Guaranteed Restore Points

The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode.

A flash recovery area must be configured Guaranteed restore points use a mechanism similar to flashback logging. Oracle must store the required logs in the flash recovery area.

Oracle 10.2
If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point
 ALTER DATABASE FLASHBACK ON;

Oracle 11.x
There is no need to mount the database. Flashback can be tunred on at open state.
SQL_BOLISETTY >>  ALTER DATABASE FLASHBACK ON;

2. Creating Restore points [CREATE RESTORE POINT]

# Create Normal restore points

SQL_BOLISETTY >>  CREATE RESTORE POINT before_upgrade; 

#Create guaranteed restore points
SQL_BOLISETTY >>  CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;



3. Listing restore points [V$RESTORE_POINT]

# To see a list of the currently defined restore points 

SQL_BOLISETTY >> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, 

    GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; 

#To view only the guaranteed restore points: 

SQL_BOLISETTY >> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, 
    GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT 
    WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

4. Dropping restore points [DROP RESTORE POINT]
1  #Same statement is used to drop both normal and guaranteed restore 
2 points.
SQL_BOLISETTY >>  DROP RESTORE POINT before_app_upgrade;

5. Turn of Flashback
SQL_BOLISETTY >>  ALTER DATABASE FLASHBACK OFF;

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

1. Your database is very big

2. You have a major application schema upgrade, in case of failure you would like to reset your database rapidly

3. You are familiar with a restore from RMAN backups but it takes a long time since your database is very big

4. You are using at least release 10G R2 and have heard about restore points

5. Huge DML avoids you enable flashback database since it generates far too much flashback logs 

Here is an example how you create easily a restore point and how you can flashback your database to that restore point

a) WITHOUT the need to enable flashback database

b) WITHOUT the need to restore backups 

SQL_BOLISETTY >> ; shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL_BOLISETTY >> ; startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 473957720 bytes
Database Buffers 142606336 bytes
Redo Buffers 7532544 bytes
Database mounted.
SQL_BOLISETTY >> ; select log_mode,flashback_on from v$database;
LOG_MODE     FLASHBACK_ON
------------ ------------------
NOARCHIVELOG NO
SQL_BOLISETTY >> ; create restore point BEFORE_SCHEMA_CHANGES guarantee flashback database;
create restore point BEFORE_SCHEMA_CHANGES guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_SCHEMA_CHANGES'.
ORA-38785: Media recovery must be enabled for guaranteed restore point.
SQL_BOLISETTY >> ; alter database archivelog;
Database altered.
SQL_BOLISETTY >> ; create restore point BEFORE_SCHEMA_CHANGES guarantee flashback database;
Restore point created.
SQL_BOLISETTY >> ; alter database open;
Database altered.
SQL_BOLISETTY >> ; /* an error occurs */ drop user TUNING cascade;
User dropped.
SQL_BOLISETTY >> ; flashback database to restore point BEFORE_SCHEMA_CHANGES;
flashback database to restore point BEFORE_SCHEMA_CHANGES
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL_BOLISETTY >> ; shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL_BOLISETTY >> ; startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 473957720 bytes
Database Buffers 142606336 bytes
Redo Buffers 7532544 bytes
Database mounted.
SQL_BOLISETTY >> ; flashback database to restore point BEFORE_SCHEMA_CHANGES;
Flashback complete.
SQL_BOLISETTY >> ; alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL_BOLISETTY >> ; /* database has been reset */ connect tuning/TUNING
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
...
T_SUPPLIER_SU
T_ORDER_OR
T_CURRENCY_CR
25 rows selected.
SQL> select count(*) from t_order_or;
COUNT(*)
----------------
1000000
SQL_BOLISETTY >> ; select * from v$flash_recovery_area_usage;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ----------------
CONTROL FILE                          0                         0                0
REDO LOG                              0                         0                0
ARCHIVED LOG                        .74                         0                3
BACKUP PIECE                          0                         0                0
IMAGE COPY                           25                         0                1
FLASHBACK LOG                      1.22                         0                2
FOREIGN ARCHIVED LOG                  0                         0                0
7 rows selected.
SQL_BOLISETTY >> ; select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY