oracle log file switch (checkpoint incomplete) 解决

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle log file switch (checkpoint incomplete) 解决，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3612字，纯文字阅读大概需要6分钟。

内容图文

问题描述：oracle log file switch (checkpoint incomplete)，Every 15 min, tpm will drop to almost zero. cpu负载不高，很低。

1 set the parameter :

ALTER SYSTEM SET log_checkpoint_interval=20000 SCOPE=BOTH; 
#没有解决掉

2 生成@?/rdbms/admin/awrrpt.sql 报告

-- see SQL ordered by Elapsed Time
-- SQL Statistics
-- see the cpu
select ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))
"IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
group by ash.sql_id
order by sum(decode(ash.session_state,'ON CPU',1,1)) desc;

拿到执行的SQL,baidu之，google之，发现很多人都说redo需要加大。

3 see redo logs

select * from v$log; select * from v$log;select?fromvlogfile; 为300M，大家推荐的为1G以下，还有调整的空间。

4 add new redo logs 添加新的redo log组

alter database add logfile thread 1  group 6 ('/opt/app/product/11.2/oradata/novadb/redo16.log') size 600M;
alter database add logfile thread 1  group 7 ('/opt/app/product/11.2/oradata/novadb/redo17.log') size 600M;
alter database add logfile thread 1  group 8 ('/opt/app/product/11.2/oradata/novadb/redo18.log') size 600M;
alter database add logfile thread 1  group 9 ('/opt/app/product/11.2/oradata/novadb/redo19.log') size 600M;
alter database add logfile thread 1  group 10 ('/opt/app/product/11.2/oradata/novadb/redo20.log') size 600M;

5 see the new logs

5.1 see the status

SQL> select status,GROUP# from v$log;
STATUS                                               GROUP#
------------------------------------------------ ----------
ACTIVE                                                    1
ACTIVE                                                    2
ACTIVE                                                    3
CURRENT                                                   4
ACTIVE                                                    5
ACTIVE                                                    6
ACTIVE                                                    7
ACTIVE                                                    8
ACTIVE                                                    9
ACTIVE                                                   10
10 rows selected.

5.2 see the log file directory

SQL> select member,GROUP#  from v$logfile;
MEMBER
--------------------------------------------------------------------------------
    GROUP#
----------
/opt/app/product/11.2/oradata/novadb/redo06.log
         1
/opt/app/product/11.2/oradata/novadb/redo01.log
         2
/opt/app/product/11.2/oradata/novadb/redo03.log
         3

MEMBER
--------------------------------------------------------------------------------
    GROUP#
----------
/opt/app/product/11.2/oradata/novadb/redo16.log
         6
/opt/app/product/11.2/oradata/novadb/redo17.log
         7
/opt/app/product/11.2/oradata/novadb/redo18.log
         8

MEMBER
--------------------------------------------------------------------------------
    GROUP#
----------
/opt/app/product/11.2/oradata/novadb/redo19.log
         9
/opt/app/product/11.2/oradata/novadb/redo20.log
        10
/opt/app/product/11.2/oradata/novadb/redo04.log
         4

MEMBER
--------------------------------------------------------------------------------
    GROUP#
----------
/opt/app/product/11.2/oradata/novadb/redo05.log
         5

10 rows selected.

6 oracle can continue to use the old redo logs .

OK,每隔15分钟，oracle并发800/秒的时候，应用卡住的现象没有了。

7，下午又报同样的问题了，正在解决中

内容总结

以上是互联网集市为您收集整理的oracle log file switch (checkpoint incomplete) 解决全部内容，希望文章能够帮你解决oracle log file switch (checkpoint incomplete) 解决所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---