CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISTORY參數(shù)詳解

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 數(shù)量一致

查看創(chuàng)建數(shù)據(jù)庫(kù)DBCA開(kāi)始的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  --這里L(fēng)OG HISTORY RECORD為1，我理解是雖然是1但還是要分配一個(gè)CONTROLFILE BLOCK存放，既然分配了一個(gè)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建庫(kù)后默認(rèn)分配1個(gè)CONTROLFILE BLOCK存放292條LOG HISTORY RECORD。但后續(xù)發(fā)現(xiàn)業(yè)務(wù)增加后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.

至此，個(gè)人理解：
CONTROL FILE中類似MAXLOGHISTORY參數(shù)再10.2之后版本意義已經(jīng)不大，record保留完全通過(guò)control_file_record_keep_time控制

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

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

參考：

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參數(shù)詳解
分享網(wǎng)址：http://bm7419.com/article30/iihopo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)網(wǎng)站制作、自適應(yīng)網(wǎng)站、網(wǎng)站建設(shè)、靜態(tài)網(wǎng)站、品牌網(wǎng)站建設(shè)、動(dòng)態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)