Coskan’s Approach to Oracle

August 7, 2007

Guaranteed Restore Points

Filed under: Backup/Recovery — coskan @ 12:15 pm

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)

About these ads

5 Comments »

  1. 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

    Unless you have a standby database. If you have s standby database you can upgrade it by applying the archive logs from the primary site.

    Comment by Yas — August 7, 2007 @ 12:36 pm

  2. Yes a good and an important point. Thank you for correction Yasin.

    Comment by coskan — August 7, 2007 @ 12:44 pm

  3. [...] post made me sad that we don’t have such a thing in PostgreSQL. It’s on Coskan’s Approach to Oracle by Coskan Gundogar and tells about permanent (guaranteed) restore points. Great [...]

    Pingback by </depesz> » Blog Archive » Log Buffer #57: a Carnival of the Vanities for DBAs — August 10, 2007 @ 4:02 pm

  4. I think the name of the parameter is “db_recovery_file_dest”

    Comment by jose — May 29, 2009 @ 7:22 pm

  5. [...] database and probably that’s why flashback log files kept creating on FLA. Coskan’s posting “Guaranteed Restore Points” also mentioned that “The best part of this feature is, it uses db_file_recovery_dest to keep [...]

    Pingback by Guaranteed Restore Point creates Flashback Log even Flashback logging is Disabled | Oracle Point, Oracle Life — March 31, 2011 @ 10:26 pm


RSS feed for comments on this post. TrackBack URI

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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: