Prior to Oracle 23ai, Flashback Logs had to be stored exclusively in the Fast Recovery Area (FRA). If FRA was not configured, the Flashback Database feature could not be enabled, as illustrated below:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
SQL> alter database flashback on;
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
With Oracle 23ai, two new parameters have been introduced that allow Flashback Logs to be stored outside FRA:
- db_flashback_log_dest
- db_flashback_log_dest_size Configuring Flashback Logs Outside FRA First, check the status of the parameters related to FRA:
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
Next, configure the new parameters and enable Flashback Database:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter system set db_flashback_log_dest_size=800m;
System altered.
SQL> alter system set db_flashback_log_dest='/oracle23ai/FlashbackLog';
System altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
After enabling the feature, Flashback Logs with the .flb extension are created in the specified path:
SQL> select NAME, SPACE_LIMIT/1024/1024 as "SPACE_LIMIT_MB",
SPACE_USED/1024/1024 as "SPACE_USED_MB", NUMBER_OF_FILES
from V$FLASHBACK_LOG_DEST;
NAME SPACE_LIMIT_MB SPACE_USED_MB NUMBER_OF_FILES
------------------------------ -------------- ------------- ---------------
/oracle23ai/FlashbackLog 800 200 1
The logs are stored as shown below:
[oracle@target ~]$ cd /oracle23ai/FlashbackLog/FREE/flashback/
[oracle@target flashback]$ ls -l
-rw-r----- 1 oracle oinstall 209723392 Jul 26 11:32 o1_mf_ld1k8lnr_.flb
-rw-r----- 1 oracle oinstall 209723392 Jul 26 11:33 o1_mf_ld1k8p4m_.flb
-rw-r----- 1 oracle oinstall 209723392 Jul 26 11:32 o1_mf_ld1k8xfv_.flb
Relationship Between Flashback Logs and Redo Logs
There is an apparent correlation between the size of Flashback Logs and the size of redo log files. For instance, with the above configuration, each Flashback Log is approximately 200 MB, matching the redo logfile size. To explore this further, new redo log groups with a different size are created:
SQL> alter database add logfile group 13 size 800m;
Database altered.
SQL> alter database add logfile group 14 size 800m;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#, bytes from v$log;
GROUP# BYTES
---------- ----------
13 838860800
14 838860800
After creating new log files, enable and disable Flashback Database to generate new Flashback Logs with updated sizes:
SQL> alter database flashback off;
Database altered.
SQL> alter database flashback on;
Database altered.
The newly generated logs now reflect the updated size:
[root@target flashback]# ls -l
-rw-r----- 1 oracle oinstall 838868992 Jul 26 13:34 o1_mf_ld1lcpjx_.flb
Restore Point Logs
When configuring the Flashback Database parameters, logs related to restore points are also moved to the specified path:
SQL> alter database flashback off;
Database altered.
SQL> create restore point test guarantee flashback database;
ORA-38784: Cannot create restore point 'TEST'.
ORA-38786: Recovery area is not enabled.
SQL> alter system set db_recovery_file_dest_size=10g;
System altered.
SQL> alter system set db_recovery_file_dest='/oracle23ai/fra';
System altered.
SQL> create restore point test guarantee flashback database;
Restore point created.
[oracle@target flashback]$ ls -l
-rw-r----- 1 oracle oinstall 209723392 Jul 26 15:03 o1_mf_ld1x4vfk_.flb
Top comments (0)