Archive for the ‘jobs’ tag
I don’t think there’s a punch-line scheduled, is there?
I’ve been fighting a really bizarre issue with Oracle Warehouse Builder 11.1.0.7 this last month or so. It looks like it finally got resolved today. The resolution has implications for other people so I’m putting it up here, partly for them and partly so I don’t forget the oddity again.
The warehouse Builder architecture is covered in the docs here but the relevant diagram I reproduce below for the purposes of this blog article.
OWB has a repository containing metadata, plus source and target deployment locations. In addition a java application known variously as the OWWB runtime, the control centre service, rtp etc runs on the database server o/s. The database server keeps track of the status of this service using some scheduled jobs. In addition Oracle provide some scripts in $ORACLE_HOME/owb/rtp/sql to start/stop and diagnose this service.
Our OWB service would not start – and did not produce error logs as expected. The service_doctor script reported:
SQL> @service_doctor
Role set.
All PL/SQL packages and functions are valid
Platform properties have been loaded correctly
Platform location has been seeded correctly
NLS messages have been loaded correctly
>>>>>> The platform service is not available
Service script is accessible to the database server
Connection information stored within the repository is correct
Whilst trying to start the service hangs for 3 minutes ( a timeout in the script I believe) and then fails
SQL> @start_service
Role set.
Not Available
Diagnostics:
started service using command "ORACLE_HOME/owb/bin/unix/ru
n_service.sh -automatic 1 ORACLE_HOME OWBSYS HOSTNAME PORT SERVICE_NAME"
PL/SQL procedure successfully completed.
Running the owbcollect script to collect diagnostic details was unenlightening – it indicated that all was well, apart from some invalid objects in the target schemas, and that the jobs had been created. At this point my analyst was running out of hair, there were no logfiles to investigate, the owbcollect indicated all was well with the world – and looked the same as another instance that was running fine. We rechecked JOB_QUEUE_PROCESSES and AQ_TM_PROCESSES and recreated the advanced queues used by the service for good measure.
It was at this point I remembered problems we had had elsewhere with enterprise manager grid control. Under upgrade circumstances EM GC can fail to create partitions for uploaded metrics to be stored in, this happens when the dbms_jobs that create the partitions are either missing or disabled. The jobs weren’t missing in this case, but they sure weren’t being executed. It turns out that as of 10g there are now 3 ways to disable dbms_job execution.
1) set JOB_QUEUE_PROCESSES
2) for AQ jobs set AQ_TM_PROCESSES
3) Disable DBMS_SCHEDULER.
Item 3 had hit us. Because the new and old job queue systems use the same co-ordinator disabling the new system also disables the old one. So if you are facing a situation where scheduled jobs aren’t running and all the old fashioned parameters look OK take a look at DBA_SCHEDULER_GLOBAL_ATTRIBUTE -0 if the scheduler is disabled there will be a row in that view indicating it.