— The Savior from Human Error: Explaining Oracle’s Powerful Rewind Feature —
Oracle Database is equipped with a magical feature called Flashback Technology that allows you to “rewind” time. It is a powerful tool that enables users to quickly restore data to a previous state without the need for traditional restores from backups, especially when data is accidentally deleted or improperly updated.
This article provides an easy-to-understand guide to the basic concepts of Flashback, its various functions, and the management of UNDO information (undo_retention), which is essential for operations.
1. What is Flashback Technology?
Flashback primarily uses UNDO data (information about data before it was changed) to view or restore the state of the database at a specific point in time.
Unlike traditional recovery (Media Recovery), which requires restoring backup files and applying logs—a time-consuming process—Flashback performs a “rewind” on the live database, making it significantly faster.
List of Main Flashback Features
| Feature Name | Primary Use | Data Source |
| Flashback Query | Viewing past data using a SELECT statement. | UNDO Information |
| Flashback Table | Reverting a specific table to a past state. | UNDO Information |
| Flashback Drop | Restoring a dropped table from the “Trash.” | Recycle Bin |
| Flashback Version Query | Checking “when and how” a specific row changed. | UNDO Information |
| Flashback Transaction | Backing out only a specific transaction. | UNDO Information |
| Flashback Database | Rewinding the entire DB to a past point in time. | Flashback Logs |
2. Implementation Examples of Frequently Used Features
✅ Flashback Query (View past state)
Useful when you think, “I just updated the data, but what did it look like 5 minutes ago?”
-- SELECT the state of the SCOTT.EMP table as it was 5 minutes ago
SELECT * FROM scott.emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
✅ Flashback Table (Revert a table to the past)
Reverts the entire table to a previous point in time.
-- 1. Enable ROW MOVEMENT (Required setting)
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
-- 2. Rewind the table to its state from 1 hour ago
FLASHBACK TABLE scott.emp TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
✅ Flashback Drop (Restore a deleted table)
Even if you accidentally execute a DROP TABLE, you can recover it just like the Windows Recycle Bin.
-- Accidentally drop the table
DROP TABLE scott.emp;
-- Restore from the Recycle Bin
FLASHBACK TABLE scott.emp TO BEFORE DROP;
3. The Importance of UNDO Retention (undo_retention)
Most Flashback features rely on UNDO information. If this information is overwritten by new changes, you can no longer travel back in time.
The undo_retention Parameter
Specifies the minimum number of seconds the system should try to retain UNDO information.
-- Check current retention settings
SHOW PARAMETER undo_retention;
-- Extend retention period to 1800 seconds (30 minutes)
ALTER SYSTEM SET undo_retention = 1800;
RETENTION GUARANTEE
Normally, if the UNDO tablespace runs out of space, the system will overwrite old UNDO information even if it hasn’t reached the undo_retention limit. Enabling this setting ensures that information is “never deleted” during the specified period.
-- Example: Creating an UNDO tablespace with RETENTION GUARANTEE
CREATE UNDO TABLESPACE undotbs2
DATAFILE 'undotbs2.dbf' SIZE 1G
RETENTION GUARANTEE;
4. Operational Pros and Cons (Precautions)
While Flashback is convenient, overly strict settings can negatively impact database performance.
| Setting | Pros | Cons / Risks |
Extending undo_retention | Allows recovery to a much older state. | Increased usage of UNDO tablespace. Updates may stop (ORA-30036) if size is insufficient. |
| RETENTION GUARANTEE | Guarantees the ability to go back in time. | Higher risk of new transactions failing due to lack of space. |
Operational Tips:
- Size Estimation: If you increase
undo_retention, ensure you have a sufficiently large UNDO tablespace. - Monitoring: Constantly monitor the usage rate of the UNDO tablespace to prevent DML (update) errors.
5. Flashback Database Settings (Rewinding the entire DB)
To revert the entire database, you need the Fast Recovery Area (FRA) and specific log settings.
-- 1. Check if ARCHIVELOG mode is enabled
ARCHIVE LOG LIST;
-- 2. Configure Fast Recovery Area (FRA)
ALTER SYSTEM SET db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area';
ALTER SYSTEM SET db_recovery_file_dest_size = 5G;
-- 3. Enable Flashback while the DB is in MOUNT state
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
6. FAQ
Q: Are there cases where Flashback cannot restore data?
A: You cannot restore data if the UNDO information has been overwritten (exceeding undo_retention) or if the PURGE option was used during DROP TABLE. Additionally, there are limitations when trying to restore across DDL (table definition) changes.
Q: When should I use Flashback Table vs. Flashback Database?
A: Use Flashback Table if the error only affected specific tables. Choose Flashback Database for massive batch failures or logical corruption where the entire database needs to be reverted.
Q: Is there any downside to enabling ROW MOVEMENT?
A: When enabled, the physical location of a row (ROWID) may change during update operations. Caution is needed if you have applications that access data by directly referencing ROWIDs.
7. Summary
Oracle Flashback Technology is the “ultimate safety net” for preventing data loss due to operational errors.
- To view only: Flashback Query
- To revert a whole table: Flashback Table
- To recover a deleted table: Flashback Drop
Mastering these requires proper setting of the UNDO retention period and appropriate sizing of the UNDO tablespace. To be prepared for emergencies, ensure Flashback is configured correctly alongside your regular backup strategy.
Note: This article focuses on Oracle Database 19c. Screens and default values may vary in other versions.
[reference]
Using Oracle Flashback Technology
コメント