【故障处理】ORA-19809错误处理
一.1 BLOG文档结构图
一.2 前言部分
一.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~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
② 闪回恢复区占用大小的查询
Tips：
① 本文在ITpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）有同步更新
② 文章中用到的所有代码，相关软件，相关资料请前往小麦苗的云盘下载（http://blog.itpub.net/26736162/viewspace-1624453/）
③ 若文章代码格式有错乱，推荐使用搜狗、360或QQ浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://blog.itpub.net/26736162/viewspace-1624453/
④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
一.3 故障分析及解决过程
一.3.1 故障环境介绍
| 项目 | source db |
| db 类型 | RAC |
| db version | 11.2.0.4 |
| db 存储 | ASM |
| OS版本及kernel版本 | AIX 64位 6.1.0.0 |
一.3.2 故障发生现象及报错信息
rman执行0级全备的时候报错：
[ZFXDESKDB2:root]:/>su - oracle
[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=raclhr2
[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
一.3.3 故障分析及解决过程
看着这个错误听陌生的，之前没有遇到过，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>ORACLE_SID=raclhr2
[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
一.4 故障处理总结
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设置为更大的值即可解决问题，要想更深入分析该问题就得分析闪回恢复区里存放了哪些内容。
一.5 如何查看闪回恢复区大小的占用情况
我们从健康检查的脚本可以看出：
点击连接可以跳转到相应的部分：
数据库闪回空间使用情况
● 数据库闪回空间总体使用情况
| NAME | LIMIT | USED_GB | USED% | RECLAIM | NUMBER_OF_FILES |
| +DATA | 10G | 5.953G | 59.531% | 4.154G | 144 |
[回到目录]
● 数据库闪回空间详细使用情况
| FILE_TYPE | USED_GB | PERCENT_SPACE_USED | PERCENT_SPACE_RECLAIMABLE | RECLAIM_GB | NUMBER_OF_FILES |
| ARCHIVED LOG | 4.185 | 41.85 | 41.46 | 4.146 | 138 |
| BACKUP PIECE | 1.619 | 16.19 | .08 | .008 | 3 |
| CONTROL FILE | 0 | 0 | 0 | 0 | 0 |
| FLASHBACK LOG | 0 | 0 | 0 | 0 | 0 |
| FOREIGN ARCHIVED LOG | 0 | 0 | 0 | 0 | 0 |
| IMAGE COPY | 0 | 0 | 0 | 0 | 0 |
| REDO LOG | .149 | 1.49 | 0 | 0 | 3 |
| Total: | 5.953 | 59.53 | 41.54 | 4.154 | 144 |
[回到目录]
可以看出，闪回空间目前是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)
;
一.6 用到的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)
;
About Me
..........................................................................................................................................................................................................
v 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
v 本文在ITpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号（xiaomaimiaolhr）上有同步更新
v QQ群：230161599 微信群：私聊
v 本文地址：http://blog.itpub.net/26736162/viewspace-2122820/
v 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）
v 小麦苗分享的其它资料：http://blog.itpub.net/26736162/viewspace-1624453/
v 联系我请加QQ好友(642808185)，注明添加缘由
v 于 2016-08-01 15:00~ 2016-08-01 19:00 在中行完成
v 【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】
..........................................................................................................................................................................................................
长按识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号：xiaomaimiaolhr,学习最实用的数据库技术。