如何為oracle添加重做日志組及重做日志成員

本篇內容主要講解“如何為oracle添加重做日志組及重做日志成員”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“如何為oracle添加重做日志組及重做日志成員”吧!

添加重做日志組：
SYS@orcl 08-SEP-14>select group#,type,member from v$logfile;

 GROUP# TYPE    MEMBER
------- ------- --------------------------------------------------
      3 ONLINE  /u01/app/oracle/oradata/orcl/redo03.log
      2 ONLINE  /u01/app/oracle/oradata/orcl/redo02.log
      1 ONLINE  /u01/app/oracle/oradata/orcl/redo01.log

SYS@orcl 08-SEP-14>alter database add logfile group 4
  2  ('/u01/app/oracle/oradata/orcl/redo04.log')
  3  size 50m;

SYS@orcl 08-SEP-14>select group#,sequence#,bytes/1024/1024||'MB',status from v$log;


 GROUP#  SEQUENCE# BYTES/1024/1024||'MB'                      STATUS
------- ---------- ------------------------------------------ ----------------
      1         25 50MB                                       CURRENT
      2         23 50MB                                       INACTIVE
      3         24 50MB                                       INACTIVE
      4          0 50MB                                        UNUSED

為日志組添加成員：
SYS@orcl 09-SEP-14>select group#,status,member from v$logfile;


 GROUP# STATUS  MEMBER
------- ------- --------------------------------------------------
      3         /u01/app/oracle/oradata/orcl/redo03.log
      2         /u01/app/oracle/oradata/orcl/redo02.log
      1         /u01/app/oracle/oradata/orcl/redo01.log
      4         /u01/app/oracle/oradata/orcl/redo04.log

SYS@orcl 09-SEP-14>alter database add logfile member
  2  '/u01/app/redolog/redo01a.log' to group 1,
  3  '/u01/app/redolog/redo02a.log' to group 2,
  4  '/u01/app/redolog/redo03a.log' to group 3,
  5  '/u01/app/redolog/redo04a.log' to group 4;

Database altered.

SYS@orcl 09-SEP-14>select group#,status,member,type from v$logfile;


 GROUP# STATUS  MEMBER                                             TYPE
------- ------- -------------------------------------------------- -------
      3         /u01/app/oracle/oradata/orcl/redo03.log            ONLINE
      2         /u01/app/oracle/oradata/orcl/redo02.log            ONLINE
      1         /u01/app/oracle/oradata/orcl/redo01.log            ONLINE
      4         /u01/app/oracle/oradata/orcl/redo04.log            ONLINE
      1 INVALID /u01/app/redolog/redo01a.log                       ONLINE
      2 INVALID /u01/app/redolog/redo02a.log                       ONLINE
      3 INVALID /u01/app/redolog/redo03a.log                       ONLINE
      4 INVALID /u01/app/redolog/redo04a.log                       ONLINE

8 rows selected.

切換日志四次之后，狀態為invalid的日志組成員變為null。
SYS@orcl 09-SEP-14>select group#,status,member,type from v$logfile;


 GROUP# STATUS  MEMBER                                             TYPE
------- ------- -------------------------------------------------- -------
      3         /u01/app/oracle/oradata/orcl/redo03.log            ONLINE
      2         /u01/app/oracle/oradata/orcl/redo02.log            ONLINE
      1         /u01/app/oracle/oradata/orcl/redo01.log            ONLINE
      4         /u01/app/oracle/oradata/orcl/redo04.log            ONLINE
      1         /u01/app/redolog/redo01a.log                       ONLINE
      2         /u01/app/redolog/redo02a.log                       ONLINE
      3         /u01/app/redolog/redo03a.log                       ONLINE
      4         /u01/app/redolog/redo04a.log                       ONLINE

8 rows selected.

刪除日志組成員：(注意，如果這個組里面只有唯一的一個成員，那么是不能刪除的)
SYS@orcl 09-SEP-14>alter database drop logfile member
  2  '/u01/app/redolog/redo01a.log',
  3  '/u01/app/redolog/redo02a.log',
  4  '/u01/app/redolog/redo03a.log',
  5  '/u01/app/redolog/redo04a.log';
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/redolog/redo01a.log'

可以看到實例正在使用的redo log是不能刪除的，于是我們先刪除其他組的成員：
SYS@orcl 09-SEP-14>alter database drop logfile member
  2  '/u01/app/redolog/redo02a.log',
  3  '/u01/app/redolog/redo03a.log',
  4  '/u01/app/redolog/redo04a.log';

Database altered.

接著，我們再切換日志：
SYS@orcl 09-SEP-14>select group#,sequence#,status from v$log;

 GROUP#  SEQUENCE# STATUS
------- ---------- ----------------
      1         29 CURRENT
      2         27 INACTIVE
      3         28 INACTIVE
      4         26 INACTIVE

SYS@orcl 09-SEP-14>alter system switch logfile;


System altered.


SYS@orcl 09-SEP-14>select group#,sequence#,status from v$log;


 GROUP#  SEQUENCE# STATUS
------- ---------- ----------------
      1         29 ACTIVE
      2         27 INACTIVE
      3         28 INACTIVE
      4         30 CURRENT

再刪除日志組1的成員redo01a.log:
SYS@orcl 09-SEP-14>alter database drop logfile member
  2  '/u01/app/redolog/redo01a.log';

Database altered.

查看日志組及日志組成員信息：
SYS@orcl 09-SEP-14>select group#,status,type,member from v$logfile;


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

到此，相信大家對“如何為oracle添加重做日志組及重做日志成員”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！