Oracle11g R2 RAC增加日志文件组
默认情况下，创建4组日志组，每个实例2组日志组，显然，每个实例2组日志理论上一定不是最佳方案。本测试就是基于默认情况下的日志组添加、删除的测试。
1、查看日志组情况
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 CURRENT 20 3 2 1 CURRENT 20 4 2 1 INACTIVE 20 SYS@RACDB2>col member for a50 SYS@RACDB2>select group#,member from v$logfile order by 1; GROUP# MEMBER ---------- -------------------------------------------------- 1 +DATA/racdb/onlinelog/group_1.261.838213365 2 +DATA/racdb/onlinelog/group_2.262.838213367 3 +DATA/racdb/onlinelog/group_3.266.838214121 4 +DATA/racdb/onlinelog/group_4.267.838214123 SYS@RACDB2> |
2、添加日志组需求如下：
- 每个实例增加到4组日志
- 日志组1、2、3、4分配给实例1；日志组5、6、7、8为实例2
- 每个日志组2个成员，每个成员大小为30M
如果需求是这样的，那么首先每个实例要先创建两组日志作为过渡，为什么要创建两组日志组用于过渡？因为，1、2、3、4组日志要全部删除重新创建，在删除时有一些注意事项，如下：
- 日志组为active和current状态时不可以删除
- 日志组在数据库级别删除后操作系统上的文件不会被级链删除
- 对于一个Oracle数据库实例，至少要包含两个联机重做日志组（每个实例创建两组日志组就是为了满足第这个需求）
3、添加日志组（注：在创建日志组时，在一个节点创建即可）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SYS@RACDB1>alter database add logfile thread 1 group 9 '+DATA' size 20M ; Database altered. SYS@RACDB1>alter database add logfile thread 2 group 10 '+DATA' size 20M ; Database altered. SYS@RACDB1>alter database add logfile thread 1 group 11 '+DATA' size 20M ; Database altered. SYS@RACDB1>alter database add logfile thread 2 group 12 '+DATA' size 20M ; Database altered. SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 CURRENT 20 3 2 1 CURRENT 20 4 2 1 INACTIVE 20 9 1 1 UNUSED 20 10 2 1 UNUSED 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> |
4、切换日志组
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 |
--节点1：切换到9号日志组 SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 CURRENT 20 3 2 1 CURRENT 20 4 2 1 INACTIVE 20 9 1 1 UNUSED 20 10 2 1 UNUSED 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> SYS@RACDB1> SYS@RACDB1> SYS@RACDB1>alter system switch logfile; System altered. SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 ACTIVE 20 3 2 1 CURRENT 20 4 2 1 INACTIVE 20 9 1 1 CURRENT 20 10 2 1 UNUSED 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> SYS@RACDB1>alter system checkpoint; System altered. SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 INACTIVE 20 3 2 1 CURRENT 20 4 2 1 INACTIVE 20 9 1 1 CURRENT 20 10 2 1 UNUSED 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> --节点2：切换到10号日志组 SYS@RACDB2>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 INACTIVE 20 3 2 1 CURRENT 20 4 2 1 INACTIVE 20 9 1 1 CURRENT 20 10 2 1 UNUSED 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> SYS@RACDB2>alter system switch logfile; System altered. SYS@RACDB2>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 INACTIVE 20 3 2 1 ACTIVE 20 4 2 1 INACTIVE 20 9 1 1 CURRENT 20 10 2 1 CURRENT 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> SYS@RACDB2>alter system checkpoint; System altered. SYS@RACDB2>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 1 INACTIVE 20 2 1 1 INACTIVE 20 3 2 1 INACTIVE 20 4 2 1 INACTIVE 20 9 1 1 CURRENT 20 10 2 1 CURRENT 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 8 rows selected. SYS@RACDB1> |
5、删除日志组1、2、3、4日志组（在一个节点操作即可）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SYS@RACDB1>alter database drop logfile group 1; Database altered. SYS@RACDB1>alter database drop logfile group 2; Database altered. SYS@RACDB1>alter database drop logfile group 3; Database altered. SYS@RACDB1>alter database drop logfile group 4; Database altered. SYS@RACDB1> --查看删除后日志组的情况 SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 9 1 1 CURRENT 20 10 2 1 CURRENT 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 SYS@RACDB1> |
6、按照需求创建日志组
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
alter database add logfile thread 1 group 1 ('+DATA','+DATA') size 30M; alter database add logfile thread 1 group 2 ('+DATA','+DATA') size 30M; alter database add logfile thread 1 group 3 ('+DATA','+DATA') size 30M; alter database add logfile thread 1 group 4 ('+DATA','+DATA') size 30M; alter database add logfile thread 2 group 5 ('+DATA','+DATA') size 30M; alter database add logfile thread 2 group 6 ('+DATA','+DATA') size 30M; alter database add logfile thread 2 group 7 ('+DATA','+DATA') size 30M; alter database add logfile thread 2 group 8 ('+DATA','+DATA') size 30M; SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 2 UNUSED 30 2 1 2 UNUSED 30 3 1 2 UNUSED 30 4 1 2 UNUSED 30 5 2 2 UNUSED 30 6 2 2 UNUSED 30 7 2 2 UNUSED 30 8 2 2 UNUSED 30 9 1 1 CURRENT 20 10 2 1 CURRENT 20 11 1 1 UNUSED 20 12 2 1 UNUSED 20 12 rows selected. SYS@RACDB1> |
7、删除过渡的日志组（按照当前日志组具体情况进行切换、删除）
目前数据库的情况，可以先删除11、12两组日志（在其中一个节点完成即可），然后再做切换，切换后再删除9、10日志组
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
SYS@RACDB1>alter database drop logfile group 11; Database altered. SYS@RACDB1>alter database drop logfile group 12; Database altered. SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 2 UNUSED 30 2 1 2 UNUSED 30 3 1 2 UNUSED 30 4 1 2 UNUSED 30 5 2 2 UNUSED 30 6 2 2 UNUSED 30 7 2 2 UNUSED 30 8 2 2 UNUSED 30 9 1 1 CURRENT 20 10 2 1 CURRENT 20 10 rows selected. SYS@RACDB1> --节点1切换日志 SYS@RACDB1>alter system switch logfile; System altered. SYS@RACDB1>alter system checkpoint; System altered. SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 2 CURRENT 30 2 1 2 UNUSED 30 3 1 2 UNUSED 30 4 1 2 UNUSED 30 5 2 2 UNUSED 30 6 2 2 UNUSED 30 7 2 2 UNUSED 30 8 2 2 UNUSED 30 9 1 1 INACTIVE 20 10 2 1 CURRENT 20 10 rows selected. SYS@RACDB1> --节点2切换日志 SYS@RACDB2>alter system switch logfile; System altered. SYS@RACDB2>alter system checkpoint; System altered. SYS@RACDB2>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 2 CURRENT 30 2 1 2 UNUSED 30 3 1 2 UNUSED 30 4 1 2 UNUSED 30 5 2 2 CURRENT 30 6 2 2 UNUSED 30 7 2 2 UNUSED 30 8 2 2 UNUSED 30 9 1 1 INACTIVE 20 10 2 1 INACTIVE 20 10 rows selected. SYS@RACDB2> --删除9、10两组日志组（在其中一个节点完成即可） SYS@RACDB1>alter database drop logfile group 9; Database altered. SYS@RACDB1>alter database drop logfile group 10; Database altered. SYS@RACDB1> |
8、哦了，完美完成需求
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log; GROUP# THREAD# MEMBERS STATUS SIZE_M ---------- ---------- ---------- ---------------- ---------- 1 1 2 CURRENT 30 2 1 2 UNUSED 30 3 1 2 UNUSED 30 4 1 2 UNUSED 30 5 2 2 CURRENT 30 6 2 2 UNUSED 30 7 2 2 UNUSED 30 8 2 2 UNUSED 30 8 rows selected. SYS@RACDB1> --查看asm中的日志文件 ASMCMD> pwd +data/racdb/ONLINELOG ASMCMD> ls -l Type Redund Striped Time Sys Name ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_1.266.839351927 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_1.267.839351925 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_2.262.839351983 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_2.276.839351985 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_3.277.839351987 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_3.278.839351989 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_4.279.839351995 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_4.280.839351997 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_5.281.839352001 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_5.282.839352001 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_6.283.839352005 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_6.284.839352007 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_7.285.839352009 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_7.286.839352009 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_8.287.839352011 ONLINELOG UNPROT COARSE FEB 12 17:00:00 Y group_8.288.839352015 ASMCMD> |