DEV Community: Kumaran Murugan

Flashback Database with a Guaranteed Restore Point

Kumaran Murugan — Wed, 14 Oct 2026 05:17:17 +0000

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;

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;

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;

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

Step 5: Flash the database back

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT demo2;
ALTER DATABASE OPEN READ ONLY;

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

SELECT * FROM V$TABLESPACE;   -- 21 rows, TBS1 is back

Once you're satisfied, finish the recovery:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;

Step 6: Clean up

ALTER DATABASE FLASHBACK OFF;
DROP RESTORE POINT demo2;

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).

How to Restore and Clone an OCI DB System from an Automatic Backup

Kumaran Murugan — Thu, 24 Sep 2026 07:31:57 +0000

If you're running Oracle databases on OCI's Base Database Service, one of the most useful recovery skills to have is knowing how to spin up a completely new DB system from an existing automatic backup — whether you're recreating a test environment, recovering from an accidental termination, or just validating that your backups actually work.

In this walkthrough, I'll take you through the full process: terminating an existing DB system, and rebuilding it from scratch using nothing but an automatic backup.

Why this matters

OCI decouples compute and storage lifecycle from your actual data. That means even if a DB system is gone, your data isn't — as long as a backup exists, you can rebuild the entire compute/storage/networking stack around it. This is a pattern worth practicing before you need it in a real incident.

Step-by-step walkthrough

1. Locate your source backup
Navigate to your DB system's Backups tab and review the available automatic incremental backups, each with its own start/end timestamp.

2. Start the restore
Select the backup you want and choose "Create database" from its action menu — this launches the Create Database from Backup wizard.

3. Choose what to restore
Decide whether to include all PDBs from the backup, or select specific ones.

4. Pick your target compartment
The new DB system doesn't have to go back into the same compartment — useful if you're rebuilding into a test/dev compartment instead of production.

5. Configure compute shape
Choose your shape series (AMD, Intel, or Ampere) and OCPU count.

6. Configure storage
Pick your storage management software (Oracle Grid Infrastructure or Logical Volume Manager) and performance tier.

7. Add SSH access and licensing
Paste or upload your SSH key, and choose License Included or Bring Your Own License.

8. Set up networking
Select your VCN, subnet, and hostname prefix.

9. Set the database name and credentials
Name your database, then provide the SYS password — plus the source database's TDE wallet/RMAN password, since that's required to unlock the backup.

10. Submit and monitor
The whole operation runs as a single Work Request, so you can track provisioning, networking setup, and the actual database restore in one place, right through to "Available."

What I learned

A few things stood out to me doing this hands-on:

The TDE wallet/RMAN password is non-negotiable — without the source database's original password, the backup can't be unlocked, so document it defensively.
Recovery area storage is read-only and auto-derived from the backup — you don't configure it directly.
You get real flexibility on where the new system lands — different compartment, VCN, even subnet — which makes this a genuinely practical way to spin up isolated test/dev copies of production data without touching the original.

If you're studying for the OCI Architect certifications or just want to build confidence in your OCI story, I'd recommend running through this exact exercise once in a sandbox — it's the kind of thing that's much better to have already practiced before you need it under pressure.

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 www.linkedin.com/in/kumaranmurugan.