Post seems to be running fine when issuing "status" or "show post detail" on sp_ctrl. There are no Oracle errors or other errors reported in event_log. The alert log shows the following message:
"Thread 1 cannot allocate new log, sequence <number>"
The same problem can occur with Capture process as well with same type of symptoms, namely Capture seems to be running fine whereas the log offset is not changing and it is not processing any data. And the alert.log shows the same type of message as above.
This warning usually indicates that your redo log files are either too small, or that you have too few. Basically it means that all the available on-line redo log files have been used up whilst a single checkpoint is running. At this point Oracle will wait until all the previously queued dirty blocks have been written from the buffer to disc before any of the redo log files can be considered redundant and available for re-use.
When the checkpoint is finally complete, Oracle will mark all the redo log files as available, select the next one in the cycle, and allow redo to be written out again. Of course, since the redo buffer cannot be written to a redo log file whilst the checkpoint is completing, it is quite likely that the redo buffer will rapidly fill, and all activity on the database that needs to generate redo will be suspended.
Fortunately (for 'ordinary' databases) the time required for a checkpoint is quite small, so business will resume quite rapidly. However, if you have numerous warnings about consecutive log sequences, you probably need to increase the size of your log files (although it may be sufficient just to add a few more log files). There is no very good rule of thumb that allows you to pick a good size of log file, but if you are checkpointing every one or two minutes in busy periods then you need larger log files rather than more log files.
The alert log shows something like below:
Current log# 1 seq# 50776 mem# 0: /u01/app/oracle/oradata/jupiter/redo01.log
Thread 1 cannot allocate new log, sequence 50777
Checkpoint not complete
Current log# 1 seq# 50776 mem# 0: /u01/app/oracle/oradata/jupiter/redo01.log
Thread 1 advanced to log sequence 50777
Current log# 2 seq# 50777 mem# 0: /u01/app/oracle/oradata/jupiter/redo02.log
Thread 1 cannot allocate new log, sequence 50778
Checkpoint not complete
Tune your checkpoint
1. Increase the size of the Redo logs. If your redo logs are too small, you are into frequent log switching and thus cause more frequent checkpoints. Very bad for performance.
2. Tune init.ora parameters. If you are using 9i, you only need to tune
FAST_START_MTTR_TARGET.
In you are using 8i, tune FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL.
Once the underlying Oracle issue is resolved, Shareplex process will start functioning normally and may at most require bouncing.
Disclaimer: The underlying problem is with database and not Shareplex. The above workarounds can be verified from other resources such as Metalink or you can refer it to database vendor before trying the remedies suggested above.
