V$CONTROLFILE_RECORD_SECTION-LOG HISTORY-MAXLOGHISTORY
之所以对LOG HISTORY 这个有兴趣,是源于一个查询日志切换频率的SQL,如下:
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 |
SELECT to_char(first_time, 'mm/dd') "Date", SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) "h00", SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) "h01", SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) "h02", SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) "h03", SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) "h04", SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) "h05", SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) "h06", SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) "h07", SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) "h08", SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) "h09", SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) "h10", SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) "h11", SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) "h12", SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) "h13", SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) "h14", SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) "h15", SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) "h16", SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) "h17", SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) "h18", SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) "h19", SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) "h20", SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) "h21", SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) "h22", SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) "h23", COUNT(*) "Total" FROM v$log_history WHERE trunc(first_time) > to_date('&time', 'yyyymmdd') GROUP BY to_char(first_time, 'mm/dd') ORDER BY 2; |
这个SQL可以查询出日志切换的频率,从中可发现业务高峰,也可判断日志大小设置是否合理等。
问题是这样的:
①在20多套生产库中,为什么,有一些库的日志切换记录的保留时间很长,一个月甚至两个月以上,有的库保留的很短,7天。
②是什么控制它的保留期限呢?
在手工建库的时候我们会发现其中有一个参数“MAXLOGHISTORY”这个值其实就是保留日志切换记录的条数。
参数文件中,参数“control_file_record_keep_time”是控制保留的时间。
我们先测试一下参数“MAXLOGHISTORY”
测试环境:
操作系统:OEL 5.6 64bit
数据库:Oracle11.2.0.4
1)为了方便测试,我们采用手工建库,步骤参见:http://xxf-home.net/?p=135
注:MAXLOGHISTORY参数设置为1,意思是,最大的保存条数为1
2)数据库创建完成后,执行以下命令,我们可以发现records_total不是我们设置的1而是292,日志已经切换的记录数为60
1 2 3 4 5 6 7 |
SYS@PROD> select type,records_total,records_used from v$controlfile_record_section where type='LOG HISTORY'; TYPE RECORDS_TOTAL RECORDS_USED ---------------------------- ------------- ------------ LOG HISTORY 292 60 SYS@PROD> |
3)我们手工切换日志200次
1 2 3 4 5 6 7 8 9 10 |
SYS@PROD>BEGIN 2 FOR i IN 1 .. 200 LOOP 3 EXECUTE IMMEDIATE 'alter system switch logfile'; 4 END LOOP; 5 END; 6 / PL/SQL procedure successfully completed. SYS@PROD> |
4)再次查看动态性能视图v$controlfile_record_section,我们可以看到,最大值依然为292,不过切换的次数已经为260次了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SYS@PROD> select type,records_total,records_used from v$controlfile_record_section where type='LOG HISTORY'; TYPE RECORDS_TOTAL RECORDS_USED ---------------------------- ------------- ------------ LOG HISTORY 292 260 SYS@PROD> SYS@PROD> select count(*),max(first_time),min(first_time) from v$log_history; COUNT(*) MAX(FIRST_TIME) MIN(FIRST_TIME) ---------- ------------------- ------------------- 260 2014-11-19 15:27:48 2014-11-19 14:39:49 SYS@PROD> |
5)再次切换50次日志,目的是切换的次数超出最大值292
1 2 3 4 5 6 7 8 9 10 |
SYS@PROD>BEGIN 2 FOR i IN 1 .. 50 LOOP 3 EXECUTE IMMEDIATE 'alter system switch logfile'; 4 END LOOP; 5 END; 6 / PL/SQL procedure successfully completed. SYS@PROD> |
6)再次查看动态性能视图v$controlfile_record_section
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SYS@PROD>select type,records_total,records_used from v$controlfile_record_section where type='LOG HISTORY'; TYPE RECORDS_TOTAL RECORDS_USED ---------------------------- ------------- ------------ LOG HISTORY 2920 310 SYS@PROD>select count(*),max(first_time),min(first_time) from v$log_history; COUNT(*) MAX(FIRST_TIME) MIN(FIRST_TIME) ---------- ------------------- ------------------- 310 2014-11-19 15:31:54 2014-11-19 14:39:49 SYS@PROD> |
7)通过上面的测试,我们可以得出如下结论(当然,只测了一次,也不能完全说明问题:)
a) MAXLOGHISTORY这个参数,初始值在292以下的,那么默认的最大值就为292,原因是,分配记录数是以块来分配的,1个块约能存放292条记录,后续又做过验证,如果初始值设置为293,那么,MAXLOGHISTORY即为584,也就是,默认值是按照块的数量来计算最终的大小,当然,如果初始值特别大,是否还按照块块来分配,有兴趣可以测测;
b) 当记录日志切换记录数超出最大值时,同时又没有过期的数据,那么最大值得限制会增大10倍;
c) 最大值只会扩大不会缩小,也就是说,一旦MAXLOGHISTORY变大,那么将永远变大(除非重建控制文件);
d) 如果想让日志切换记录保存1个月的,那么只需要修改参数“control_file_record_keep_time”为31即可,无需理会MAXLOGHISTORY的大小。