it caused by too many invalid archive log entries are kept in control file.
to resolve the problem just run the following command with RMAN.
RMAN> delete NOPROMPT expired archivelog all ;the following message could be found in alert.log
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #14147320 Recno 46258 Record timestamp
05/09/07 05:20:02
Object type=245 Object recid=24025 Object timestamp=
04/05/07 12:25:34
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #14147321 Recno 46259 Record timestamp
05/09/07 05:20:02
Object type=245 Object recid=24026 Object timestamp=
04/05/07 13:01:33
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #14147322 Recno 46260 Record timestamp
05/09/07 05:20:02
Object type=245 Object recid=24027 Object timestamp=
04/05/07 18:42:45
...
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #4125758 Recno 41618 Record timestamp
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.
alter system set control_file_record_keep_time=0 scope=spfile;
附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.
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 '
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
No comments:
Post a Comment