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

加入收藏设为首页

Oracle管理: "Checkpoint not complete",优化重做日志文件
时间：2014/10/15 20:31:49 作者：solgle 来源：www.solgle.com 查看：1331 评论：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 版权所有，欢迎分享！！！

上一篇：修改Data Guard主库备份库为异步io
下一篇：Linux下安装Oracle 11g服务器

相关文章

相关评论

本类更新

本类推荐

本类排行

: 国家数据 | 中证指数 | Oracle官方文档 | 沪深行情 | 流量统计 | 深交所 | 上交所 | 中国证券登记 | 同业拆放利率 | 巨潮资讯 | 指数估值 | 看财报 |

联系我们 - 关于我们 - 网站同盟