今天数据库告警日志报错ORA-16038/ORA-19809/ORA-00312

new_svn> tail -f alert_ccdb.log

Errors in file /u01/app/oracle/diag/rdbms/ccdb/ccdb/trace/ccdb_ora_25560.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 92623872 bytes disk space from 5218762752 limit
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/CCDB/archivelog/2013_06_08/o1_mf_1_332_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/ccdb/ccdb/trace/ccdb_ora_25560.trc:
ORA-16038: log 2 sequence# 332 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ccdb/redo02.log'
USER (ospid: 25560): terminating the instance due to error 16038
Instance terminated by USER, pid = 25560


Errors in file /u01/app/oracle/diag/rdbms/ccdb/ccdb/trace/ccdb_arc3_25915.trc:
ORA-19809: 超出了恢复文件数的限制
ORA-19804: 无法回收 92623872 字节磁盘空间 (从 5218762752 限制中)


故障原因：


由于初始化设置的db_recovery_file_dest_size=5G不足，导致online redo
log无法归档，因此可以有三种办法解决此问题：一是修改初始化参数db_recovery_file_dest_size，增加至大于归档文件总容量；二是
指定其他的归档日志路径，使归档日志保存至别处；三是备份数据库，然后用RMAN删除归档文件
   
注：此问题仅针对10g及以上版本，9i没有db_recovery_file_dest_size参数，可以直接指定路径，也可以直接手动删除来释放空间。
解决方法：
RMAN
使用rman对归档日志惊醒cross check
RMAN> crosscheck archivelog all;
使用rman 从数据库记录中删除归档日志的记录
RMAN> delete expired archivelog all;

1：check闪回区占用情况
SELECT substr(name, 1, 30) name, space_limit AS quota,
              space_used        AS used,
              space_reclaimable AS reclaimable,
            number_of_files   AS files
       FROM  v$recovery_file_dest ;
sql>col file_type for a15      
sql>select * from v$flash_recovery_area_usage;

计算flash recovery area已经占用的空间：
select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
查看大小参数
show parameter db_recovery
2：清空闪回区
alter system set db_recovery_file_dest = '' scope=spfile;
alter system set log_archive_dest = '' scope=spfile;
alter system set log_archive_dest_1 = 'location=/oracle/app/arc' scope=spfile; //修改归档目录
问题2：
ORA-16038: log 2 sequence# 332 cannot be archived
解决方法是清理redo2
SQL>alter database clear unarchived logfile group 2;
SQL>alter database open;


 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14661773/viewspace-763533/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14661773/viewspace-763533/