Cannot Allocate New Log

故障報錯

Thread 1 cannot allocate new log, sequence 2594

Checkpoint not complete

故障現象

redo log頻繁切換，資料庫DML性能下降。額外的日志歸檔影響IO性能。

故障原因及解決方案

該故障大部分情況下是由于redo log切換過于頻繁導緻沒有及時進行日志歸檔。此外，日志切換前沒有完成檢查點操作也會導緻同樣報錯，可以通過調整fast_start_mttr_target參數優化檢查點操作。

通過如下腳本可以檢測出redo log的切換頻率

SELECT
        TO_CHAR(first_time, 'YYYY-MON-DD') DAY
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)), '99') "00"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)), '99') "01"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)), '99') "02"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)), '99') "03"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)), '99') "04"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)), '99') "05"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)), '99') "06"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)), '99') "07"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)), '99') "0"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)), '99') "09"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)), '99') "10"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)), '99') "11"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)), '99') "12"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)), '99') "13"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)), '99') "14"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)), '99') "15"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)), '99') "16"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)), '99') "17"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)), '99') "18"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)), '99') "19"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)), '99') "20"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)), '99') "21"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)), '99') "22"
      , TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)), '99') "23"
   FROM v$log_history
GROUP BY TO_CHAR(first_time, 'YYYY-MON-DD') ; 
根據經驗值，redo log的在高峰期的切換不應超過每小時10次，redo log 檔案組為5組，每個日志檔案組的日志位于不同的檔案系統或者儲存設備上。具體redo log大小和檔案組數需要根據實際情況确定。
查詢現有redo log大小,日志狀态，日志檔案組和每組日志檔案的成員數。
SELECT
        group#
      , thread#
      , bytes / 1024 / 1024 mb
      , members
      , status
   FROM v$log;
 
可以看到共有個日志檔案組9個日志檔案組，每組1個日志檔案，第4組為正在使用的日志檔案組，如果status為active說明該日志檔案需要用于執行個體恢複。應進行歸檔後才能将其删除。
redo日志調整
計算redo日志大小
30(最多每小時切換次數) * 5000MB / 10 = 15000MB
根據上面計算值，理論上應将日志檔案組大小擴大至15000MB，考慮到ODS資料庫DML操作時間段分布不均的特點，過大的redo日志設定會使資料庫在丢失目前日志檔案組進行資料庫恢複的時候丢失較多資料。是以，綜合考慮将日志檔案擴大至10GB，這樣也保證了系統在不繁忙的時候可以進行redo日志的歸檔和切換。具體操作腳本如下，請根據實際情況調整日志檔案路徑和日志檔案大小。
 -- 注意，在删除所有歸檔日志之前，請保留3個日志檔案組已確定系統穩定，待修改完日志檔案組後再将其删除。

sqlplus / as sysdba
host mkdir -p $ORACLE_BASE/standby/redo
host mkdir -p /mnt/EMC1/redo/

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database add logfile group 1 ('/mnt/EMC1/redo/group1redo1.log', '/opt/app/oracle/standby/redo/group1redo2.log') size 10G;
alter database add logfile group 2 ('/mnt/EMC1/redo/group2redo1.log', '/opt/app/oracle/standby/redo/group2redo2.log') size 10G;
alter database add logfile group 3 ('/mnt/EMC1/redo/group3redo1.log', '/opt/app/oracle/standby/redo/group3redo2.log') size 10G;

-- 切換目前日志檔案組
alter system archive log current;
alter system archive log current;
alter system checkpoint;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database add logfile group 4 ('/mnt/EMC1/redo/group4redo1.log', '/opt/app/oracle/standby/redo/group4redo2.log') size 10G;
alter database add logfile group 5 ('/mnt/EMC1/redo/group5redo1.log', '/opt/app/oracle/standby/redo/group5redo2.log') size 10G;

-- 确認日志檔案組目前狀态
select * from v$logfile order by member;
select * from v$log;

-- 删除作業系統上無用的日志檔案組
rm -f /mnt/EMC1/redo1.log
rm -f /mnt/EMC1/redo10.log
rm -f /mnt/EMC1/redo11.log
rm -f /mnt/EMC1/redo12.log
rm -f /mnt/EMC1/redo2.log
rm -f /mnt/EMC1/redo3.log
rm -f /mnt/EMC1/redo4.log
rm -f /mnt/EMC1/redo5.log
rm -f /mnt/EMC1/redo6.log
rm -f /mnt/EMC1/redo7.log
rm -f /mnt/EMC1/redo8.log
rm -f /mnt/EMC1/redo9.log      

-- 注意，在删除所有歸檔日志之前，請保留3個日志檔案組已確定系統穩定，待修改完日志檔案組後再将其删除。

調整FAST_START_MTTR_TARGET

參數FAST_START_MTTR_TARGET參數是一個加快執行個體恢複的參數，我們可以根據服務界别來定義一個合理的、可接受的值。該值得機關為秒。比如設定為60S，假定該值處于合理的情況之下，則一旦執行個體崩潰，在60S以内執行個體應當能夠被恢複。合理即該值不能太大，也不能太小。太大則執行個體恢複所需的時間較長，太小則導緻大量資料的及時寫入，增加了系統的I/O。

影響執行個體恢複時間長短的主要因素是從最近檢查點位置到聯機重做日志尾部之間的距離。距離越長則所需要的cache recovery和undo、redo的時間越長。是以如何有效的縮短最近檢查點位置與聯機重做日志尾部之間的距離，正是FAST_START_MTTR_TARGET的目的。

FAST_START_MTTR_TARGET的值實際上是通過觸發檢查點來實作它的目的的。當記憶體中産生的dirty buffer所需的恢複時間（estimated_mttr）到達FAST_START_MTTR_TARGET所指定的時間，則檢查點程序被觸發。檢查點程序一旦被觸發，将通過DBWn程序按檢查點隊列順序将髒資料寫入到資料檔案，進而縮短了最後檢查點位置與聯機重做日志間的距離，減少了執行個體恢複所需的時間。

alter system set fast_start_mttr_target=30;

相關文檔可以參閱:Can Not Allocate Log (文檔 ID 1265962.1)