Practical Guide to Oracle Restore Points and Flashback Database

In critical environments where database changes carry high risk—patches, deployments, or large data loads—it’s essential to have a fast and reliable rollback mechanism. That’s where Oracle Restore Points and Flashback Database come in.

This guide offers a practical, DBA-friendly overview, including prerequisites, commands, best practices, and clear differences between normal and guaranteed restore points.

📌 What is a Restore Point?

A restore point is a named SCN (System Change Number) marker in the Oracle database. It allows you to rewind the entire database to that exact state using Flashback technolog.. without needing a traditional restore from RMAN backups.

✅ Prerequisites

Before creating a restore point, make sure the following configurations are in place:

1. Flashback Database must be enabled:

SELECT flashback_on FROM v$database;

Expected result:

FLASHBACK_ON
------------------
YES

If not enabled, configure the Flash Recovery Area (FRA) and activate Flashback:

-- Set FRA location and size
ALTER SYSTEM SET db_recovery_file_dest_size = 20G SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area' SCOPE=BOTH;
— Enable flashback
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

2. ArchiveLog mode must be active:

ARCHIVE LOG LIST;

If ArchiveLog is disabled, Flashback Database will not work.

🧱 Types of Restore Points

There are two types of restore points:

🔹 Normal Restore Point

CREATE RESTORE POINT before_test;

  • Lightweight and easy to create.

  • Oracle may automatically delete it to free up space.

  • Ideal for non-critical testing.

🔸 Guaranteed Restore Point

CREATE RESTORE POINT before_patch GUARANTEE FLASHBACK DATABASE;

  • Oracle guarantees retention of all required flashback logs.

  • Will not be purged automatically.

  • Heavier on FRA usage.

  • Critical for major operations like patching or upgrades.

🔍 How to Check Existing Restore Points

SELECT name, guarantee_flashback_database, time, scn
FROM v$restore_point;

❌ How to Drop a Restore Point

DROP RESTORE POINT before_patch;

Use this to clean up after a successful operation.

🔙 How to Flashback to a Restore Point

Flashback is only possible if Flashback Database is enabled and the restore point is guaranteed.

1. Shut down and mount the database:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

2. Perform Flashback:

FLASHBACK DATABASE TO RESTORE POINT before_patch;

3. Open with RESETLOGS:

ALTER DATABASE OPEN RESETLOGS;

⚠️ Warning: Flashback rewinds all data to the chosen SCN. Not suitable for partial data recovery.

🆚 Normal vs. Guaranteed Restore Points

Feature Normal Restore Point Guaranteed Restore Point
Retention of Flashback Data Not guaranteed Guaranteed
Auto-deletion possible? Yes No
FRA usage Lower Higher
Suitable for… Tests, quick markers Critical operations
Safe for production use? No Yes

🧠 Best Practices

  • 💡 Use descriptive names:
    rp_before_patch_july, rp_post_upgrade

  • 🧼 Clean up after successful operations:

    DROP RESTORE POINT rp_name;

  • 📊 Monitor FRA usage:

    SELECT * FROM v$recovery_area_usage;

  • 🛡️ Combine with backup strategies:
    Flashback is fast, but not a replacement for full RMAN backups.

📌 Conclusion

Restore Points and Flashback Database are powerful tools every Oracle DBA should master. They provide a reliable safety net, allowing you to experiment, deploy, and upgrade with confidence. Especially in production environments, using Guaranteed Restore Points can be the difference between a controlled rollback and a disaster recovery nightmare.

🧾 Bonus Script – Full Workflow Example

-- Enable flashback (if not already enabled)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
-- Create guaranteed restore point
CREATE RESTORE POINT before_patch GUARANTEE FLASHBACK DATABASE;
-- Perform risky operation...
-- If rollback is needed:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_patch;
ALTER DATABASE OPEN RESETLOGS;
-- Cleanup
DROP RESTORE POINT before_patch;
Tags:

Leave A Comment

All fields marked with an asterisk (*) are required

plugins premium WordPress