Yesterday, i have gotten into the situation where flashback logs have building up for months in the Flash Recovery Area of our production database. And i was dealing with a critical production database that no downtime is allowed. So how do we cleanup the flashback logs from FRA without any downtime ?
Oracle does not recommend deleting these files from the operating system because that might corrupt your database.
The idea is to shrink the FRA size to the real used space, and Oracl will think that FRA is 100% full, so it will start deleting the files automaticly to make space on the FRA.
Let us see the steps to accomplish this 🙂
1. Check the FRA Size configuration
SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /opt/oracle/FRA db_recovery_file_dest_size big integer 50G
The size of the FLASH_RECOVERY_AREA is 50GB.
2. Check the actual space used from the FRA
SQL> col name for a32 col size_m for 999,999,999 col used_m for 999,999,999 col pct_used for 999 SELECT name , ceil( space_limit / 1024 / 1024) SIZE_M , ceil( space_used / 1024 / 1024) USED_M , decode( nvl( space_used, 0), 0, 0 , ceil ( ( space_used / space_limit) * 100) ) PCT_USED FROM v$recovery_file_dest ORDER BY name / NAME SIZE_M USED_M PCT_USED -------------------------------- ------------ ------------ -------- /opt/oracle/FRA 51,200 35,840 70
So only 35G of space is been used.
We can also see how much of space is being split among flashback logs, archivelogs and backups.
SQL> set line 250 select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 26.61 5.86 100 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 43,39 40,67 245 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0
- 26.61% of FRA space is used for archivelog files, out of which 5.86% can be reclaimable.
- 43.39% of FRA space is used for flashback logs, out of which 40.67% can be reclaimable.
As per the above, we can reclaime 40.65% of FRA space just for flashback log.
3. Set log_archive_dest_1 to a different location
Firstly, make sure that LOG_ARCHIVE_MIN_SUCCEED_DEST parameter is set to 1:
SQL> show parameter log_archive_min_succeed_dest NAME TYPE VALUE ------------------------------------ ----------- ----------------------- log_archive_min_succeed_dest integer 1
So, now , to avoid that the database hang for a lack of space while we shrink db_recovery_file_destination_size, we have ot set log_archive_dest_1 to a different location where there is enough space for the new archivelogs creation.
SQL> alter system set log_archive_dest_1='LOCATION=/opt/oracle/tempo_arch' scope=memory;
Lest’s make sure that archivelog file will be created in the new location /opt/oracle/tempo_arch
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
[oracle@ora_node /opt/PADDC2/oracle/tempo_arch $ l -lrt total 374840 -rw-rw----. 1 oracle oracle 383825920 Dec 18 17:45 o1_mf_1_438_hylj05j_.arc -rw-r-----. 1 oracle oracle 2048 Dec 18 17:45 o1_mf_1_439_hylj1vw_.arc -rw-rw----. 1 oracle oracle 1024 Dec 18 17:45 o1_mf_1_440_hylj1wq_.arc
4. Shrink the FRA to the actual space usage
Set the db_recovery_file_size to 35GB, same as the actual space usage to make oracle think that FRA is 100% Full and make automatic deleting older files.
SQL> alter system set db_recovery_file_dest_size=35G scope=memory; System altered. SQL> show parameter db_recovery_file_dest_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 35G
Now let’s see Oracle reaction! so let’s show what alert Log display :
ALTER SYSTEM SET db_recovery_file_dest_size='35G' SCOPE=MEMORY; Fri Dec 18 17:50:21 2020 Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxlmb7f8_.flb Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxlm5yxj_.flb Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxln319v_.flb Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxln47q5_.flb Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxllnkc3_.flb Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxlmj5xt_.flb Deleted Oracle managed file /opt/oracle/FRA/ORADB/flashback/o1_mf_hxll5kp1_.flb
As you see, Oracle start deleting old flashback log automaticly to make free space on the FRA 😉
SQL> set line 250 select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 73.91 30.36 100 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 26,09 5,67 45 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0
5.Revoke the FRA size and log_archive_dest_1 old location
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=memory; SQL> alter system set db_recovery_file_dest_size=50G scope=memory; System altered. SQL> show parameter db_recovery_file_dest_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 50G
Good reading !!
Founder of RakDBA.com
Expert database administrator on Oracle & SQL Server databases with 10 years experience. Certified OCA for Oracle and MSCP for SQL SERVER 2012 administration.