Sometimes if the search indexing job hasn't run for a while for any number of reasons, it will create a large backlog of items to process which the normal job cannot do. Symptoms of this include ORA-07445 errors and a large number of items in the ctxsys.dr$pending table in the Vista database.
How to check for number of items in the pending queue
On the vista database log in as the sys or ctxsys user and run:
If the number returned is more than a few hundred thousand, then it is likely that this needs to be cleaned up manually before the search indexing job can run normally.
Steps for cleaning up the pending queue
- Stop normal search indexing background job
- Check the currently running background jobs as server admin and make sure that a job isn't running. Wait for it to complete if it is.
- Execute this sql to insert a pending job to keep indexing jobs from firing off (this will set a job to start in 30 days, it will be removed later to resume the normal schedule)
- Clear the dr$waiting queue (it isn't needed for this procedure)
- Backup the contents of dr$pending into a temporary table
- Truncate the dr$pending table
- Execute this script in sqlplus as sys on the Vista database
- You can use a shell script to run this script for a number of iterations if there are a lot to do. Something like this would work.
- If the script fails and generates an ORA-7445 error, you need to re-run the last set with a smaller chunk size.
- To reset the last run, execute the following:
- Then decrease the chunk size in the script and continue running.
- Optimize the search indexes
- Once everything is done you can drop the temporary table
- Re-enable the regular search indexing job by deleting the row the was inserted in step #1
- The easiest way to do this is to search for the row that contains 'Manu' for the field arg1 in the background_jobs table. (There should be only one)