Select Page

Oracle RMAN Backup and Recovery with Restore Points

Cindy Putnam | | May 3, 2019

Oracle restore points let you “rewind” an Oracle database to a given point in the past. This helps protect you from errors and accidental mishaps, preserving your data in the event of a mistake or failure.

 
Restore points can also revert the database back to a previous state. This functionality is useful during benchmark testing, which may require the database to be in the same pristine state before each iteration of the test.

In this article, we’ll discuss how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

What is a Restore Point in Oracle Database?

A restore point is a name assigned to a system change number (SCN) in Oracle. An SCN is a number that uniquely identifies each new change to an Oracle database; this number is incremented whenever users commit a new transaction to the database.

The restore point and the SCN are stored together in a control file, which is a small binary file that contains information about the physical structure of an Oracle database.

There are two types of restore points in Oracle Database: a normal restore point and a guaranteed restore point. The differences are:

  • Guaranteed restore points let you revert to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter. In other words, using guaranteed restore points is always possible as long as you have enough space in the flash recovery area.
  • Guaranteed restore points never “age out” of the control file, as normal restore points do. Instead, you must always explicitly drop (i.e. delete) them.

How to Create a Guaranteed Restore Point in Oracle Database

In order to create a guaranteed restore point in Oracle Database, you need the following prerequisites:

 
The first step is to create a guaranteed restore point, so that you can return to it each time that you start a new workload. First, run the commands:

1. $> su – oracle

2. $> sqlplus / as sysdba;

To find out if ARCHIVELOG is enabled, run the command:

3. SQL> select log_mode from v$database;

If ARCHIVELOG is not enabled then continue; otherwise, skip to step 8.

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.

To view the guaranteed restore point, run the command:

9. SQL> select * from v$restore_point;

How to Flashback to a Guaranteed Restore Point

To restore your database to a 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 this SCN to the reference SCN.
 
To flashback to a point in time:

1. $> su – oracle

2. $> RMAN

3. RMAN> connect target /

4. RMAN> shutdown immediate;

5. RMAN> startup mount;

6. RMAN> run

7. RMAN> {

8. RMAN> set until restore point <restore_point_name>;

9. RMAN> restore database;

10. RMAN> recover database;

11. RMAN> }

12. RMAN> alter database open resetlogs;

13. Database opened

14. RMAN>

15. SQL> select current_scn from v$database;

How to Drop a Guaranteed Restore Point

Once a restore point is no longer needed, the best practice is to drop (i.e. remove) the restore point, because it uses a good deal of space in the flash recovery area.

To show the restore point names:

select name from v$restore_point;

To drop a given restore point:

drop restore point <restore point name>;

Final Thoughts

Oracle restore points and guaranteed restore points can easily restore a database to a previous point in time or to a “golden” state. From benchmark testing and loss of data to creating checkpoints before major DML or DDL releases, there are many possible uses for restore points in Oracle Database.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over one hundred logins in the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS