Clean flash recovery area archive logs

archive-logoraclerman

I have an Oracle 11g on RHEL server. My /oradata volume reach 95% disk usage :

[ora11g@bocc7-1-qrec-01 ~]$ df -h  
/dev/sdb1             689G  571G   84G  95% /oradata

The Flash Recovery Area (FRA) is the larger folder : 

[ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/* | sort -h
50G     /oradata/qtest
175G    /oradata/qrec
346G    /oradata/flash_recovery_area

Inside the FRA I can see that the archive logs take approx 230Gb: 

[ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/flash_recovery_area/qrec/*
232G    /oradata/flash_recovery_area/qrec/archivelog
7.9G    /oradata/flash_recovery_area/qrec/onlinelog

I checked the v$recovery_file_dest table and my SPACE_LIMIT is 20Gb and my SPACE_USED is only 10Mb!

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

qrec> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata/flash_recovery_area
 2.1475E+10   10984448                 0               1

So, I deduced that it had nothing to do with this table.

Now I'm checking with RMAN : 

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc RECID=1057 STAMP=969146207
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1057_965658004.arc RECID=1058 STAMP=969149808
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1058_965658004.arc RECID=1059 STAMP=969153409
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1059_965658004.arc RECID=1060 STAMP=969157010
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1060_965658004.arc RECID=1061 STAMP=969160610
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1061_965658004.arc RECID=1062 STAMP=969164210
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1062_965658004.arc RECID=1063 STAMP=969167813
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1063_965658004.arc RECID=1064 STAMP=969171413
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1064_965658004.arc RECID=1065 STAMP=969175014
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1065_965658004.arc RECID=1066 STAMP=969178614
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1066_965658004.arc RECID=1067 STAMP=969182214
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1067_965658004.arc RECID=1068 STAMP=969185814
Crosschecked 12 objects

RMAN> delete noprompt expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
specification does not match any archived log in the repository

So the 1st question is: Why does RMAN not see my 2752 ARC files here : 

[ora11g@bocc7-1-qrec-01 ~]$ ls -1 /oradata/flash_recovery_area/qrec/archivelog | wc -l
2752

And the 2nd question is: What should I do to free some disk space by removing old archivelog files? (the older ones are more than 1 year old!!) :

[ora11g@bocc7-1-qrec-01 archivelog]$ find -type f -printf '%T+ %p\n' | sort | head -n 1
2017-02-22+16:54:41.0000000000 ./log_2_1_936636608.arc

Best Answer

0) You have FRA configured, but it is not your archivelog destination.

Setting log_archive_dest_N to the FRA directory is not the same as actually setting the FRA as an archivelog destination.

The configuration you most likely have:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/flash_recovery_area
db_recovery_file_dest_size           big integer 20G
log_archive_dest_1                   string      location=/oradata/flash_recovery_area/qrec/archivelog

The proper configuration would be:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/flash_recovery_area
db_recovery_file_dest_size           big integer 20G
log_archive_dest_1                   string      location=use_db_recovery_file_dest

The naming of archivelog files also confirms this, as archivelogs in the FRA are use OMF format, and they are actually placed in seperate directories (named based on the date) each day.

So instead of this:

/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc

The name of your archivelog file would be:

/oradata/flash_recovery_area/qrec/archivelog/2018_02_22/o1_mf_....arc

1) This is typically caused by inappropriate configuration of the control_file_record_keep_time parameter combined with a lacking backup method. The default value 7 means that records (of archivelogs or backups) are kept for 7 days, after that, they can be reused. If you do not backup/delete your archivelogs for 7 days, the database may reuse the controlfile records pointing to them, basically the database "forgets" these entries and leaves them in the filesystem.

You should regulary backup and delete your archivelogs and set control_file_record_keep_time appropriately based on your backup strategy.

If you use user-managed backups instead of RMAN, you need to take care of deleting these logs manually. RMAN can backup and delete these files in a single command.

2) Depends on your backup requirements.

If you use user-managed backups, make sure you have backups of these files, then delete them.

If you use RMAN, you can use catalog start with '/oradata/flash_recovery_area/qrec/archivelog/';, then use RMAN to backup and delete them.

If you do not need any backup of these files, simply delete them.

Related Question