修改online redo日誌檔案大小

要將日誌檔案組1，2，3從100M修改到200M方法如下
$ ls
control01.ctl  drsys01.dbf    redo01.log     temp01.dbf     xdb01.dbf
control02.ctl  example01.dbf  redo02.log     tools01.dbf
control03.ctl  indx01.dbf     redo03.log     undotbs01.dbf
cwmlite01.dbf  odm01.dbf      system01.dbf   users01.dbf
$ ls -l redo01.log
-rw-r-----   1 oracle   dba       104858112 Jan 04 16:34 redo01.log
$ du -m redo01.log
100.00  redo01.log

登陸SQLPLUS
--------------------
$ sqlplus /nolog

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Jan 4 16:48:40 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1377274120 bytes
Fixed Size                   743688 bytes
Variable Size             738197504 bytes
Database Buffers          637534208 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.
SQL> select group# from v$log;

    GROUP#
----------
         1
         2
         3

建立日誌檔案組4,5
---------------------------
SQL> alter database add logfile group 4 '/opt/oracle/product/9i/oradata/oracle9i/redo04.log' size 200M;

Database altered.

SQL> alter database add logfile group 5 '/opt/oracle/product/9i/oradata/oracle9i/redo05.log' size 200M;

Database altered.

將日誌檔案組切換到新建立的4,5日誌檔案組上
--------------------------------------------------------------
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 CURRENT

刪除已有的1,2,3日誌檔案組
---------------------------------------
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

增加新的日誌檔案1,2,3，並設定容量為200M
------------------------------------------------------------
SQL> alter database add logfile group 1 '/opt/oracle/product/9i/oradata/oracle9i/redo01.log' size 200M;

Database altered.

SQL> alter database add logfile group 2 '/opt/oracle/product/9i/oradata/oracle9i/redo02.log' size 200M;

Database altered.

SQL> alter database add logfile group 3 '/opt/oracle/product/9i/oradata/oracle9i/redo03.log' size 200M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED
         4 INACTIVE
         5 INACTIVE

刪除剛剛增加的日誌檔案組4,5
-----------------------------------------
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

注意：每一步刪除drop操作，都需要手工刪除os中的實體檔案。
           另外注意備份控制檔案。

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