在生產環境中，高效能伺服器的RAC將預設日誌50M擴大，有助於效能提升

1.檢視當前預設日誌組

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 821 52428800 1 YES INACTIVE
2 1 822 52428800 1 NO CURRENT
3 2 241 52428800 1 YES INACTIVE
4 2 242 52428800 1 NO CURRENT

2.新增日誌組和成員

節點1
alter database add logfile thread 1 group 11 ('+DATA/funddb/onlinelog/redo11a.log','+DATA/funddb/onlinelog/redo11b.log') size 500M;
alter database add logfile thread 1 group 12 ('+DATA/funddb/onlinelog/redo12a.log','+DATA/funddb/onlinelog/redo12b.log') size 500M;
alter database add logfile thread 1 group 13 ('+DATA/funddb/onlinelog/redo13a.log','+DATA/funddb/onlinelog/redo13b.log') size 500M;

節點2
alter database add logfile thread 2 group 14 ('+DATA/funddb/onlinelog/redo14a.log','+DATA/funddb/onlinelog/redo14b.log') size 500M;
alter database add logfile thread 2 group 15 ('+DATA/funddb/onlinelog/redo15a.log','+DATA/funddb/onlinelog/redo15b.log') size 500M;
alter database add logfile thread 2 group 16 ('+DATA/funddb/onlinelog/redo16a.log','+DATA/funddb/onlinelog/redo16b.log') size 500M;

3.切換日誌

節點1
SQL> alter system switch logfile;

System altered.

節點2
SQL> alter system switch logfile;

System altered.

4.查詢並等待

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 821 52428800 1 YES INACTIVE
2 1 822 52428800 1 YES ACTIVE
3 2 241 52428800 1 YES INACTIVE
4 2 242 52428800 1 YES ACTIVE
11 1 823 524288000 2 NO CURRENT
12 1 0 524288000 2 YES UNUSED
13 1 0 524288000 2 YES UNUSED
14 2 243 524288000 2 NO CURRENT
15 2 0 524288000 2 YES UNUSED
16 2 0 524288000 2 YES UNUSED

10 rows selected.

等待......

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 821 52428800 1 YES INACTIVE
2 1 822 52428800 1 YES INACTIVE
3 2 241 52428800 1 YES INACTIVE
4 2 242 52428800 1 YES INACTIVE
11 1 823 524288000 2 NO CURRENT
12 1 0 524288000 2 YES UNUSED
13 1 0 524288000 2 YES UNUSED
14 2 243 524288000 2 NO CURRENT
15 2 0 524288000 2 YES UNUSED
16 2 0 524288000 2 YES UNUSED

10 rows selected.

5.刪除舊日誌組

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> alter database drop logfile group 4;

Database altered.

6.確認完成日誌組的擴大

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
11 1 823 524288000 2 NO CURRENT
12 1 0 524288000 2 YES UNUSED
13 1 0 524288000 2 YES UNUSED
14 2 243 524288000 2 NO CURRENT
15 2 0 524288000 2 YES UNUSED
16 2 0 524288000 2 YES UNUSED

本文建立時間為2016年8月3日 15:25:00
作者：洪卓輝 kingsql
Oracle DBA，資深培訓師
hongzhuohui@kingsql.com
轉載請註明出處

鄙視某些小型網站剽竊文章後修改作者名的可恥行為

【kingsql分享】RAC擴大online日誌組大小

相關文章