■現象

下記内容がアラートログに出力された。

Errors in file /opt/oracle/admin/****/bdump/****_arc1_23677.trc:
> ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 85.15% used, and has 3188039680 remaining bytes available.


db_recovery_file_dest_sizeに設定した値を使い切りそう、、という内容。
ちなみに実際に使い切るとこうなる（以前やらかした経験あり。。）

ARC0: Failed to archive thread 1 sequence 1293 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORA01 - Archival Error
ORA-16038: log 3 sequence# 1293 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1:
'/opt/oracle/db01/ORA01/onlinelog/o1_mf_3_2dmpd4dw_.log'
ORA-00312: online log 3 thread 1:
'/opt/oracle/db02/ORA01/onlinelog/o1_mf_3_2dmpd4x3_.log'
ORA-00312: online log 3 thread 1:
'/opt/oracle/db03/ORA01/onlinelog/o1_mf_3_2dmpd5r1_.log'
Errors in file /opt/oracle/admin/ORA01/bdump/ORA01_arc1_12029.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is
99.87% used, and has 28377088 remaining bytes available.
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************



■対策

db_recovery_file_dest_sizeを拡張する。
実際のファイルシステム上にも同じサイズの余裕があることをあらかじめ確認すること。


$ sqlplus ***/*** as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 19 19:42:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> SHOW PARAMETER db_recovery_file_dest
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest string
/opt/oracle/flash_recovery_area
db_recovery_file_dest_size big integer
20G
SQL> alter system set db_recovery_file_dest_size='40G' scope=BOTH ;
System altered.
SQL> SHOW PARAMETER db_recovery_file_dest
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest string
/opt/oracle/flash_recovery_area
db_recovery_file_dest_size big integer
40G
SQL>






詳細はこのあたりが参考になる。

Oracle Technology Network (OTN) Japan - 掲示板 : 空きがあるのにdb_recovery_f ...



現在の利用状況を調べるにはこんな感じのSQLを流す。

select space_used from v$recovery_file_dest;