Oracle11g更换日志组

　 空间管理您的位置: ITPUB个人空间 ? comebackdog的个人空间 ? 日志RAC环境下的redo日志组重建上一篇 / 下一篇 ?2012-03-08 11:21:54 / 个人分类：oracle查看( 239 ) / 评论( 1 ) / 评分( 0 / 0 )os：RHES 4.6db：.0参考文章/rulev5/article/details/7164548查询redo日志的轮换情况rac环境下使用如下语句（实例1，如果查询其他实例修改a.thread#=1这里就好了）：? ?select b.recid,?to_char(b.first_time, 'dd-mon-yy hh24:mi:ss') start_time,?a.recid,?to_char(a.first_time, 'dd-mon-yy hh24:mi:ss') end_time,?round(((a.first_time - b.first_time) * 25) * 60, 2) minutes?from v$log_history a, v$log_history b?where a.recid = b.recid + 1 and a.thread#=b.thread# and a.thread#=1?order by a.first_time desc;?查询结果（截取一部分切换较频繁的时段）? RECID START_TIME RECID END_TIME ?MINUTES---------- ----------------------- ---------- ----------------------- ---------- 185765 07-3月 -12 19:42:20 185766 07-3月 -12 19:42:26 .1 185764 07-3月 -12 19:42:14 185765 07-3月 -12 19:42:20 .1 185761 07-3月 -12 19:42:07 185762 07-3月 -12 19:42:11 ? .07 185760 07-3月 -12 19:42:02 185761 07-3月 -12 19:42:07 ? .09 185757 07-3月 -12 19:41:51 185758 07-3月 -12 19:41:57 .1 185756 07-3月 -12 19:41:48 185757 07-3月 -12 19:41:51 ? .05 185753 07-3月 -12 19:41:40 185754 07-3月 -12 19:41:46 .1 185752 07-3月 -12 19:41:35 185753 07-3月 -12 19:41:40 ? .09 185751 07-3月 -12 19:41:29 185752 07-3月 -12 19:41:35 .1 185748 07-3月 -12 19:41:22 185749 07-3月 -12 19:41:25 ? .05 185747 07-3月 -12 19:41:16 185748 07-3月 -12 19:41:22 .1 185744 07-3月 -12 19:41:05 185745 07-3月 -12 19:41:11 .1 185743 07-3月 -12 19:41:02 185744 07-3月 -12 19:41:05 ? .05 185740 07-3月 -12 19:40:53 185741 07-3月 -12 19:40:59 .1 185739 07-3月 -12 19:40:47 185740 07-3月 -12 19:40:53 .1 185736 07-3月 -12 19:40:39 185737 07-3月 -12 19:40:42 ? .05 185735 07-3月 -12 19:40:36 185736 07-3月 -12 19:40:39 ? .05 185734 07-3月 -12 19:40:31 185735 07-3月 -12 19:40:36 ? .09 185731 07-3月 -12 19:40:20 185732 07-3月 -12 19:40:25 ? .09 185730 07-3月 -12 19:40:13 185731 07-3月 -12 19:40:20 ? .12 185727 07-3月 -12 19:40:05 185728 07-3月 -12 19:40:10 ? .09 185726 07-3月 -12 19:40:00 185727 07-3月 -12 19:40:05 ? .09 185725 07-3月 -12 19:39:53 185726 07-3月 -12 19:40:00 ? .12 185722 07-3月 -12 19:39:49 185723 07-3月 -12 19:39:51 ? .03 185721 07-3月 -12 19:39:44 185722 07-3月 -12 19:39:49 ? .09 185718 07-3月 -12 19:39:34 185719 07-3月 -12 19:39:39 ? .09 185717 07-3月 -12 19:39:31 185718 07-3月 -12 19:39:34 ? .05 185714 07-3月 -12 19:39:24 185715 07-3月 -12 19:39:29 ? .09 185713 07-3月 -12 19:39:19 185714 07-3月 -12 19:39:24 ? .09 185710 07-3月 -12 19:39:09 185711 07-3月 -12 19:39:13 ? .07 185709 07-3月 -12 19:39:06 185710 07-3月 -12 19:39:09 ? .05这个时间段内平均不到每分钟就有1次切换，redo log切换太过频繁。检查当前日志组SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 90065? ? 1 YES ACTIVE 9.7016E+12 07-3月 -12 ?2 1 90066? ? 1 NO? CURRENT 9.7016E+12 07-3月 -12 ?3 2 95453? ? 1 YES INACTIVE 9.7016E+12 07-3月 -12 ?4 2 95455? ? 1 YES ACTIVE 9.7016E+12 07-3月 -12 ?5 2 95452? 104857600 ? 1 YES INACTIVE 9.7016E+12 07-3月 -12 ?6 2 95456? 104857600 ? 1 NO? CURRENT 9.7016E+12 07-3月 -12 ?7 2 95454? 104857600 ? 1 YES ACTIVE 9.7016E+12 07-3月 -12 ?8 1 90063? 104857600 ? 1 YES INACTIVE 9.7016E+12 07-3月 -12 ?9 1 90064? 104857600 ? 1 YES ACTIVE 9.7016E+12 07-3月 -12 10 1 90062? 104857600 ? 1 YES INACTIVE 9.7016E+12 07-3月 -12一共有10个日志组，每个组1个成员，4个50M和6个100M。按照平均每0.5分钟（高峰期，其实看到的切换时间更短，我暂取0.5分这个值）切换一次50M的redo log，平均一分钟会写入100M（50/0.5）的redo，如果要半小时切换一次redo log就要求redo log大小为30×100M=3000M约3G。这个rodo文件太大了，先增加到500M即平均5分钟切换一次，运行观察一段时间，新增加8个redo组每组两个成员各500M被删除原有的日志组。查看当前日志文件位置SQL> select * from v$logfile; GROUP# STATUS? TYPE MEMBER IS_---------- ------- ------- ---------------------------------------------------------------- 2 ? ONLINE +DATA01/zjcsc/onlinelog/group_2.262.729380855 NO 1 ? ONLINE +DATA01/zjcsc/onlinelog/group_1.261.729380855 NO 3 ? ONLINE +DATA01/zjcsc/onlinelog/group_3.265.729380935 NO 4 ? ONLINE +DATA01/zjcsc/onlinelog/group_4.266.729380935 NO 5 ? ONLINE +DATA01/zjcsc/onlinelog/group_5.315.729393001 NO 6 ? ONLINE +DATA01/zjcsc/onlinelog/group_6.316.729393021 NO 7 ? ONLINE +DATA01/zjcsc/onlinelog/group_7.317.729393041 NO 8 ? ONLINE +DATA01/zjcsc/onlinelog/group_8.318.729393053 NO 9 ? ONLINE +DATA01/zjcsc/onlinelog/group_9.319.729393091 NO 10 ? ONLINE +DATA01/zjcsc/onlinelog/group_10.320.729393109 NO10 rows selected.保险起见先测试了下：在asm里指定文件名创建redo日志组SQL> alter database add logfile thread 1 group 11 ('+DATA01/zjcsc/onlinelog/group_11a')size 50M;Database altered.SQL> select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;NAME STATE TYPE? TOTAL_MB FREE_MB USABLE_FILE_MB OFFLINE_DISKS------------------------------ ----------- ------ ---------- ---------- -------------- -------------DATA01 CONNECTED EXTERN 1126400 ?106255 106255 0删除该组后发现，asm的空间并未释放，因为创建时候指定文件名后再删除时不会删除该文件，会保留在asm里。SQL> alter database drop logfile group 11;Database altered.SQL> select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;NAME STATE TYPE? TOTAL_MB FREE_MB USABLE_FILE_MB OFFLINE_DISKS------------------------------ ----------- ------ ---------- ---------- -------------- -------------DATA01 CONNECTED EXTERN 1126400 ?106255 106255 0这是asm特性造成的，所有不建议自己指定文件名。还是得用OMF的方式来添加redo日志组，要怎么做呢？这样做就好了：添加日志组group11，里面有一个成员ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ('+DATA01') SIZE 50M;添加一个成员在日志组group11里ALTER DATABASE ADD LOGFILE MEMBER '+DATA01' TO GROUP 11;删除这个日志组的一个成员查找到该成员的文件名后删除ALTER DATABASE DROP LOGFILE MEMBER '+DATA01/zjcsc/onlinelog/group_11.390.777314119';删除整个日志组alter database drop logfile group 11;添加日志组group11，里面有两个成员ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ('+DATA01', '+DATA01') SIZE 50M;删除整个日志组alter database drop logfile group 11;正式操作：准备：检查asm空间大小SQL> select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;NAME STATE TYPE? TOTAL_MB FREE_MB USABLE_FILE_MB OFFLINE_DISKS------------------------------ ----------- ------ ---------- ---------- -------------- -------------DATA01 CONNECTED EXTERN 1126400 ?106255 106255 01、添加日志组group11，两个成员，给实例1.SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ('+DATA01', '+DATA01') SIZE 500M;Database altered.2、添加日志组group12，两个成员，给实例2.SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 12 ('+DATA01', '+DATA01') SIZE 500M;Database altered.######################################################################这里强调一下，THREAD 2和THREAD 1是指这个日志组要指定给那个实例，####1，2为实例号，RAC环境下必须为每个日志组指定他们的实例。 ##?####################################################################3、检查日志情况SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 90388? ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?2 1 90389? ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?3 2 95683? ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?4 2 95685? ? 1 NO? CURRENT 9.7016E+12 08-3月 -12 ?5 2 95682? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?6 2 95681? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?7 2 95684? 104857600 ? 1 YES ACTIVE 9.7016E+12 08-3月 -12 ?8 1 90386? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?9 1 90387? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 10 1 90390? 104857600 ? 1 NO? CURRENT 9.7016E+12 08-3月 -12 11 1 0? 524288000 ? 2 YES UNUSED 0 12 2 0? 524288000 ? 2 YES UNUSED 04、删除日志组group1和group3（按照实例分别删除）SQL> alter database drop logfile group 1;Database altered.SQL> alter database drop logfile group 3;Database altered.SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?2 1 90389? ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?4 2 95685? ? 1 YES ACTIVE 9.7016E+12 08-3月 -12 ?5 2 95682? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?6 2 95681? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?7 2 95684? 104857600 ? 1 YES ACTIVE 9.7016E+12 08-3月 -12 ?8 1 90386? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?9 1 90387? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 10 1 90390? 104857600 ? 1 YES ACTIVE 9.7016E+12 08-3月 -12 11 1 90391? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 12 2 95686? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -125、重建日志组group1，两个成员，给实例1.SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATA01', '+DATA01') SIZE 500M;Database altered.6、重建日志组group3，两个成员，给实例2.SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+DATA01', '+DATA01') SIZE 500M;Database altered.7、检查日志情况SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 0? 524288000 ? 2 YES UNUSED 0 ?2 1 90389? ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?3 2 0? 524288000 ? 2 YES UNUSED 0 ?4 2 95685? ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?5 2 95682? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?6 2 95681? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?7 2 95684? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?8 1 90386? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?9 1 90387? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 10 1 90390? 104857600 ? 1 YES ACTIVE 9.7016E+12 08-3月 -12 11 1 90391? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 12 2 95686? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -128、删除日志组group2和group4.SQL> alter database drop logfile group 2;Database altered.SQL> alter database drop logfile group 4;Database altered.SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 0? 524288000 ? 2 YES UNUSED 0 ?3 2 0? 524288000 ? 2 YES UNUSED 0 ?5 2 95682? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?6 2 95681? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?7 2 95684? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?8 1 90386? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?9 1 90387? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 10 1 90390? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 11 1 90391? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 12 2 95686? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -129、重建日志组group2，两个成员，给实例1.SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATA01', '+DATA01') SIZE 500M;Database altered.10、重建日志组group4，两个成员，给实例2SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATA01', '+DATA01') SIZE 500M;Database altered.11、检查日志状态SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 0? 524288000 ? 2 YES UNUSED 0 ?2 1 0? 524288000 ? 2 YES UNUSED 0 ?3 2 0? 524288000 ? 2 YES UNUSED 0 ?4 2 0? 524288000 ? 2 YES UNUSED 0 ?5 2 95682? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?6 2 95681? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?7 2 95684? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?8 1 90386? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 ?9 1 90387? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 10 1 90390? 104857600 ? 1 YES INACTIVE 9.7016E+12 08-3月 -12 11 1 90391? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 12 2 95686? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -1212 rows selected.12、删除日志组5，6，7，8，9，10SQL> alter database drop logfile group 5;Database altered....其他内容省略SQL> alter database drop logfile group 10;Database altered.8 rows selected.13、重建日志组5，7，6，8SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATA01', '+DATA01') SIZE 500M;Database altered.SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+DATA01', '+DATA01') SIZE 500M;Database altered.SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ('+DATA01', '+DATA01') SIZE 500M;Database altered.SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+DATA01', '+DATA01') SIZE 500M;Database altered.14、查询日志状态SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 0? 524288000 ? 2 YES UNUSED 0 ?2 1 0? 524288000 ? 2 YES UNUSED 0 ?3 2 0? 524288000 ? 2 YES UNUSED 0 ?4 2 0? 524288000 ? 2 YES UNUSED 0 ?5 1 0? 524288000 ? 2 YES UNUSED 0 ?6 1 0? 524288000 ? 2 YES UNUSED 0 ?7 2 0? 524288000 ? 2 YES UNUSED 0 ?8 2 0? 524288000 ? 2 YES UNUSED 0 11 1 90391? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 12 2 95686? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -1215、日志切换分别登录两个实例执行一下操作：切换redo日志到下一个日志组SQL> alter system switch logfile;System altered.更新检查点，讲redo log的内容写入数据文件，归档redo log。SQL> alter system checkpoint;System altered.SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 90392? 524288000 ? 2 YES ACTIVE 9.7016E+12 08-3月 -12 ?2 1 90393? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 ?3 2 95687? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 ?4 2 0? 524288000 ? 2 YES UNUSED 0 ?5 1 0? 524288000 ? 2 YES UNUSED 0 ?6 1 0? 524288000 ? 2 YES UNUSED 0 ?7 2 0? 524288000 ? 2 YES UNUSED 0 ?8 2 0? 524288000 ? 2 YES UNUSED 0 11 1 90391? 524288000 ? 2 YES INACTIVE 9.7016E+12 08-3月 -12 12 2 95686? 524288000 ? 2 YES INACTIVE 9.7016E+12 08-3月 -1210 rows selected.ok日志切换正常，检查点正常。16、删除日志组group11和group12SQL> alter database drop logfile group 11;Database altered.SQL> alter database drop logfile group 12;Database altered.SQL> select * from v$log; GROUP# THREAD#? SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ?1 1 90392? 524288000 ? 2 YES ACTIVE 9.7016E+12 08-3月 -12 ?2 1 90393? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 ?3 2 95687? 524288000 ? 2 NO? CURRENT 9.7016E+12 08-3月 -12 ?4 2 0? 524288000 ? 2 YES UNUSED 0 ?5 1 0? 524288000 ? 2 YES UNUSED 0 ?6 1 0? 524288000 ? 2 YES UNUSED 0 ?7 2 0? 524288000 ? 2 YES UNUSED 0 ?8 2 0? 524288000 ? 2 YES UNUSED 08 rows selected.17、至此完成redo日志组的重建工作，每个实例各有4个日志组，每组2个成员，各500M。检查日志文件情况：SQL> select * from v$logfile; GROUP# STATUS? TYPE MEMBER IS_---------- ------- ------- ---------------------------------------------------------------------------------------------------- --- ?2 ONLINE? +DATA01/zjcsc/onlinelog/group_2.266.777378229 NO ?1 ONLINE? +DATA01/zjcsc/onlinelog/group_1.265.777377921 NO ?1 ONLINE? +DATA01/zjcsc/onlinelog/group_1.261.777377921 NO ?2 ONLINE? +DATA01/zjcsc/onlinelog/group_2.262.777378231 NO ?5 ONLINE? +DATA01/zjcsc/onlinelog/group_5.318.777378469 NO ?5 ONLINE? +DATA01/zjcsc/onlinelog/group_5.317.777378471 NO ?7 ONLINE? +DATA01/zjcsc/onlinelog/group_7.316.777378499 NO ?7 ONLINE? +DATA01/zjcsc/onlinelog/group_7.315.777378501 NO ?6 ONLINE? +DATA01/zjcsc/onlinelog/group_6.320.777378983 NO ?6 ONLINE? +DATA01/zjcsc/onlinelog/group_6.319.777378983 NO ?3 ONLINE? +DATA01/zjcsc/onlinelog/group_3.394.777377939 NO ?3 ONLINE? +DATA01/zjcsc/onlinelog/group_3.395.777377941 NO ?4 ONLINE? +DATA01/zjcsc/onlinelog/group_4.396.777378245 NO ?4 ONLINE? +DATA01/zjcsc/onlinelog/group_4.397.777378247 NO ?8 ONLINE? +DATA01/zjcsc/onlinelog/group_8.398.777378995 NO ?8 ONLINE? +DATA01/zjcsc/onlinelog/group_8.399.777378997 NO检查下asm使用情况，使用了8个G，减掉原有的的800M，共使用了7226M的asm空间SQL> select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;NAME STATE TYPE? TOTAL_MB FREE_MB USABLE_FILE_MB OFFLINE_DISKS------------------------------ ----------- ------ ---------- ---------- -------------- -------------DATA01 CONNECTED EXTERN 1126400 ? 99029 ?99029 0注意：在删除日志组的时候务必保持当前每个实例必须有2个日志组存在，这样才能完成正常的切换。后续：注意观察相同时段内的日志切换情况