hpunix oracle 10G重启,偷梁换柱-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。