ORA-19809: limit exceeded for recovery files

前言部分

导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① ORA-19809: limit exceeded for recovery files错误的处理方法

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/26/2016 17:35:35

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 68157440 bytes disk space from 4385144832 limit

② 闪回恢复区占用大小的查询

故障分析及解决过程

故障环境介绍

故障发生现象及报错信息

rman执行0级全备的时候报错：

[ZFXDESKDB2:root]:/>su - oracle

ZFXDESKDB2:oracle:/oracle>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 26 17:35:15 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: RACLHR (DBID=4156381309)

RMAN> backup incremental level 0 database;

Starting backup at 2016-07-26 17:35:19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=70 instance=raclhr2 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=+DATA/raclhr/datafile/sysaux.365.916601225

input datafile file number=00001 name=+DATA/raclhr/datafile/system.359.916601225

input datafile file number=00005 name=+DATA/raclhr/datafile/example.351.916601377

input datafile file number=00003 name=+DATA/raclhr/datafile/undotbs1.364.916601225

input datafile file number=00006 name=+DATA/raclhr/datafile/undotbs2.343.916601713

input datafile file number=00004 name=+DATA/raclhr/datafile/users.363.916601225

input datafile file number=00007 name=+DATA/raclhr/datafile/rsa_cm_data.519.917516173

input datafile file number=00008 name=+DATA/raclhr/datafile/rsa_cm_data.520.917516173

input datafile file number=00009 name=+DATA/raclhr/datafile/rsa_cm_data.521.917516173

input datafile file number=00010 name=+DATA/raclhr/datafile/ts_lhr.522.917516295

input datafile file number=00011 name=+DATA/raclhr/datafile/ts_lhr.523.917516295

input datafile file number=00012 name=+DATA/raclhr/datafile/ts_lhr.524.917516295

input datafile file number=00013 name=+DATA/raclhr/datafile/test.525.917516413

input datafile file number=00014 name=+DATA/raclhr/datafile/test.526.917516413

input datafile file number=00015 name=+DATA/raclhr/datafile/test.527.917516413

channel ORA_DISK_1: starting piece 1 at 2016-07-26 17:35:20

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/26/2016 17:35:35

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 68157440 bytes disk space from 4385144832 limit

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2016-07-26 17:35:37

channel ORA_DISK_1: finished piece 1 at 2016-07-26 17:35:38

piece handle=+DATA/raclhr/backupset/2016_07_26/ncsnn0_tag20160726t173520_0.568.918236137 tag=TAG20160726T173520 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/26/2016 17:35:35

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 68157440 bytes disk space from 4385144832 limit

故障分析及解决过程

看着这个错误听陌生的，之前没有遇到过，oerr看一下解释：

ZFXDESKDB2:oracle:/oracle>oerr rman 3009

3009, 1, "failure of %s command on %s channel at %s"

// *Cause: This message should be accompanied by other error message(s)

// indicating the cause of the error.

// *Action: Check the accompanying errors.

ZFXDESKDB2:oracle:/oracle>oerr ora 19809

19809, 00000, "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.

ZFXDESKDB2:oracle:/oracle>oerr ora 19804

19804, 00000, "cannot reclaim %s bytes disk space from %s limit"

// *Cause: Oracle cannot reclaim disk space of specified bytes from the

// DB_RECOVERY_FILE_DEST_SIZE limit.

// *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.

由oerr的解释可以看出该错误是由于闪回恢复区大小参数DB_RECOVERY_FILE_DEST设置过小导致的，下边我们来修复该错误:

ZFXDESKDB2:oracle:/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 1 15:38:13 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SYS@raclhr2> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size big integer 4182M

SYS@raclhr2> alter system set db_recovery_file_dest_size=10G sid='*';

System altered.

SYS@raclhr2> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

ZFXDESKDB2:oracle:/oracle>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 1 15:46:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: RACLHR (DBID=4156381309)

RMAN> backup incremental level 0 database;

Starting backup at 2016-08-01 15:47:10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=67 instance=raclhr2 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=+DATA/raclhr/datafile/sysaux.365.916601225

input datafile file number=00001 name=+DATA/raclhr/datafile/system.359.916601225

input datafile file number=00005 name=+DATA/raclhr/datafile/example.351.916601377

input datafile file number=00003 name=+DATA/raclhr/datafile/undotbs1.364.916601225

input datafile file number=00006 name=+DATA/raclhr/datafile/undotbs2.343.916601713

input datafile file number=00004 name=+DATA/raclhr/datafile/users.363.916601225

input datafile file number=00007 name=+DATA/raclhr/datafile/rsa_cm_data.519.917516173

input datafile file number=00008 name=+DATA/raclhr/datafile/rsa_cm_data.520.917516173

input datafile file number=00009 name=+DATA/raclhr/datafile/rsa_cm_data.521.917516173

input datafile file number=00010 name=+DATA/raclhr/datafile/ts_lhr.522.917516295

