One of the most powerful “insurance policies” in operating Oracle Database is Flashback Database. When a user accidentally performs a large-scale update or data is corrupted due to an application bug, the entire database can be instantly rewound to its state from several hours ago.
In this article, we will explain the specific procedures to enable this feature and the critical points for practical operation in a way that is easy for beginners to understand.
🔁 What is Flashback Database?
It is a feature that “rewinds” the entire database to a specific SCN (System Change Number) or point in time.
Unlike conventional restore and recovery from backups, it characterizes an extremely short recovery time because it reproduces the past state using “Flashback Logs” without dismantling the database.
Note: This feature is for rescuing from logical erroneous operations (such as accidental deletion of data). Since it cannot handle “physical failures” such as disk damage, it is essential to use it in conjunction with regular backups (RMAN, etc.).
🔧 Configuration Procedures for Flashback Database
Enable the feature using the following 4 steps. All tasks are performed with SYSDBA privileges.
Step 1: Enable Archive Log Mode
An absolute condition for the flashback feature is that the database is operating in “Archive Mode.”
-- Check current mode
ARCHIVE LOG LIST
-- If it is NOARCHIVELOG (Non-archive mode), change it using the following steps
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
-- Confirmation (It is OK if it says "Archive Mode")
ARCHIVE LOG LIST;
Execution Log:
SQL> ARCHIVE LOG LIST
Database log mode No Archive Mode ★
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
Oldest online log sequence 10
Current log sequence 12
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> ALTER DATABASE ARCHIVELOG;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode ★
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
Step 2: Configure the FRA (Fast Recovery Area)
Specify the size and path of the location (FRA) where flashback logs will be stored.
-- Set FRA size (Example: 10GB)
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH;
-- Set FRA directory (The directory must be created on the OS side in advance)
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
Execution Log:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440 SCOPE=BOTH;
System altered.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
------------------------------------------------------
YES
Step 3: Set the Retention Target
Specify “how many minutes back you want to be able to return.”
-- Set retention period (Example: 1440 minutes = 24 hours)
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440 SCOPE=BOTH;
Step 4: Enable the Flashback Feature
Finally, turn the feature ON for the database.
-- Enable Flashback
ALTER DATABASE FLASHBACK ON;
-- Confirm if it is enabled (Success if the result is YES)
SELECT FLASHBACK_ON FROM V$DATABASE;
⚠ Critical Operational Checkpoints
Even if the construction is finished, you will fall into a situation where you “cannot return!” in an emergency unless you understand the following 5 points.
1. Insufficient Free Space in FRA is Fatal
In addition to flashback logs, archive logs and backups are also stored in the FRA. If this area becomes full, Oracle will automatically delete old flashback logs.
-- Monitor the usage of FRA (usage rate, etc.)
SELECT * FROM V$RECOVERY_FILE_DEST;
Monitoring Example:
SQL> set lin 1000 pages 1000
SQL> col name for a50
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
-------------------------------------------------- ----------- ---------- ----------------- --------------- ----------
/u01/app/oracle/fast_recovery_area 1.0737E+10 419430400 0 2 0
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
Session altered.
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
2274294 2025/03/29 16:24:41
2. “Retention Target” is Just a Best-Effort Target
Even if DB_FLASHBACK_RETENTION_TARGET is set to 24 hours, logs will be deleted if the free space in the FRA is insufficient. Do not overconfide that “you can return 24 hours because it was set,” and always grasp the current “effective oldest point.”
-- Confirm how far back you can actually return now (Oldest time and SCN)
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
3. It is Not a Substitute for Backups
As mentioned above, it is powerless against physical disk failures or the loss of data files. “Flashback for rapid logical recovery” and “RMAN backup for physical recovery” are two wheels of a cart.
4. Impact on Performance
Since it continues to write logs constantly, a slight overhead occurs in systems where updates (DML) are extremely intense.
5. Turn OFF to Release Resources When Unnecessary
If it becomes temporarily unnecessary in a development environment, etc., you can save disk capacity (FRA) by turning it OFF.
-- Disable Flashback
ALTER DATABASE FLASHBACK OFF;
FAQ
Q: What happens to the subsequent data after executing Flashback?
A: “Everything” returns to the specified point. If you start with ALTER DATABASE OPEN RESETLOGS; after rewinding, the update contents after the rewound point will be lost.
Q: Can it be used in Standard Edition?
A: Yes, in the latest versions such as Oracle Database 19c, it is available in Standard Edition 2 (in previous versions, it was a feature limited to Enterprise Edition).
Q: I want to return only a specific table.
A: In that case, it is more efficient to use “Flashback Table” or “Flashback Drop” rather than “Flashback Database.”
✅ Summary
| Step | Operation | Content | Important Confirmation Command |
| 1. | Log Mode | Change to Archive Log Mode | ARCHIVE LOG LIST |
| 2. | Destination | Define FRA location and size | V$RECOVERY_FILE_DEST |
| 3. | Retention | Set target time in minutes | V$FLASHBACK_DATABASE_LOG |
| 4. | Enable | Turn the feature ON | SELECT FLASHBACK_ON FROM V$DATABASE |
🔚 Conclusion
Flashback Database is a wonderful feature that can dramatically shorten the recovery time from large-scale failures. However, its effectiveness depends completely on the “free space in the FRA.”
As an IT engineer, do not just set it and finish; keep in mind the operational design including daily free space monitoring.
If you would like to know more about the operation of FRA or more specific recovery procedures (FLASHBACK DATABASE TO SCN …), I can continue to tell you.
[reference]
Using Oracle Flashback Technology
コメント