There are two types of restore points: normal and guaranteed. The main difference between a guaranteed restore point and a normal restore point is that a guaranteed restore point is not eventually aged out of the control file; a guaranteed restore point will persist until you drop it.

Guaranteed restore points do require an FRA. However, for incomplete recovery using a guaranteed restore point, you do not need to have flashback database enabled.

You can create a normal restore point using SQL*Plus, as follows:

SQL> create restore point MY_RP;

This command creates a restore point, named MY_RP, which is associated with the SCN of the database at the time the command was issued. You can view the current SCN of your database, as shown here:

SQL> select current_scn from v$database;

You can view restore point information in the V$RESTORE_POINT view, like so:

SQL> select name, scn from v$restore_point;

The restore point acts like a synonym for the particular SCN. The restore point allows you to restore and recover to an SCN without having to specify a number. RMAN will restore and recover up to, but not including, the SCN associated with the restore point.

This example restores and recovers to the MY_RP restore point:

$ rman target / RMAN> startup mount;

RMAN> restore database until restore point MY_RP; RMAN> recover database until restore point MY_RP; RMAN> alter database open resetlogs;

Restoring Tables to a Previous Point

You can restore individual tables from RMAN backups via the RECOVER TABLE command. This gives you the ability to restore and recover a table back to a point in time in the past.

The table-level restore feature uses a temporary auxiliary instance and the Data Pump utility. Both the auxiliary instance and Data Pump create temporary files when restoring the table.

Before initiating a table-level restore, first create two directories: one to hold files used by the auxiliary instance and one to store a Data Pump dump file:

$ mkdir /tmp/oracle $ mkdir /tmp/recover

The prior two directories are referenced within the RECOVER TABLE command via the AUXILIARY DESTINATION and DATAPUMP DESTINATION clauses. In the following bit of code, the INV table, owned by MV_MAINT, is restored as it was at a prior SCN:

RMAN> recover table mv_maint.inv of pluggable database salepdb until scn 4689805 auxiliary destination ‘/tmp/oracle’ datapump destination ‘/tmp/recover’;

Providing that RMAN backups are available that contain the state of the table at the specified SCN, a table-level restore and recovery is performed. You can also restore a table to an SCN, a point in time, or a log sequence number.

When RMAN performs a table-level recovery, it automatically creates a temporary auxiliary database, uses Data Pump to export the table, and then imports the table back into the target database as it was at the specified restore point. After the restore is finished, the auxiliary database is dropped, and the Data Pump dump file is removed.