原 ORA-19809: limit exceeded for recovery files
Tags： Oracle原创故障处理闪回恢复区FRAORA-19804ORA-19809
👉 本文共约1869个字，系统预计阅读时间或需8分钟。
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~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
② 闪回恢复区占用大小的查询
故障分析及解决过程
故障环境介绍
| 项目 | source db |
|---|---|
| db 类型 | RAC |
| db version | 11.2.0.4 |
| db 存储 | ASM |
| OS版本及kernel版本 | AIX 64位 6.1.0.0 |
故障发生现象及报错信息
rman执行0级全备的时候报错：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | [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 |
故障分析及解决过程
看着这个错误听陌生的，之前没有遇到过，oerr看一下解释：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | [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设置过小导致的，下边我们来修复该错误:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | [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 |
如何查看闪回恢复区大小的占用情况
我们从健康检查的脚本可以看出：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
