Guaranteed Restore Points

Fantastic feature called guaranteed restore point came with Oracle 10G R2. By this feature, you can set a restore point which will remain until you drop it. The best part of this feature is, it uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database. The only thing you must do is setting db_file_recovery_dest parameter and create a guaranteed restore point (for the first restore point you must be in mount mode). Before image of changed blocks, will be kept on flashback logs after you created the guaranteed restore point. Flashback logs of guaranteed restore points are not big because of keeping before image of changed blocks for only 1 time after first change.

To create first guaranteed restore point when flashback off, you first start the database in mount state after a consistent shut down. After opening in mount state you create your first guaranteed restore point like below.

SQL>CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Subsequent guaranteed restore points can be created when database is open. When you want to see the available guaranteed restore points you can use the select below

SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’; NAME SCN TIME DI GUA STORAGE_SIZE ——————– – ——— —————————— ———- — ———— BEFORE_UPGRADE 1011771 07.08.2007 14:14:56,0000 5 YES 0 BEFORE_TRUNCATE 1013509 07.08.2007 14:20:42,0000 5 YES 8192000

When you want to flashback to the guaranteed restore point you must run the command below from rman when DB is in mount mode.

RMAN> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_UPGRADE’;

You can drop the restore point if you don’t need it anymore. Then you drop the guaranteed restore point ,the relevant logs will be deleted too.

SQL> drop restore point before_upgrade; Restore point dropped.

What you have to consider is, to keep the flashback logs for the restore point. No file in the flash recovery area is eligible for deletion, if it is required to satisfy the
guarantee. To monitor the db_file_recovery_area usage you can use the query below.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ——————– ———– ———- —————– ————— D:\oracle\oradata\fl 1098907648 500303872 0 12 ash_recover_area

If you loose flashback logs or db_file_recovery_dest is full, you should face instance termination. For instance I face with, ORA-38760: This database instance failed to turn on flashback database, error in the middle of 10.2.0.3 upgrade (I had to remove some of flashback logs in db_file_recovery_area during upgrade operation because of space lack) .

The database was not opening because of missed flashback logs. The word guarantee was working quite perfect 🙂 When I drop the guaranteed restore point, database logged the lines below in the alert log and let me open the database smoothly at last.

Deleted Oracle managed file D:\ORACLE\ORADATA\FLASH_RECOVER_AREA\DABAK\FLASHBACK\O1_MF_3C674SNO_.FLB

The moral of story can be, as it will be advised you by OUI, do not enable archive log process when you are doing an upgrade, if you don’t have any standby as Yasin mentioned as a comment below:)

References Used:

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)