在线修改REDO LOG的大小及增加新的日志组

最新推荐文章于 2022-08-15 14:00:48 发布

cszj38709

最新推荐文章于 2022-08-15 14:00:48 发布

阅读量222

点赞数

文章标签：数据库

我管理的数据库最近的alert日志中有发现如下错误：

Thread 1 cannot allocate new log, sequence 36685

Checkpoint not complete

并且切换非常频繁。查看了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的大小，建议的操作步骤应该如下：

1) 先创建两组新的日志组5、6

2) SWITCH LOGFILE到新创建的日志组5、6

3) 删除旧的日志组1、2、3

4) 重建旧的日志组1、2、3

5) SWITCH LOGFILE到日志组1、2、3

6) 删除过渡的日志组5、6

但本次操作，我既要修改原有日志组的大小，又要增加新的日志组，所以省去了中间过渡日志组的操作，我的操作过程如下：

1) 查看当前日志组的状态，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 INACTIVE

sys@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_redo12

6 rows selected.

2) 增加新的日志组

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 UNUSED

6 rows selected.

可以看到，新增的三组日志GROUP4、5、6，状态为UNUSED。

3) 切换当前的redo log

sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;

System altered.

4) 查看切换后的状态

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 UNUSED

6 rows selected.

可以看见，现在切换到新增的第4组日志上使用。

5) 改变检查点

sys@BFMDB> ALTER SYSTEM CHECKPOINT;

System altered.

6) 查看切换后的状态

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 UNUSED

6 rows selected.

可以看见，GROUP1、2、3三组日志状态变成了INACTIVE了，这时候可以删除它们。

7) 删除状态为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.

8) 重建新的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>
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.

这时候创建成功了！

9) 切换测试

sys@BFMDB> ALTER SYSTEM SWITCH LOGFILE;

System altered.

10) 检查切换后的状态

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 UNUSED

6 rows selected.

11) 多切换几次，让他每组日志都使用一遍。

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 UNUSED

6 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 INACTIVE

6 rows selected.

日志切换没有发生错误，每组日志都能正常使用和切换。顺利结束！

整个过程用了12分钟。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10356975/viewspace-739994/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10356975/viewspace-739994/

cszj38709

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
在线修改REDO LOG的大小及增加新的日志组

我管理的数据库最近的alert日志中有发现如下错误：Thread1cannotallocatenewlog,sequence36685Checkpointnotcomplete...
复制链接

扫一扫