DB version: 19c
OS : RHEL 7.9
Datafiles and FRA stored in Linux XFS filesystem.
In one of my DBs, the Flash Recovery Area gets full quite frequently and when I check, I can see that flashback logs consume the most space. Roughly 99%.
In FRA, I see flashback logs, but, there are no restore points created in this DB.
To remove all the flashback logs I toggle FLASHBACK (OFF and then ON) using the following commands and flahsback logs get deteleted.
Alter database FLASHBACK OFF;
Alter database FLASHBACK On;
But, lately, the FRA is getting due full to the above mentioned flashaback logs three to four times a day and it has become bit of a headache.
Any idea why flashback logs are created when there is no restore point ?
Any proactive measures I can do to prevent this happening very frequently ?
Select con_id,
file_type,
percent_space_used,
percent_space_reclaimable as reclaimable,
number_of_files as "number"
6 from v$recovery_area_usage;
CON_ID FILE_TYPE PERCENT_SPACE_USED RECLAIMABLE number
---------- ----------------------- ------------------ ----------- ----------
0 CONTROL FILE 0 0 0
0 REDO LOG 0 0 0
0 ARCHIVED LOG 0 0 0
0 BACKUP PIECE .62 0 10
0 IMAGE COPY 0 0 0
0 FLASHBACK LOG 98.67 0 41
0 FOREIGN ARCHIVED LOG 0 0 0
0 AUXILIARY DATAFILE COPY 0 0 0
8 rows selected.
SQL> select name, scn, TIME, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selected
SQL> select name, bytes/1024/1024/1024 sizeGB from v$flashback_database_logfile;
NAME SIZEGB
--------------------------------------------------------------------------- ------------------
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfbt5c_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfbxvs_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfdtr1_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfgzm3_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfk1qq_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhflkkj_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfnq7r_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfpyvs_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfs7jn_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfvh83_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfxq8f_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhfzfhm_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhg20kl_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhg3yo1_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhg6520_.flb 1
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhg8k1r_.flb 2
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhgbyb6_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhghdj4_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhgrpnk_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhgzqyf_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhh5zcv_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhhc9x4_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhhjhkb_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhho6df_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhhsvfw_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhhyy8d_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhj4l2q_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhjc2ng_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhjgzfz_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhjlxk2_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhjq2cr_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhjv7vq_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhjzz3y_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhk3vrj_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhkb3t3_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhkhlmc_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhkp6dl_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhkvst3_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhl1y0v_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhl4y09_.flb 3
/orafra/WMCP/rcv/DB_WMCP/flashback/o1_mf_lbhl96yc_.flb 2
41 rows selected.
SQL> show parameter db_recovery_file_dest
PARAMETER_NAME TYPE VALUE
------------------------------- -------------------------------- ---------------------------
db_recovery_file_dest string /orafra/WMCP/rcv
db_recovery_file_dest_size big integer 100G
SQL>
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
PARAMETER_NAME TYPE VALUE
--------------------------------- -------------------------------- ----------
db_flashback_retention_target integer 120