Oracle redo log 大小及组数的设置（在线修改日志组数目及组成员个数）

来源：互联网发布：碧桂园森林城市知乎编辑：程序博客网时间：2024/05/24 08:33

Oracle redo log 大小及组数的设置（在线修改日志组数目及组成员个数）
1.查看当前系统现有的redo log状况（组数/大小/名称/状态）
SYS@ORCL>desc v$log
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                            NUMBER
THREAD#                                            NUMBER
SEQUENCE#                                          NUMBER
BYTES                                              NUMBER
BLOCKSIZE                                          NUMBER
MEMBERS                                            NUMBER
ARCHIVED                                          VARCHAR2(3)
STATUS                                            VARCHAR2(16)
FIRST_CHANGE#                                      NUMBER
FIRST_TIME                                        DATE
NEXT_CHANGE#                                      NUMBER
NEXT_TIME                                          DATE

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          1        50 INACTIVE        YES
          2        50 INACTIVE        YES
          3        50 CURRENT          NO

SYS@ORCL>desc v$logfile;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                            NUMBER
STATUS                                            VARCHAR2(7)
TYPE                                              VARCHAR2(7)
MEMBER                                            VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SYS@ORCL>select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_b0fh9w87_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_b0fh9wqw_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_b0fh9nk4_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_b0fh9o8x_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_b0fh9drr_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_b0fh9dxx_.log

6 rows selected. --查看日志文件的路径

2.添加3组大小为100M的日志组；
SYS@ORCL>alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log'size 100m;

Database altered.

SYS@ORCL>alter database add logfile group 5'/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log'size 100m;

Database altered.

SYS@ORCL>alter database add logfile group 6'/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log'size 100m;

Database altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          1        50 INACTIVE        YES
          2        50 INACTIVE        YES
          3        50 CURRENT          NO
          4        100 UNUSED          YES
          5        100 UNUSED          YES
          6        100 UNUSED          YES

6 rows selected.

3.删除原有的日志组；（只操作状态为inactive的日志组）
若要操作日志组为current时，需先进行日志切换：alter system switch logfile;
若为active时，则可强制进行检查点：alter system checkpoint;

SYS@ORCL>alter system switch logfile;

System altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          1        50 INACTIVE        YES
          2        50 INACTIVE        YES
          3        50 ACTIVE          YES
          4        100 CURRENT          NO
          5        100 UNUSED          YES
          6        100 UNUSED          YES

6 rows selected.

SYS@ORCL>alter database drop logfile group 1;

Database altered.

SYS@ORCL>alter database drop logfile group 2;

Database altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          3        50 ACTIVE          YES
          4        100 CURRENT          NO
          5        100 UNUSED          YES
          6        100 UNUSED          YES

SYS@ORCL>alter system switch logfile;

System altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          3        50 ACTIVE          YES
          4        100 ACTIVE          YES
          5        100 CURRENT          NO
          6        100 UNUSED          YES
SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          3        50 ACTIVE          YES
          4        100 ACTIVE          YES
          5        100 CURRENT          NO
          6        100 UNUSED          YES

SYS@ORCL>alter system checkpoint; --强制执行检查点

System altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          3        50 INACTIVE        YES
          4        100 ACTIVE          YES
          5        100 CURRENT          NO
          6        100 UNUSED          YES

SYS@ORCL>alter database drop logfile group 3;

Database altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          4        100 ACTIVE          YES
          5        100 CURRENT          NO
          6        100 UNUSED          YES

4.向新创建的日志组添加成员
注意：若在生产库中，则将新创建的成员放到不同的磁盘上
SYS@ORCL>alter system switch logfile;

System altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          4        100 ACTIVE          YES
          5        100 ACTIVE          YES
          6        100 CURRENT          NO

SYS@ORCL>alter system switch logfile;

System altered.

SYS@ORCL>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    SIZE_M STATUS          ARC
---------- ---------- ---------------- ---
          4        100 CURRENT          NO
          5        100 ACTIVE          YES
          6        100 ACTIVE          YES

SYS@ORCL>alter database add logfile member'/u01/app/oracle/oradata/ORCL/onlinelog/redo06_b.log' to group 6;

Database altered.

SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS    SIZE_M STATUS          ARC
---------- ---------- ---------- ---------------- ---
          4          1        100 CURRENT          NO
          5          1        100 INACTIVE        YES
          6          2        100 ACTIVE          YES

SYS@ORCL>alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/redo05_b.log' to group 5;

Database altered.

SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS    SIZE_M STATUS          ARC
---------- ---------- ---------- ---------------- ---
          4          1        100 CURRENT          NO
          5          2        100 INACTIVE        YES
          6          2        100 ACTIVE          YES

SYS@ORCL>alter system switch logfile;

System altered.

SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS    SIZE_M STATUS          ARC
---------- ---------- ---------- ---------------- ---
          4          1        100 ACTIVE          YES
          5          2        100 CURRENT          NO
          6          2        100 INACTIVE        YES

SYS@ORCL>alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/redo04_b.log' to group 4;

Database altered.

SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS    SIZE_M STATUS          ARC
---------- ---------- ---------- ---------------- ---
          4          2        100 ACTIVE          YES
          5          2        100 CURRENT          NO
          6          2        100 INACTIVE        YES

SYS@ORCL>alter system checkpoint;

System altered.

SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS    SIZE_M STATUS          ARC
---------- ---------- ---------- ---------------- ---
          4          2        100 INACTIVE        YES
          5          2        100 CURRENT          NO
          6          2        100 INACTIVE        YES

SYS@ORCL>select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/redo06_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo05_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo04_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log

6 rows selected.

SYS@ORCL>spool off

0 0