Oracle 10GR2 Flashback Database

Author: Ron Shaffer and Joe Cuesta, Ross Group, Inc.

June 2010

Situation

The company is implementing a new application for managing its business and a database has been configured with the perfect data and user accounts for the training phase.

Problem

Employees will be using the data on a regular basis throughout the training and it must be refreshed each training session.

Solution

There are many ways to approach this problem from backup and restore to export and even duplicate database approaches. The approach chosen here is 10G’s feature of Flashback Database. The benefits are many. First, for this instance the changes in the database only amount to ~150MB per day so a ten day window for flashback will only take ~1.5GB of storage. This is a fraction of the size of a full database backup or the duplicate database method.

Tips

Determine how long you need to flashback. How long will training last? Determine how much data is changing based on log switches and use that as a starting point.

1. Enabling Flashback in 10GR2:

Alter system set db_recovery_file_dest = ‘<path>’ sid=’*’; (sid is required, * applies to all instances)

Alter system set  db_recovery_file_dest_size = 2G sid=’*’; (sid is required, * applies to all instances)

Alter system set  db_flashback_retention_target = 14400; (minutes so 14400 is 10 days)

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

2. Checking flashback for important stats:

Select * from v$flashback_database_log;

Sizes are in bytes so divide by 1048576 to get MB. The target is in minutes and set your NLS_DATE_FORMAT or use to_char() to get the right time format.

3. Flashing the database back before the next training session:

shutdown immediate;

startup mount;

flashback database to timestamp to_timestamp(’22-FEB-2007 10:00:00’,’DD-MON-YYYY HH24:MI:SS’);

alter database open resetlogs;

If you need more information or support with your company’s databases, contact Gary Codeluppi at 937-912-3273 or visit the Ross Group Inc web site at www.rossgroupinc.com.