Oracle RMAN Backup and Recovery with Restore Points

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:

• You must have SYSDBA system privileges.
• You must have created a flash recovery area.
• The database must be in ARCHIVELOG mode.

 
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.