A Oracle restore point is an alias corresponding to a system change number (SCN). Oracle restore point Provides capabilities related to Flashback Database there are two types of restore points.

1- Normal Restore Points

  • Normal restore points are the aliases for SCNs in recovery operations. You can refer to the target time using the name of the restore point instead of a time expression or SCN
  • The name of the restore point and the SCN are recorded in the control file.
  • Used with Flashback Database, Flashback Table, or point-in-time recovery.
  • Normal restore points are very lightweight. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.
  • Followings Commands Supports the Use of Restore Points
RECOVER DATABASE AND FLASHBACK DATABASE commands IN RMAN
FLASHBACK TABLE statement IN SQL*Plus

2- Guaranteed Restore Points

  • Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However, they also provide specific functionality related to the use of the Flashback Database feature.
  • A guaranteed restore point can be used to revert a whole database to a known good state days or weeks ago.
  • Shrinking a datafile or dropping a tablespace can prevent flashing back the affected datafiles to the guaranteed restore point.
  • Guaranteed restore points provide a useful alternative to storage snapshots, which are often used to protect a database before risky operations like large-scale database updates, or application patching or upgrading.
  • You can create a guaranteed restore point on a primary or standby database, and then perform the risky operation, with the certainty that the required flashback logs are retained.
  • Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Oracle Restore Point Operations

Following operations can be perform using oracle restore point.

  1. Create Normal Restore Point
  2. Create Guaranteed Restore Point
  3. List Normal Restore Point
  4. List Guaranteed Restore Point
  5. Drop Restore Point

a. Create Normal Restore Point

  • The database can be open or mounted when creating restore points. If it is mounted, then it must have been shut down cleanly (unless this is a physical standby database).
  • Use the CREATE RESTORE POINT statement in SQL*Plus
--Create Normal restore points
SQL> CREATE RESTORE POINT before_upgrade;

b. Create Guaranteed Restore Point

  • The COMPATIBLE initialization parameter must be set to 10.2 or greater.
  • The database must be running in ARCHIVELOG mode.
  • A flash recovery area must be configured Guaranteed restore points use a mechanism similar to flashback logging. Oracle must store the required logs in the flash recovery area.
  • If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point.
--Create guaranteed restore points
SQL> CREATE RESTORE POINT before_upgrade
GUARANTEE FLASHBACK DATABASE;

c. List Normal Restore Point

-- To see a list of the currently defined restore points 
SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

For normal restore points, STORAGE_SIZE is zero.

d. List Guaranteed Restore Point

--To view only the guaranteed restore points: 
SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

e. Drop restore points

--Same statement is used to drop both normal and guaranteed restore points.
SQL> DROP RESTORE POINT before_app_upgrade;
Go to top
JSN Boot template designed by JoomlaShine.com