今天資料庫告警日誌報錯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/，如需轉載，請註明出處，否則將追究法律責任。