How to use the Oracle Guaranteed Restore Points (GRP) in Oracle database

Leave a reply

When performing risky database upgrades or schema changes, Oracle Guaranteed Restore Points (GRP) allow for a quick rollback. This guide covers the necessary commands and steps to set up, use, and remove GRPs.

Prerequisites

Before making a GRP, check that your database is in ARCHIVELOG mode, has Flashback on, and enough space in the Flash Recovery Area (FRA).

-- 1. Check Archive Log Mode
SELECT LOG_MODE FROM V$DATABASE;
-- 2. Check if Flashback is Enabled
SELECT FLASHBACK_ON FROM V$DATABASE;
-- If OFF, enable it (requires downtime / mount state if not already set up with an FRA)
-- ALTER DATABASE ARCHIVELOG;
-- ALTER DATABASE FLASHBACK ON;
-- 3. Check FRA Usage & Free Space
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
-- List all active restore points
SELECT * FROM V$RESTORE_POINT;
-- Check current incarnation (changes after opening with resetlogs)
SELECT * FROM V$DATABASE_INCARNATION;

Creating the Guaranteed Restore Point

-- Create the Guaranteed Restore Point
CREATE RESTORE POINT pre_upgrade_grp GUARANTEE FLASHBACK DATABASE;
-- Verify creation and status
SELECT NAME, CREATED, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK, STORAGE_SIZE 
FROM V$RESTORE_POINT;

Scenario A: The Upgrade Succeeded

If the upgrade is successful and doesn’t need a rollback, delete the restore point right away to free up FRA space.

-- Drop the restore point
DROP RESTORE POINT pre_upgrade_grp;

Scenario B: The Upgrade Failed (The Rollback)

If there are data issues or errors, follow these steps to restore the database.

-- 1. Close the database and mount it
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
-- 2. Flashback database to the restore point
FLASHBACK DATABASE TO RESTORE POINT pre_upgrade_grp;
-- 3. Open the database with resetlogs
ALTER DATABASE OPEN RESETLOGS;