Oracle db_recovery_file_dest空間不足報ora-00257問題解決

今天在虛擬機器上用impdp匯入資料，到index部分的時候，突然硬碟不轉了
用plsql dev客戶端登入報
ORA-00257: archiver error. Connect internal only, until freed
這樣的錯誤，跟昨天客戶現場遇到的問題一樣。
[oracle@localhost data]$ oerr ora 00257
00257, 00000, "archiver error. Connect internal only, until freed."
// *Cause:  The archiver process received an error while trying to archive
//       a redo log.  If the problem is not resolved soon, the database
//       will stop executing transactions. The most likely cause of this
//       message is the destination device is out of space to store the
//       redo log file.
// *Action:  Check archiver trace file for a detailed description
//        of the problem. Also verify that the
//       device specified in the initialization parameter
//       ARCHIVE_LOG_DEST is set up properly for archiving.

很開心，沒想到這麼容易就重現了該問題。
於是趕緊查alert log，發現瞭如下資訊：
Errors in file /oracle/admin/lyon/bdump/lyon_arc0_4348.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Feb 12 08:16:24 2010
************************************************************************
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.
************************************************************************
Fri Feb 12 08:16:24 2010
Errors in file /oracle/admin/lyon/bdump/lyon_arc0_4348.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47696384 bytes disk space from 2147483648 limit
ARC0: Error 19809 Creating archive log file to '/arch/flash_recovery_area/LYON/archivelog/2010_02_12/o1_mf_1_62_0_.arc'
ARC0: Failed to archive thread 1 sequence 62 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Feb 12 08:16:24 2010
ORACLE Instance lyon - Archival Error
Fri Feb 12 08:16:24 2010
ORA-16038: log 1 sequence# 62 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'
Fri Feb 12 08:16:24 2010
Errors in file /oracle/admin/lyon/bdump/lyon_arc0_4348.trc:
ORA-16038: log 1 sequence# 62 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'
Fri Feb 12 08:16:25 2010
ARC1: Archiving not possible: No primary destinations
Fri Feb 12 08:16:25 2010
ARC0: Archiving not possible: No primary destinations
Fri Feb 12 08:16:25 2010
ARC1: Failed to archive thread 1 sequence 62 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Feb 12 08:16:25 2010
ORACLE Instance lyon - Archival Error
Fri Feb 12 08:16:25 2010
ORA-16014: log 1 sequence# 62 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'
Fri Feb 12 08:16:25 2010
Errors in file /oracle/admin/lyon/bdump/lyon_arc1_4350.trc:
ORA-16014: log 1 sequence# 62 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'

看來是db_recovery_file_dest空間不夠了，但是奇怪，我剛才為了防止這個空間不夠，而特意擴充了4G的磁碟分割槽。
而且在歸檔日誌增加的過程中我也一點點的把歸檔檔案給刪除掉了，檢視剩餘空間還有很多呢。
仔細看了下警告日誌，看到有這麼一行：
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.
************************************************************************
第一種方法是改變rman retention policy。即檔案保留策略。如果使用的是Data Guard，則改變rman 歸檔日誌備份策略。
第二種方法是使用rman backup recovery area將備份歸檔日誌到第三方裝置
第三種是增加磁碟空間，然後增加db_recovery_file_dest_size引數的值。
第四種是用rman delete刪除不必要的歸檔日誌檔案。如果使用作業系統命令刪除的（如rm），則要用rman crosscheck命令進行交叉檢查，
並使用delete expired命令刪除失效的檔案。

這裡採用的是第三種和第四種結合的方式，增加了db_recovery_file_dest_size引數。
由於磁碟空間本來就是充足的，於是只要引數值即可：
alter system set db_recovery_file_dest_size=5g scope=memory;--只修改臨時的大小，不影響以後的。
然後到rman中刪除所有的歸檔日誌檔案（沒有進行交叉檢查）：
delete archivelog all;
雖然這樣太過草率，不過最重要的還是能夠解決這個問題了。並且以後可以遵循從以上的幾種標準的模式來解決問題了。
先用第三種方式擴大引數值的目的是儘快讓客戶可以登入（因為實際磁碟空間是夠的）。然後再採用第四種方式刪除沒必要的歸檔日誌。這樣此次問題就很快地解決了。