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/

新闻标题：CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISTORY参数详解
地址分享：http://chengdu.cdxwcx.cn/article/iihopo.html