Overview:
- In previous Database releases, you could store flashback database logs only in the fast recovery area
- Oracle 23ai Database introduces the option to store flashback database logs in a separate location
- With this new option, you can choose to write the flashback logs to faster disks
- Using a separate destination also eliminates the manual administration to manage the free space in the fast recovery area
- A new database instance initialization parameter (DB_FLASHBACK_LOG_DEST) has been introduced to specify a separate location for the flashback database logs. This parameter is a system modifiable on the CDB Level
In this blog, I’ll demonstrate the steps to set a separate location for the flashback database logs.
Prerequisites:
- Oracle Database 23ai running in archive log mode.
Setting Steps:
1. Connect to a root container using SYS user and confirm the database is running in archive log mode and flashback is ON.
- Notice that the FRA destination is +DATA, and flashback logs were generated in FRA (+DATA)
- Currently, DB_FLASHBACK_LOG_DEST is not configured
2. Before assigning a value for the initialization parameter (DB_FLASHBACK_LOG_DEST), you must specify the size of the flashback area by setting the initialization parameter (DB_FLASHBACK_LOG_DEST_SIZE) to a nonzero value.
3. When modifying the DB_FLASHBACK_LOG_DEST parameter with the ALTER SYSTEM statement, you must disable and reenable flashback logging.
- Notice that the Flashback destination is +RECO
SQL> alter system set db_flashback_log_dest_size=100G scope=both;
SQL> alter system set db_flashback_log_dest='+RECO' scope=both;
- Archive log files are still generated in FRA (+DATA)