Skip to end of metadata
Go to start of metadata

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

  1. Stop normal search indexing background job
    1. 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.
    2. 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)
  2. Clear the dr$waiting queue (it isn't needed for this procedure)
  3. Backup the contents of dr$pending into a temporary table
  4. Truncate the dr$pending table
  5. Execute this script in sqlplus as sys on the Vista database
    Icon

    The chunk_size variable must be tuned. I've had good luck with 100000, sometimes it will run out of memory before completing and a smaller number must be used, try 40,000 in that case.

    Icon

    You can watch the progress using the temporary table.
    The update_flag column in the temporary table is as follows:

    • 0 = Not yet processed
    • 1 = Currently processing
    • 2 = Proccessed. Once everything is flagged a 2, it's done.
  • 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.
  1. Optimize the search indexes
  2. Once everything is done you can drop the temporary table
  3. 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)

That should do it!

  • No labels