Control File Is Growing

Today, the following message appears into a alert.log for a database I manage;

 
Expanded controlfile section 28 from 560 to 1120 records
Requested to grow by 560 records; added 8 blocks of records

To see what is the section 28, we have to look at the view V$CONTROLFILE_RECORD_SECTION. Although this view does not tell us the number of sections. In this case Oracle makes us easy, we only have to count the rows in order. With the following query, which has the virtual rownum we get the sections;

 
SELECT
  ROWNUM,
  TYPE,
  RECORDS_TOTAL,
  RECORDS_USED
FROM 
  V$CONTROLFILE_RECORD_SECTION

If we execute this statement, we get the following output, Looking at line 28;

 
    ROWNUM TYPE                         RECORDS_TOTAL RECORDS_USED
__________ ____________________________ _____________ ____________
         1 DATABASE                                 1            1
         2 CKPT PROGRESS                           19            0
         3 REDO THREAD                             16            1
         4 REDO LOG                                32            8
         5 DATAFILE                              4500         2926
         6 FILENAME                              4629         3013
         7 TABLESPACE                            4500          456
         8 TEMPORARY FILENAME                    4500           57
         9 RMAN CONFIGURATION                       7            1
        10 LOG HISTORY                          18150        13026
        11 OFFLINE RANGE                        10210         1794
        12 ARCHIVED LOG                         18150        13032
        13 BACKUP SET                            1021         1021
        14 BACKUP PIECE                          1003         1003
        15 BACKUP DATAFILE                       1056            0
        16 BACKUP REDOLOG                        3424         3424
        17 DATAFILE COPY                         1000         1000
        18 BACKUP CORRUPTION                     1113            0
        19 COPY CORRUPTION                       1021            0
        20 DELETED OBJECT                        4084         4084
        21 PROXY COPY                            1000            0
        22 BACKUP SPFILE                          226            0
        23 DATABASE INCARNATION                   145            1
        24 FLASHBACK LOG                         2048            0
        25 RECOVERY DESTINATION                     1            0
        26 INSTANCE SPACE RESERVATION            1055            1
        27 REMOVABLE RECOVERY FILES              1000            0
        28 RMAN STATUS                           1120         1120
        29 THREAD INSTANCE NAME MAPPING            16           16
        30 MTTR                                    16           16
        31 DATAFILE HISTORY                        52            0
        32 STANDBY DATABASE MATRIX                 10           10
        33 GUARANTEED RESTORE POINT              2048            0
        34 RESTORE POINT                         2080            0

Thus, RMAN STATUS section is what has grown, it makes sense because of this database is having changes in RMAN backups since a couple of weeks. As a reminder, in the Control File are two types of records, Circular reuse records and Noncircular reuse records. From the doc;

Circular reuse records

These records contain noncritical information that is eligible to be overwritten if needed. When all available record slots are full, the database either expands the control file to make room for a new record or overwrites the oldest record. Examples include records about:

LOG HISTORY
OFFLINE RANGE
ARCHIVED LOG
BACKUP SET
BACKUP PIECE
BACKUP DATAFILE
BACKUP REDOLOG
DATAFILE COPY
BACKUP CORRUPTION
COPY CORRUPTION
DELETED OBJECT
PROXY COPY

Noncircular reuse records

These records contain critical information that does not change often and cannot be overwritten. Examples of information include tablespaces, data files, online redo log files, and redo threads. Oracle Database never reuses these records unless the corresponding object is dropped from the tablespace. Examples of non-circular controlfile sections (the ones that can only expand)

DATABASE (info)
CKPT PROGRESS (Checkpoint progress)
REDO THREAD, REDO LOG (Logfile)
DATAFILE (Database File)
FILENAME (Datafile Name)
TABLESPACE
TEMPORARY FILENAME
RMAN CONFIGURATION

NOTE;
———
Reading and writing the control file blocks is different from reading and writing data blocks. For the control file, Oracle Database reads and writes directly from the disk to the program global area (PGA). Each process allocates a certain amount of its PGA memory for control file blocks.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s