環境：ORACLE 11.2.0.4.0
系統: Oracle Linux Server release 6.5

需求：資料庫日誌檢視資料庫redo log，建議每個例項的redo不少於6組，每組日誌成員個數不限，每個日誌檔案不少於500MB；
知識點：
1、每個例項必須至少有兩個日誌組；
2、當一個組處於ACTIVE或者CURRENT的狀態時不可刪除；
3、刪除日誌組的操作只對資料庫進行更改，作業系統的檔案尚未刪除，需要手工刪除；
4、當刪除時適用DROP LOGFILE GROUP N語句時，此時GROUP N內的所有成員都將被刪除。

查詢當前日誌檔案大小：
SQL> select group#,thread#,status,bytes/1024/1024 MB from v$log;

GROUP# THREAD# STATUS MB
---------- ---------- ---------------- ----------
11 1 ACTIVE 300
12 1 ACTIVE 300
13 1 ACTIVE 300
14 1 ACTIVE 300
15 1 CURRENT 300
16 1 INACTIVE 300
21 2 CURRENT 300
22 2 INACTIVE 300
23 2 ACTIVE 300
24 2 ACTIVE 300
25 2 ACTIVE 300
26 2 ACTIVE 300

12 rows selected.

新增新日誌組每個資料檔案設定500MB：
alter database add logfile thread 1 group 1('+REDODG/hlwdsj/onlinelog/redo1a.log','+REDODG/hlwdsj/onlinelog/redo1b.log') size 500M;
alter database add logfile thread 1 group 2('+REDODG/hlwdsj/onlinelog/redo2a.log','+REDODG/hlwdsj/onlinelog/redo2b.log') size 500M;
alter database add logfile thread 1 group 3('+REDODG/hlwdsj/onlinelog/redo3a.log','+REDODG/hlwdsj/onlinelog/redo3b.log') size 500M;
alter database add logfile thread 1 group 4('+REDODG/hlwdsj/onlinelog/redo4a.log','+REDODG/hlwdsj/onlinelog/redo4b.log') size 500M;
alter database add logfile thread 1 group 5('+REDODG/hlwdsj/onlinelog/redo5a.log','+REDODG/hlwdsj/onlinelog/redo5b.log') size 500M;
alter database add logfile thread 1 group 6('+REDODG/hlwdsj/onlinelog/redo6a.log','+REDODG/hlwdsj/onlinelog/redo6b.log') size 500M;
alter database add logfile thread 2 group 7('+REDODG/hlwdsj/onlinelog/redo7a.log','+REDODG/hlwdsj/onlinelog/redo7b.log') size 500M;
alter database add logfile thread 2 group 8('+REDODG/hlwdsj/onlinelog/redo8a.log','+REDODG/hlwdsj/onlinelog/redo8b.log') size 500M;
alter database add logfile thread 2 group 9('+REDODG/hlwdsj/onlinelog/redo9a.log','+REDODG/hlwdsj/onlinelog/redo9b.log') size 500M;
alter database add logfile thread 2 group 10('+REDODG/hlwdsj/onlinelog/redo10a.log','+REDODG/hlwdsj/onlinelog/redo10b.log') size 500M;
alter database add logfile thread 2 group 11('+REDODG/hlwdsj/onlinelog/redo11a.log','+REDODG/hlwdsj/onlinelog/redo11b.log') size 500M;
alter database add logfile thread 2 group 12('+REDODG/hlwdsj/onlinelog/redo12a.log','+REDODG/hlwdsj/onlinelog/redo12b.log') size 500M;

查詢新增後的狀態
select group#,thread#,bytes/1024/1024 MB,archived,status,members from v$log;
select * from v$logfile order by 1;

切換日誌組，刪除原有日誌組
alter system switch logfile;
select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;

刪除原有的日誌檔案
$asmcmd
thread1:
rm +REDODG/HLWDSJ/ONLINELOG/group_11.276.913480051
rm +REDODG/HLWDSJ/ONLINELOG/group_11.277.913480051
rm +REDODG/HLWDSJ/ONLINELOG/group_12.278.913480157
rm +REDODG/HLWDSJ/ONLINELOG/group_12.279.913480157
rm +REDODG/HLWDSJ/ONLINELOG/group_13.280.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_13.281.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_14.282.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_14.283.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_15.284.913480161
rm +REDODG/HLWDSJ/ONLINELOG/group_15.285.913480161
rm +REDODG/HLWDSJ/ONLINELOG/group_16.286.913480161
rm +REDODG/HLWDSJ/ONLINELOG/group_16.287.913480163

thread2
rm +REDODG/HLWDSJ/ONLINELOG/group_21.288.913480167
rm +REDODG/HLWDSJ/ONLINELOG/group_21.289.913480167
rm +REDODG/HLWDSJ/ONLINELOG/group_22.290.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_22.291.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_23.292.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_23.293.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_24.294.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_24.295.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_25.296.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_25.297.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_26.298.913480173
rm +REDODG/HLWDSJ/ONLINELOG/group_26.299.913480173

至此完成，新增日誌組時有可能報錯，提示日誌檔案已存在，這時說明日誌組已刪除，系統級別的日誌檔案沒有刪除，需要手工刪除，注意先後順序
1、切換日誌組；
2、刪除INACTIVE狀態日誌組；
3、刪除日誌組物理檔案。

OK,完成。

【Oracle】 Oracle11gR2 擴充套件資料庫聯機日誌檔案

相關文章