Oracle管理

"Checkpoint not complete",优化重做日志文件

时间:2014-10-15 20:31:49  作者:solgle  来源:www.solgle.com  查看:899  评论:0
内容摘要:alert log报"Checkpoint not complete",重做日志文件的优化日志文件会影响DBWR和检查点的行为模式;检查点受两方面影响:参数fast_start_mttr_target和日志文件大小alter日志部分内容:
alert log报"Checkpoint not complete",重做日志文件的优化
 
日志文件会影响DBWR和检查点的行为模式;
检查点受两方面影响:参数fast_start_mttr_target和日志文件大小
本文出自:http://www.solgle.com/news/?293.html
顺利的修改步骤参考:http://www.solgle.com/news/?129.html
 
alter日志部分内容:
<msg time='2014-10-04T10:09:08.075+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='RAC1' host_addr='192.168.100.10' module=''
 pid='9844'>
 <txt>Thread 1 cannot allocate new log, sequence 8379
 </txt>
</msg>
<msg time='2014-10-04T10:09:08.076+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='RAC1' host_addr='192.168.100.10' module=''
 pid='9844'>
 <txt>Checkpoint not complete
 </txt>
</msg>
<msg time='2014-10-04T10:09:08.076+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='RAC1' host_addr='192.168.100.10' module=''
 pid='9844'>
 <txt>  Current log# 2 seq# 8378 mem# 0: +ASMDISK/solgle_db/onlinelog/group_2.264.856529245
 </txt>
</msg>
... ...
<msg time='2014-10-04T10:14:49.990+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='RAC1' host_addr='192.168.100.10' module=''
 pid='9844'>
 <txt>Thread 1 advanced to log sequence 8381 (LGWR switch)
 </txt>
</msg>
<msg time='2014-10-04T10:16:57.377+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='RAC1' host_addr='192.168.100.10' module=''
 pid='9844'>
 <txt>Thread 1 advanced to log sequence 8382 (LGWR switch)
 </txt>
