Shulou(Shulou.com)06/01 Report--

Before we begin, let's take a look at the official documentation on CONTROL_FILE_RECORD_KEEP_TIME and MAXLOGHISTORY.

CONTROL_FILE_RECORD_KEEP_TIME

Why is the MAXLOGHISTORY Parameter Ignored When Creating a Controlfile? (document 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, 100records (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 records. If the DB_BLOCK_SIZE parameter was 2048, then 2 blocks would have been needed but only 113 records would have been created.

View v$log_history:

Select count (1) from v$log_history

COUNT (1)

126-same number as V$CONTROLFILE_RECORD_SECTION.RECORDS_USED

View the alert.log that started with the creation of the database DBCA:

Thu Nov 23 15:07:06 2017

QMNC started with pid=22, OS id=14684

Completed: CREATE DATABASE "lenovo"

MAXINSTANCES 8

MAXLOGHISTORY 1-here the LOGHISTORY RECORD is 1. I understand that although it is 1, you still need to allocate a CONTROLFILE BLOCK for storage. Since you have assigned a BLOCK, you can use all 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

……

At present, it is basically understandable that after the establishment of the dbca database, the default allocation of 1 CONTROLFILE BLOCK to store 292 LOG HISTORY RECORD. But why did the V$CONTROLFILE_RECORD_SECTION.RECORDS_TOTAL increase after the subsequent discovery of the increase in business?

Find another MOS document: Master Note: Overview of Database ControlFiles (document ID 1493674.1) mentions:

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.

At this point, one person understands:

The version meaning of the similar MAXLOGHISTORY parameter in CONTROL FILE after 10.2 is no longer significant, and the record retention is completely controlled by control_file_record_keep_time.

Control_file_record_keep_time: is a guarantee value, which means that the reuse part of the code can be retained for 7 days, rather than being overridden after 7 days.

Usage is roughly divided into three situations:

1. When there is a wealth in the controlfile space, the record of 7 days ago will not be reused.

two。 When controlfile space is insufficient, but there is no record that can be reused 7 days ago, controlfile space will be automatically expanded to make up for the space needed by the new record.

3. When the controlfile space is insufficient, the record of 7 days ago will be reused, and the controlfile space will not be automatically expanded.

If you want to make sure that you can keep the guarantee for 15 days, set up control_file_record_keep_time=16 as a guarantee.

Reference:

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/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.