Guaranteed Restore Point creates Flashback Log even Flashback logging is Disabled


;

Recently, we experienced the pressure of nearly exhausted disk space on one of our production database servers. The problem is that the Flashback Recovery Area (FLA), where we do daily backup, has been facing shortage of disk space (90% full). As we have preserved sufficient space for FLA (near two times of actual size of database) and configured backup retention copy to 1,  that problem should be caused by exceptional database activities. After investigation, we shortly found that a folder /flashback, which sits under FLA, consumed over 30% disk space on FLA.

It’s doubtable that the files within with extension name flb are Flashback Log Files. But, the question was raised next: Why is flashback log files created even Flashback Logging is disabled?

According to “Oracle Database Backup and Recovery Basics 10gR2”, flashback log files are deleted automatically when:

  • If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
  • If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

As new feature since Oracle 10g database, Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. To enable flashback, we need to explicitly issue command “alter database flashback on” and the flashback status could be found at view v$database.

Even I’m sure that flashback feature was disabled, I would like to check the view v$database again.

SQL> select name, log_mode, flashback_on from 
v$database;

NAME  LOG_MODE   FLASHBACK_ON  
----- ---------  ------------------
ORCL  ARCHIVELOG RESTORE POINT ONLY            

After I issued this query, I surprisedly found that the value of field “FLASHBACK_ON” is “RESTORE POINT ONLY”, instead of “YES” and “NO”. Thus, it’s obvious that we have (guaranteed) restore point inside the 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 the changed blocks, even if flashback logging is not enabled for your database.”

Next, I executed query to check the Restore Point on this database.

SQL> select name, scn, time, guarantee_flashback_database 
from v$restore_point;

NAME    SCN     TIME       GUA
------  ------  ---------  -------------

ORCL_AF_UPGRADE_MAY22_2010_0600AM                                              
3322582780                                                                      
22-MAY-10 05.56.39.000000000 AM

YES

The query results clearly  shows that there is Guaranteed Restore Point created almost year ago. And then, I tried to drop this obsolete restore point.

SQL> drop restore point ORCL_AF_UPGRADE_MAY22_2010_0600AM;

Restore point dropped.

This command execution has been last for quite long time and during the processing, we found that the flashback log files within folder flashback were purged automatically. Meanwhile, I monitored the process with TOAD and noticed that the major session waits of this process is “control file parallel write”. It’s quite understandable that the “drop” command kept reading/writing the control file to reflect the SCN changes incurred with this command.

session_wait 

Once the execution was finished, the folder flaskback has been purged to empty. Out attempt to release disk space then got success by lowering the disk space utilization from 90% to 66%. Also, the field “FLASHBACK_ON” of view v$database was set back to “NO” instead.

SQL> select name, log_mode, flashback_on from 
v$database;

NAME  LOG_MODE    FLASHBACK_ON
----- ----------- ------------
ORCL   ARCHIVELOG           NO

My approach to solve this problem verified that “Guaranteed Restore Points” does generate flashback log files even flashback logging is disabled.

Furthermore, I would like to share several practical points relating to restore point from AskTom.

Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.

This entry was posted in Oracle Case Study, Oracle Point and tagged , . Bookmark the permalink.

;

4 Responses to Guaranteed Restore Point creates Flashback Log even Flashback logging is Disabled

  1. Lior says:

    Thanks, very interesting

  2. ahmed.ibrahim says:

    thanks very much

  3. Raghava says:

    Researched a lot for restore point only for control file loss scenario. Helped a lot to turn off the flashback database. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>