闪回恢复区无可用空间带来的问题及解决

测试Oracle版本为11.2.0.1

1、闪回恢复区没有可用空间，于是手动删除其中的归档日志，问题并没有解决，于是修改参数并重启数据库：

alter system set db_recovery_file_dest_size=10G scope=spfile;

2、随后发现RMAN无法每天备份了，日志如下：

Starting backup at 04-JAN-15
current log archived
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 01/04/2015 23:30:06
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2014_12_26/o1_mf_1_6_b9smjw3v_.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3、过了几天数据库连不上，还是因为闪回恢复区没有空间了，告警日志内容如下：

ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc0_2711.t
rc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% u
sed, and has 0 remaining bytes available.
USER (ospid: 2497): terminating the instance due to error 16038
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc1_2501.t
rc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% u
sed, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************

4、原因

虽然手动删除归档日志，但是oracle中记录的关于闪回恢复区使用情况的信息并没有更新，导致闪回恢复区仍然空间不足，除非扩大空间。

RMAN无法备份是因为RMAN找不到预期的归档日志，所以终止备份过程。

5、解决方法

在RMAN中删除归档

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
validation failed for archived log
archived log file name=/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2015_01_09/o1_mf_1_319_bby147rw_.arc RECID=314 STAMP=868516103
validation failed for archived log
archived log file name=/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2015_01_09/o1_mf_1_320_bby7tn4f_.arc RECID=315 STAMP=868522964
Crosschecked 2 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
List of Archived Log Copies for database with db_unique_name TESTDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
314     1    319     X 08-JAN-15
        Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2015_01_09/o1_mf_1_319_bby147rw_.arc

315     1    320     X 09-JAN-15
        Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2015_01_09/o1_mf_1_320_bby7tn4f_.arc


Do you really want to delete the above objects (enter YES or NO)? y
deleted archived log
archived log file name=/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2015_01_09/o1_mf_1_319_bby147rw_.arc RECID=314 STAMP=868516103
deleted archived log
archived log file name=/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2015_01_09/o1_mf_1_320_bby7tn4f_.arc RECID=315 STAMP=868522964
Deleted 2 EXPIRED objects

最后查询确认闪回恢复区空间是否释放

SQL> SELECT substr(name, 1, 30) name, space_limit/1024/1024/1024 AS limit,
  2           space_used        AS used,
  3           space_reclaimable AS reclaimable,
  4           number_of_files   AS files
  5           FROM  v$recovery_file_dest ;

NAME                                                              LIMIT
------------------------------------------------------------ ----------
      USED RECLAIMABLE      FILES
---------- ----------- ----------
/u01/app/oracle/flash_recovery                                       10
         0           0          0