CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISTORY参数详解

开始前我们先看看官方文档对CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISTORY说明

CONTROL_FILE_RECORD_KEEP_TIME

Property	Description
Parameter type	Integer
Default value	7 (days)
Modifiable	ALTER SYSTEM
Range of values	0 to 365 (days)
Basic	No

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.

If the number of reusable records in the control file exceeds the circular reuse record limit UB4MAXVAL, then reusable records will be overwritten even if CONTROL_FILE_RECORD_KEEP_TIME has not elapsed. UB4MAXVAL is defined in the oratypes.h header file, which is found in the public directory. Its value may vary according to the operating system you are using.

大致意思是说：CONTROL_FILE_RECORD_KEEP_TIME 参数指明control file中reusable record最小保留天数

官方文档SQL Language Reference/CREATE CONTROLFILE章这样介绍MAXLOGHISTORY：

MAXLOGHISTORY Clause 

This parameter is useful only if you are using Oracle Database in ARCHIVELOG mode. Specify your current estimate of the maximum number of archived redo log file groups needed for automatic media recovery of the database. The database uses this value to determine how much space to allocate in the control file for the names of archived redo log files.

The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file. The database will continue to add additional space to the appropriate section of the control file as needed, so that you do not need to re-create the control file if your your original configuration is no longer adequate. As a result, the actual value of this parameter can eventually exceed the value you specify.

看到这里觉得有点凌乱了，如：v$log_history 中的保存条目数到底是哪个参数决定的呢？

一下说CONTROL_FILE_RECORD_KEEP_TIME  指明control file中reusable record最小保留天数

一下说由CONTROL FILE中MAXLOGHISTORY参数决定

官方文档中又查到了一个相关的view: V$CONTROLFILE_RECORD_SECTION

displays information about the control file record sections.

查看一台新装不久的DB(archive mode)：

SELECT * FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE in('LOG HISTORY');

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

LOG HISTORY 56    292   126   1     126   126

解释：

RECORD_SIZE：56   每条log history record是占56 byte

RECORDS_TOTAL：292 当前分配可保存292条log history record

RECORDS_USED：126 当前保存了126条log history record

为什么RECORDS_TOTAL是292？

CONTROL FILE中LOG HISTORY分配总大小= RECORD_SIZE* RECORDS_TOTAL=292*56 byte=16352 byte ≈16384=16K 细心的人可以发现这个值约等于16KB,而CONTROL FILE一个BLOCK SIZE正是16KB,查询V$CONTROLFILE可以看到CONTROL FILE BLOCK SIZE：

SELECT NAME,BLOCK_SIZE FROM V$CONTROLFILE

NAME  BLOCK_SIZE

/data/lenovo/control01.ctl    16384

因为一个CONTROL FILE中保留信息最小的单位是16KB, RECORD_SIZE：56

16384没法整除56，不可能保留0.5条RECORD,所以截取：

SELECT TRUNC(16384/56) FROM DUAL

292  --截取后正好为292和RECORDS_TOTAL一致，说明CONTROLFILE初始分配了一个BLOCK记录LOG HISTORY

此块算法：MOS中有说明，但应该是针对早期版本，RECORD_SIZE，CONTROLFILE BLOCK SIZE不一致，但算法一样

Why is the MAXLOGHISTORY Parameter Ignored When Creating a Controlfile? (文档 ID 217718.1)
fix: When a controlfile is created, it is not obvious how the MAXLOGHISTORY parameter relates to the number of records created in the Log History section. For example, creating a controlfile with MAXLOGHISTORY parameter set to 100 may result in the controlfile being created with 227 records in the Log History section. This is an expected behaviour. When controlfile is created, each section of it is sized initially and space allocated accordingly. The space for each section is in terms of Oracle blocks and not records. The size of a single record in each of the controlfile section is fixed. In case of log history records, the size is 36 bytes. So, 100 records (MAXLOGHISTORY) would need 3600 bytes. Depending on the size of the Oracle block size - the controlfile block size is the same as DB_BLOCK_SIZE parameter, a certain number of blocks wold be allocated to the log history record section. For example, if the DB_BLOCK_SIZE is 8192, then one block will be sufficient and accordingly will be allocated. In one block, we can have 8192/36 ~ 227 records. If the DB_BLOCK_SIZE parameter was 2048, then 2 blocks would have been needed but only 113 records would have been created.

查看v$log_history:

select count(1) from v$log_history

COUNT(1)

126    --和V$CONTROLFILE_RECORD_SECTION.RECORDS_USED 数量一致

查看创建数据库DBCA开始的alert.log:

Thu Nov 23 15:07:06 2017

QMNC started with pid=22, OS id=14684

Completed: CREATE DATABASE "lenovo"

MAXINSTANCES 8

MAXLOGHISTORY 1  --这里LOG HISTORY RECORD为1，我理解是虽然是1但还是要分配一个CONTROLFILE BLOCK存放，既然分配了一个BLOCK就可以全部使用292条RECORD

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1000

DATAFILE '/data/lenovo/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

……

目前，基本可以理解dbca建库后默认分配1个CONTROLFILE BLOCK存放292条LOG HISTORY RECORD。但后续发现业务增加后V$CONTROLFILE_RECORD_SECTION.RECORDS_TOTAL增加是为什么呢？

查到MOS另一篇文档：Master Note: Overview of Database ControlFiles (文档 ID 1493674.1) 提到：
http://blog.itpub.net/25583515/viewspace-2150930/

• You want to change the parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES, when the compatibility is earlier than 10.2.0. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.

至此，個人理解：
CONTROL FILE中类似MAXLOGHISTORY参数再10.2之后版本意义已经不大，record保留完全通过control_file_record_keep_time控制

control_file_record_keep_time：是一個guarantee值，意思是說重用部分最起碼可以被保留7天，而不是到了7天就一定被覆蓋。
使用用大致分為三種情況：
1. 在controlfile空間有富餘時，不會重用覆蓋7天前的record
2. 在controlfile空間不足時，但沒有7天前可重用覆蓋的record，就會自動擴展controlfile空間，以滿足新record需要的空間
3. 在controlfile空間不足時，會重用覆蓋7天前的record，并不會自動擴展controlfile空間

如果你想確保備份一定可以保留15天，就設置control_file_record_keep_time=16 這樣才是保險的。

参考：

https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams036.htm#CHDDBCDB

https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1088.htm#REFRN30044

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5003.htm#SQLRF01203

https://docs.oracle.com/cd/E11882_01/server.112/e10839/appg_db_lmts.htm#UNXAR015

http://blog.itpub.net/25583515/viewspace-2150930/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25583515/viewspace-2150933/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25583515/viewspace-2150933/