增大redo log檔案大小

增大online redo log的大小並不像增大datafile的大小那麼簡單，它需要新建一個group，然後把原來的刪除，下面用一個例子來說明：
1. 檢視當前logfile情況：
A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         4          1       1799  209715200          1 NO  CURRENT             6622373533 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11
A105024@O02DMS1>select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         4         ONLINE  K:\INTEL_INDEX\O02DMS1\REDO04.O02DMS1                                      NO
         5         ONLINE  J:\INTEL_LOG\O02DMS1\REDO05.O02DMS1                                              NO
         6         ONLINE  I:\INTEL_DATA\O02DMS1\REDO06.O02DMS1                                             NO
group 4的大小為200M，我們需要把它增大為300M。
2. 新建一個group：
A105024@O02DMS1>alter database add logfile group 1 ('K:\INTEL_INDEX\O02DMS1\REDO01.O02DMS1') size 300M;

Database altered.

3. 確保要被drop的group是inactive卻已被archived：

A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0  314572800          1 YES UNUSED                       0
         4          1       1799  209715200          1 NO  CURRENT             6622373533 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11
可以看到，group 4是當前正在用的redo log，此時無法刪除：
A105024@O02DMS1>alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance o02dms1 (thread 1) - cannot drop
ORA-00312: online log 4 thread 1: 'K:\INTEL_INDEX\O02DMS1\REDO04.O02DMS1'

此時應該手動做switch logfile和checkpoint：
A105024@O02DMS1>alter system switch logfile;

System altered.

A105024@O02DMS1>alter system checkpoint;

System altered.

A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       1800  314572800          1 NO  CURRENT             6622373663 09-OCT-11
         4          1       1799  209715200          1 YES INACTIVE            6622373533 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11

可以看到，group 4的狀態已經是inactive，且已被archived。

4. 刪除原有的group：
A105024@O02DMS1>alter database drop logfile group 4;

Database altered.

A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       1800  314572800          1 NO  CURRENT             6622373663 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11

A105024@O02DMS1>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         5         ONLINE  J:\INTEL_LOG\O02DMS1\REDO05.O02DMS1                                              NO
         6         ONLINE  I:\INTEL_DATA\O02DMS1\REDO06.O02DMS1                                             NO
         1         ONLINE  K:\INTEL_INDEX\O02DMS1\REDO01.O02DMS1                                       NO


group 4被刪除了，且group 1正在被使用。

注意：在資料庫層面，group 4已經被刪除了，但是在作業系統層面，原有的logfile還在，所以還得到作業系統層面把原有的logfile刪除。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26277071/viewspace-708846/，如需轉載，請註明出處，否則將追究法律責任。