事发: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。