ORACLE rman備份之ORA-19809 ORA-19804

    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.