關於”kccrsz“錯誤處理一則

故障現象：

使用者日誌出現：“kccrsz: denied expansion of controlfile section 19 by 2026 record(s)the number of records is already at maximum value (65535)”警告。

察看alert日誌：

Wed Aug 13 08:31:55 2008
ARCH: Completed archiving  log 1 thread 1 sequence 59888
Wed Aug 13 08:38:37 2008
DISTRIB TRAN 28.71645F61707061000000000000000000000000000000000000000000000000000005046A48A0584E000130C5
  is local tran 49.18.22252 (hex=31.12.56ec))
  delete pending forced rollback tran, scn=1118960303765 (hex=104.873b3e95)
Wed Aug 13 08:44:22 2008
kccrsz: expanded controlfile section 19 from 61668 to 63710 records
  requested to grow by 1907 record(s); added 5 block(s) of records
Wed Aug 13 08:44:40 2008
kccrsz: requested to expand controlfile section 19 by 1970 record(s)
  limited expansion to 1825 records since maximum numrecs is 65535
kccrsz: expanded controlfile section 19 from 63710 to 65535 records
  requested to grow by 1970 record(s); added 5 block(s) of records
Wed Aug 13 08:44:53 2008
Thread 1 advanced to log sequence 59890
  Current log# 3 seq# 59890 mem# 0: /dev/vx/rdsk/sidatadg/silog31
  Current log# 3 seq# 59890 mem# 1: /dev/vx/rdsk/sidatadg/silog32
Wed Aug 13 08:44:53 2008
ARCH: Evaluating archive   log 5 thread 1 sequence 59889
ARCH: Beginning to archive log 5 thread 1 sequence 59889
Creating archive destination LOG_ARCHIVE_DEST_1: '/siarclog/arch_1_59889.arc'
Wed Aug 13 08:44:53 2008
ARC1: Evaluating archive   log 5 thread 1 sequence 59889
ARC1: Unable to archive log 5 thread 1 sequence 59889
      Log actively being archived by another process
Wed Aug 13 08:44:55 2008
ARCH: Completed archiving  log 5 thread 1 sequence 59889
Wed Aug 13 08:44:57 2008
Thread 1 advanced to log sequence 59891
  Current log# 4 seq# 59891 mem# 0: /dev/vx/rdsk/sidatadg/silog41
  Current log# 4 seq# 59891 mem# 1: /dev/vx/rdsk/sidatadg/silog42
Wed Aug 13 08:44:57 2008
ARCH: Evaluating archive   log 3 thread 1 sequence 59890
ARCH: Beginning to archive log 3 thread 1 sequence 59890
Creating archive destination LOG_ARCHIVE_DEST_1: '/siarclog/arch_1_59890.arc'
Wed Aug 13 08:44:57 2008
ARC1: Evaluating archive   log 3 thread 1 sequence 59890
ARC1: Unable to archive log 3 thread 1 sequence 59890
      Log actively being archived by another process
Wed Aug 13 08:44:58 2008
ARCH: Completed archiving  log 3 thread 1 sequence 59890
Wed Aug 13 08:46:52 2008
kccrsz: denied expansion of controlfile section 19 by 2026 record(s)
  the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
 RECID #250626 Recno 42131 Record timestamp
 08/06/08 13:19:02
  Object type=245  Object recid=73090  Object timestamp=
 07/09/08 13:20:07
kccrsz: denied expansion of controlfile section 19 by 2026 record(s)
  the number of records is already at maximum value (65535)

附metalink上的解釋：

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.

一開始，我建議使用者關閉control_file_record_keep_time，因此：

 SQL> alter system set control_file_record_keep_time=0 SCOPE=BOTH;

可是使用者說，資料庫當前使用的RMAN備份。

一開始我以為使用者用TSM FOR ORACLE進行備份，只是呼叫RMAN介面；後來我瞭解，使用者是通過恢復目錄，備份到本地磁碟，然後將本地磁碟以檔案形式用TSM備份到帶庫。

那麼上面的做法走不通。

我讓使用者使用：

select * from v$controlfile_record_section

察看結果：

20

DELETED OBJECT

20

65535

65535

50975

50974

325004

罪魁禍首是“DELETED OBJECT”，那麼經察看http://space.itpub.net/7882490/viewspace-202051

和作者本人的幫助，發現這個不是什麼嚴重問題。“control_file_record_keep_time”引數可以預設7天內覆蓋。所以只是一個警告。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8334342/viewspace-426918/，如需轉載，請註明出處，否則將追究法律責任。