control_file_record_keep_time

One of my production databases running 9.2.0.8 was generating an excessive amount of redo information to the point where the redo logs were switching every 4 to 5 minutes for more than 12 hours. Each redo log group is 100MB in size. Originally sized this way so as to log switch every 30 to 45 minutes with normal activity. Found the information below in the alert log during my research.

kccrsz: expanded controlfile section 11 from 601 to 615 records requested to grow by 6 record(s); added 1 block(s) of records

Went to Metalink for additional information on this message which eventually lead me to the CONTROL_FILE_RECORD_KEEP_TIME parameter. This initialization parameter determines the minimum number of days that records are retained in the control file before they are candidates for being overwritten. You can dynamically adjust the setting as shown below.

ALTER SYSTEM SET control_file_record_keep_time=3;

Went to Google and found information about automatic controlfile resizing from Steve Adams.

Setting this parameter appropriately in relation to the frequency of your database backups is significant in order to ensure smooth recovery. Searched AskTom.com where I found these two paragraphs as valuable information.

“If you are using RMAN with just the control file and no recovery catalog, it’ll totally depend on how fast you cycle through and reuse reusable records in the control file. In any case — you can always recover manually regardless (you do not lose the ability to recover, we just might not be able to supply you the filenames).”

“Set your control_file_record_keep_time to be at least one day GREATER then the period of time between your backups, else there is a chance that an archive record gets aged out during the backup which thoroughly confuses the situation.”