Select Page

Oracle RMAN Backup and Recovery with Restore Points

Author: 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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

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

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

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