-
Very often I get the message in the Alert.log:
“Thread 1 cannot allocate new log, sequence 16407
Checkpoint not complete”
Are the redo logs small?
GROUP# STATUS MEMBER BYTES
------ ------- ----------------------------------------------------------------------------------
1 D:\ORANT\DATABASE\LOG1PHC1.ORA 83886080
2 D:\ORANT\DATABASE\LOG2PHC1.ORA 83886080
3 D:\ORANT\DATABASE\LOG3PHC1.ORA 83886080
log_checkpoint_interval = 85000000
log_checkpoint_timeout = 0
checkpoint_process = true
Please give your thoughts
Thank you!
-
Yes, your logs are too small.
-
symptom: Thread cannot allocate new log sequence %s check point not complete log.ora
cause: Oracle uses two parameters to determine how to record the activity history in a database.
The MAXLOGHISTORY parameter dictates the the maximum number or history logs records the control file can maintain. This value can be seen with the "alter database backup controlfile to trace" command. This value defaults to an
operating system dependant value, typically 65535. It can only be reset by recreating the Oracle control file.
The CONTROL_FILE_RECORD_KEEP_TIME parameter dictates how old a log history record must be before it can be reused. This value defaults to 7 days if not specified in the init.ora.
During the large Conventional path import, Oracle records the insert of all records in the redo log buffers and rollback segments. This history is also recorded in the log history. If the MAXLOGHISTORY value is reached and the age of the oldest log history record is less than the CONTROL_FILE_RECORD_KEEP_TIME,
the control file cannot record the historical transaction and cannot complete the sequence check point. This will cause the above error and cause the system to freeze.
fix:
The reuse limitation imposed by aging can be prevented by performing the following steps:
1. Set the following parameter in the init.ora:
control_file_record_keep_time = 0
Setting the control_file_record_keep_time = 0 will allow log history records to reused when needed during the import without the limitation of age. This will avoid hitting the MAXLOGHISTORY ceiling value problem.
2. Cycle the database (shutdown then restart) and then reattempt the large import.
3. The parameter can be removed or returned to its original value, if desired, when the large import has been completed.
BUT , as jeff says , you can eliminate all above , by increasing the RODOLOG file size , 80MM is small and you are having checkpoints only at a log switch .
if you are not affordable of much space for redo, you wish to follow as the above
Thanks,
Madhu
