Tuesday, July 3, 2012

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


3 comments:

octomeow said...

I tried this fix and it did not work. ANy reasons why?

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILE
-------------------- ------------------ ------------------------- --------------
IMAGE COPY 44.7 (ONLY RMAN )

SQL> select space_limit/1024/1024/1024 GB,space_used/1024/1024/1024 GB from v$re covery_file_dest;

GB GB
---------- ----------
10 4.46972656

Unknown said...

Thankyou very much!! Very rich and helpfull your blog ! Saved my day! :)

Bianca said...

gooed job thnx

Post a Comment

Auto Scroll Stop Scroll