ORA-19809

官方描述:

ORA-19809: limit exceeded for recovery files

Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded. 
Action: There are five possible solutions: 1) Take frequent backup of recovery area using RMAN. 2) Consider changing RMAN retention policy. 3) Consider changing RMAN archived log deletion policy. 4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE. 5) Delete files from recovery area using RMAN.

 

情况一:

DB版本:11.2.0.1.0

OS版本:WIN XP SP3

 

问题描述:

alert中发现大量错误信息:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 861184 bytes disk space from 4039114752 limit
ARC1: Error 19809 Creating archive log file to 'F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2013_03_29\O1_MF_1_141_%U_.ARC'
Errors in file f:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_arc0_4240.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.

处理过程:

1. 增大闪回空间

alter system set db_recovery_file_dest_size=8G scope=both;

2. 删除部分归档文件(我这里删除了7天前的归档文件)

rman target /

RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

处理后查看:
14:47:01 SYS@orcl> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from  v$flash_recovery_area_usage;


FILE_TYPE                  USED RECLAIMABLE     number
-------------------- ---------- ----------- ----------
CONTROL FILE                  0           0          0
REDO LOG                      0           0          0
ARCHIVED LOG               4.94           0          6  --处理后空间使用降低到4.94%
BACKUP PIECE                .25         .25          3
IMAGE COPY                    0           0          0
FLASHBACK LOG              4.56        2.18         23
FOREIGN ARCHIVED LOG          0           0          0