Procedures for Executing Flashback Database Using a Guaranteed Restore Point

English

When updating a system or applying large-scale data patches, the anxiety of “what if it fails?” is always present. In such cases, Oracle’s Guaranteed Restore Point is an invaluable tool.

In this article, we will provide a clear explanation of how to use “Guaranteed Restore Points”—one of the most powerful features in the Flashback family—along with practical operational examples.

🔰 What is a Guaranteed Restore Point?

A restore point is a feature that allows you to place a “bookmark” at a specific point in time in the database.

By adding the GUARANTEE option, you prevent the logs required for a flashback from being automatically deleted from the Fast Recovery Area (FRA). This ensures that, as long as disk space is available, you can “guaranteed” rewind the database to that exact point, even if a massive amount of updates occur.

✅ Common Use Cases

  • Patching and Upgrades: As a “fallback” measure if the work fails.
  • Application Releases: A safety net before large-scale data migrations.
  • Initialization of Verification Environments: Instantly resetting to the starting state after tests are completed.

🗂 Overall Flow (Diagram)

  1. Step 1: Configure Archive Log + FRA (Assumption: already configured)
  2. Step 2: Create a Guaranteed Restore Point (Mark the point to return to)
  3. Step 3: Perform arbitrary operations or changes (Patching, testing, etc.)
  4. Step 4: Rewind via Flashback (Specify the restore point)
  5. Step 5: Open with RESETLOGS

🛠 Step 1: Preparation (Prerequisites)

To use this feature, the following configurations must be complete:

  • Archive Log Mode: ARCHIVELOG
  • FRA (Fast Recovery Area): Configured (DB_RECOVERY_FILE_DEST)
  • Flashback Enabled: ALTER DATABASE FLASHBACK ON;

🛠 Step 2: Creating a Guaranteed Restore Point

Immediately before starting your work, create the restore point using SQL*Plus.

-- Execute as a SYSDBA user
CREATE RESTORE POINT before_test GUARANTEE FLASHBACK DATABASE;

Engineer’s Insight: The GUARANTEE keyword is the key. Without it, you risk logs being deleted when FRA capacity runs low, making it impossible to rewind.

🧪 Step 3: Arbitrary Operations (Example: Patching or Testing)

Now, perform your work as usual. Even if you accidentally delete data, as in the example below, it is fine.

-- Check current status
SQL> SELECT * FROM scott.sales;

        ID ITEM           AMOUNT
---------- ---------- ----------
         1 LaptopPC       150000
         2 Mouse            2000

-- Accidental operation: Deleted the table!
SQL> DROP TABLE scott.sales;

Table dropped.

-- Unreadable
SQL> SELECT * FROM scott.sales;
ORA-00942: table or view does not exist

🔁 Step 4: Executing Flashback (Rewinding)

If a problem occurs, use RMAN (Recovery Manager) to return to the created point in one go.

① Start the Instance in MOUNT Mode

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

② Execute Flashback in RMAN

$ rman target /
RMAN> FLASHBACK DATABASE TO RESTORE POINT before_test;

🔓 Step 5: Open with RESETLOGS

After the flashback, the database must be opened with a new timeline (incarnation).

RMAN> ALTER DATABASE OPEN RESETLOGS;

This successfully recovers the deleted table, and normal operations can resume.

SQL> SELECT * FROM scott.sales; -- The table is back!

Full Execution Log

SQL> SELECT * FROM scott.sales;

        ID ITEM           AMOUNT
---------- ---------- ----------
         1 LaptopPC       150000
         2 Mouse            2000

SQL> CREATE RESTORE POINT before_test GUARANTEE FLASHBACK DATABASE;  ★Create Guaranteed Restore Point

Restore point created.

SQL> DROP TABLE scott.sales;  ★Delete table

Table dropped.

SQL> SELECT * FROM test1.sales;
SELECT * FROM test1.sales
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1543500120 bytes
Fixed Size                  8925528 bytes
Variable Size             889192448 bytes
Database Buffers          637534208 bytes
Redo Buffers                7847936 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
[oracle@v19single ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 29 17:15:22 2025
Version 19.21.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to target database: V19 (DBID=2957249400, not open)

RMAN> FLASHBACK DATABASE TO RESTORE POINT before_test;  ★Execute Flashback Database to Guaranteed Restore Point

Starting flashback at 25-03-29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK


Starting media recovery
Media recovery complete, elapsed time: 00:00:01

Finished flashback at 25-03-29

RMAN> ALTER DATABASE OPEN RESETLOGS;

Statement processed

RMAN> exit


Recovery Manager complete.
[oracle@v19single ~]$ sqlplus scott/tiger

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 29 17:16:06 2025
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 29 2025 16:49:10 +09:00


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SQL> col item for a10
SQL> SELECT * FROM scott.sales;  ★The table is restored

        ID ITEM           AMOUNT
---------- ---------- ----------
         1 LaptopPC       150000
         2 Mouse            2000

❌ Delete the Restore Point When No Longer Needed

Be sure to delete it once your work is finished.

DROP RESTORE POINT before_test;

Note: Ensure the name matches the one you created (e.g., before_test or before_patch).

❓ FAQ: Frequently Asked Questions

Q: Can I use this even if Flashback Database (FLASHBACK ON) is disabled?

A: Yes. If it is “Guaranteed,” you can create it even if global database flashback is off. However, keep in mind that log generation begins the moment it is created, so watch your FRA free space carefully.

Q: Can I create multiple restore points?

A: Yes. However, if you leave old points while creating new ones, all logs required to return to the oldest point will be retained, accelerating storage consumption.

Q: What happens if I forget to DROP RESTORE POINT?

A: Oracle assumes you “might return someday” and will absolutely never delete the old logs. Consequently, the FRA will fill up, and the database will stop (hang) because it cannot output archive logs. Deletion after work is mandatory.

📝 Summary of Operational Precautions

PrecautionContent
FRA Capacity ManagementLogs will continue to increase and won’t be deleted while a restore point is held.
RESETLOGS RequirementAn OPEN RESETLOGS is mandatory after returning to the past.
DROP After WorkIt is an iron-clad rule to delete the restore point immediately after completing work.
Functional LimitationIt only works for points in time after the feature was enabled/created.

🏁 Conclusion

  1. Verify Configuration: Check Archive Log, FRA, and Flashback ON.
  2. Create: CREATE RESTORE POINT ... GUARANTEE ...
  3. Work: Perform fixes, changes, or tests.
  4. Recover: FLASHBACK DATABASE TO RESTORE POINT ...
  5. Resume: OPEN RESETLOGS & Don’t forget to DROP!

A Guaranteed Restore Point is the strongest “insurance” for a DBA. Please make use of it during major changes that involve planned downtime.

This article is based on Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Using Oracle Flashback Technology

How to Perform Backup and Recovery Using Oracle RMAN Commands
In Oracle Database operations, automating and streamlining backup and recovery is essential. This article explains the p…
oracle-master00000.com
2026.03.27

コメント

Copied title and URL