ORA-19809: limit exceeded for recovery files

近期查看alert_orcl.log,发现经常有类似的error:

Errors in file d:\app\qiao\diag\rdbms\orcl\orcl\trace\orcl_arc3_3904.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash 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 d:\app\qiao\diag\rdbms\orcl\orcl\trace\orcl_arc3_3904.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 48401920 bytes disk space from 2147483648 limit
ARC3: Error 19809 Creating archive log file to 'D:\APP\qiao\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_400_%U_.ARC'

原来oracle11g在默认情况下,归档日志是保存在闪存恢复区的,并且闪存恢复区的大小默认是2g,空间满了之后就没有办法再归档了。

 

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      d:\app\qiao\flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G

 

按照网上说的解决办法,有3种:

1.加大闪存恢复区。

ALTER SYSTEM SET db_recovery_file_dest_size=4g scope=both;

2.归档路径设置到其它地方。

alter system set log_archive_dest = 其他路径

3.删除或转移归档日志。

 

因为对我这个测试数据库来说,这些归档日志是没有用的,所以干脆删掉得了。

于是在os层删除了flash_recovery_area\ORCL\ARCHIVELOG下面所有的文件,以为这样就好了。

但过了一段时间,发现还是报错!

 

上网又找了些资料,用下面的方法就可以搞定:

rman

connect target  sys/oracle

crosscheck archivelog all;

delete expired archivelog all;

具体什么意思,有待下一步的继续学习!

posted @ 2011-09-16 10:33  【小洲】  阅读(809)  评论(0)    收藏  举报