笔者在有一次工作的时候，发现一线的数据库环境非常慢，做了一份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
- 转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
