且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

ORACLE rman备份之ORA-19809 ORA-19804

更新时间:2022-07-01 01:07:47

    2016年3月8日,接到某综合网管系统负责人申告,该系统RMAN备份失败,RMAN备份失败日志如下:
操作系统:REDHAT linux 5.6
数据库版本:11.2.0.3
channel ORA_DISK_1: starting piece 1 at 07-MAR-16

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/07/2016 11:36:24

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 67108864 bytes disk space from 4322230272 limit

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

channel ORA_DISK_1: starting compressed full 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 07-MAR-16

channel ORA_DISK_1: finished piece 1 at 07-MAR-16

piece handle=/opt/ora_install/fast_recovery_area/CSWG/backupset/2016_03_07/o1_mf_ncsnf_TAG20160307T112117_cfsxssb7_.bkp tag=TAG20160307T112117 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 03/07/2016 11:36:24

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 67108864 bytes disk space from 4322230272 limit
    
这个问题较明显,问题原因就是数据库的RMAN备份路径指定到了闪回区,经过与系统负责人沟通发现RMAN备份脚本没有指定备份路径,所以备份就写到闪回区了。
    通过下面命令查询当前数据库的闪回区目录,可以发现闪回区目录下,Oracle允许存放的文件空间确实很小,是默认的4G
SQL> show parameter db_recovery_file;
NAME                                       TYPE             VALUE
------------------------------------ -----------       ------------------------------
db_recovery_file_dest          string           /opt/ora_install/fast_recovery_area
db_recovery_file_dest_size  big integer 4122M
   查看闪回区目录存放的文件信息,可以发现,闪回目录下只有一个1MB的备份集,并没有达到4G,通过SQLPLUS查询也能印证。
[oracle][/opt/ora_install/fast_recovery_area/CSWG/backupset/2016_03_04]$ls -al
total 0
drwxr-x---    2 oracle   oinstall        256 Mar 04 00:14 .
drwxr-x---    4 oracle   oinstall        256 Mar 07 11:21 ..
-rw-r-----    1 oracle   oinstall    1114112 Mar 04 00:14 o1_mf_ncsnf_TAG20160304T000004_cfjrq8r2_.bkp
SQL> select * from v$recovery_file_dest;
NAME                                                         SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------          -----------          ----------            -----------------                ---------------
/opt/ora_install/fast_recovery_area   4322230272    2211840 
   这一点比较有意思,如果闪回区满,通过rm -rf删除闪回区目录下的文件,ORACLE数据库还是会不停的告警闪回目录满,其实需要我们到rman工作台执行删除备份集及归档同步catalog库记录信息。
[oracle][/opt/ora_install/fast_recovery_area/CSWG/backupset/2016_03_04]$rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 8 09:33:11 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database:**** (DBID=3404523328)
RMAN> crosscheck archivelog all ;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2280 device type=DISK
validation failed for archived log
archived log file name=/opt/ora_log/1_167_905310595.dbf RECID=3 STAMP=905401082
validation failed for archived log
archived log file name=/opt/ora_log/1_168_905310595.dbf RECID=1 STAMP=905401081
validation failed for archived log
.
.
.
validation succeeded for archived log
archived log file name=/opt/ora_log/1_642_905310595.dbf RECID=476 STAMP=905937113
Crosschecked 476 objects
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2280 device type=DISK
List of Archived Log Copies for database with db_unique_name CSWG
=====================================================================
Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
3       1    167     X 02-MAR-16
        Name: /opt/ora_log/1_167_905310595.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/opt/ora_log/1_167_905310595.dbf RECID=3 STAMP=905401082
deleted archived log
.
.
.
deleted archived log
archived log file name=/opt/ora_log/1_643_905310595.dbf RECID=477 STAMP=905938535
Deleted 477 objects
RMAN> exit
Recovery Manager complete.
   还有一个解决方法,就是调大闪回区的空间限制,增加db_recovery_file_dest_size,需要注意的是需要重启数据库。
SQL> startup mount;
.
.
SQL
> alter system set db_recovery_file_dest_size=20G;
System altered.
SQL> alter database open;
Database altered.