oracle 修改redo size,oracle 在線修改online redo logfiles size 大小

在線修改online redo logfiles size 大小

oracle redolog size 過小有時候會導致性能問題，現在我們在線修改redolog，一般在業務量比較小的時候進行此操作

1. 首先查看當前的日志組大小，路徑名

[oracle@tora10 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 23 17:08:33 2013

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

GROUP# BYTES STATUS

---------- ---------- ----------------

1 524288000 INACTIVE

2 524288000 CURRENT

3 524288000 INACTIVE

當前的日志組大小是 512MB 文件系統，每個組成員只有1個member

SQL> select group#, member from v$logfile;

GROUP# MEMBER

---------- ----------------------------------------

1 /opt/oracle/oradata/test/redo01.log

2 /opt/oracle/oradata/test/redo02.log

3 /opt/oracle/oradata/test/redo03.log

2. 現在創建新的redolog group 4, 5, and 6 每個log大小為1024MB

SQL> alter database add logfile group 4

'/opt/oracle/oradata/test/redo04.log ' size 1024M;

SQL> alter database add logfile group 5

'/opt/oracle/oradata/test/redo05.log ' size 1024M;

SQL> alter database add logfile group 6

'/opt/oracle/oradata/test/redo06.log ' size 1024M;

3. 創建完新的日志組后，查詢所有日志組狀態:

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

GROUP# STATUS

--------- ----------------

1 INACTIVE

2 CURRENT

3 INACTIVE

4 UNUSED

5 UNUSED

6 UNUSED

當前使用的是group 2 日志組，現在通過“多次”切日志換命令將group 1,2,3狀態變為INACTIVE，

讓oracle使用新建的group4,5,6，然后將小的日志組group 1,2,3刪除

SQL> alter system switch logfile;

SQL> alter system checkpoint;

4.切換完成后，再次查詢當前的日志組狀態，當前使用的日志組是group 5

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

GROUP# STATUS

-------- ------

1 INACTIVE

2 INACTIVE

3 INACTIVE

4 INACTIVE

5 CURRENT

6 INACTIVE

當redolog group 1,2,3狀態為active時，意味着不能刪除當前redo,直至用"alter system switch log file" ，"alter system checkpoint"

命令將其切換為“inactive”狀態才能將其刪除

5.刪除group 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.

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

GROUP# BYTES STATUS

--------- --------- ----------------

4 10485760 CURRENT

5 10485760 UNUSED

6 10485760 UNUSED

6. 整個操作完成后，最好做一個backup

7.在操作系統層面刪除原來的redolog

[oracle@tora10 ~]$ cd /opt/oracle/oradata/test/

[oracle@tora10 test]$ ll -al redo*

-rw-r----- 1 oracle oinstall 524288512 Feb 23 17:15 redo01.log

-rw-r----- 1 oracle oinstall 524288512 Feb 23 17:15 redo02.log

-rw-r----- 1 oracle oinstall 524288512 Feb 23 17:15 redo03.log

-rw-r----- 1 oracle oinstall 1073742336 Feb 23 17:16 redo04.log

-rw-r----- 1 oracle oinstall 1073742336 Feb 23 17:16 redo05.log

-rw-r----- 1 oracle oinstall 1073742336 Feb 23 17:17 redo06.log

刪除小的redolog

[oracle@tora10 test]$ rm -rf redo01.log

[oracle@tora10 test]$ rm -rf redo02.log

[oracle@tora10 test]$ rm -rf redo03.log

值得注意的是，加完新的redolog group 后，要監控下alert log 查看新的redolog的切換頻率與原來情況相比較