Setting guaranteed restore point with/without enabling flashback logging

Completing full backup/restore from RMAN backup or cold backup should now be a "thing of the past" due to flashback technology.  It is highly suggested to create a guaranteed restore point (GRP) in 10g/11g Oracle database without enabling flashback logging; the reason this would be beneficial is if you are not concerned with being able to flashback the database to a particular point-in-time AFTER the GRP was setup in the database.  If you would like to be able to flashback the database to any point in time AFTER the GRP was setup in the database, you WOULD need to enable flashback logging.

Both GRP without flashback logging and flashback logging will generate flashback logs; however, without flashback logging enabled, the amount of flashback logs will be much less and much smaller than if flashback logging were enabled.

Starting with Oracle 11gR2, you can now enable/disable flashback logging and create/drop GRPs at will WITHOUT requiring a database outage.

I will go through both here:

To enable flashback logging along with creating a GRP (prior to 11gR2):

1.  Shutdown the database.

      shutdown immediate;

2.  Startup mount the database.

      startup mount;

3.  Enable flashback logging.  Note: db_file_recovery_dest and db_file_recovery_dest_size must be configured prior to being able to enable flashback logging.

  
  alter system set db_file_recovery_dest_size=10G;

  alter system set db_file_recovery_dest='/destination_of_your_choice';
  CREATE RESTORE POINT name_of_your_choice GUARANTEE FLASHBACK DATABASE;

  alter database flashback on;

4.  Open the database.

     ALTER DATABASE OPEN;

To create a GRP only (no flashback logging):

1.  Shutdown the database.

      shutdown immediate;

2.  Startup mount the database.

      startup mount;

3.  Create GRP. Note: db_file_recovery_dest and db_file_recovery_dest_size must be configured prior to being able to create a GRP.

     alter system set db_file_recovery_dest_size=10G;

  alter system set db_file_recovery_dest='/destination_of_your_choice';

  CREATE RESTORE POINT name_of_your_choice GUARANTEE FLASHBACK DATABASE;

4.  Open the database.

     ALTER DATABASE OPEN;

Remember with GRPs and/or flashback logging enabled, the destination you specify to hold the flashback logs needs to be large enough to hold the logs.  If you have a GRP set and the location fills up, the database will be in a hung state resulting in downtime to the database until that space has been increased.

If you have flashback logging enabled, you can use the following query to obtain Oracle's suggested sizing of FRA.

SELECT round(ESTIMATED_FLASHBACK_SIZE/1024/1024,2) 
FROM V$FLASHBACK_DATABASE_LOG;


The result is an estimate of the disk space needed to meet the current flashback retention target, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE to your flash recovery area size, to hold the expected database flashback logs.