ORA-19815: WARNING: db_recovery_file_dest_size of 4227858432 bytes is 100.00% used

In any oracle environment this is most common error you see when you not plan to delete the archive logs periodically for the databases which are running on ARCHIVE LOG mode. This is very Dangerous when happens in PRODUCTION database entire Database will get hung and which we dont want.

I'have faced this error on one of my non-prod env where there is no proper care has been care for archive log deletion because of this all my SQL views getting hung on my LINUX standalone database, if I query any view then the output is displaying nothing. Checked the alert log and found this error below

Mon Jul 02 19:06:06 2012

Errors in file /opt/oracle/app/diag/rdbms/dev7/DEV7/trace/DEV7_arc3_17880.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 4227858432 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

   then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

   BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

   reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

   system command was used to delete files, then use RMAN CROSSCHECK and

   DELETE EXPIRED commands.

************************************************************************

ARC3: Error 19809 Creating archive log file to '/opt/oracle/app/fast_recovery_area/DEV7/archivelog/2012_07_02/o1_mf_1_26_%u_.arc'

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

SQL> show parameter db_recovery_file_dest ;

NAME                                           TYPE           VALUE

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

db_recovery_file_dest                string         /opt/oracle/app/fast_recovery_area

db_recovery_file_dest_size      big integer   4G

go to RMAN and do crosscheck to verify any expired archive logs

Rman>  crosscheck archivelog all;

Output displayed 3 expired .arc logs

Rman>   delete expired archivelog all;

This will delete the Expired archivelogs

(OR)

If there are NO expired archive logs then backup the archive logs from the 'db_recovery_file_dest ' location to some other location (like TAPE) then delete archivelogs

If you want to delete archive logs older than ‘x’ days, then

RMAN> delete archivelog until time 'SYSDATE-7';       (deleting older than 7 days)

NOTE : Remember while deleting arch logs that delete only if you dont want these arch logs like for the upstream process, standby, etc and have backed up already

Now, my sql queries are working fine………..

Since my db_recovery_file_dest_size is less, then I increased it to high value to avoid this problem again in the future

SQL>  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G scope=both;

SQL> show parameter db_recovery_file_dest ;

NAME                                           TYPE           VALUE

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

db_recovery_file_dest                string         /opt/oracle/app/fast_recovery_area

db_recovery_file_dest_size      big integer   50G

Max value for db_recovery_file_dest_size is 17179869182G   (in oracle 10.2, 11.1, 11.2)

check space used and max limit using

SQL> select name, space_limit/1024/1024/1024 MaxGB, space_used/1024/1024/1024 UsedGB, (space_used/space_limit)*100 "USED %", number_of_files from v$recovery_file_dest;

name           MaxGB       UsedGB            USED%  number_of_lines
----------   ------------         ------------       ----------       -----------------
dev7       50              5.04552555           9.90               28

Since this is my non-prod we are not worried on arch logs but if it is a prod environment then always have ARCH backup job either from OEM or some shell script and backup periodically, having said that we also need to have a scheduled cleanup too for the same logs via same oem Or Cronjobs

Also Check  :

ORA-00257: archiver error. Connect internal only, until freed