Checkpoint not complete造成oracle故障报告及解决方案

WARNING: inbound connection timed out (ORA-3136)
Mon Jan  6 09:24:22 2014
WARNING: inbound connection timed out (ORA-3136)
Mon Jan  6 09:24:22 2014
WARNING: inbound connection timed out (ORA-3136)
Mon Jan  6 09:28:54 2014
Thread 1 cannot allocate new log, sequence 222017
Checkpoint not complete
  Current log# 6 seq# 222016 mem# 0: /u02/oradata/vids/redo06_1.log

  Current log# 6 seq# 222016 mem# 1: /u02/oradata/vids/redo06_2.log

产生此问题的原因分析：

CKPT这个后台进程的就是做checkpoint这件事，checkpoint被触发的条件之一是就发生redo log switch，Checkpoint的具体工作包括： 

• 触发DBWn向磁盘写入Dirty data。 
  
  
• 把checkpoint信息更新到datafile header上。

• 把checkpoint信息更新到control file里。 

Checkpoint做的事情之一是触发DBWn把buffer cache中的Dirty cache磁盘。另外就是把最近的系统的SCN更新到datafile header和control file（每一个事务都有一个SCN），做第一件事的目的是为了减少由于系统突然宕机而需要的恢复时间，做第二件事实为了保证数据库的一致性。 
　 　而redo log switch就是触发checkpoint的主要的事件(event) ，当第一组redo log被用完之后，Oracle就要停止使用当前的redo log，转而使用另一组redo log，这就叫做log switch。而log switch触发checkpoint。 
　　Oracle要求的最少的redo group 的是2个，但我们一般都建议配置3个或3个以上redo log group。假设我们只有两个redo log group：group 1和group 2，并且系统中总是有大量的dirty block需要写入datafile，当我们从group 1 switch to group 2的时候，会触发checkpoint, checkpoint要求DBWn把buffer cache中的dirty block写入datafile，然而，当我们再次用完group 2里面的空间，需要再次switch to group 1并重用group 1的时候，如果我们发现redo log group 1所保护的那些dirty block还没有完全写入到datafile，整个数据库必须等待DBWn把所有的dirty block写入到datafile之后才能做其他的事情，这就是我们遇到的"checkpoint not complete"问题。这个问题往往暗示了redo log的配置有问题，就本例而言，要么是redo log太小，要么是像我们这里的redo log group太少，只有2个。而这个问题的解决方案就是加大redo log或添加更多redo log group，不管哪一种解决方案，我们的目的都是给DBWn争取更多的时间。

问题的解决办法：

此系统当前配置6组logfile，每组有两个成员，大小为50M，建议将log更改为200－500M的大小。

经查询（如下）每天日志switch数量大致在100－120之间，高峰时超出500多（此处必须得考虑业务突然繁忙的时刻，从中我们可以看出，1月12日switch数量是505，刚好是宕机的那一天），需要优化：

                       Number

                           of

Date                 Switches Redo Size

-------------------- -------- ---------

23-NOV-06                  44     2,200

24-NOV-06                 111     5,550

25-NOV-06                 112     5,600

26-NOV-06                 110     5,500

27-NOV-06                 109     5,450

28-NOV-06                 114     5,700

29-NOV-06                 124     6,200

30-NOV-06                 107     5,350

01-DEC-06                 113     5,650

02-DEC-06                 103     5,150

03-DEC-06                 103     5,150

04-DEC-06                 104     5,200

05-DEC-06                 103     5,150

06-DEC-06                 105     5,250

07-DEC-06                 111     5,550

08-DEC-06                 108     5,400

09-DEC-06                 110     5,500

10-DEC-06                 107     5,350

11-DEC-06                 108     5,400

12-DEC-06                 113     5,650

13-DEC-06                 107     5,350

14-DEC-06                 118     5,900

15-DEC-06                 109     5,450

16-DEC-06                 111     5,550

17-DEC-06                 112     5,600

18-DEC-06                 106     5,300

19-DEC-06                 108     5,400

20-DEC-06                 105     5,250

21-DEC-06                 116     5,800

22-DEC-06                 122     6,100

23-DEC-06                 110     5,500

24-DEC-06                 122     6,100

25-DEC-06                 125     6,250

26-DEC-06                 117     5,850

27-DEC-06                 110     5,500

28-DEC-06                 114     5,700

29-DEC-06                 112     5,600

30-DEC-06                 115     5,750

31-DEC-06                 128     6,400

01-JAN-07                 127     6,350

02-JAN-07                 104     5,200

03-JAN-07                 101     5,050

04-JAN-07                 111     5,550

05-JAN-07                 122     6,100

06-JAN-07                 114     5,700

07-JAN-07                 107     5,350

08-JAN-07                 110     5,500

09-JAN-07                 110     5,500

10-JAN-07                  82     4,100

11-JAN-07                 180     9,000

12-JAN-07                 505    25,250

13-JAN-07                 506    25,300

14-JAN-07                 444    22,200

15-JAN-07                 108     5,400

16-JAN-07                 120     6,000

17-JAN-07                 115     5,750

18-JAN-07                 128     6,400

19-JAN-07                 124     6,200

20-JAN-07                 118     5,900

21-JAN-07                 123     6,150

22-JAN-07                 117     5,850

23-JAN-07                 119     5,950

