Using Flashback during repeated load tests

We are in the middle of migrating several databases to a new data center. One of the requirement is to do rigorous load and stress tests on the new servers to make sure they can handle the load and the configuration is correct. For this purpose, there are several load/stress test scripts are written and tested using OATS [Oracle Application Testing Suite]. 

Since the load/stress tests are done repeatedly, the test data [database] need to be restored to its pre-test state before doing the next test. As these database sizes are large, it will take a while to do the restore. But, the flashback feature of the database comes handy.

Enable Flashback

To enable flashback, from 11gR2 onward there is no need to restart the database in mount mode. Flashback can be enabled and disabled while the database is online.

Before you enable flashback, set the following parameters.

 

Enable flashback using:

You can verify if flashback is enabled by querying v$database.


 

Flashback logs are written to the directory speficied by the DB_RECOVERY_FILE_DEST parameter. The retention of these files depends on the setting of parameter:

 

The database must be in ARCHIVELOG mode.

Create Restore Point

During load/stress tests we like to make sure the database is flashbackable to pre-test state. We ensure this by creating a guaranteed restore point.

Restore points created in the database can be viewed from v$restore_point view.


You will have to keep track of the space usage on the flash recovery area to make sure you have enough free space.


After the test is done, it is time to flashback!

Flashback the Database

To flashback, the database has to be restarted in mount state. Here the example shows a RAC database shutdown and startup.

Use RMAN to do the flashback, the advantage is that if any archivelog that is not present in the disk is required for flashback recovery, RMAN can automatically restore the archivelogs from the backup media.


Now ready to flashback the database…


 

Database flashback is done!
You may now start the other instances in the cluster, if applicable.

 

Flashback using time, instead of restore point.

Sometimes, it is required to flashback a database without a restore point – to a time. It can be done using the syntax:
If you are restoring to a time, instead of restore point, use the below syntax.

 

Leave a Reply

%d bloggers like this: