Oracle 10GR2 Flashback Database

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.

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

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.

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;
   
   

   OLDEST_FLASHBACK_SCN	OLDEST_FLASHBACK_TIME	RETENTION_TARGET	FLASHBACK_SIZE	ESTIMATED_FLASHBACK_SIZE
   11436633	19-FEB-2007 22:42:29	14400	185688064	1150156800

   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 on how the Ross Group can help you solve your IT problems, contact Gary Codeluppi at 800.734.9304 or visit the Ross Group Inc web site at www.rossgroupinc.com.