ORA-19815: WARNING: db_recovery_file_dest_size of 4227858432 bytes is 100.00% used
http://chandu208.blogspot.nl/2012/07/ora-19815-warning-dbrecoveryfiledestsiz.html
Here because of this error 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)
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 space_limit/1024/1024/1024 GB,space_used/1024/1024/1024 GB from v$recovery_file_dest;
GB GB
---------- ----------
50 5.04552555
Delete unessasary files:
find /u01/app/oracle/diag/rdbms -mtime +30 -name *.trc -exec rm {} \;
find /u01/app/oracle/diag/rdbms -mtime +30 -name *.trm -exec rm {} \;
find /u01/app/oracle/diag/rdbms -mtime +365 -name alert*.log.* -exec rm {} \;
find /u01/app/oracle/product/11.1.1/ofm_1/instances/instance1/diagnostics/logs/OHS/ohs1 -mtime +30 -name acceslog* -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/your-server/listener/alert -mtime +5 -name log*.xml -exec rm {} \;
find /u01/app/oracle/diag/rdbms -mtime +30 -name *.trm -exec rm {} \;
find /u01/app/oracle/diag/rdbms -mtime +365 -name alert*.log.* -exec rm {} \;
find /u01/app/oracle/product/11.1.1/ofm_1/instances/instance1/diagnostics/logs/OHS/ohs1 -mtime +30 -name acceslog* -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/your-server/listener/alert -mtime +5 -name log*.xml -exec rm {} \;
No Comments