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