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

flashback

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;

flashback2

  • Archive log files are still generated in FRA (+DATA)

flashback3