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

最新推荐文章于 2024-06-14 11:10:45 发布

Demonson

最新推荐文章于 2024-06-14 11:10:45 发布

阅读量3.4k

点赞数

分类专栏： redo数据 Oracle数据库文章标签： Oracle redo log

本文链接：https://blog.csdn.net/demonson/article/details/39343971

版权

Oracle数据库同时被 2 个专栏收录

146 篇文章 8 订阅

订阅专栏

redo数据

3 篇文章 0 订阅

订阅专栏

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