笔者在有一次工作的时候,发现一线的数据库环境非常慢,做了一份awr日志,发现redo日志切换太频繁,导致log file switch等待事件。
数据库环境为oracle 11gr1 ,服务器为linux suse 11, 内存大小24G,cpu个数:12个
大家注意看awr报告中显示redo size每秒3MB,
top5中显示日志切换频繁导致了log file switch等待事件
查询数据库中的redo大小:
1 2 3 4 5 6 |
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 49 52428800 512 1 NO CURRENT 1322694 21-JUN-13 2.8147E+14 2 1 47 52428800 512 1 NO INACTIVE 1217499 14-JUN-13 1267733 17-JUN-13 3 1 48 52428800 512 1 NO INACTIVE 1267733 17-JUN-13 1322694 21-JUN-13 |
我们可以看到如上的redo共3组,每组大小才50M,按照每秒3M的写入,那一组redo写满只需要15秒左右,然后切换下一组redo,这样redo状态来不及更新。就会导致log file switch等待事件。
1 2 3 4 5 6 |
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------- --- 3 ONLINE /opt/oracle/oradata/orcl/redo03.log NO 2 ONLINE /opt/oracle/oradata/orcl/redo02.log NO 1 ONLINE /opt/oracle/oradata/orcl/redo01.log NO |
增加新的日志文件组,ps:我这里大小就只创建50m了,这里按照实际情况设置
SQL> alter database add logfile group 4 ‘/opt/oracle/oradata/orcl/redo04.log’ size 50m; Database altered. SQL> alter database add logfile group 5 ‘/opt/oracle/oradata/orcl/redo05.log’ size 50m; Database altered. SQL> alter database add logfile group 6 ‘/opt/oracle/oradata/orcl/redo06.log’ size 50m; Database altered.
此时我们看到的新增redo文件没用使用
1 2 3 4 5 6 7 8 9 |
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 49 52428800 512 1 NO CURRENT 1322694 21-JUN-13 2.8147E+14 2 1 47 52428800 512 1 NO INACTIVE 1217499 14-JUN-13 1267733 17-JUN-13 3 1 48 52428800 512 1 NO INACTIVE 1267733 17-JUN-13 1322694 21-JUN-13 4 1 0 52428800 512 1 YES UNUSED 0 0 5 1 0 52428800 512 1 YES UNUSED 0 0 6 1 0 52428800 512 1 YES UNUSED 0 0 |
切换日志文件组到新的日志文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> alter system switch logfile ; System altered. SQL> alter system switch logfile ; System altered. SQL> alter system switch logfile ; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 49 52428800 512 1 NO ACTIVE 1322694 21-JUN-13 1356682 22-JUN-13 2 1 47 52428800 512 1 NO INACTIVE 1217499 14-JUN-13 1267733 17-JUN-13 3 1 48 52428800 512 1 NO INACTIVE 1267733 17-JUN-13 1322694 21-JUN-13 4 1 50 52428800 512 1 NO ACTIVE 1356682 22-JUN-13 1356686 22-JUN-13 5 1 51 52428800 512 1 NO ACTIVE 1356686 22-JUN-13 1356689 22-JUN-13 6 1 52 52428800 512 1 NO CURRENT 1356689 22-JUN-13 2.8147E+14 6 rows selected. |
如果status显示为inactive状态,我们删掉原来旧的日志文件,在这里也就是group#为1,2,3
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database drop logfile group 1; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 4 1 50 52428800 512 1 NO INACTIVE 1356682 22-JUN-13 1356686 22-JUN-13 5 1 51 52428800 512 1 NO INACTIVE 1356686 22-JUN-13 1356689 22-JUN-13 6 1 52 52428800 512 1 NO CURRENT 1356689 22-JUN-13 2.8147E+14 |
此时我们看到的redo日志文件组为4,5,6,如果需要,我们这里可以重新吧group#按照上面的方法改为1,2,3。步骤如下:
创建日志文件组:
1 2 3 4 5 6 7 8 9 10 11 |
SQL> alter database add logfile group 1 '/opt/oracle/oradata/orcl/redo01.log' size 50m; alter database add logfile group 1 '/opt/oracle/oradata/orcl/redo01.log' size 50m * ERROR at line 1: ORA-00301: error in adding log file '/opt/oracle/oradata/orcl/redo01.log' - file cannot be created ORA-27038: created file already exists ----这里需要注意,当drop掉redo的时候,物理文件还是存在的,这里我们直接rm掉即可 Additional information: 1 > rm redo01.log > rm redo02.log > rm redo03.log |
然后再创建日志文件组:大小一定要规划一下。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> alter database add logfile group 1 '/opt/oracle/oradata/orcl/redo01.log' size 50m; Database altered. SQL> alter database add logfile group 2 '/opt/oracle/oradata/orcl/redo02.log' size 50m; Database altered. SQL> alter database add logfile group 3 '/opt/oracle/oradata/orcl/redo03.log' size 50m; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 0 52428800 512 1 YES UNUSED 0 0 2 1 0 52428800 512 1 YES UNUSED 0 0 3 1 0 52428800 512 1 YES UNUSED 0 0 4 1 50 52428800 512 1 NO INACTIVE 1356682 22-JUN-13 1356686 22-JUN-13 5 1 51 52428800 512 1 NO INACTIVE 1356686 22-JUN-13 1356689 22-JUN-13 6 1 52 52428800 512 1 NO CURRENT 1356689 22-JUN-13 2.8147E+14 6 rows selected. |
然后切换日志文件:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 53 52428800 512 1 NO ACTIVE 1356985 22-JUN-13 1356990 22-JUN-13 2 1 54 52428800 512 1 NO ACTIVE 1356990 22-JUN-13 1356994 22-JUN-13 3 1 55 52428800 512 1 NO CURRENT 1356994 22-JUN-13 2.8147E+14 4 1 50 52428800 512 1 NO INACTIVE 1356682 22-JUN-13 1356686 22-JUN-13 5 1 51 52428800 512 1 NO INACTIVE 1356686 22-JUN-13 1356689 22-JUN-13 6 1 52 52428800 512 1 NO ACTIVE 1356689 22-JUN-13 1356985 22-JUN-13 6 rows selected. |
如果status显示为inactive状态,我们删掉那些日志文件,在这里也就是group#为4,5,6
1 2 3 4 5 6 7 8 9 10 |
SQL> alter database drop logfile group 4; Database altered. SQL> alter database drop logfile group 5; Database altered. SQL> alter database drop logfile group 6; alter database drop logfile group 6 * ERROR at line 1: ORA-01624: log 6 needed for crash recovery of instance ora11g (thread 1) ORA-00312: online log 6 thread 1: '/opt/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_6_8wcqldww_.log' |
当状态为active的时候,删除是会报错的,当前group 4和group 5已经删除成功
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 53 52428800 512 1 NO ACTIVE 1356985 22-JUN-13 1356990 22-JUN-13 2 1 54 52428800 512 1 NO ACTIVE 1356990 22-JUN-13 1356994 22-JUN-13 3 1 55 52428800 512 1 NO CURRENT 1356994 22-JUN-13 2.8147E+14 6 1 52 52428800 512 1 NO ACTIVE 1356689 22-JUN-13 1356985 22-JUN-13 SQL> alter system flush buffer_cache; ---清除缓存池,状态会立马变成inactive,但是这一步操作需要谨慎 System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 53 52428800 512 1 NO INACTIVE 1356985 22-JUN-13 1356990 22-JUN-13 2 1 54 52428800 512 1 NO INACTIVE 1356990 22-JUN-13 1356994 22-JUN-13 3 1 55 52428800 512 1 NO CURRENT 1356994 22-JUN-13 2.8147E+14 6 1 52 52428800 512 1 NO INACTIVE 1356689 22-JUN-13 1356985 22-JUN-13 |
删除redo group 6 成功
1 2 3 4 5 6 7 8 |
SQL> alter database drop logfile group 6; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 53 52428800 512 1 NO INACTIVE 1356985 22-JUN-13 1356990 22-JUN-13 2 1 54 52428800 512 1 NO INACTIVE 1356990 22-JUN-13 1356994 22-JUN-13 3 1 55 52428800 512 1 NO CURRENT 1356994 22-JUN-13 2.8147E+14 |
最后不要忘记删掉物理文件哦
这样我们的日志就切换过来了,当然要看当前数据库的负载情况,需要多大的redo。需要几组。都需要经过DBA的计算。
- 本文固定链接: http://www.savedba.com/?p=240
- 转载请注明: 版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!