Clean the flashback recovery area

free space in flash recovery area

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

 NOTE: You can remove the file on the filesystem level.

cd /fast_recovery_area/TESTDB/archivelog
ls -al 
2022_10_07/
2022_10_08/
2022_10_09/

2022_10_10/

I am going to remove all files older then 5 hours.

find /fast_recovery_area/TESTDB/archivelog/2022*  \( -mmin +360 -a -name "*.arc" \) -exec rm {} \; > /dev/null 2>&1 

Login into DB and check the parameter:

sqlplus / as sysdbaq

col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999

select
name,
ceil( space_limit / 1024 / 1024 /1024) size_gb,
ceil( space_used / 1024 / 1024 /1024) used_gb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by

name desc;

NAME                                  SIZE_GB      USED_GB  PCT_USED
-------------------------------- ------------  ------------ --------

/fast_recovery_area                     2,600        2,590       100

Increate the sieze as follow. 

NOTE: NO NEED DB RESART:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =3500G SCOPE=BOTH;

RMAN command required to make empty space show via sql command..

rman target /
crosscheck archivelog all;

delete noprompt force expired archivelog all;

Make empty all files older then 0ne day:

#delete noprompt archivelog all completed before 'sysdate - 1';

Or older the 6 hours:

delete noprompt archivelog all completed before 'sysdate - 6/24';

Now execute the above sql command again and check the space:

NAME                                  SIZE_MB      USED_MB PCT_USED
-------------------------------- ------------ ------------ --------

/fast_recovery_area                     3,500        2,590       74

You can use the follwing scrip to execute the RMAN commands.

delete_archivelog.sh

more delete_archivelog.sh

#!/bin/bash

date '+%Y-%m-%d  %H:%M:%S'

#. /users/oracle/set_home_env TSTDB

ORACLESID=$(grep -P '^\w+' /etc/oratab -o | tail -1)

. /users/oracle/set_home_env ${ORACLESID}

export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'

rman target / <<EOF

crosscheck archivelog all;

delete noprompt force archivelog all completed before 'sysdate - 1/24';

delete noprompt force expired archivelog all;

EOF

date '+%Y-%m-%d  %H:%M:%S'

cd /fast_recovery_area/${ORACLE_SID}/archivelog

find /fast_recovery_area/${ORACLE_SID}/archivelog/* -type d -empty -print -exec rm -r {} \;

⚠️ Important rule for flashback files !!

Do NOT delete .flb files at OS level unless you’re in an emergency and understand the consequences. It can corrupt recovery capability.

Use following Methode.

SQL> SHOW PARAMETER db_flashback_retention_target;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target        integer     1440

 ALTER SYSTEM SET db_flashback_retention_target=120;

 SHOW PARAMETER db_recovery_file_dest_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 800G

 ALTER SYSTEM SET db_recovery_file_dest_size = 400G;

ALTER SYSTEM SWITCH LOGFILE;

rman target /

DELETE OBSOLETE;

⚠️ Common blocker (very important)

If you have:

• Guaranteed restore points

👉 Oracle will keep ALL .flb files . Check the restore point

 SELECT NAME, GUARANTEE_FLASHBACK_DATABASE

FROM V$RESTORE_POINT;

Your comments are most valuable and it would help me to come up with better posts.