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.
To list the Restore Points that exist in the database...
SELECT * FROM v$restore_point;
To create a Guaranteed Restore Point use...
CREATE RESTORE POINT arbitraryRestorePointName GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT arbitraryRestorePointName;
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;
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 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;
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;