CommitDBA

Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs


See What we are Talking About!

CommitDBA Blog


Oracle 10gR2 Flashback Database

By: Ron Shaffer and Joe Cuesta, CommitDBA staff DBAs

November 04, 2011


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 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.

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 = 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)


    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     11436633
    OLDEST_FLASHBACK_TIME     19-FEB-2007 22:42:29
    RETENTION_TARGET     14400
    FLASHBACK_SIZE     185688064
    ESTIMATED_FLASHBACK_SIZE     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:DD-MON-YYYY HH24:MI:);
    alter database open resetlogs;

From database assessments to complete database management, CommitDBA can assist your company today. For more information about how CommitDBA can work for you – give us a call at (800) 734-9304.



Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today