</msg>
----现在每个实例拥有两组日志文件,这样肯定日志切换频繁,且alert日志里面出现“checkpoint not complete”;
 
 
--当前值查看:
C:\Users\user>sqlplus sys/********@solgle_db as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 10月 20 10:57:26 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 95
SQL> show parameter fast_start_mttr_target
 
NAME
------------------------------------
TYPE                                                             VALUE
---------------------------------------------------------------- ---------------
fast_start_mttr_target
integer                                                          0
SQL>
---当前fast_start_mttr_target未设置自定义值,是采用的系统默认值
 
---该语句也可以查看
SQL> select estimated_mttr fast_start_mttr_target,optimal_logfile_size log_size from v$instance_recovery;
 
FAST_START_MTTR_TARGET   LOG_SIZE
---------------------- ----------
                     0
SQL>
---上面的语句未能正确显示日志文件大小,通过下面的方式直接计算
SQL> col space_num format a10
SQL> select group#,bytes/1024/1024 ||'m' space_num ,status from v$log;
    GROUP# SPACE_NUM  STATUS
---------- ---------- ----------------
         1 50m        INACTIVE
         2 50m        CURRENT
         3 50m        INACTIVE
         4 50m        CURRENT
 
---50M偏小,下面修改日志文件的大小
 
---查看当前日志文件的位置
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------
+DATA1/solgle_db/onlinelog/group_2.262.855683027
+DATA1/solgle_db/onlinelog/group_1.261.855683021
+DATA1/solgle_db/onlinelog/group_3.265.855683483
+DATA1/solgle_db/onlinelog/group_4.266.855683487
 
 
SQL> alter system switch logfile;
System altered.
 
SQL> select group#,bytes/1024/1024 ||'m' space_num ,status from v$log;
    GROUP# SPACE_NUM  STATUS
---------- ---------- ----------------
         1 50m        INACTIVE
         2 50m        CURRENT
         3 50m        CURRENT
         4 50m        ACTIVE
         
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01567: dropping log 1 would leave less than 2 log files for instance
solgle_db1 (thread 1)
ORA-00312: online log 1 thread 1:
'+DATA1/solgle_db/onlinelog/group_1.261.855683021'
---尝试删除 group 1后再添加 group 1,但是失败
 
---那就先添加
SQL> alter database add logfile  group 5 '+DATA1' size 200m;
Database altered.
SQL> alter database add logfile  group 6 '+DATA1' size 200m;
Database altered.
SQL> alter database add logfile  group 7 '+DATA1' size 200m;
Database altered.
SQL> alter database add logfile  group 8 '+DATA1' size 200m;
Database altered.
 
SQL> select group#,bytes/1024/1024 ||'m' space_num ,status from v$log;
 
    GROUP# SPACE_NUM  STATUS
---------- ---------- ----------------
         1 50m        INACTIVE
         2 50m        CURRENT
         3 50m        CURRENT
         4 50m        INACTIVE
         5 200m       UNUSED
         6 100m       UNUSED
         7 200m       UNUSED
         8 200m       UNUSED
 
8 rows selected.
SQL>
SQL> alter system switch logfile;
System altered.
... ...
SQL> alter system switch logfile;
System altered.
 
 
SQL> select group#,bytes/1024/1024 ||'m' space_num ,status from v$log;
 
    GROUP# SPACE_NUM  STATUS
---------- ---------- ----------------
         1 50m        UNUSED
         2 50m        CURRENT
         3 50m        ACTIVE
         4 50m        INACTIVE
         5 200m       ACTIVE
         6 100m       ACTIVE
         7 200m       CURRENT
         8 200m       UNUSED
 
8 rows selected.
SQL>
 
----日志文件状态为INACTIVE时,即可以删除
SQL> set pagesize 200
SQL> select group#,thread#,bytes/1024/1024 ||'m' space_num ,status from gv$log o
rder by group#;
    GROUP#    THREAD# SPACE_NUM  STATUS
---------- ---------- ---------- ----------------
         1          1 50m        CURRENT
         1          1 50m        CURRENT
         2          1 50m        INACTIVE
         2          1 50m        INACTIVE
         4          2 50m        INACTIVE
         4          2 50m        INACTIVE
         5          2 200m       CURRENT
         5          2 200m       CURRENT
         6          2 100m       INACTIVE
         6          2 100m       INACTIVE
         7          2 200m       INACTIVE
         7          2 200m       INACTIVE
         8          2 200m       INACTIVE
         8          2 200m       INACTIVE
14 rows selected.
 
---上面的操作忽略了thread,当前是rac环境,会话在服务器rac2上面,(猜测可能是因为thread#为1所以删除失败)
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance
solgle_db1 (thread 1)
ORA-00312: online log 2 thread 1:
'+DATA1/solgle_db/onlinelog/group_2.262.855683027'
 
SQL> alter database disable thread 1;
alter database disable thread 1
*
ERROR at line 1:
ORA-01615: instance solgle_db1 (thread 1) is mounted - cannot disable
 
SQL>
---现在删除thead#为2的,因为在rac2上面,所有删除成功
SQL> alter database drop logfile group 4;
Database altered.
SQL>
 
---从这句SQL分析我们可以查出,上面的操作有误,因为在多次switch logfile时,我们发现
thread#为1,即rac1(实例1)并没有使用后面的group#为5,6,7,8的组,明显日志对应关系没有建正确,
查证了其它在用rac环境,确实是这样的:可以理解为日志文件是各个实例私有的,并不是rac公用的;
 
----下面进行调整
 
----从该查询结果可以看出,日志文件只添加了默认的thread为2的,所以rac1无法用到新日志租
SQL> select group#,thread#,bytes/1024/1024 ||'m' space_num ,status from gv$log order by group# asc;
 
    GROUP#    THREAD# SPACE_NUM  STATUS
---------- ---------- ---------- ----------------
         1          1 50m        INACTIVE
         1          1 50m        INACTIVE
         2          1 50m        CURRENT
         2          1 50m        CURRENT
         5          2 200m       UNUSED
         5          2 200m       UNUSED
         6          2 200m       UNUSED
         6          2 200m       UNUSED
         7          2 200m       INACTIVE
         7          2 200m       INACTIVE
         8          2 200m       CURRENT
         8          2 200m       CURRENT
16 rows selected.
SQL>
 
----正确修改方式如下:
---添加日志组,并限定thread,因已经成功删除了组3和4,所以现在直接重建;
SQL> alter database add logfile thread 1 group 3 size 200m;
Database altered.
 
SQL> alter database add logfile thread 1 group 4 size 200m;
Database altered.
SQL>
 
----多次切换了日志,让group组1的状态为INACTIVE
SQL> alter system archive log current;
System altered.
 
SQL> select group#,thread#,bytes/1024/1024 ||'m' space_num ,status from gv$log order by group#;
 
    GROUP#    THREAD# SPACE_NUM  STATUS
---------- ---------- ---------- ----------------
         1          1 50m        INACTIVE
         1          1 50m        INACTIVE
         2          1 50m        ACTIVE
         2          1 50m        ACTIVE
         3          1 200m       CURRENT
         3          1 200m       CURRENT
         4          1 200m       UNUSED
         4          1 200m       UNUSED
         5          2 200m       CURRENT
         5          2 200m       CURRENT
         6          2 200m       UNUSED
         6          2 200m       UNUSED
         7          2 200m       INACTIVE
         7          2 200m       INACTIVE
         8          2 200m       ACTIVE
         8          2 200m       ACTIVE
16 rows selected.
 
SQL> alter database drop logfile group 1;
Database altered.
 
----N次切换日志,让group组2的状态为INACTIVE
SQL> alter system archive log current;
System altered.
 
SQL> select group#,thread#,bytes/1024/1024 ||'m' space_num ,status from gv$log order by group#;
    GROUP#    THREAD# SPACE_NUM  STATUS
---------- ---------- ---------- ----------------
         2          1 50m        INACTIVE
         2          1 50m        INACTIVE
         3          1 200m       CURRENT
         3          1 200m       CURRENT
         4          1 200m       INACTIVE
         4          1 200m       INACTIVE
         5          2 200m       INACTIVE
         5          2 200m       INACTIVE
         6          2 200m       INACTIVE
         6          2 200m       INACTIVE
         7          2 200m       INACTIVE
         7          2 200m       INACTIVE
         8          2 200m       CURRENT
         8          2 200m       CURRENT
         
14 rows selected.
 
SQL> alter database drop logfile group 2;
Database altered.
 
----添加日志文件组
SQL> alter database add logfile thread 1 group 1 size 200m;
Database altered.
 
SQL> alter database add logfile thread 1 group 2 size 200m;
Database altered.
 
---现在日志文件修改成功
SQL> select group#,thread#,bytes/1024/1024 ||'m' space_num ,status from gv$log o
rder by group#;
 
    GROUP#    THREAD# SPACE_NUM  STATUS
---------- ---------- ---------- ----------------
         1          1 200m       UNUSED
         1          1 200m       UNUSED
         2          1 200m       UNUSED
         2          1 200m       UNUSED
         3          1 200m       CURRENT
         3          1 200m       CURRENT
         4          1 200m       INACTIVE
         4          1 200m       INACTIVE
         5          2 200m       INACTIVE
         5          2 200m       INACTIVE
         6          2 200m       INACTIVE
         6          2 200m       INACTIVE
         7          2 200m       INACTIVE
         7          2 200m       INACTIVE
         8          2 200m       CURRENT
         8          2 200m       CURRENT
 
16 rows selected.
 
SQL>
 
标签:修改重做日志文件 ORA-01567 ORA-00312 ORA-01615 Checkpoint not complete 

solgle.com 版权所有,欢迎分享!!!

相关评论
   Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1