Tips and Tricks: Oracle 10G Training Instance Needs Data Refreshed
Author: Ron Shaffer
June 2007
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
The students will be using the data on a regular basis throughout the class and it must be refreshed prior to each class to a clean condition.
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 Oracle’s 10G new 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 and Tricks
Determine how long you need to flashback. Is the class a week long class or a simple one day seminar? Determine how much data is changing based on log switches and use that as a starting point. If you don’t know, set up flashback and find out, it is training right?!
1. Enabling Flashback in 10GR2:
- Alter system set db_recovery_file_dest = ‘
’ 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_ |
OLDEST_ |
RETENTION_ |
FLASHBACK_SIZE |
ESTIMATED_ |
|
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 class:
- 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;
Optional Extra Credit: Set a Guaranteed Restore Point at the SCN where the data is just the way the trainers want it.