input datafile file number=00011 name=+DATA/raclhr/datafile/ts_lhr.523.917516295

input datafile file number=00012 name=+DATA/raclhr/datafile/ts_lhr.524.917516295

input datafile file number=00013 name=+DATA/raclhr/datafile/test.525.917516413

input datafile file number=00014 name=+DATA/raclhr/datafile/test.526.917516413

input datafile file number=00015 name=+DATA/raclhr/datafile/test.527.917516413

channel ORA_DISK_1: starting piece 1 at 2016-08-01 15:47:12

channel ORA_DISK_1: finished piece 1 at 2016-08-01 15:47:57

piece handle=+DATA/raclhr/backupset/2016_08_01/nnndn0_tag20160801t154711_0.597.918748035 tag=TAG20160801T154711 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2016-08-01 15:47:59

channel ORA_DISK_1: finished piece 1 at 2016-08-01 15:48:00

piece handle=+DATA/raclhr/backupset/2016_08_01/ncsnn0_tag20160801t154711_0.598.918748079 tag=TAG20160801T154711 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2016-08-01 15:48:00

RMAN> list backupset summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- ------------------- ------- ------- ---------- ---

1 B 0 A DISK 2016-07-26 17:35:37 1 1 NO TAG20160726T173520

2 B 0 A DISK 2016-08-01 15:47:51 1 1 NO TAG20160801T154711

3 B 0 A DISK 2016-08-01 15:47:59 1 1 NO TAG20160801T154711

故障处理总结

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 68157440 bytes disk space from 4385144832 limit

ORA-19809错误是由于闪回恢复区设置过小导致，只需要修改参数db_recovery_file_dest_size设置为更大的值即可解决问题，要想更深入分析该问题就得分析闪回恢复区里存放了哪些内容。

如何查看闪回恢复区大小的占用情况

我们从健康检查的脚本可以看出：

点击连接可以跳转到相应的部分：

数据库闪回空间使用情况

● 数据库闪回空间总体使用情况

[回到目录]

● 数据库闪回空间详细使用情况

[回到目录]

可以看出，闪回空间目前是10G，使用了将近60%，共144个文件，每种文件的占用情况参考如上的表格，可以看出主要是归档文件和备份集占用空间比较大。

有关健康检查报告中的用到的脚本如下：

闪回恢复区总大小：

SELECT NAME,

round(space_limit / 1024 / 1024 / 1024, 3) "LIMIT_GB",

round(space_used / 1024 / 1024 / 1024, 3) "USED_GB",

round(space_used / space_limit * 100, 3) "USED%",

round(space_reclaimable / 1024 / 1024 / 1024, 3) "RECLAIM_GB",

number_of_files

FROM v$recovery_file_dest v

WHERE v.SPACE_LIMIT\<>0;

闪回恢复区详细使用大小：

SELECT nvl(frau.file_type,'\<font color="#990000">\<b>Total:\</b>\</font>') file_type,

sum(round(frau.percent_space_used / 100 * rfd.space_limit / 1024 / 1024 / 1024,3)) USED_GB,

sum(frau.percent_space_used) percent_space_used,

sum(frau.percent_space_reclaimable) percent_space_reclaimable,

sum(round(frau.percent_space_reclaimable / 100 * rfd.space_limit / 1024 / 1024 / 1024,3)) RECLAIM_GB,

sum(frau.number_of_files) number_of_files

FROM v$flash_recovery_area_usage frau,

v$recovery_file_dest rfd

GROUP BY ROLLUP(file_type)

;

用到的SQL集合

0级全备：backup incremental level 0 database;

oerr查看错误：oerr rman 3009 、oerr ora 19809

设置闪回恢复区的大小：alter system set db_recovery_file_dest_size=10G sid='*';

------闪回恢复区总大小：

SELECT NAME,

round(space_limit / 1024 / 1024 / 1024, 3) "LIMIT_GB",

round(space_used / 1024 / 1024 / 1024, 3) "USED_GB",

round(space_used / space_limit * 100, 3) "USED%",

round(space_reclaimable / 1024 / 1024 / 1024, 3) "RECLAIM_GB",

number_of_files

FROM v$recovery_file_dest v

WHERE v.SPACE_LIMIT\<>0;

-----闪回恢复区详细使用大小：

SELECT nvl(frau.file_type,'\<font color="#990000">\<b>Total:\</b>\</font>') file_type,

sum(round(frau.percent_space_used / 100 * rfd.space_limit / 1024 / 1024 / 1024,3)) USED_GB,

sum(frau.percent_space_used) percent_space_used,

sum(frau.percent_space_reclaimable) percent_space_reclaimable,

sum(round(frau.percent_space_reclaimable / 100 * rfd.space_limit / 1024 / 1024 / 1024,3)) RECLAIM_GB,

sum(frau.number_of_files) number_of_files

FROM v$flash_recovery_area_usage frau,

v$recovery_file_dest rfd

GROUP BY ROLLUP(file_type)

;