Sunday, September 30, 2012

Checkpoint Not Complete


Error Will be In AlertSID.log Like This :
/u01/app/oracle/oradata/redo04.log
Thu Jan 14 22:12:55 2011
Thread 1 cannot allocate new log, sequence 46352
Checkpoint not complete


To Solve this Issue you Can Do More Than One Thing But All of the Solution is Simple :

Solution One :

Modify Database Parameter archive_lag_target Like This :

alter system set archive_lag_target=0 scope=both;

Solution two :

1-backup Full Database
2-Check Free Disk Space
3-Start Do the Following :

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#, status from v$log;

Now You need to Work Step by Step On Inactive Logs , and Stat Drop Them like the following :

alter database drop logfile group 1;
Re add the Log File

alter database add logfile group 1 ( ‘/Log-Name01.log‘,/Log-Name02.log’ ) size 75M

alter system switch logfile; / Alter System Checkpoint
 Do this For The Group that you have , and rearrange them again By Adding new Group contain more than one redo log inside them


Simple !!!

Enjoy
Osama Mustafa

2 comments:

  1. Hello Osama!! thanks for this blog, it was so helpfully, just i have a question, can i "reuse" the file from redo log dropped?, or drop the file too using "alter database drop logfile group 1;", i tried but appears this error "SQL Error: ORA-01184: logfile group 1 already exists 01184. 00000 - logfile group %s already exists", so after drop the file with sql command, also i removed the file with "rm" in S.O. command line. Thanks for your comments.

    ReplyDelete
  2. You need to delete it on OS level as well, Oracle will not do that for you when you drop redo log file from sqlplus

    ReplyDelete