2012-01-12
现象
归档日志切换太频繁。
查看ALERT日志:
Thread 1 advanced to log sequence 12391
Current log# 3 seq# 12391 mem# 0: /usr/app/ora_files/redo03a.log
Currentlog# 3 seq# 12391 mem# 1: /app/ora_server/ora_base/oradata/gis/redo03b.log
Thu Jan 12 10:58:51 2012
Thread 1 advanced to log sequence 12392
Current log# 1 seq# 12392 mem# 0: /usr/app/ora_files/redo01a.log
Current log# 1 seq# 12392 mem# 1: /app/ora_server/ora_base/oradata/gis/redo01b.log
Thu Jan 12 11:01:26 2012
Thread 1 advanced to log sequence 12393
Current log# 2 seq# 12393 mem# 0: /usr/app/ora_files/redo02a.log
Current log# 2 seq# 12393 mem# 1:/app/ora_server/ora_base/oradata/gis/redo02b.log
可以看出平均2-3分钟进行一次归档。再查看下归档的历史记录,如下:
可以看出每小时发生的归档量很大。Redo切换太频繁。
原因
突然增加了很多辆车,数据量瞬间增长,而且REDO的大小为100M,当前的数据量的增长导致REDO切换太多频繁。如图:
处理
之前调整过一次,从默认的50M调整到100M,这次再次调整为200M,同时再增加两组日志组:
1、查看原有日志组
SQL>SELECT L.GROUP#, BYTES / 1024 / 1024 AS BYTEM, MEMBERS, ARCHIVED, L.STATUS, MEMBER
FROM V$LOG L, V$LOGFILE F
WHERE L.GROUP# = F.GROUP#(+)
ORDER BY 1;
GROUP# | BYTEM | MEMBERS | ARCHIVED | STATUS | MEMBER |
1 | 100 | 2 | NO | CURRENT | /usr/app/ora_files/redo01a.log |
1 | 100 | 2 | NO | CURRENT | /app/ora_server/ora_base/oradata/gis/redo01b.log |
2 | 100 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo02b.log |
2 | 100 | 2 | YES | ACTIVE | /usr/app/ora_files/redo02a.log |
3 | 100 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo03b.log |
3 | 100 | 2 | YES | ACTIVE | /usr/app/ora_files/redo03a.log |
可以看出每个日志组,2个成员,每个成员100M大小。
2、添加日志组及成员
SQL> alter database add logfile group 4('/usr/app/ora_files/redo4a.log')size 200m;
Database altered.
SQL> alter database add logfile group 5('/usr/app/ora_files/redo5a.log')size 200m;
Database altered.
SQL> alter database add logfile member'/app/ora_server/ora_base/oradata/gis/redo4b.log' to group 4;
Database altered.
SQL> alter database add logfile member'/app/ora_server/ora_base/oradata/gis/redo5b.log' to group 5;
Database altered.
3、确认添加的日志组
GROUP# | BYTEM | MEMBERS | ARCHIVED | STATUS | MEMBER |
1 | 100 | 2 | YES | ACTIVE | /usr/app/ora_files/redo01a.log |
1 | 100 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo01b.log |
2 | 100 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo02b.log |
2 | 100 | 2 | YES | ACTIVE | /usr/app/ora_files/redo02a.log |
3 | 100 | 2 | YES | INACTIVE | /usr/app/ora_files/redo03a.log |
3 | 100 | 2 | YES | INACTIVE | /app/ora_server/ora_base/oradata/gis/redo03b.log |
4 | 200 | 2 | NO | CURRENT | /usr/app/ora_files/redo4a.log |
4 | 200 | 2 | NO | CURRENT | /app/ora_server/ora_base/oradata/gis/redo4b.log |
5 | 200 | 2 | YES | UNUSED | /usr/app/ora_files/redo5a.log |
5 | 200 | 2 | YES | UNUSED | /app/ora_server/ora_base/oradata/gis/redo5b.log |
4、删除日志组
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance gis (thread 1) - cannot drop
ORA-00312: online log 3 thread 1:'/usr/app/ora_files/redo03a.log'
ORA-00312: online log 3 thread 1:
'/app/ora_server/ora_base/oradata/gis/redo03b.log'
5、删除错误处理
注意,上面删除日志组3的时候出现错误,
1) 首先查看日志组状态
3 | 100 | 2 | YES | CURRENT | /usr/app/ora_files/redo03a.log |
3 | 100 | 2 | YES | CURRENT | /app/ora_server/ora_base/oradata/gis/redo03b.log |
4 | 200 | 2 | NO | ACTIVE | /usr/app/ora_files/redo4a.log |
4 | 200 | 2 | NO | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo4b.log |
5 | 200 | 2 | YES | ACTIVE | /usr/app/ora_files/redo5a.log |
5 | 200 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo5b.log |
CURRENT此时日志组正在被使用这个时候,应该做日志切换和归档。
注意:只有status为inactive并且archived 为YES时方可删除日志组(这个的意思为该重做日志己经归档,否则会报ora-01623)
6、切换日志组
SQL>alter system switch logfile;
Database altered.
7、归档日志组
SQL>alter system switch logfile;
Database altered.
8、再次查看日志组状态
3 | 100 | 2 | YES | CURRENT | /usr/app/ora_files/redo03a.log |
3 | 100 | 2 | YES | CURRENT | /app/ora_server/ora_base/oradata/gis/redo03b.log |
4 | 200 | 2 | NO | ACTIVE | /usr/app/ora_files/redo4a.log |
4 | 200 | 2 | NO | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo4b.log |
5 | 200 | 2 | YES | ACTIVE | /usr/app/ora_files/redo5a.log |
5 | 200 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo5b.log |
9、删除日志组
SQL> alter database drop logfile group 3;
Database altered.
10、删除日志文件
操作系统级别删除数据文件(我的系统是redhat5)
rm -rf redo01.log
11、恢复原有日志组
增加日志成员(最好把成员放到不同的磁盘上做到多功,避免一个组的日志文件全部损坏数据库宕机)
SQL> alter database add logfile group 1('/usr/app/ora_files/redo01a.log')size 200m;
Database altered.
SQL> alter database add logfile member'/app/ora_server/ora_base/oradata/gis/redo01b.log ' to group 1;
Database altered.
SQL> alter database add logfile group 2('/usr/app/ora_files/redo02a.log')size 200m;
Database altered.
SQL> alter database add logfile member'/app/ora_server/ora_base/oradata/gis/redo02b.log ' to group 2;
Database altered.
SQL> alter database add logfile group 3('/usr/app/ora_files/redo03a.log')size 200m;
Database altered.
SQL> alter database add logfile member '/app/ora_server/ora_base/oradata/gis/redo03b.log' to group 3;
Database altered.
12、确认最终结果
GROUP# | BYTEM | MEMBERS | ARCHIVED | STATUS | MEMBER |
1 | 200 | 2 | NO | CURRENT | /usr/app/ora_files/redo01a.log |
1 | 200 | 2 | NO | CURRENT | /app/ora_server/ora_base/oradata/gis/redo01b.log |
2 | 200 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo02b.log |
2 | 200 | 2 | YES | ACTIVE | /usr/app/ora_files/redo02a.log |
3 | 200 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo03b.log |
3 | 200 | 2 | YES | ACTIVE | /usr/app/ora_files/redo03a.log |
4 | 200 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo04b.log |
4 | 200 | 2 | YES | ACTIVE | /usr/app/ora_files/redo04a.log |
5 | 200 | 2 | YES | ACTIVE | /app/ora_server/ora_base/oradata/gis/redo05b.log |
5 | 200 | 2 | YES | ACTIVE | /usr/app/ora_files/redo05a.log |
改善措施和建议