Oracle扩展redo

wx62e28ac222a20 2022-08-04 数据库 132 0 百度已收录

ECS 云服务器 SWAS 轻量应用服务器 RDS 云数据库 Redis 云数据库 CDN 内容分发 OSS 对象存储 SLB 负载均衡 NAT 网关 DNS 云解析 MAIL 企业邮箱 WAF 应用防火墙 DDoS 高防 SMS 短信包 MK 云市场 XIN 心选 IM 商标注册 JZ 自营建站

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 有几个值分别是：​

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)
查看日志文件 

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日志组。

1、创建3个新的日志组​​​​

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、切换当前日志到新的日志组​

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组​

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

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

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 

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