See What we are Talking About!
Oracle 10gR2 Flashback Database
By: Ron Shaffer and Joe Cuesta, CommitDBA staff DBAs
November 04, 2011
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 10GR2'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.
- Enabling Flashback in 10GR2:
Alter system set db_recovery_file_dest = xxxxx<path>yyyyyy; (sid is required, * applies to all instances)
Alter system set db_recovery_file_dest_size = 2G sid=zzzzzzz; (sid is required, * applies to all instances)
Alter system set db_flashback_retention_target = 14400; (minutes so 14400 is 10 days)
alter database flashback on;
alter database open;
- Checking flashback for important stats:
Select * from v$flashback_database_log;
OLDEST_FLASHBACK_TIME 19-FEB-2007 22:42:29
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.
- Flashing the database back before the next training session:
flashback database to timestamp to_timestamp(22-FEB-2007 10:00:DD-MON-YYYY HH24:MI:);
alter database open resetlogs;