When you get, give. When you Know, Share. When we Give and Share, we get and acquire more.
Tuesday, 2 April 2013
ORA-19815: WARNING: db_recovery_file_dest_size of 4227858432 bytes is 100.00% used
Here because of this error my SQL views getting hung, if I query any view then the output is displaying nothing.
Checked alert log and found
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 that location and backup the old archivelogs which you want to delete and delete them, here in this case I deleted 3 archive logs
Rman> crosscheck archivelog all;
Output displayed 3 expired .arc logs
Rman> delete expired archivelog all;
This will delete the Expired archivelogs
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
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G 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 10G
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
---------- ----------
10 5.04552555
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment