DEV Community

Kumaran Murugan
Kumaran Murugan

Posted on

Flashback Database with a Guaranteed Restore Point

#oracle #oracledatabase #restore

Before a patch, an upgrade, or a risky schema change, the most useful question a DBA can ask is: "How fast can I get back if this goes wrong?" A full RMAN restore can take hours. Flashback Database with a guaranteed restore point can take minutes.

In this walkthrough, I create a guaranteed restore point, simulate an accidental loss, and flash the database back to the exact state it was in before.

Why a guaranteed restore point?

A normal restore point can age out as the flashback retention window moves forward. A guaranteed restore point tells Oracle to keep every flashback log needed to return to that point, however long it takes. The trade-off is that those logs consume space in the Fast Recovery Area (FRA) until you drop the restore point.

Prerequisites

Flashback Database needs:

  • The database in ARCHIVELOG mode
  • A configured and sized Fast Recovery Area
  • Flashback logging enabled

Step 1: Enable ARCHIVELOG mode 

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST;
Enter fullscreen mode Exit fullscreen mode

ARCHIVE LOG LIST should now show Archive Mode with automatic archival enabled.

Step 2: Configure the FRA and enable Flashback 

SELECT FLASHBACK_ON FROM V$DATABASE;   -- NO

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/path/to/fast_recovery';
ALTER DATABASE FLASHBACK ON;

SELECT LOG_MODE, FLASHBACK_ON, NAME, OPEN_MODE FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

Set the size parameter before the destination parameter. Size the FRA for your change rate. Flashback logs live here, and a full FRA can stall the database.

Step 3: Create the guaranteed restore point 

CREATE TABLESPACE tbs1 DATAFILE 'tbs1_data.dbf' SIZE 1m;

CREATE RESTORE POINT demo2 GUARANTEE FLASHBACK DATABASE;
Enter fullscreen mode Exit fullscreen mode

This is the checkpoint. Everything after this moment can be undone.

Step 4: Simulate the accident

I dropped the test tablespace, then confirmed it was gone:

DROP TABLESPACE tbs1;
SELECT * FROM V$TABLESPACE;   -- 20 rows, TBS1 missing
Enter fullscreen mode Exit fullscreen mode

Step 5: Flash the database back 

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT demo2;
ALTER DATABASE OPEN READ ONLY;
Enter fullscreen mode Exit fullscreen mode

Opening read-only first lets you check the result before committing to it:

SELECT * FROM V$TABLESPACE;   -- 21 rows, TBS1 is back
Enter fullscreen mode Exit fullscreen mode

Once you're satisfied, finish the recovery:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Step 6: Clean up 

ALTER DATABASE FLASHBACK OFF;
DROP RESTORE POINT demo2;
Enter fullscreen mode Exit fullscreen mode

Don't skip this step. A guaranteed restore point keeps holding FRA space until it is dropped.

What I learned

  • Flashback is much faster than a full restore. It applies flashback logs rather than restoring datafiles.
  • Read-only first is a cheap safety check. You can verify the recovered state before opening with RESETLOGS, which is irreversible.
  • FRA sizing is the real operational risk. An undersized FRA is the most common way a restore point goes wrong.
  • Clean up after yourself. A forgotten guaranteed restore point quietly eats storage.

I now treat a guaranteed restore point as a standard step in change windows for patching and upgrades, because it gives you a fast, rehearsed way back.

Kumaran Murugan is an Oracle Apps DBA and OCI Cloud Administrator with 5+ years of experience in Oracle E-Business Suite, database migrations, and OCI infrastructure. Connect on (http://www.linkedin.com/in/kumaranmurugan).

Top comments (0)