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

骑猪看日落 2022-06-16 11:20 203阅读 0赞

#  #

# 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;

可以看出每个日志组，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、确认添加的日志组 ###

<table> 
 <tbody> 
 <tr> 
 <td> GROUP# </td> 
 <td> BYTEM </td> 
 <td> MEMBERS </td> 
 <td> ARCHIVED </td> 
 <td> STATUS </td> 
 <td> MEMBER </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo01a.log </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo01b.log </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo02b.log </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo02a.log </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> INACTIVE </td> 
 <td> /usr/app/ora_files/redo03a.log </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> INACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo03b.log </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> NO </td> 
 <td> CURRENT </td> 
 <td> /usr/app/ora_files/redo4a.log </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> NO </td> 
 <td> CURRENT </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo4b.log </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> UNUSED </td> 
 <td> /usr/app/ora_files/redo5a.log </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> UNUSED </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo5b.log </td> 
 </tr> 
 </tbody> 
</table>

### 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） 首先查看日志组状态**

<table> 
 <tbody> 
 <tr> 
 <td> 3 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> CURRENT </td> 
 <td> /usr/app/ora_files/redo03a.log </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> 100 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> CURRENT </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo03b.log </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> NO </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo4a.log </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> NO </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo4b.log </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo5a.log </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo5b.log </td> 
 </tr> 
 </tbody> 
</table>

CURRENT此时日志组正在被使用这个时候，应该做日志切换和归档。

**注意：只有status为inactive并且archived 为YES时方可删除日志组(这个的意思为该重做日志己经归档,否则会报ora-01623)**

### 6、切换日志组 ###

SQL>alter system switch logfile;

Database altered.

### 7、归档日志组 ###

SQL>alter system switch logfile;

Database altered.

### 8、再次查看日志组状态 ###

### 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、确认最终结果 ###

<table> 
 <tbody> 
 <tr> 
 <td> GROUP# </td> 
 <td> BYTEM </td> 
 <td> MEMBERS </td> 
 <td> ARCHIVED </td> 
 <td> STATUS </td> 
 <td> MEMBER </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> NO </td> 
 <td> CURRENT </td> 
 <td> /usr/app/ora_files/redo01a.log </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> NO </td> 
 <td> CURRENT </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo01b.log </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo02b.log </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo02a.log </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo03b.log </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo03a.log </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo04b.log </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo04a.log </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /app/ora_server/ora_base/oradata/gis/redo05b.log </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> 200 </td> 
 <td> 2 </td> 
 <td> YES </td> 
 <td> ACTIVE </td> 
 <td> /usr/app/ora_files/redo05a.log </td> 
 </tr> 
 </tbody> 
</table>

## 改善措施和建议 ##