ORA-19815: WARNING: db_recovery_file_dest_size is 100%

测试系统上检查警告日志时发现以下警告：

Wed May 26 14:55:16 2010
Errors in file /oracle/admin/infodb/bdump/infodb_arc1_18774.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Wed May 26 14:55:16 2010
************************************************************************
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.
************************************************************************

Oracle给了4中解决办法：

1. 修改RMAN的备份保持策略，如果使用了Data Guard 改变archivelog 删除策略。

2. 使用Rman 把文件备份到磁带上。（这里好像只能把recovery area 备份到磁带上，备份到磁盘上
   会报错，有待进一步确认。）

3. 修改db_recovery_file_dest_size 参数增加更多的空间。

4. 使用操作系统或RMAN DELETE 命令删除过期的命令。如果使用操作系统命令删除文件，要用rman执行
   crosscheck archivelog all;

以下是我的解决步骤：
SQL> conn / as sysdba
SQL> show parameter db_recovery_file_dest_size

NAME                                          TYPE                                VALUE
------------------------------------ -----------           ------------------------------
db_recovery_file_dest_size           big integer                             2G
SQL> col name for a30
SQL> SELECT substr(name, 1, 30) name, space_limit AS quota,
2 space_used        AS used,
3 space_reclaimable AS reclaimable,
4 number_of_files   AS files
5 FROM v$recovery_file_dest ;

NAME                                QUOTA       USED RECLAIMABLE      FILES
------------------------------ ---------- ---------- ----------- ----------
/oracle/flash_recovery_area    2147483648 2146841600           0         52

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

SQL> SELECT substr(name, 1, 30) name, space_limit AS quota,
2 space_used        AS used,
3 space_reclaimable AS reclaimable,
4 number_of_files   AS files
5* FROM v$recovery_file_dest

NAME                                QUOTA       USED RECLAIMABLE      FILES
------------------------------ ---------- ---------- ----------- ----------
/oracle/flash_recovery_area    2147483648   15521792           0          3

问题解决。

也可以通过修改过db_recovery_file_dest_size，把其值改大些。

SQL> alter system set db_recovery_file_dest_size=4G scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size                  1266464 bytes
Variable Size             125832416 bytes
Database Buffers           50331648 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 4g