CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISTORY参数详解
开始前我们先看看官方文档对CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISTORY说明
CONTROL_FILE_RECORD_KEEP_TIME
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/
