Applies to:
Oracle database – 11gR2

Often while conducting benchmarking tests, it is required to load a clean database before the start of a new run. One way to ensure a clean database is to recreate the entire database before each test run, but depending on the size of it, this approach may be very time consuming or inefficient.

This article describes how to use Oracle’s flashback feature to quickly restore a database to a state that existed just before running the workload. More specifically, this article describes steps on how to use the ‘guaranteed restore points’.

Restore point:
Restore point is nothing but a name associated with a timestamp or an SCN of the database. One can create either a normal restore point or a guaranteed restore point. The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

NOTE: In this article Flashback logging was not turned ON.

Guaranteed Restore point:

Prerequisites: Creating a guaranteed restore point requires the following prerequisites:

  • The user must have the SYSDBA system privileges
  • Must have created a flash recovery area
  • The database must be in ARCHIVELOG mode

Create a guaranteed restore point:
After you have created or migrated a fresh database, first thing to do is to create a guaranteed restore point so you can flashback to it each time before you start a new workload. The steps are as under:

  1. $> su – oracle
  2. $> sqlplus / as sysdba;
  3. Find out if ARCHIVELOG is enabled
    SQL> select log_mode from v$database;
    If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step 8 below.
  4. SQL> shutdown immediate;
  5. SQL> startup mount;
  6. SQL> alter database archivelog;
  7. SQL> alter database open;
  8. SQL> create restore point CLEAN_DB guarantee flashback database;
    where CLEAN_DB is the name given to the guaranteed restore point.

Viewing the guaranteed restore point
SQL> select * from v$restore_point;

Verify the information about the newly created restore point. Also, note down the SCN# for reference and we will refer to it as “reference SCN#”

Flashback to the guaranteed restore point
Now, in order to restore your database to the guaranteed restore point, follow the steps below:

  1. $> su – oracle
  2. $> sqlplus / as sysdba;
  3. SQL> select current_scn from v$database;
  4. SQL> shutdown immediate;
  5. SQL> startup mount;
  6. SQL> select * from v$restore_point;
  7. SQL> flashback database to restore point CLEAN_DB;
  8. SQL> alter database open resetlogs;
  9. SQL> select current_scn from v$database;

Compare the SCN# from step 9 above to the reference SCN#.

NOTE: The SCN# from step 9 above may not necessarily be the exact SCN# as the reference SCN# but it will be close enough.