New Flashback Log Features in Oracle Database 23ai
Oracle Flashback is a powerful database feature that allows users to quickly and easily "rewind" tables or rows to an earlier point in time, recover the data for an entire table or individual records to a previous state, roll back transactions, and view historical metadata in the database. It's a far more flexible solution than the alternative—performing media recovery of a copy of the database, then manually extracting and (if necessary) applying the data to the original database.
There are two types of Flashback, each powered by a different set of mechanisms. Logical Flashback—querying historical data or rolling back a transaction or query to an earlier state—uses undo data. Physical Flashback—Flashback of the database—is supported by specialized Flashback Logs. DBAs can tune and manage Logical Flashback features via the undo_retention and undo_tablespace parameters.
Prior to Oracle Database 23ai, the options for tuning Physical Flashback were more limited. DBAs can set a retention target but had no control over the location of the Flashback logs, which are stored in the database Fast Recovery Area, or FRA. Flashback was a potential issue in write-intensive databases when storage in the FRA wasn't fast enough to keep up with workloads and could cause contention for
resources in the FRA.
Because Flashback Logs were saved in the FRA, the total space allocation set through the DB_RECOVERY_FILE_DEST_SIZE parameter needed to consider the space required for database and recovery files stored there—including control files, redo logs, archived log files, datafile image copies, and RMAN backups and autobackups—as well as its Flashback Logs. The use of Guaranteed Restore Points
adds another dimension to this calculation.
Unlike the other files saved in the FRA, Flashback Logs can't be backed up or restored. Issuing the RMAN BACKUP RECOVERY AREA command will back up everything in the FRA except Flashback Logs; similarly, BACKUP RECOVERY FILES will create a backup of all recovery-related files—but again, not Flashback Logs. This makes the Flashback Logs outliers within the Fast Recovery Area and can complicate its space management.
Oracle Database 23ai addresses this by adding two new parameters that offer greater control over Flashback Logs:
- DB_FLASHBACK_LOG_DEST defines a separate location for Flashback Logs that separates writes to Flashback Logs from competing activities in the Fast Recovery Area and permits the use of dedicated disk optimized for Flashback activity. Using a separate location also simplifies management of the FRA itself.
- DB_FLASHBACK_LOG_DEST_SIZE sets a size limit for Flashback Logs under the DB_FLASHBACK_LOG_DEST directory. Oracle uses this value, along with the DB_FLASHBACK_RETENTION_TARGET to automatically maintain its Flashback Logs and manage space under the Flashback directory.
A new view, V$FLASHBACK_LOG_DEST , provides information about the Flashback Log destination:
desc V$FLASHBACK_LOG_DEST
Name Null? Type
------------------------------------------- -------- ------------------
NAME VARCHAR2(513)
SPACE_LIMIT NUMBER
SPACE_USED NUMBER
NUMBER_OF_FILES NUMBER
CON_ID NUMBER
The destination can be updated in a running instance by setting the size, then the directory:
SQL> alter system set db_flashback_log_dest_size=10g scope=both;
System altered.
SQL> alter system set db_flashback_log_dest='/u03/app/oracle/
flashback_log_dest'
scope=both;
System altered.
And the results queried from the new V$FLASHBACK_LOG_DEST view:
SQL> select * from v$flashback_log_dest;
NAME SPACE_LIMIT SPACE_USED NUMBER_OF_FILES
CON_ID
------------------------------------ ----------- ---------- ---------------
-----
/u03/app/oracle/flashback_log_dest 1.0737E+10 12388974 12
0
Beginning with Oracle 23ai, Oracle recommends writing Flashback Logs to a fast disk outside of the FRA, which eliminates the need to manage the contents of the FRA to accommodate Flashback Logs. Note that the COMPATIBLE parameter must be set to 19.0.0 or higher to enable automatic deletion of Flashback Logs. DBAs should closely monitor the Flashback Log destination on databases when COMPATIBLE is set to older versions.
Conclusion
Oracle Database 23ai introduces significant enhancements to Flashback Log management, addressing longstanding challenges by allowing DBAs to separate Flashback Logs from the Fast Recovery Area. This separation not only reduces contention in write-intensive environments but also simplifies FRA management and enables optimized storage configurations.
With these improvements, Flashback operations become more efficient and adaptable, making recovery tasks faster and less complex. For DBAs managing modern, high-demand databases, these changes provide powerful tools to ensure seamless, reliable data recovery.
Happy Holidays! 🎄
SUBMIT YOUR COMMENT