Database writer processes idle until 'Checkpoint not complete' message (even with multiple redo logs
Our application generates a steady data stream of 400 MB/hr. We have setup 10 redo log groups with a size 100 MB each. Therefore, we roll over a log approx. every 15 min. Because of limited disk space, we have a retention job which deletes 'old' data after a certain time. When this job runs it is doing a delete cascade based on time stamps. So far so good... When the data removal process is running the redo log interval shortens to 15s and after 9 log rollovers we see the infamous 'Checkpoint not completed' message. We observed with top and iotop how the log writer (ora_lgwr_XXXX) and database writers (ora_dbwX_XXXX) behave and found that the database writers are completely idle until the point of 'Checkpoint not complete'. |
1