RAC 在线日志的管理

由于我的测试环境+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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1059257/