24-JAN-07                 113     5,650

25-JAN-07                 113     5,650

26-JAN-07                 115     5,750

27-JAN-07                 127     6,350

28-JAN-07                 111     5,550

29-JAN-07                 112     5,600

30-JAN-07                  76     3,800

                     -------- ---------

sum                      8848   442,400

就本机而言，建议只更改redo log的大小即可，更改大小为200M。

当前log信息如下：

SQL> select * from v$log;                                                 

                                                                          

GROUP#   THREAD#  SEQUENCE#     BYTES    MEMBERS  ARC  STATUS         

---------- 　　----------　 ---------- 　　　　---------- 　　　----------  --- -----  -----------

     1          1     190547   　　　52428800          2    YES  INACTIVE       

     2          1     190548   　　　52428800          2    YES  INACTIVE        

     3          1     190549   　　　52428800          2    YES  INACTIVE       

     4          1     190546   　　　52428800          2    YES  INACTIVE       

     5          1     190550   　　　52428800          2    NO   CURRENT        

     6          1     190545   　　　52428800          2    YES  INACTIVE       

        

6 rows selected.

SQL> select member from v$logfile;      

                                        

MEMBER                                  

-----------------------------------------

/opt/oracle/db02/oradata/ORCL/

/opt/oracle/db03/oradata/ORCL/

/opt/oracle/db02/oradata/ORCL/

/opt/oracle/db03/oradata/ORCL/

/opt/oracle/db02/oradata/ORCL/

/opt/oracle/db03/oradata/ORCL/

/opt/oracle/db02/oradata/ORCL/

/opt/oracle/db03/oradata/ORCL/

/opt/oracle/db02/oradata/ORCL/

/opt/oracle/db03/oradata/ORCL/

/opt/oracle/db02/oradata/ORCL/

/opt/oracle/db03/oradata/ORCL/

12 rows selected.

此oracle的LOG大小为50M，共6组，每组有两个成员，分别位于/opt/oracle/db02和/opt/oracle/db03目录。

查询空间利用情况：

df -k查询这两个目录的空间利用情况：

所处盘区        kbytes            已使用　　　　剩余空间大小    已使用百分比   

/opt/oracle/db02   51200000 　　　　12900020 　　　35906297    　　　　27%   

/opt/oracle/db03　 2048000          343661         1597823             18%

建议更改大小之后的redo log及其member仍然放在原来所处盘区/opt/oracle/db02、/opt/oracle/db03，/opt/oracle/db02剩 余空间足够大，不必担心，而/opt/oracle/db03则至少需200M*6＝，也不必担心，至少原来member所占用空间还可以重新利 用！

• 1、 查询如下语句：

select GROUP#   THREAD#  SEQUENCE#     BYTES    MEMBERS  ARC  STATUS         

---------- 　　----------　 ---------- 　　　　---------- 　　　----------  --- -----  -----------

     1          1     190547   　　　52428800          2    YES  INACTIVE       

     2          1     190548   　　　52428800          2    NO   CURRENT       

     3          1     190549   　　　52428800          2    YES  INACTIVE       

     4          1     190546   　　　52428800          2    YES  INACTIVE       

     5          1     190550   　　　52428800          2    YES  INACTIVE        

     6          1     190545   　　　52428800          2    YES  INACTIVE       

• 2、 建议先查询并将用alter system switch logfile手工切换日志组，例如CURRENT在第二组，这样我可以先删除第六组，再重新创建第六组日志，大小为200M，这样做的原因是日志是按照 顺序来使用的，而第一组日志归档时是需要几秒钟的，很快。

SQL> alter database drop logfile group 6;

手动删除第六组日志及成员文件：

SQL>! rm /opt/oracle/db02/oradata/ORCL/

SQL>! rm /opt/oracle/db03/oradata/ORCL/

接下来重新创建第六组，这里要注意，大小不是原来的50M，而是200M：

SQL> alter database add logfile group 6 '/opt/oracle/db02/oradata/ORCL/' size 200m;

SQL> alter database add logfile member '/opt/oracle/db03/oradata/ORCL/' to group 6;

• 3、 接下来将CURRENT切换至第三组，并删除第一组：

SQL> alter database drop logfile group 1;

手动删除第一组日志及成员文件：

SQL>! rm /opt/oracle/db02/oradata/ORCL/

SQL>! rm /opt/oracle/db03/oradata/ORCL/

接下来重新创建第一组，大小为200M：

SQL> alter database add logfile group 1 '/opt/oracle/db02/oradata/ORCL/' size 200m;

SQL> alter database add logfile member '/opt/oracle/db03/oradata/ORCL/' to group 6;

4、接下来将CURRENT切换至第四组，并删除第二组，方法跟上面两个一样，直至将所有的日志组及成员全部更改为200M大小。

• 5、 做完后再查询如下语句进行确认无误

SQL> select * from v$log;

SQL> select member from v$logfile;

注：这些步骤可以在线做，没有必要担心数据库出现问题，但建议避免业务高峰期，并且此问题必须尽快解决为好！

内容：

每天日志switch数量的查询方法：select to_char(first_time,'yyyy-mm-dd') day1,count(*)  from v$log_history  where first_time>=to_date('2014-01-03','yyyy-mm-dd') group by to_char(first_time,'yyyy-mm-dd')