我管理的数据库最近的alert日志中有发现如下错误：
并且切换非常频繁。查看了REDO日志组之后，发现这个数据库原本是3组日志，每组大小仅100M。在出现Checkpoint not complete时数据库会短暂的hung，因此打算将原来的日志调整为1024MB大小，并增加3组新的日志。 相关的知识普及： 1、 Redo log File存放了Redo log信息，最少有两组日志文件，供Oracle循环使用。 2、 Redo log File每组最少一个，建议两个，防止损坏而导致的数据丢失。 3、 每组中的文件大小必须一致，因为他们是同时修改的，不同组的文件大小可以不一致。 4、 每组中的文件个数必须一致。 由于ORACLE并没有提供类似RESIZE的参数来重新调整REDO LOG FILE的大小，因此只能先把这个文件删除了，然后再重建。又由于ORACLE要求最少有两组日志文件在用，所以不能直接删除，必须要创建中间过渡的REDO LOG日志组。因此，如果只是修改REDO LOG FILE的大小，建议的操作步骤应该如下：- 先创建两组新的日志组5、6
- SWITCH LOGFILE到新创建的日志组5、6
- 删除旧的日志组1、2、3
- 重建旧的日志组1、2、3
- SWITCH LOGFILE到日志组1、2、3
- 删除过渡的日志组5、6
但本次操作，我既要修改原有日志组的大小，又要增加新的日志组，所以省去了中间过渡日志组的操作，我的操作过程如下：- 查看当前日志组的状态，GROUP编号，日志文件所在的位置。
sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 100 CURRENT 2 2 100 INACTIVE 3 2 100 INACTIVEsys@BFMDB> SELECT group#,member FROM v$logfile;
GROUP# MEMBER———- ——————–
3 /dev/rlv_ora_redo13 3 /dev/rlv_ora_redo14 2 /dev/rlv_ora_redo22 2 /dev/rlv_ora_redo21 1 /dev/rlv_ora_redo11 1 /dev/rlv_ora_redo126 rows selected.
- 增加新的日志组
2.1 查看新增日志组的裸设备情况，大小，等基本信息[nm_ora@/dev$]() lsvg -l vg_ora_sys4
vg_ora_sys4:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
lv_ora_redo30 raw 18 18 3 closed/syncd N/A
lv_ora_redo31 raw 18 18 3 closed/syncd N/A
lv_ora_redo32 raw 18 18 3 closed/syncd N/A
lv_ora_redo33 raw 18 18 3 closed/syncd N/A
lv_ora_redo34 raw 18 18 3 closed/syncd N/A
lv_ora_redo35 raw 18 18 3 closed/syncd N/A
[nm_ora@/dev$]() lsvg vg_ora_sys4
VOLUME GROUP: vg_ora_sys4 VG IDENTIFIER: 00c52b5b00004c000000011e219c275f
VG STATE: active PP SIZE: 128 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 2397 (306816 megabytes)
MAX LVs: 256 FREE PPs: 2289 (292992 megabytes)
LVs: 6 USED PPs: 108 (13824 megabytes)
OPEN LVs: 0 QUORUM: 2 (Enabled)
TOTAL PVs: 3 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 3 AUTO ON: no
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 1024 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
由上，每一个LV的大小为18PPS，每一个PPS大小为128MB，因此大小为128*18=2304MB，满足要求的1024大小的要求。 2.2 增加新的日志组sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 4 ('/dev/rlv_ora_redo30','/dev/rlv_ora_redo31') SIZE 1024M;
Database altered.
sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 5 ('/dev/rlv_ora_redo32','/dev/rlv_ora_redo33') SIZE 1024M;
Database altered.
sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 6 ('/dev/rlv_ora_redo34','/dev/rlv_ora_redo35') SIZE 1024M;
Database altered.
2.3 查看新增日志组的状态sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 100 CURRENT 2 2 100 INACTIVE 3 2 100 INACTIVE 4 2 1024 UNUSED 5 2 1024 UNUSED 6 2 1024 UNUSED6 rows selected.
可以看到，新增的三组日志GROUP4、5、6，状态为UNUSED。- 切换当前的redo log
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
- 查看切换后的状态
sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 100 ACTIVE 2 2 100 INACTIVE 3 2 100 INACTIVE 4 2 1024 CURRENT 5 2 1024 UNUSED 6 2 1024 UNUSED6 rows selected.
可以看见，现在切换到新增的第4组日志上使用。- 改变检查点
sys@BFMDB> ALTER SYSTEM CHECKPOINT;
System altered.
- 查看切换后的状态
sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 100 INACTIVE 2 2 100 INACTIVE 3 2 100 INACTIVE 4 2 1024 CURRENT 5 2 1024 UNUSED 6 2 1024 UNUSED6 rows selected.
可以看见，GROUP1、2、3三组日志状态变成了INACTIVE了，这时候可以删除它们。- 删除状态为inactive的日志
sys@BFMDB> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
sys@BFMDB> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
sys@BFMDB> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
- 重建新的GROUP 1、2、3三组日志
8.1 检查旧的日志组的LV大小是否符合要求[nm_ora@/dev$]() lsvg -l vg_ora_sys
vg_ora_sys:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
lv_ora_sys raw 128 128 1 open/syncd N/A
lv_ora_redo11 raw 64 64 1 open/syncd N/A
lv_ora_redo12 raw 64 64 1 open/syncd N/A
lv_ora_redo13 raw 64 64 1 open/syncd N/A
lv_ora_redo14 raw 64 64 1 open/syncd N/A
lv_ora_redo15 raw 64 64 1 closed/syncd N/A
lv_ora_ctl1 raw 32 32 1 open/syncd N/A
lv_ora_ctl2 raw 32 32 1 open/syncd N/A
lv_ora_ctl3 raw 32 32 1 open/syncd N/A
lv_ora_spf raw 1 1 1 closed/syncd N/A
lv_ora_temp raw 640 640 1 open/syncd N/A
lv_ora_undo1 raw 640 640 1 open/syncd N/A
lv_ora_undo2 raw 640 640 1 open/syncd N/A
lv_ora_redo21 raw 8 8 1 open/syncd N/A
lv_ora_redo22 raw 8 8 1 open/syncd N/A
lv_ora_redo23 raw 8 8 1 closed/syncd N/A
[nm_ora@/dev$]() lsvg vg_ora_sys
VOLUME GROUP: vg_ora_sys VG IDENTIFIER: 00c528eb00004c000000010b0d653929
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 6399 (102384 megabytes)
MAX LVs: 512 FREE PPs: 3910 (62560 megabytes)
LVs: 16 USED PPs: 2489 (39824 megabytes)
OPEN LVs: 13 QUORUM: 2 (Enabled)
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: no
MAX PPs per VG: 128016
MAX PPs per PV: 7112 MAX PVs: 18
LTG size (Dynamic): 1024 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
–其中lv_ora_redo11、lv_ora_redo12、lv_ora_redo13、lv_ora_redo14的大小为64*16=1024MB，正好满足要求。而lv_ora_redo21、lv_ora_redo22大小只有8*16=128MB,不满足1024的要求，因此这两个LV将被废弃。
–启用了/dev/rlv_ora_redo15和/dev/rlv2gaioxdat010来替换GROUP 2中原有的LV
8.2 重建sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 1 ('/dev/rlv_ora_redo11','/dev/rlv_ora_redo12') SIZE 1024M;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/dev/rlv_ora_redo11','/dev/rlv_ora_redo12') SIZE 1024M
*
ERROR at line 1:
ORA-00301: error in adding log file '/dev/rlv_ora_redo11' – file cannot be created
ORA-27042: not enough space on raw partition to fullfill request
IBM AIX RISC System/6000 Error: 6: No such device or address
Additional information: 1
Additional information: 1
Additional information: -1
sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 2 ('/dev/rlv_ora_redo15','/dev/rlv2gaioxdat010') SIZE 1024M;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/dev/rlv_ora_redo15','/dev/rlv2gaioxdat010') SIZE 1024M
*
ERROR at line 1:
ORA-00301: error in adding log file '/dev/rlv_ora_redo15' – file cannot be created
ORA-27042: not enough space on raw partition to fullfill request
IBM AIX RISC System/6000 Error: 6: No such device or address
Additional information: 1
Additional information: 1
Additional information: -1
sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 3 ('/dev/rlv_ora_redo13','/dev/rlv_ora_redo14') SIZE 1024M;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/dev/rlv_ora_redo13','/dev/rlv_ora_redo14') SIZE 1024M
*
ERROR at line 1:
ORA-00301: error in adding log file '/dev/rlv_ora_redo13' – file cannot be created
ORA-27042: not enough space on raw partition to fullfill request
IBM AIX RISC System/6000 Error: 6: No such device or address
Additional information: 1
Additional information: 1
Additional information: -1
0.0 重建的时候出现了错误，根据错误信息no enough space，猜测可能是由于LV大小刚好1024M，导致没有空间写文件头，于是我尝试将他们缩小到1000MB。sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 1 ('/dev/rlv_ora_redo11','/dev/rlv_ora_redo12') SIZE 1000M;
Database altered.
sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 2 ('/dev/rlv_ora_redo15','/dev/rlv2gaioxdat010') SIZE 1000M;
Database altered.
sys@BFMDB> ALTER DATABASE ADD LOGFILE GROUP 3 ('/dev/rlv_ora_redo13','/dev/rlv_ora_redo14') SIZE 1000M;
Database altered.
这时候创建成功了！- 切换测试
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
- 检查切换后的状态
sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 1000 CURRENT 2 2 1000 UNUSED 3 2 1000 UNUSED 4 2 1024 ACTIVE 5 2 1024 UNUSED 6 2 1024 UNUSED6 rows selected.
- 多切换几次，让他每组日志都使用一遍。
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 1000 ACTIVE 2 2 1000 CURRENT 3 2 1000 UNUSED 4 2 1024 ACTIVE 5 2 1024 UNUSED 6 2 1024 UNUSED6 rows selected.
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;
System altered.
sys@BFMDB> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS———- ———- ———- ————————————————
1 2 1000 CURRENT 2 2 1000 INACTIVE 3 2 1000 INACTIVE 4 2 1024 INACTIVE 5 2 1024 INACTIVE 6 2 1024 INACTIVE6 rows selected.
日志切换没有发生错误，每组日志都能正常使用和切换。顺利结束！ 整个过程用了12分钟。