Oracle在线日志添加

作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
擅长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      

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      

3.STATUS 四种状态

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