SQL> ALTER DATABASE OPEN;
ORA-16014: log 3 sequence# 157 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/opt/oracle/software/oradata/orcl/redo03.log'
根因:快速恢復區 (FRA) 空間 100% 滿,歸檔無法寫入,導致線上日誌無法切換/歸檔,資料庫掛起。
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 3G SCOPE = BOTH;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SCOPE=BOTH 同時修改記憶體與 SPFILE,無需重啟。
-- 歸檔模式確認
SQL> ARCHIVE LOG LIST;
-- FRA 各類型佔用
SELECT * FROM v$flash_recovery_area_usage;
-- 總佔用率
SELECT SUM(percent_space_used) * 3 / 100 AS total_pct_used FROM v$flash_recovery_area_usage;
-- 注意:percent_space_used 已是百分比,乘 3 是因為範例中 FRA 設 3G,實際應直接看 percent_space_used 總和
RMAN> CROSSCHECK BACKUPSET;
RMAN> DELETE NOPROMPT EXPIRED BACKUPSET;
RMAN> DELETE OBSOLETE; -- 依保留策略刪除過期備份
RMAN> DELETE NOPROMPT EXPIRED BACKUP;
RMAN> DELETE NOPROMPT EXPIRED COPY;
-- 清理歸檔日誌
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
-- 強制刪除 7 天前歸檔 (極端情況)
RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/nfsdisk/oraarc' SCOPE = BOTH;
System altered.
SQL> ARCHIVE LOG LIST;
Database log mode: Archive Mode
Archive destination: /nfsdisk/oraarc
...
-- 測試切換日誌
SQL> ALTER SYSTEM SWITCH LOGFILE;
新目錄權限不足會導致歸檔進程 (ARCn) 錯誤,V$ARCHIVE_DEST 狀態顯示 ERROR。
SQL> COL DESTINATION FOR A30
SQL> SELECT DEST_ID, DEST_NAME, STATUS, DESTINATION FROM V$ARCHIVE_DEST;
DEST_ID DEST_NAME STATUS DESTINATION
------- ------------------- -------- ------------------------------
1 LOG_ARCHIVE_DEST_1 ERROR /nfsdisk/oraarc <-- 權限問題
# OS 層修正
$ chown oracle:oinstall /nfsdisk/oraarc
$ chmod -R 775 /nfsdisk/oraarc
# 再次 SWITCH LOGFILE 驗證 STATUS 變為 VALID
DB_RECOVERY_FILE_DEST 監控告警(> 80% 警告,> 90% 緊急)。LOG_ARCHIVE_DEST_1=LOCATION=/fra ...、LOG_ARCHIVE_DEST_2=LOCATION=/nfs/arc ...。CROSSCHECK + DELETE OBSOLETE 納入 RMAN 維護腳本。FRA 滿是 Oracle DBA 最常見的「半夜被叫醒」故障,**擴容止血 → 清理釋放 → 歸檔外移 → 權限驗證 → 監控固化** 五步走,徹底杜絕復發。