RAC 線上日誌的管理

jolly10發表於2012-08-24

由於我的測試環境+DATA沒有空間了,現在準備將聯機日誌檔案都放在+FLASH_RECOVERY_AREA
SQL> select name,total_mb,free_mb from v$asm_diskgroup;

NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
DATA 2038 34
FLASH_RECOVERY_AREA 2038 1894

[@more@]


我的環境是node1,node2下各有兩組log,每個loggroup下分別有兩個Logfile,分別存放在+DATA和+FLASH_RECOVERY_AREA

下面做的映象。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 56 5242880 2 YES INACTIVE 834944 24-AUG-12
2 1 57 5242880 2 NO CURRENT 845134 24-AUG-12
3 2 17 5242880 2 YES INACTIVE 806616 24-AUG-12
4 2 18 5242880 2 NO CURRENT 833051 24-AUG-12

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE +DATA/orcl/onlinelog/group_2.262.679060343 NO
2 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_2.258.67 YES
9060343

1 ONLINE +DATA/orcl/onlinelog/group_1.261.679060339 NO
1 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.257.67 YES
9060341

3 STALE ONLINE +DATA/orcl/onlinelog/group_3.265.679060525 NO
3 STALE ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_3.259.67 YES
9060525

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---

4 ONLINE +DATA/orcl/onlinelog/group_4.266.679060527 NO
4 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_4.260.67 YES
9060527

先建幾組log在+FLASH_RECOVERY_AREA上


SQL> alter database add logfile thread 1 group 5 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_5') size

50m;

Database altered.

SQL> alter database add logfile thread 2 group 6 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_6') size

50m;

Database altered.

SQL> alter database add logfile thread 1 group 7 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_7') size

50m;

Database altered.

SQL> alter database add logfile thread 2 group 8 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_8') size

50m;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 58 5242880 2 NO CURRENT 857127 24-AUG-12
2 1 57 5242880 2 NO INACTIVE 845134 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
4 2 18 5242880 2 NO INACTIVE 833051 24-AUG-12
5 1 0 52428800 1 YES UNUSED 0
6 2 0 52428800 1 YES UNUSED 0
7 1 0 52428800 1 YES UNUSED 0
8 2 0 52428800 1 YES UNUSED 0

8 rows selected.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE +DATA/orcl/onlinelog/group_2.262.679060343 NO
2 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_2.258.67 YES
9060343

1 ONLINE +DATA/orcl/onlinelog/group_1.261.679060339 NO
1 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.257.67 YES
9060341

3 ONLINE +DATA/orcl/onlinelog/group_3.265.679060525 NO
3 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_3.259.67 YES
9060525

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---

4 ONLINE +DATA/orcl/onlinelog/group_4.266.679060527 NO
4 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_4.260.67 YES
9060527

5 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_5 NO
6 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_6 NO
7 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_7 NO
8 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_8 NO

12 rows selected.


下面開始switch logfile,drop掉inactive的loggroup,需要注意的是要分別到兩個節點上switch logfile.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 58 5242880 2 NO ACTIVE 857127 24-AUG-12
2 1 61 5242880 2 NO CURRENT 859873 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
4 2 18 5242880 2 NO INACTIVE 833051 24-AUG-12
5 1 59 52428800 1 NO ACTIVE 859811 24-AUG-12
6 2 0 52428800 1 YES UNUSED 0
7 1 60 52428800 1 NO ACTIVE 859865 24-AUG-12
8 2 0 52428800 1 YES UNUSED 0

8 rows selected.

SQL> alter database drop logfile group 4;

Database altered.


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 5242880 2 NO CURRENT 860001 24-AUG-12
2 1 61 5242880 2 NO ACTIVE 859873 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
5 1 59 52428800 1 NO ACTIVE 859811 24-AUG-12
6 2 0 52428800 1 YES UNUSED 0
7 1 60 52428800 1 NO ACTIVE 859865 24-AUG-12
8 2 0 52428800 1 YES UNUSED 0

7 rows selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 5242880 2 NO INACTIVE 860001 24-AUG-12
2 1 61 5242880 2 NO INACTIVE 859873 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
5 1 63 52428800 1 NO INACTIVE 860089 24-AUG-12
6 2 0 52428800 1 YES UNUSED 0
7 1 64 52428800 1 NO CURRENT 860117 24-AUG-12
8 2 0 52428800 1 YES UNUSED 0

7 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

在第二個節點上執行:

SQL> alter system switch logfile;


System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system flush buffer_cache;


System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
3 2 19 5242880 2 NO INACTIVE 858328 24-AUG-12
5 1 65 52428800 1 NO INACTIVE 860433 24-AUG-12
6 2 20 52428800 1 NO INACTIVE 860499 24-AUG-12
7 1 66 52428800 1 NO CURRENT 860448 24-AUG-12
8 2 21 52428800 1 NO CURRENT 860567 24-AUG-12


SQL> alter database drop logfile group 3;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
5 1 65 52428800 1 NO INACTIVE 860433 24-AUG-12
6 2 20 52428800 1 NO INACTIVE 860499 24-AUG-12
7 1 66 52428800 1 NO CURRENT 860448 24-AUG-12
8 2 21 52428800 1 NO CURRENT 860567 24-AUG-12

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
5 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_5 NO
6 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_6 NO
7 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_7 NO
8 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_8 NO


現在+DATA上面沒有log檔案了,都放在了+FLASH_RECOVERY_AREA上面。


SQL> select name,total_mb,free_mb from v$asm_diskgroup;

NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
DATA 2038 98
FLASH_RECOVERY_AREA 2038 1702


可以看出DATA增加了98-34=64M的空間,而實際刪除的logfile只應該有4*5M=20M的空間,這是由於ASM的冗餘政策是

NORMAL的原因。

SQL> select group_number,name,total_mb,type from v$asm_diskgroup
2 ;

GROUP_NUMBER NAME TOTAL_MB TYPE
------------ ------------------------------ ---------- ------
1 DATA 2038 NORMAL
2 FLASH_RECOVERY_AREA 2038 EXTERN


SQL> column path format a30
SQL> select name, header_status, state, path, failgroup from v$asm_disk;

NAME HEADER_STATU STATE PATH FAILGROUP
---------- ------------ -------- ------------------------------ --------------------
VOL1 UNKNOWN NORMAL ORCL:VOL1
VOL2 UNKNOWN NORMAL ORCL:VOL2
VOL3 UNKNOWN NORMAL ORCL:VOL3
VOL4 UNKNOWN NORMAL ORCL:VOL4


我的ASM disk組成是VOL1~4每個1G,VOL1和VOL2組成 DATA,NORMAL的Redundancy,VOL3和VOL4組成

FLASH_RECOVERY_AREA,External的Redundancy.

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

相關文章