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)

 

本文原创，转载请注明出处，作者

如有错误，欢迎指正

邮箱:[e-mail]