Skip to main content
  • Place orders quickly and easily
  • View orders and track your shipping status
  • Enjoy members-only rewards and discounts
  • Create and access a list of your products
  • Manage your Dell EMC sites, products, and product-level contacts using Company Administration.

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

Summary: Our charter is to deliver solutions that simplify IT by providing database solutions, custom development, dynamic datacenters, flexible computing.

This article may have been automatically translated. If you have any feedback regarding its quality, please let us know using the form at the bottom of this page.

Article Content


Symptoms

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

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

 
SLN310475_en_US__1icon 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 Properties


Last Published Date

21 Feb 2021

Version

3

Article Type

Solution

Rate This Article


Accurate
Useful
Easy to Understand
Was this article helpful?

0/3000 characters