Oracle扩展redo-CSDN博客

本文链接：https://blog.csdn.net/hbhe0316/article/details/122701463

Oracle更改redo log大小 or 增加redo log组
(1)redo log的大小可以影响 DBWR 和 checkpoint ；
(2)arger redo log files provide better performance. Undersized logfiles increase checkpoint activity and reduce performance.
大的log file可以提供更好的性能，小的logfile 会增加checkpoint 和降低性能；
(3) A rough guide is to switch log files at most once every 20 minutes.(推荐日志切换的时间不要超多20分钟).
查看redolog

SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME              CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------ ----------
         1          1         64  209715200        512          1 YES       INACTIVE                                               3658049 2021:10:1322:19:37      3729021 2021:10:1322:37:17          0
         2          1         65  209715200        512          1 YES       INACTIVE                                               3729021 2021:10:1322:37:17      3870943 2021:10:2020:53:55          0
         3          1         66  209715200        512          1 NO        CURRENT                                                3870943 2021:10:2020:53:55   9.2954E+18                             0

status 有几个值分别是：https://www.cndba.cn/hbhe0316/article/4964

unused(还没有使用过)；
current(正在使用)；
active(Log isactive but is not the current log. It is needed for crash recovery)
inactive(Log is nolonger needed for instance recovery)
查看日志文件

https://www.cndba.cn/hbhe0316/article/4964

SQL> col member for a40
SQL>  select * from v$logfile ;

    GROUP# STATUS                TYPE                  MEMBER                                   IS_RECOVE     CON_ID
---------- --------------------- --------------------- ---------------------------------------- --------- ----------
         3                       ONLINE                /oradata/ORCL/redo03.log                 NO                 0
         2                       ONLINE                /oradata/ORCL/redo02.log                 NO                 0
         1                       ONLINE                /oradata/ORCL/redo01.log                 NO                 0

由于ORACLE并没有提供类似RESIZE的参数来重新调整REDO LOG FILE的大小，因此只能先把这个文件删除了，然后再重建。又由于ORACLE要求最少有两组日志文件在用，所以不能直接删除，必须要创建中间过渡的REDO LOG日志组。

https://www.cndba.cn/hbhe0316/article/4964

1、创建3个新的日志组https://www.cndba.cn/hbhe0316/article/4964 https://www.cndba.cn/hbhe0316/article/4964

ALTER DATABASE ADD LOGFILE GROUP 4('/oradata/ORCL/redo04a.log','/oradata/ORCL/redo04b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 5('/oradata/ORCL/redo05a.log','/oradata/ORCL/redo05b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 6('/oradata/ORCL/redo06a.log','/oradata/ORCL/redo06b.log') SIZE 2048M;

2、切换当前日志到新的日志组https://www.cndba.cn/hbhe0316/article/4964

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME              CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------ ----------
         1          1         64  209715200        512          1 YES       INACTIVE                                               3658049 2021:10:1322:19:37      3729021 2021:10:1322:37:17          0
         2          1         65  209715200        512          1 YES       INACTIVE                                               3729021 2021:10:1322:37:17      3870943 2021:10:2020:53:55          0
         3          1         66  209715200        512          1 YES       ACTIVE                                                 3870943 2021:10:2020:53:55      3874182 2021:10:2021:19:11          0
         4          1         67 2147483648        512          2 YES       ACTIVE                                                 3874182 2021:10:2021:19:11      3874188 2021:10:2021:19:13          0
         5          1         68 2147483648        512          2 YES       ACTIVE                                                 3874188 2021:10:2021:19:13      3874200 2021:10:2021:19:15          0
         6          1         69 2147483648        512          2 NO        CURRENT                                                3874200 2021:10:2021:19:15   9.2954E+18                             0

3、删除旧的日志组，删除状态为INACTIVE的redo组https://www.cndba.cn/hbhe0316/article/4964

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

查看日志组的状态看一下哪个是当前组，哪个是inactive状态的。删除掉inactive的那个组。如果状态为current和active 在删除的时候会报错

https://www.cndba.cn/hbhe0316/article/4964

4、操作系统下删除原日志组1、2、3中的文件
注意：每一步删除drop操作，都需要手工删除操作系统中的实体文件。
5、重建日志组1、2、3

ALTER DATABASE ADD LOGFILE GROUP 1('/oradata/ORCL/redo01a.log','/oradata/ORCL/redo01b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 2('/oradata/ORCL/redo02a.log','/oradata/ORCL/redo02b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 3('/oradata/ORCL/redo03a.log','/oradata/ORCL/redo03b.log') SIZE 2048M;

6.查看redo

https://www.cndba.cn/hbhe0316/article/4964

SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME              CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------ ----------
         1          1          0 2147483648        512          2 YES       UNUSED                                                       0                               0                             0
         2          1          0 2147483648        512          2 YES       UNUSED                                                       0                               0                             0
         3          1          0 2147483648        512          2 YES       UNUSED                                                       0                               0                             0
         4          1        106 2147483648        512          2 YES       ACTIVE                                                 3874983 2021:10:2021:33:51      3875029 2021:10:2021:34:33          0
         5          1        107 2147483648        512          2 YES       ACTIVE                                                 3875029 2021:10:2021:34:33      3875035 2021:10:2021:34:35          0
         6          1        108 2147483648        512          2 NO        CURRENT                                                3875035 2021:10:2021:34:35   9.2954E+18                             0

Linux,oracle