oracle log file switch (checkpoint incomplete) 解决
来源:互联网 发布:畑亚贵 知乎 编辑:程序博客网 时间:2024/05/16 11:19
问题描述: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$logfile; 为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) 解决
- Log file switch (checkpoint incomplete)
- redo log 在线扩容 解决:log file switch (checkpoint incomplete)
- 等待事件:log file switch (checkpoint incomplete)
- log file switch (checkpoint incomplete)等待事件
- Log file switch(checkpoint incomplete)等待事件
- log file switch completion,log file switch(checkpoint incomplete),log file switch(archiving needed)
- log file switch completion,log file switch(checkpoint incomplete),log file switch(archiving needed)
- log file switch (checkpoint incomplete)的问题定位
- log buffer —— log file switch (archiving needed)、log file switch (checkpoint incomplete)
- Oracle wait event --- log file switch
- log file switch and log file sync
- log buffer space、log file switch、log file sync
- log buffer —— log file switch completion
- oracle checkpoint
- SCN、checkpoint、redo log
- oracle基础:redo log file
- Clean Oracle related log file
- 区分Activity的四种加载模式
- ./buile_native.sh出现please define NDK_ROOT解决方法
- 为VS2005工程添加新的SDK
- ASP、JSP、PHP 三种技术比较
- window.onload与document.ready的区别
- oracle log file switch (checkpoint incomplete) 解决
- Android的onCreateOptionsMenu()创建菜单Menu详解
- \n \r 理解
- TMS320F28335 ADC模块的学习
- java Mail之html格式邮件
- 最大流问题
- Android中的一个TextView中的字体设置不同大小
- win7 64位安装informix客户端
- NoSQL开篇——为什么要使用NoSQL