ORA-19815，ORA-19809 ：limit exceeded for recovery files-阿里云开发者社区

数据库重新启动的时候，收到了ORA-19815的错误。从错误的提示来看，是由于闪回区的空间被填满导致无法成功启动。这种情形我们通常考虑的是清除归档日志，那就直接在OS层面rm了，真的是这样吗？客官，如果你有相同的情形，接下往下看......

1、故障现象
idle> startup
ORACLE instance started.

Total System Global Area 238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             155192916 bytes
Database Buffers           75497472 bytes
Redo Buffers                6504448 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3562
Session ID: 125 Serial number: 5

Fri Sep 13 16:28:15 2013
ARC3 started with pid=27, OS id=4231
Errors in file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_4205.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4294967296 bytes is 100.00% used, 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.
************************************************************************
Errors in file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_4205.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 41592320 bytes disk space from 4294967296 limit
ARCH: Error 19809 Creating archive log file to '/u02/database/usbo/fr_area/USBO/archivelog/2013_09_13/o1_mf_1_12_%u_.arc'
Errors in file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_4205.trc:
ORA-16038: log 3 sequence# 12 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u02/database/usbo/oradata/redo03.log'
USER (ospid: 4205): terminating the instance due to error 16038
System state dump is made for local instance
System State dumped to trace file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_diag_4162.trc
Fri Sep 13 16:28:16 2013
Trace dumping is performing id=[cdmp_20130913162815]
Instance terminated by USER, pid = 4205

运行环境:
[oracle@linux1 ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m

[oracle@linux1 ~]$ sqlplus -V

SQL*Plus: Release 11.2.0.1.0 Production

2、故障分析
#从上面的错误消息大致可以判断闪回区空间不够用了"100.00% used"
#下面我们来看看错误的解决方案是是什么
[oracle@linux1 usbo]$ oerr ora 19815
19815, 00000, "WARNING: %s of %s bytes is %s%% used, and has %s remaining bytes available."
// *Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
// *Action: One of the following:
//          1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          2. Backup files to tertiary device using RMAN.
//          3. Consider changing RMAN retention policy.
//          4. Consider changing RMAN archived log deletion policy.
//          5. Delete files from recovery area using RMAN.

[oracle@linux1 usbo]$ 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.

#上面两个ORA错误的解决方案基本上相同，修改RMAN保留策略，使用RMAN删除归档日志及归档文件等，也可以通过增加调整闪回区的大小

3、故障解决
#我们来看看当前数据库的归档日志的大小
[oracle@linux1 archivelog]$ du -sh *
202M    2013_09_09
39M     2013_09_10
4.0K    2013_09_11
4.0K    2013_09_12
[oracle@linux1 archivelog]$ cd /u02/database/usbo

#下面查看当前数据库对应闪回区的大小，仅仅242M
[oracle@linux1 usbo]$ du -sh *
1.1G    adump
150M    diag
242M     fr_area
4.7G    oradata

#下面我们尝试直接在OS层面删除归档日志
[oracle@linux1 usbo]$ cd /u02/database/usbo/fr_area/USBO/archivelog
[oracle@linux1 archivelog]$ rm -rf *

#删除完毕后再次启动依旧收到上面的错误提示，所以我们必须根据Oracle给出的解决方案来实施
#也就是说Oracle根本认为其闪回区的空间根本没有得到释放

[oracle@linux1 ~]$ sqlplus / as sysdba

idle> startup nomount;

idle> show parameter db_reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/database/usbo/fr_area
db_recovery_file_dest_size           big integer 4G

idle> alter system set db_recovery_file_dest_size=5g;

idle> alter database mount;

idle> alter database open;

# Author : Leshami
# Blog : http://blog.csdn.net/leshami

idle> SET LINES 100
idle> COL name FORMAT a60
idle> SELECT name, FLOOR (space_limit / 1024 / 1024) "Size MB", CEIL (space_used / idle> 024 / 1024) "Used MB"
2 FROM v$recovery_file_dest
3 ORDER BY name
4 /

NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
/u02/database/usbo/fr_area 5120 4197

#从上面的查询可知，Oracle认为闪回区已经达到了4197MB，尽管OS层面已经删除了文件，但Oracle并不知道，因此从RMAN来着手

[oracle@linux1 ~]$ rman target /
connected to target database: USBO (DBID=3454448158)

RMAN> crosscheck archivelog all;

RMAN> delete noprompt archivelog all;

#如果你的archive log 不能够删除，可以考虑Oracle给出的解决方案，比如删除冗余的数据备份，修改保留策略等等
#再次在SQL*Plus查询，其使用空间已经只有2MB
#关于归档日志的清除，可以参考: Oracle RMAN 清除归档日志
idle > SELECT name, FLOOR (space_limit / 1024 / 1024) "Size MB", CEIL (space_used / 1024 / 1024) "Used MB"
2 FROM v$recovery_file_dest
3 ORDER BY name
4 /

NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
/u02/database/usbo/fr_area 5120 2