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;