Oracle : Restore points

Restore point plays important role in restore and recovery scenario as it allows to store state of the database to certain point, Lets discuss oracle restore points in details with example

• Restore point is said to be "point" in database which is associated with timestamp or an SCN of the database
• Restore point is used to flash back a table or the whole database to certain point specified as per restore point
• Various RMAN operations such as backup and archival backup also uses these restore point
• Restore point can be name up to 128 characters
• There can be 2048 restore points in database provided database has enough FRA
• Privileges required to create restore points

Normal restore point	Guaranteed restore point
SELECT ANY DICTIONARY	SYSDBA
FLASHBACK ANY TABLE	SYSBACKUP
SYSDBA	SYSDG
SYSBACKUP
SYSDG

Privileges required to view or use of restore point

      SELECT ANY DICTIONARY

      FLASHBACK ANY TABLE

      SYSDBA

      SYSBACKUP or

      SYSDG or

      SELECT_CATALOG_ROLE

Requirements:

• Database must have fast recovery area (FRA) set for guaranteed restore point
• Database is in Archive log mode (guaranteed restore point)

Types of Restore points

There are two types of restore points

1. Guaranteed restore point
2. Normal restore point

Guaranteed restore point

• A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting
• Guaranteed restore point must be dropped explicitly by user using DROP RESTORE POINT command
• Guaranteed restore point never ages out

Normal Restore Point

• A normal restore point enables you to flash the database back to a restore point within time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting
• Normal restore point can be dropped explicitly
• The control files stores name of restore point and the SCN

Creating restore point

In below example, we are going to create guaranteed restore point

Check for flashback on in the database using below query

SQL>  select name,FLASHBACK_ON,open_mode from v$database;

NAME      FLASHBACK_ON     OPEN_MODE

--------- ------------     --------------------------

TECHNO_DB  NO              READ WRITE

Turn the flashback on using below statement

SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

YES

Check for Fast Recovery Area size and location

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

db_recovery_file_dest                string      +FLASH

db_recovery_file_dest_size           big integer 100G

SQL>

Check for any restore point using v$restore_point view

set lines 200

col TIME for a40

col NAME for a40

select inst_id,NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

no rows selected

Create actual restore point using CREATE RESTORE POINT command

SQL> create restore point RESTORE_POINT_20FEB2019 guarantee flashback database;

Restore point created.

Verify if restore point is created

set lines 200

col TIME for a40

col NAME for a40

select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

    NAME                 TIME           GUA STORAGE_SIZE/1024/1024/1024

-------------         ------------ -------------------------- --- ----------------

RESTORE_POINT_20FEB2019 20-FEB-19 06.20.40.000000000 AM    YES        2

DROP Restore Point

Drop restore point can be performed using DROP RESTORE POINT command and we need turn off flashback_on option

SQL> DROP RESTORE POINT RESTORE_POINT_20FEB2019;

Restore point dropped.

SQL> alter database flashback on;

Database altered.