Oracle在线日志添加

作者:IT邦德
中国DBA联盟(ACDU)成员,目前从事DBA及程序编程
(Web\java\Python)工作,主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase(OBCA)认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作,在数据库领域有丰富的经验
B站主播Oracle、Mysql、PG实战课程,请搜索:jeames007

擅长Oracle数据库运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。

在这里插入图片描述

文章目录

  • 前言
    • 1.增加一个日志组(Widows)
    • 2.添加日志组的成员(Linux)
    • 3.STATUS 四种状态

前言

本文总结了Oracle单实例redo日志的维护及添加

1.增加一个日志组(Widows)

SYS@ORCL> select group#,sequence#,bytes/1024/1024 M,archived,status from v$log;
    GROUP#  SEQUENCE#          M ARC STATUS
---------- ---------- ---------- --- ----------------
         1        269         50 NO  INACTIVE
         2        270         50 NO  ACTIVE
         3        271         50 NO  CURRENT

SYS@ORCL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log            NO


SYS@ORCL> alter database add logfile group 4  '/u01/app/oracle/oradata/ORCL/redo04.log' size 50m;
SYS@ORCL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log            NO
         4         ONLINE  /u01/app/oracle/oradata/ORCL/redo04.log            NO

2.添加日志组的成员(Linux)

为每个组增加一个 member(一共是4个组)
先建好目录,准备放在/u01/app/oracle/oradata/ORCL/redoimage 下
[oracle@Jeames ~]$ cd /u01/app/oracle/oradata/ORCL
[oracle@Jeames ORCL]$ mkdir redoimage

SYS@ORCL> alter database add logfile member
'/u01/app/oracle/oradata/ORCL/redoimage/redo01.log'  to group 1,
'/u01/app/oracle/oradata/ORCL/redoimage/redo02.log'  to group 2,
'/u01/app/oracle/oradata/ORCL/redoimage/redo03.log'  to group 3,
'/u01/app/oracle/oradata/ORCL/redoimage/redo04.log'  to group 4;

SYS@ORCL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log            NO
         4         ONLINE  /u01/app/oracle/oradata/ORCL/redo04.log            NO
         1         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo01.log  NO
         2         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo02.log  NO
         3 INVALID ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo03.log  NO
         4         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo04.log  NO


注:STATUS 是 INVALID,说明 member 还没有同步好
alter system switch logfile; 至少做 4 次切换,消除  invalid

SYS@ORCL> alter system switch logfile;

SYS@ORCL> select group#,status,archived from v$log;

    GROUP# STATUS           ARC
---------- ---------------- ---
         1 INACTIVE         NO
         2 ACTIVE           NO
         3 CURRENT          NO
         4 ACTIVE           NO

SYS@ORCL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log            NO
         4         ONLINE  /u01/app/oracle/oradata/ORCL/redo04.log            NO
         1         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo01.log  NO
         2         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo02.log  NO
         3         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo03.log  NO
         4         ONLINE  /u01/app/oracle/oradata/ORCL/redoimage/redo04.log  NO

3.STATUS 四种状态

unused:
 新添加的日志组,还没有使用
inactive
 日志组对应的脏块已经从 data buffer 写入到 data file,可以覆盖
active: 
日志组对应的脏块还没有完全从 data buffer 写入到 data file,
含有实例恢复需要的信息,不能被覆盖
current:
 当前日志组对应的脏块还没有全部从 data buffer 写入到 data file,
 含有实例恢 复需要的信息,不能被覆盖

如果觉得文章对你有帮助,点赞、收藏、关注、评论,一键四连支持
您的批评指正是我写作的最大动力!

❤️ 技术交流可以 关注公众号:IT邦德 ❤️

你可能感兴趣的