kccrsz: denied expansion of controlfile section 9 by 55826 record(s)

公司一重要数据库(该数据库每天产生200G日志，总数据大概1T左右),alter.log文件异常：
ARC0: Completed archiving  log 1 thread 1 sequence 1621849
Sun Sep 27 12:14:14 2009
kccrsz: denied expansion of controlfile section 9 by 55826 record(s)
  the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
 RECID #1556315 Recno 27136 Record timestamp
 09/23/09 16:54:23
 Thread=1 Seq#=1556315 Link-Recid=1556314
  Low
scn: 0x0005.438f930f
 09/23/09 16:54:21
 Next
scn: 0x0005.438f9373

metalink的解释：
The controlfile has two sections. The reusable and the non-reusable sections. The reusable section is used by rman.
The non-reusable section should never be overwritten if you provided enough space for the controlfile.
Problem Descrīption
-------------------

In the "alert.log", you find the following warning messages:
 
    kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
     the number of records is already at maximum value (65535)
    krcpwnc: following controlfile record written over:
     RECID #520891 Recno 53663 Record timestamp
    ...
    kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
     the number of records is already at maximum value (65535)
    krcpwnc: following controlfile record written over:
     RECID #520892 Recno 53664 Record timestamp

The database is still running.

The CONTROL_FILE_RECORD_KEEP_TIME init parameter is set to 7.

If you display the records used in the LOG HISTORY section 9 of the controlfile:

   SQL> select * from v$controlfile_record_section where type='LOG HISTORY' ;

   TYPE           RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
   -------------  ------------- ------------ ----------- ---------- ----------
   LOG HISTORY            65535        65535       33864      33863     520892

The number of RECORDS_USED has reached the maximum allowed in RECORDS_TOTAL.

Solution Descrīption
--------------------

Set the CONTROL_FILE_RECORD_KEEP_TIME to 0:

 * Insert the parameter CONTROL_FILE_RECORD_KEEP_TIME = 0 IN "INIT.ORA"

   -OR-
                        
 * Set it momentarily if you cannot shut the database down now:
                                                                          
   SQL> alter system set control_file_record_keep_time=0;                         
                           

Explanation
-----------

The default value for

   * the CONTROL_FILE_RECORD_KEEP_TIME is 7 days.

     SQL> select value from v$parameter
       2  where name='control_file_record_keep_time';
        
     VALUE
     -----
         7
 
   * the MAXLOGHISTORY database parameter has already reached the maximum of
     65535 and it cannot be increased anymore.
                                                                       
     SQL> alter database backup controlfile to trace;                            
     => in the trace file, MAXLOGHISTORY is 65535
                       
     The MAXLOGHISTORY increases dynamically when the
     CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0,
     but does not exceed 65535.  Once reached, the message appears in the
     alert.log warning you that a controlfile record is written over.

Remark
~~~~~~
Though, setting control_file_record_keep_time to zero is dangerous for users
making RMAN backups: RMAN users might not be able to restore backups.

References
----------
Note 1057885.6 RDBMS: KCCRSZ, ORA-470, ORA-449, ORA-1092, DENIED EXPANSION
                 CONTROL FILE

 

 

 Did this article help solve your problem?   Select Yes No Does Not Apply   Would you recommend this document to others?   Select Yes No Not Sure      
                      

 TIP:  Click help for a detailed explanation of this page.
 Bookmark Go to End

Subject:  RDBMS: KCCRSZ, ORA-470, ORA-449, ORA-1092, DENIED EXPANSION CONTROL FILE
  Doc ID:  Note:1057885.6 Type:  PROBLEM
  Last Revision Date:  13-MAY-2002 Status:  PUBLISHED

Problem Descrīption:
====================

While running Oracle 8.0.X you try to bring the database up after a shutdown
and receive the following error messages related to denied expansion of the
control file:

  ORA-470:  LGWR process terminated with error
    Cause:  The log writer process terminated abnormally.
   Action:  Check the accompanying messages, if any, and the background
            process trace file.
            Correct the problem mentioned in the other messages.
            Then shut down and restart the instance.
            If the trace file mentions any other background process errors,
            check the trace file for the mentioned process until the root
            error is found.

  ORA-449:  background process '' unexpectedly terminated with error ''
    Cause:  A foreground process needing service from a background process has
            discovered the background process died.
   Action:  Refer to the message code given in the message and the trace file
            for the foreground and the background processes.

 ORA-1092: ORACLE instance terminated. Disconnection forced.
    Cause: The instance connected to was terminated abnormally, probably due to
           a SHUTDOWN ABORT.
           The current process was forced to disconnect from the instance.
   Action: Contact the database administrator to determine when the instance is
           restarted.
           Attempt to reconnect after the instance is running again.

In your "alert.log" you see the following:

  kccrsz: denied expansion of controlfile section ## by ## record(s)
    the number of records is already at maximum value (65535)

  kccrsz: expanded control file section ## from # to # records
    requested to grow by # records added #blocks of records

Solution Descrīption:
=====================

Records in some sections of the control file are circularly reusable while
records in other sections are never reused. The "INIT.ORA" parameter
"CONTROL_FILE_RECORD_KEEP_TIME" applies to reusable sections. It specifies the
minimum age in days that a record must have before it can be reused. This value
defaults to 7 days if not specified in your "INIT.ORA". In the event a new
record needs to be added to a reusable section and the oldest record has not
aged enough, then the record section expands.

The parameter that controls the expansion of this file is specified during
database creation, namely "MAXLOGHISTORY". In Oracle8 versions prior to 8.0.6,
there is a known issue whenever MAXLOGHISTORY = 65535. This issue is recorded
in Bug 636522. You can avoid this issue by stopping the expansion of the
control file by setting CONTROL_FILE_RECORD_KEEP_TIME = 0. If
"CONTROL_FILE_RECORD_KEEP_TIME" is set to 0, then reusable sections never
expand and records are reused as needed.

Explanation:
============

In Oracle versions prior to 8.0.6, there was a problem associated with
controlfile expansion. If MAXLOGHISTORY is set to 65535 (64K) during database
creation (this is the default value), then when log sequence number reaches
65535, LGWR dies.  This problem is now fixed in 8.0.6.

If a patch for Bug 636522 is available, you should look into the feasibility
of applying to your database instance or upgrade to a release of Oracle 8.0.6
or higher. If this is not an option, check the "MAXLOGHISTORY" value. This
value can be retrieved by issuing the command "alter database backup
controlfile to trace" and examining the resulting trace file in your
"USER_DUMP_DEST" directory. If it is set to 65535 then you may want to consider
setting it to a lower value. Changing the "MAXLOGHISTORY" requires recreating
control file. Aftwards, you should set the parameter
"CONTROL_FILE_RECORD_KEEP_TIME" to 0 in your "INIT.ORA" and restart the
database. You can also use "ALTER SYSTEM" to change the parameter's value to an
one time only basis.

References:
===========
 
Bug 636522

Search Words:
=============

CONTROL FILE KCCRSZ ORA-470 ORA-449 ORA-1092

 

 

 

解决方法：

alter system set control_file_record_keep_time=0 scope=both;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28419/viewspace-615629/，如需转载，请注明出处，否则将追究法律责任。