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,
2. Back up files to tertiary device such as tape using RMAN
3. Add disk space and increase db_recovery_file_dest_size parameter
to
4. Delete unnecessary files using RMAN DELETE command. If an
operating
************************************************************************
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
------------------------------------ -----------
------------------------------
db_recovery_file_dest
db_recovery_file_dest_size
按照网上说的解决办法,有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
crosscheck archivelog all;
delete expired archivelog all;
具体什么意思,有待下一步的继续学习!