March 2007

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

March 2007
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Oracle 10g’s Flashback Database Feature

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:

 

  • FLASHBACK DATABASE TO TIMESTAMP [timestamp];
  • FLASHBACK DATABASE TO SCN [scn];

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.

Moving Oracle Databases Across Platforms

Roger Schrag, CTO of Database Specialists has just published a new white paper entitled, Moving Oracle Databases Across Platforms without Export/Import. This is quite topical as we are seeing more companies moving to Linux and operating in multi-platform environments where there is a need to either move a database to a new platform or to share data among databases running on different platforms.

Prior to Oracle 10g, Oracle did not offer cross-platform support for transportable tablespaces. With Oracle 8i and 9i, one of the only supported ways to move an Oracle database across platforms was to export the data from the existing database and import it into a new database on the new server.

This paper opens with a high-level look at Oracle 10g’s cross-platform support for transportable tablespaces and possible uses for this feature. Next you’ll walk through a real-life case study in great detail, followed by a discussion of pitfalls, limitations, and things to keep in mind when preparing to move Oracle data across platforms.

To learn more about the enhancements to the transportable tablespace feature you can read the full white paper at: http://dbspecialists.wpengine.com/presentations/changing_platforms.html.

Resources from the Pacific Northwest

The Puget Sound Oracle Users Group website at http://www.psoug.org is a resource worth bookmarking. Check out their resources section, which as book reviews, presentations, and the ever-expanding Morgan’s Library containing a lengthy topic list Oracle issues.

Need DBA Support? Call on Us

Database Specialists offers remote DBA services with our DBA Pro program. We help companies with Oracle DBAs on staff by providing backup, a safety net, and a resource to call on for additional help and expertise. That means we are there to help when you are on vacation, sick, or out of the office for any reason.

You can offload production support work, monitoring, and proactive maintenance to us so that you can focus on projects that require specific business knowledge. We provide flexible support plans ranging from daily reviews and 24×7 support to monthly reviews with occasional coverage for DBA vacations. For additional information, see our website at http://dbspecialists.wpengine.com/dbapro.html or call 415-344-0500.

 

Here are just a few recent contributions by popular authors and speakers available for download in the presentations section:

  • Learn Data Pump
  • Cursor Sharing
  • Application Server Installation
  • Fusion Middleware Overview

Leave a Reply

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