You're invited to submit your Oracle-related questions to us at askdbspecialists03@dbspecialists.com. Please include your name and telephone number in your email. This month’s question comes to us from Bill in New York City: We are interested in implementing the “flashback database” feature in Oracle 10g to simplify our build process. How can we do this, and what issues will we face?
Ian Jones of the Database Specialists team responds: “Flashback database” is a new feature in Oracle 10g that allows a DBA to revert an entire database back to an earlier point in time. Depending upon the length of time of the required flashback, it is often significantly faster and easier to flashback the database than perform a point-in-time recovery. In this column, I will present the basic steps for setting up and monitoring the flashback database feature.
Set up the flash recovery area
If your database is not already using a flash recovery area, you must configure one before you can use the flashback database feature. Flashback database requires flashback database logs, special files that Oracle automatically creates, names, and sizes. To enable a flash recovery area, two instance parameters need to be set. The db_recovery_file_dest parameter specifies the location of the flash recovery area, and the db_recovery_file_dest_size parameter specifies the maximum amount of space that Oracle may use in the flash recovery area.
Enable the flashback database feature
To make a database flashback possible, Oracle periodically stores before-image copies of database blocks in the flashback logs. During a flashback operation, Oracle restores these before-images and then rolls the database forward to the required time by applying the necessary archived redo logs. The only instance parameter that needs to be set to manage the flashback database logs is the db_flashback_retention_target parameter, which specifies the maximum number of minutes that we want to be able to flashback to.
This parameter setting combined with the number of block changes occurring in the database will dictate the space required for the flashback logs. Oracle will shorten the flashback retention if there is not enough space available in the flash recovery area.
After setting db_flashback_retention_target, you enable the flashback database feature by issuing the following statement while the database is mounted but not open:
ALTER DATABASE FLASHBACK ON;
The following query confirms that the flashback database feature is enabled:
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------
YES
Perform a flashback
You can flashback the database to a timestamp or a previously recorded SCN. To flashback the database, issue a statement like one of the following when the database is mounted but not open:
Open the database with the RESETLOGS keyword to complete the flashback.
The flashback database feature can be very useful on physical standby databases since it allows us to activate the standby, use it for testing and then flash it back to its pre-activated state and resume applying redo from the primary database. In this situation you would use the following flashback statement:
FLASHBACK DATABASE TO BEFORE LAST RESETLOGS;
Monitor flashback status
Probably the most useful view for monitoring the flashback database feature is v$flashback_database_log. This view shows the oldest time and SCN available for flashback, the current size of the flashback data, and the estimated flashback size based on recent workload:
SELECT retention_target "RETENTION TARGET (MINS)",
ROUND ((SYSDATE - oldest_flashback_time) * 24 * 60, 0)
"CURRENT RETENTION (MINS)",
TO_CHAR (oldest_flashback_time,
'DD-MON-YYYY HH24:MI:SS') "OLDEST FLASHBACK TIME",
ROUND (flashback_size / 1024 / 1024, 1)
"FLASHBACK SIZE (M)",
ROUND (estimated_flashback_size / 1024 / 1024, 1)
"ESTIMATED FLASHBACK SIZE (M)"
FROM v$flashback_database_log;
CURRENT ESTIMATED
RETENTION RETENTION OLDEST FLASHBACK FLASHBACK
TARGET (MINS) (MINS) FLASHBACK TIME SIZE (M) SIZE (M)
------------- ---------- -------------------- ---------- ----------
120 178 21-FEB-2007 16:21:54 1339.1 2001.9
This shows that the current flashback log space usage is 1339.1 MB and that we are currently exceeding our retention target of two hours. Based on the recent workload we expect our space requirements to grow to 2001.9 MB to satisfy our two hour retention target. If the oldest flashback time is less than the retention target, it indicates that we have insufficient space allocated in the flash recovery area.
Consider performance
When a database is enabled for flashback, a new background process called RVWR is created. This process writes flashback data to the flashback logs. A new wait event called “flashback buf free by RVWR” shows delays writing to the flashback logs. If this wait event becomes significant then the accumulation of the flashback data is causing delays. There is little that can be done about this except to increase the disk bandwidth to the flash recovery area. Generally, the flash recovery area is a candidate for “slower, cheaper disks” since it generally holds just disk backups. However, this may not be sufficient for the flashback logs.
There is also a new system statistic in v$sysstat called “flashback log writes” that indicates the number of write operations to the flashback logs. In addition, there is a new view called v$flashback_database_stat that records the number of bytes written to the flashback logs, the database files and the redo logs during various intervals. This provides an indication of the relative overhead of the flashback logs and the flashback database feature which uses them.
About The Specialist 