ASM的优点总结–关于日志文件调整

    日志文件是我们经常关注的,比如出现checkpoint not complete,或者是我们发现alert.log里日志切换特频繁,这个时候就要调整日志,比如事务总是等待LGWR写日志文件,日志文件切换在等归档,可能这个时候就要添加日志组了,利用ASM,可以很容易的规范日志成员的名字和日志组的编号.

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------
3 ONLINE +DISK_GROUP1/devdb/logfile/redo03.log NO
2 STALE ONLINE +DISK_GROUP1/devdb/logfile/redo02.log NO
1 ONLINE +DISK_GROUP1/devdb/logfile/redo01.log NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.273.640618259 NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.274.640618265 YES
3 ONLINE +DISK_GROUP1/devdb/logfile/group_3.redo03.log NO
1 ONLINE +DISK_GROUP2/devdb/onlinelog/group_1.277.642080697 NO
已选择7行。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ---
1 1 9 52428800 2 YES INACTIVE 254431852 20-1月 -08
2 1 10 52428800 1 YES INACTIVE 254467357 23-1月 -08
3 1 11 52428800 2 NO CURRENT 254506062 23-1月 -08
4 1 8 104857600 2 YES INACTIVE 254407862 19-1月 -08 

SQL> alter database add logfile (\'+DISK_GROUP1/devdb/onlinelog/group_5_01.log\', 2 \'+DISK_GROUP1/devdb/onlinelog/group_5_02.log\') size 50m;
数据库已更改。 

    -利用手工方式,添加日志文件组和成员

SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ---
1 1 9 52428800 2 YES INACTIVE 254431852 20-1月 -08
2 1 10 52428800 1 YES INACTIVE 254467357 23-1月 -08
3 1 11 52428800 2 NO CURRENT 254506062 23-1月 -08
4 1 8 104857600 2 YES INACTIVE 254407862 19-1月 -08
5 1 0 52428800 2 YES UNUSED 0 

SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ----------------------------------------------------
1 ONLINE +DISK_GROUP1/devdb/logfile/redo01.log NO
1 ONLINE +DISK_GROUP2/devdb/onlinelog/group_1.277.642080697 NO
2 STALE ONLINE +DISK_GROUP1/devdb/logfile/redo02.log NO
3 ONLINE +DISK_GROUP1/devdb/logfile/group_3.redo03.log NO
3 ONLINE +DISK_GROUP1/devdb/logfile/redo03.log NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.273.640618259 NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.274.640618265 YES
5 ONLINE +DISK_GROUP1/devdb/onlinelog/group_5_01.log NO
5 ONLINE +DISK_GROUP1/devdb/onlinelog/group_5_02.log NO 

已选择9行。 

SQL> alter database add logfile group 6 \'+DISK_GROUP1\' size 50m; 

数据库已更改。 

    -利用ASM的方式添加日志组和成员

SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
 ---------- ---------- ---------- ---------- ---------- --- ---------------- --
1 1 9 52428800 2 YES INACTIVE 254431852 20-1月 -08
2 1 10 52428800 1 YES INACTIVE 254467357 23-1月 -08
3 1 11 52428800 2 NO CURRENT 254506062 23-1月 -08
4 1 8 104857600 2 YES INACTIVE 254407862 19-1月 -08
5 1 0 52428800 2 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0 

已选择6行。 

SQL> select * from v$logfile order by group#; 

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -----------------------------------------------------
1 ONLINE +DISK_GROUP1/devdb/logfile/redo01.log NO
1 ONLINE +DISK_GROUP2/devdb/onlinelog/group_1.277.642080697 NO
2 STALE ONLINE +DISK_GROUP1/devdb/logfile/redo02.log NO
3 ONLINE +DISK_GROUP1/devdb/logfile/group_3.redo03.log NO
3 ONLINE +DISK_GROUP1/devdb/logfile/redo03.log NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.273.640618259 NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.274.640618265 YES
5 ONLINE +DISK_GROUP1/devdb/onlinelog/group_5_01.log NO
5 ONLINE +DISK_GROUP1/devdb/onlinelog/group_5_02.log NO
6 ONLINE +DISK_GROUP1/devdb/onlinelog/group_6.261.644775363 NO 

已选择10行。

    我们发现日志组成员添加是一个成员,ASM是否可以添加两个呢?

SQL> alter database add logfile group 7 (\'+DISK_GROUP1\',\'+DISK_GROUP1\') size 50m; 

数据库已更改。 

SQL> select * from v$logfile order by group#; 

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -----------------------------------------------------
1 ONLINE +DISK_GROUP2/devdb/onlinelog/group_1.277.642080697 NO
1 ONLINE +DISK_GROUP1/devdb/logfile/redo01.log NO
2 STALE ONLINE +DISK_GROUP1/devdb/logfile/redo02.log NO
3 ONLINE +DISK_GROUP1/devdb/logfile/redo03.log NO
3 ONLINE +DISK_GROUP1/devdb/logfile/group_3.redo03.log NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.273.640618259 NO
4 STALE ONLINE +DISK_GROUP1/devdb/onlinelog/group_4.274.640618265 YES
5 ONLINE +DISK_GROUP1/devdb/onlinelog/group_5_01.log NO
5 ONLINE +DISK_GROUP1/devdb/onlinelog/group_5_02.log NO
6 ONLINE +DISK_GROUP1/devdb/onlinelog/group_6.261.644775363 NO
7 ONLINE +DISK_GROUP1/devdb/onlinelog/group_7.259.644775421 NO
7 ONLINE +DISK_GROUP1/devdb/onlinelog/group_7.268.644775419 NO
已选择12行。

    可以看到ASM可以自动添加两个组成员