I have a production database and a standby database. The production archive
logs are applied to the standby database to keep it up-to-date. I recently
installed STATSPACK on the production database, and since then I am unable
to open my standby database. I am getting an ORA-16000 (db open in
read-only mode). Upon issuing 'alter database open read only', this error
is created while trying to delete from the idl_ub1$ table (as shown in the
resulting trace file).

When I initially tried to run Oracle's spauto.sql script to automatically
schedule the running of the statspack.snap procedure, I immediately began
getting errors in my alert log. So I did a dbms_job.remove to remove the
job. The errors stopped. So I just ran the statspack.snap manually every x
minutes to gather stats.

When I determined that I couldn't open my standby database, I made sure that
there were no jobs in dba_jobs. There were not. So I decided to run
Oracle's spdrop.sql to remove statspack. Once it finished successfully, I
immediately began to get the same errors in my alert log (value too large
for column and exact fetch returned more than one row). It is as if there
is some phantom job still trying to run.

I've read some things on MetaLink that say you sometimes need to bounce the
database in order to get those dbms_jobs items to actually terminate when
they are in a broken status. But all of the things on MetaLink really don't
pertain to my situation. I've opened a Severity 1 TAR with Oracle, but they
are taking their own sweet time about it.

Has anyone encountered anything similar in your shop? Thanks for any
suggestions.

-John

Oracle LazyDBA home page