Oracle 23ai: Managing Flashback Database Logs Outside the Fast Recovery Area

 

- 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 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 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 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 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).