数据库系统环境:
操作系统: Microsoft Windows x86 64-bit
数据库版本:oracle 11.2.0.1.0 单实例
系统出现故障,CPU使用率飙升到100%
看alert日志发现是redo的检查点没有完成,导致数据库实例出了故障
大小不够
建议增加redo日志大小到500m,保持redo日志组数量不变
以下是增加redo日志大小的操作:
select group#, bytes, status from v$log;
select group#, member from v$logfile;
创建新的redolog group 4, 5, and 6 每个log大小为512m
alter database add logfile group 4 'E:\app\Administrator\oradata\orcl\redo04.log' size 512M;
alter database add logfile group 5 'E:\app\Administrator\oradata\orcl\redo05.log' size 512M;
alter database add logfile group 6 'E:\app\Administrator\oradata\orcl\redo06.log' size 512M;
创建完新的日志组后,查询所有日志组状态:
select group#, status from v$log;
当前使用的是group 2 日志组,现在通过“多次”切日志换命令将group 1,2,3状态变为INACTIVE,
让oracle使用新建的group4,5,6,然后将小的日志组group 1,2,3删除
alter system switch logfile;
alter system checkpoint;
切换完成后,再次查询当前的日志组状态,当前使用的日志组是group 5
select group#, status from v$log;
当redolog group 1,2,3状态为active时,意味着不能删除当前redo,直至用"alter system switch log file" ,"alter system checkpoint"
命令将其切换为“inactive”状态才能将其删除
删除group 1,2,3日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
select group#, bytes, status from v$log;
在操作系统层面删除原来的redolog
查看redo日志切换频率:
select sequence#,first_time,nexttime,round(((first_time-nexttime)*24)*60,2) diff from (select sequence#,first_time,lag(first_time) over(order by sequence#) nexttime from v$log_history where thread#=1 and to_char(first_time,'yyyy-mm-dd')='2016-04-13') order by sequence# desc;
详细操作步骤:
1、查看redo位置信息
2、查看redo大小信息
3、增加3组redo,每个大小设置为512M
4、查看redo的各组状态
5、查看redo的增加状态
6、切换redo日志组
7、删除redo的1、2、3组,保留新增的4、5、6组
完成redo日志组大小增加
操作系统: Microsoft Windows x86 64-bit
数据库版本:oracle 11.2.0.1.0 单实例
系统出现故障,CPU使用率飙升到100%
看alert日志发现是redo的检查点没有完成,导致数据库实例出了故障
![](http://img.blog.itpub.net/blog/attachment/201604/13/22996654_146053899056Up.png?x-oss-process=style/bb)
AWR报告:显示CPU消耗很高,而且log file sync单次等待达到10毫秒,表示IO可能有延迟
redo日志切换过快
![](http://img.blog.itpub.net/blog/attachment/201604/13/22996654_1460539240aCvJ.png?x-oss-process=style/bb)
redo日志切换过快
![](http://img.blog.itpub.net/blog/attachment/201604/13/22996654_14605394636M5S.png?x-oss-process=style/bb)
大小不够
![](http://img.blog.itpub.net/blog/attachment/201604/13/22996654_1460539555fg7i.png?x-oss-process=style/bb)
建议增加redo日志大小到500m,保持redo日志组数量不变
以下是增加redo日志大小的操作:
select group#, bytes, status from v$log;
select group#, member from v$logfile;
创建新的redolog group 4, 5, and 6 每个log大小为512m
alter database add logfile group 4 'E:\app\Administrator\oradata\orcl\redo04.log' size 512M;
alter database add logfile group 5 'E:\app\Administrator\oradata\orcl\redo05.log' size 512M;
alter database add logfile group 6 'E:\app\Administrator\oradata\orcl\redo06.log' size 512M;
创建完新的日志组后,查询所有日志组状态:
select group#, status from v$log;
当前使用的是group 2 日志组,现在通过“多次”切日志换命令将group 1,2,3状态变为INACTIVE,
让oracle使用新建的group4,5,6,然后将小的日志组group 1,2,3删除
alter system switch logfile;
alter system checkpoint;
切换完成后,再次查询当前的日志组状态,当前使用的日志组是group 5
select group#, status from v$log;
当redolog group 1,2,3状态为active时,意味着不能删除当前redo,直至用"alter system switch log file" ,"alter system checkpoint"
命令将其切换为“inactive”状态才能将其删除
删除group 1,2,3日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
select group#, bytes, status from v$log;
在操作系统层面删除原来的redolog
查看redo日志切换频率:
select sequence#,first_time,nexttime,round(((first_time-nexttime)*24)*60,2) diff from (select sequence#,first_time,lag(first_time) over(order by sequence#) nexttime from v$log_history where thread#=1 and to_char(first_time,'yyyy-mm-dd')='2016-04-13') order by sequence# desc;
详细操作步骤:
1、查看redo位置信息
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_14605968625my4.png?x-oss-process=style/bb)
2、查看redo大小信息
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_1460596978J4uO.png?x-oss-process=style/bb)
3、增加3组redo,每个大小设置为512M
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_1460597070r7pR.png?x-oss-process=style/bb)
4、查看redo的各组状态
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_1460597580jgj5.png?x-oss-process=style/bb)
5、查看redo的增加状态
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_14605980948OVI.png?x-oss-process=style/bb)
6、切换redo日志组
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_1460598220RacT.png?x-oss-process=style/bb)
7、删除redo的1、2、3组,保留新增的4、5、6组
![](http://img.blog.itpub.net/blog/attachment/201604/14/22996654_1460598334mKvM.png?x-oss-process=style/bb)
完成redo日志组大小增加
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2080840/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22996654/viewspace-2080840/