Guaranteed Restore Point

Guaranteed Restore Points can be used as a quick way of rolling back an entire database to an earlier point in time. For example, you may have implemented a change and testing has proved that it needs to be rolled back.


Caution: This method is not suitable if other users may be using the database during your change (rolling back will also undo their changes).Caution: NOLOGGING operations can cause corruption if your restore point falls midway through a nologging transaction.Note: The database must be in ARCHIVELOG mode to set a Guaranteed Restore Point.Note: Monitor the FRA whilst a Guaranteed Restore Point exists.. it will fill.Note: Flashing back to a guaranteed restore point will not reinstate a dropped datafile (this would need point in time media recovery)

Check

To list the Restore Points that exist in the database...

SELECT * FROM v$restore_point;

Create

To create a Guaranteed Restore Point use...

CREATE RESTORE POINT arbitraryRestorePointName GUARANTEE FLASHBACK DATABASE;

Drop

DROP RESTORE POINT arbitraryRestorePointName;

Usage Examples

Reverting Regular DML and DDL

This could include rolling back things like Application Upgrades (where data loss is acceptable) or rolling back APEX Upgrades (where instance has not been used by any other users during the Upgrade). It is ideal for testing processes, rolling back and repeating (e.g. testing release procedures) on a test instance.

CREATE RESTORE POINT arbitraryRestorePointName GUARANTEE FLASHBACK DATABASE;

<Perform your changes>

STARTUP FORCE MOUNT;

FLASHBACK DATABASE TO RESTORE POINT arbitraryRestorePointName;

ALTER DATABASE OPEN RESETLOGS;

DROP RESTORE POINT arbitraryRestorePointName;

Reverting Oracle Upgrades

The fact that the ORACLE_HOME changes during the Upgrade process complicates the rollback slightly. Failure to follow these steps correctly will lead to unrecoverable errors which would need a full database restore from backup.

Before you start the upgrade...

CREATE RESTORE POINT arbitraryRestorePointName GUARANTEE FLASHBACK DATABASE;

To rollback...

Set the environment (ORACLE_HOME, PATH) to reflect the new version (e.g. 12)

sqlplus / as sysdba

STARTUP FORCE MOUNT;

FLASHBACK DATABASE TO RESTORE POINT arbitraryRestorePointName;

SHUTDOWN IMMEDIATE

EXIT

Set the environment (ORACLE_HOME, PATH) to reflect the old version (e.g. 11)

sqlplus / as sysdba

STARTUP MOUNT;

ALTER DATABASE OPEN RESETLOGS;

DROP RESTORE POINT arbitraryRestorePointName;


Failure to follow these steps will lead to these unrecoverable errors:
ALTER DATABASE OPEN RESETLOGS*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 2ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifierProcess ID: 50397344Session ID: 403 Serial number: 5536
Attempting to startup mount without disconnecting will give...
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0Unsafe to proceedORA-03114: not connected to ORACLE

Reverting Oracle Upgrades in a Data Guard Configuration

Failure to follow these steps correctly will lead to unrecoverable errors which would need a full database restore from backup and/or a Standby rebuild.

Before you start the upgrade...

On Standby...

CREATE RESTORE POINT arbitraryRestorePointName GUARANTEE FLASHBACK DATABASE;

On Primary...

CREATE RESTORE POINT arbitraryRestorePointName GUARANTEE FLASHBACK DATABASE;


NOTE: You must create the Restore Point on the Standby first. i.e. when you flashback both databases the Standby must be slightly older then the Primary. The Standby must not have a higher SCN than the Primary otherwise managed recovery will fail.

To rollback...

On Primary...

Set the environment (ORACLE_HOME, PATH) to reflect the new version (e.g. 12)

sqlplus / as sysdba

STARTUP FORCE MOUNT;

FLASHBACK DATABASE TO RESTORE POINT arbitraryRestorePointName;

SHUTDOWN IMMEDIATE

EXIT

Set the environment (ORACLE_HOME, PATH) to reflect the old version (e.g. 11)

sqlplus / as sysdba

STARTUP MOUNT;

ALTER DATABASE OPEN RESETLOGS;

DROP RESTORE POINT arbitraryRestorePointName;

On Standby...

Set the environment (ORACLE_HOME, PATH) to reflect the new version (e.g. 12)

sqlplus / as sysdba

STARTUP FORCE MOUNT;

FLASHBACK DATABASE TO RESTORE POINT arbitraryRestorePointName;

SHUTDOWN IMMEDIATE

EXIT

Set the environment (ORACLE_HOME, PATH) to reflect the old version (e.g. 11)

sqlplus / as sysdba

STARTUP MOUNT;

Monitoring Flashback Progress

column opname format a30

column complete format a8

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select opname,

round(sofar/totalwork*100)||'%' complete,

start_time,

(sysdate+time_remaining/86400) predicted_finish

from v$session_longops

where time_remaining > 0;