偷梁换柱-HP操作系统下ORACLE 10G故障诊断与解决（一）

事发：ORACLE10G数据库alert日志报故障信息如下：

Thread 1 advanced to log sequence 952
  Current log# 1 seq# 952 mem# 0: /dev/vgdata/rredo_1
Fri Jul 31 10:51:47 2009
Thread 1 cannot allocate new log, sequence 953
Checkpoint not complete
  Current log# 1 seq# 952 mem# 0: /dev/vgdata/rredo_1
Thread 1 advanced to log sequence 953
  Current log# 2 seq# 953 mem# 0: /dev/vgdata/rredo_2
Fri Jul 31 11:03:09 2009
Thread 1 advanced to log sequence 954
  Current log# 3 seq# 954 mem# 0: /dev/vgdata/rredo_3
Fri Jul 31 11:04:04 2009
Thread 1 advanced to log sequence 955
  Current log# 1 seq# 955 mem# 0: /dev/vgdata/rredo_1
............

检查一：bdf查看与oracle相关文件系统是否存在使用率过载情况，正常；

   二：此数据库是以裸设备方式搭建，检查所有裸设备文件权限、属主，正常；

   三：ORACLE数据库归档方式，非归档；

   四：日志文件对应裸设备文件大小及实际使用量，经查看，裸设备文件大小为512MB，实际使用量仅为50MB，如下所示：

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/dev/vgdata/rredo_1
/dev/vgdata/rredo_2
/dev/vgdata/rredo_3

SQL> select group#, bytes/1024/1024 from v$log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1       48.828125
         2       48.828125
         3       48.828125

 

故障定位：日志文件过小，导致日志切换过于频繁

解决方案：在数据库使用率不高的情况下在线扩充日志文件大小到500MB。

 

解决过程：

1，额外创建两个512MB大小的裸设备文件，作为新增日志文件所用：

# lvcreate -L 512 -n redo1_4 vgdata
Logical volume "/dev/vgdata/redo1_4" has been successfully created with
character device "/dev/vgdata/rredo1_4".
Logical volume "/dev/vgdata/redo1_4" has been successfully extended.
Volume Group configuration for /dev/vgdata has been saved in /etc/lvmconf/vgdata.conf
# lvcreate -L 512 -n redo1_5 vgdata
Logical volume "/dev/vgdata/redo1_5" has been successfully created with
character device "/dev/vgdata/rredo1_5".
Logical volume "/dev/vgdata/redo1_5" has been successfully extended.
Volume Group configuration for /dev/vgdata has been saved in /etc/lvmconf/vgdata.conf

2，给予正确的属主和权限：

# chown oracle:dba rredo1_4 rredo1_5
# chmod 660 rredo1_4 rredo1_5

 

3，增加日志组以及对应日志文件：

SQL> alter database add logfile group 4 ('/dev/vgdata/rredo1_4') size 500m;

Database altered.

SQL> alter database add logfile group 5 ('/dev/vgdata/rredo1_5') size 500m;

Database altered.

 

4，查看是否添加成功：

SQL> select group#, bytes/1024/1024, status from v$log;

    GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
         1       48.828125 CURRENT
         2       48.828125 INACTIVE
         3       48.828125 INACTIVE
         4             500 UNUSED
         5             500 UNUSED

从这里可以看到，系统内部原有的日志文件组为group1、group2、group3，新增日志组为group4以及group5，均为未使用状态。原有日志文件大小为50MB，新增日志文件大小为500MB。