新增重做日誌組：
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新增重做日誌組及重做日誌成員

相關文章