How to quickly restore to a clean database using Oracle’s restore point

How to quickly restore to a clean database using Oracle’s restore point


Applies to:

Oracle database – 11gR2


Problem:


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.


Solution:


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.

References:





Article ID: SLN310475

Last Date Modified: 08/10/2018 01:12 PM


Rate this article

Accurate
Useful
Easy to understand
Was this article helpful?
Yes No
Send us feedback
Comments cannot contain these special characters: <>()\
Sorry, our feedback system is currently down. Please try again later.

Thank you for your feedback.