Flashback Database/PDB

Configuration:

• Database in Archivelog Mode

• Database in Flashback Mode & Tablespace in Flashback Mode

• Database using Force Logging - To prevent corruptions in Flashback Opperations

• db_recovery_file_dest_size must be set

• db_recovery_file_dest must be set

• Configure a properly Flashback retention

• Understand how your Data Guard will behave - Explained in the end

Limitations:

• Requires reset logs opperation

Considerations:

• Make sure you have a valid backup before the procedure

• Create a Guarantee restore point as a rollback strategy as well

Operation:

• Database Rollback

Version:

• 10gR1 / 2003

Licensing:

• Requires Enterprise Edition

Oracle Database is truly differentiated from its competitors, and today we will look at Flashback Database, a feature introduced in 2003 with 10gR1, And even today, the other databases don’t have anything like that.

This feature offers the ability to quickly rewind the entire database to the past, without the need to restore a backup.

To use this feature, the database must be configured in advance (before the need to use the feature itself, of course):

• Database in ARCHIVELOG Mode

• Database in Flashback Mode with proper flashback log retention

FORCE LOGGING is critical, especially with Data Guard, as it forces all operations to generate redo—even those that normally would not (like NOLOGGING operations). Without this, flashback operations may corrupt data files.

Also, ensure that tablespaces are in flashback mode. By default, they are enabled, but if someone disabled them to reduce log growth, that tablespace will be corrupted during flashback.

If you are using multitenant architecture, configuration of flashback happens in the root container, but the actual flashback operation takes place at the PDB level.

Configuration (non-CDB and CDB)

SQL> alter system set db_recovery_file_dest_size=100G;
SQL> alter system set db_recovery_file_dest='/fra' scope=both;

SQL> shutdown immediate;
SQL> startup mount;

SQL> alter database flashback on;

SQL> alter database open;

SQL> SELECT flashback_on FROM v$database;

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

Check the oldest flashback log available:

SQL> SELECT oldest_flashback_scn,
            to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') oldest_flashback_time
     FROM v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
             3558490 2025-08-28 18:05:57     
     

At this point the database is configured and generating flashback logs. The oldest log marks the earliest point in time you can rewind to. If no retention is configured, Oracle will clean up recovery area automatically. To guarantee a minimum period, configure retention in the root container.

In this example, I'm using a 7 days retention:

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=10080 SCOPE=BOTH;

Flashback a PDB

SQL> show pdbs

CON_ID CON_NAME   OPEN MODE
------ ---------  ----------
2      PDB$SEED   READ ONLY
3      PDB1       READ WRITE
4      PDB2       READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;

SQL> FLASHBACK PLUGGABLE DATABASE pdb1
     TO TIMESTAMP TO_TIMESTAMP('2025-08-28 18:10:00','YYYY-MM-DD HH24:MI:SS');

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

SQL> show pdbs

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

Flashback a non-CDB

SQL> SELECT oldest_flashback_scn,
            to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') oldest_flashback_time
     FROM v$flashback_database_log;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO TIMESTAMP
     TO_TIMESTAMP('2025-08-11 16:00:00','YYYY-MM-DD HH24:MI:SS');
     
SQL> ALTER DATABASE OPEN RESETLOGS;

Considerations

If you have a standby database, be aware:

- In non-CDB Flashback Database, you will need to recreate the standby because the operation is not written to the redo logs.

- In multitenant, if you flashback only a PDB, the standby can sync automatically, because the operation is written to the redo logs.