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.”

Advertisements

8 Responses to control_file_record_keep_time

  1. Faraz says:

    Dear Sir , I have a issue , I have altered system spfile for management of control files , and after confirmation form the system , when i started the instance the oracle is returning error code 01033 and the instance is not starting and i not able to log on as sys ,.please advice .

  2. Mohsin says:

    Hello Sir,

    How to change this parameter

    is it dynamic parameter or need to stop start?

    Regards
    Mohsin
    Mumbai

  3. […] 3-control_file_record_keep_time […]

  4. Konan says:

    Very GOOD !!! mine works fine after recreating the password file

  5. jasa cuci sofa says:

    I’ll right away clutch your rss as I can not find your email subscription link or newsletter service.
    Do you’ve any? Please permit me recognise in order that I could subscribe.
    Thanks.

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

%d bloggers like this: