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，
 含有執行個體恢 複需要的資訊，不能被覆寫