Oracle 19c RAC数据库日志组管理

DBA小记 2021-02-23

2662

Oracle 19c RAC数据库安装后，默认每个组的日志大小默认是200M每个，如果写入或者更新很频繁的库，建议改大日志文件的大小。

每个日志组最好要有两个以上的成员。

--查询日志组信息

select * from v$log ;

select * from v$logfile ;

---删除日志组

alter database drop logfile group 1;

---日志切换

alter system switch logfile;

---将active 的日志变为 inactive

alter system checkpoint ;

操作步骤：

1.查看日志文件位置

col status for a10
col type for a10
col member for a50
select * from v$logfile;
     GROUP# STATUS     TYPE       MEMBER                                   IS_     CON_ID
---------- ---------- ---------- -------------------------------------------------- --- 
       1            ONLINE       +DATA/HZWDB/ONLINELOG/group_1.258.1062865099          NO            
       1            ONLINE       +FRA/HZWDB/ONLINELOG/group_1.257.1062865099          YES        
       2            ONLINE       +DATA/HZWDB/ONLINELOG/group_2.259.1062865099          NO             
       2            ONLINE       +FRA/HZWDB/ONLINELOG/group_2.258.1062865099          YES        
       3            ONLINE       +DATA/HZWDB/ONLINELOG/group_3.270.1062866891          NO             
       3            ONLINE       +FRA/HZWDB/ONLINELOG/group_3.259.1062866891          YES        
       4            ONLINE       +DATA/HZWDB/ONLINELOG/group_4.271.1062866891          NO             
       4            ONLINE       +FRA/HZWDB/ONLINELOG/group_4.260.1062866891          YES

2.根据现有日志组信息，添加新的日志组

alter database add logfile thread 1 group 5 ('+DATA','+FRA') size 200M;
alter database add logfile thread 1 group 6 ('+DATA','+FRA') size 200M;
alter database add logfile thread 2 group 7 ('+DATA','+FRA') size 200M;
alter database add logfile thread 2 group 8 ('+DATA','+FRA') size 200M;

3.检查配置

SQL>  select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                    IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
       1         ONLINE  +DATA/HZWDB/ONLINELOG/group_1.258.1062865099       NO         0
       1         ONLINE  +FRA/HZWDB/ONLINELOG/group_1.257.1062865099            YES         0
       2         ONLINE  +DATA/HZWDB/ONLINELOG/group_2.259.1062865099       NO         0
       2         ONLINE  +FRA/HZWDB/ONLINELOG/group_2.258.1062865099            YES         0
       3         ONLINE  +DATA/HZWDB/ONLINELOG/group_3.270.1062866891       NO         0
       3         ONLINE  +FRA/HZWDB/ONLINELOG/group_3.259.1062866891            YES         0
       4         ONLINE  +DATA/HZWDB/ONLINELOG/group_4.271.1062866891       NO         0
       4         ONLINE  +FRA/HZWDB/ONLINELOG/group_4.260.1062866891            YES         0
       5         ONLINE  +DATA/HZWDB/ONLINELOG/group_5.285.1064933843       NO         0
       5         ONLINE  +FRA/HZWDB/ONLINELOG/group_5.313.1064933843            NO         0
       6         ONLINE  +DATA/HZWDB/ONLINELOG/group_6.284.1064933967       NO         0
       6         ONLINE  +FRA/HZWDB/ONLINELOG/group_6.312.1064933967            NO         0
       7         ONLINE  +DATA/HZWDB/ONLINELOG/group_7.286.1064934015       NO         0
       7         ONLINE  +FRA/HZWDB/ONLINELOG/group_7.314.1064934015            NO         0
       8         ONLINE  +DATA/HZWDB/ONLINELOG/group_8.287.1064934021       NO         0
       8         ONLINE  +FRA/HZWDB/ONLINELOG/group_8.315.1064934021            NO         0

SQL>  select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE        MEMBERS ARC STATUS             FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME      CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------ ----------
       1          1            75  209715200         512            2 NO  CURRENT                 4420123 22-FEB-21          1.8447E+19                       0
       2          1            72  209715200         512            2 YES INACTIVE                 4273318 21-FEB-21             4372507 21-FEB-21           0
       3          2            49  209715200         512            2 YES INACTIVE                 4181687 21-FEB-21             4328877 21-FEB-21           0
       4          2            50  209715200         512            2 YES INACTIVE                 4328877 21-FEB-21             4420114 22-FEB-21           0
       5          1            73  209715200         512            2 YES INACTIVE                 4372507 21-FEB-21             4420110 22-FEB-21           0
       6          1            74  209715200         512            2 YES INACTIVE                 4420110 22-FEB-21             4420123 22-FEB-21           0
       7          2            51  209715200         512            2 YES INACTIVE                 4420114 22-FEB-21             4420126 22-FEB-21           0
       8          2            52  209715200         512            2 NO  CURRENT                 4420126 22-FEB-21          1.8447E+19                       0

4.切换日志

alter system switch logfile;

5.删除日志组

如需要：
alter database drop logfile group 1;

然后手动asmcmd进入共享磁盘日志文件位置，手动删除group 1的成员文件。

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 19c RAC数据库日志组管理

评论