更改日志大小及添加日志组成员

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

 

改善措施和建议