If you find session is waiting on event Log File Switch Completion (Checkpoint Incomplete), the problem is log switch can not complete because check point process is writing dirty db buffer to data files.
the solution is increasing log file size and/or adding more log group(s).
this problem will stop redo generating and make database freeze.
sql examples:
1. add more log groups.
ALTER DATABASE
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
SIZE 4M;
2. force switch log file
alter system switch logfile;
3. drop log group
alter database drop logfile group 1;
reference:
http://oracledba-vinod.blogspot.com/2009/10/log-file-switch-completion-checkpoint.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo003.htm
if you can not drop a logfile group, do a checkpoint first.
ReplyDeletealter system checkpoint
Then drop logfile group.