Oracle DBMS_JOB and DLP

book

Article ID: 160073

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

The DLP Oracle Database makes limited use of Oracle's job scheduling package, DBMS_JOB.  

Resolution

It's important not to confuse DBMS_JOB with DBMS_SCHEDULER, which is a newer, feature laden package meant to replace DBMS_JOB. 

DBMS_JOB is the Oracle equivalent of "cron" in unix.  A job is created as a scheduled operation, where at a particular time a shortline of code is executed.  This line of code can contain the execution string for an enormous package.  The line of code, or the "what" in reference to the column that contains the line of code can be scheduled to be executed once or periodically over time.

A job that is created and scheduled to execute on a periodic basis is fine as long as it is not stopped or, if it errors, is "broken". 

The job executes the "what" as the user who submitted the job entry.

To see the list of dbms_jobs currently submitted for DLP, log into SQLPlus as protect and run the following script:

select job, log_user, last_date, broken, failures, next_date, what from dba_jobs;

A job can fail 16 times before it is set to broken.

A job can be "unbroken" one of two ways:

a. either rerun the job:

EXECUTE DBMS_JOB.RUN(JOB NUMBER); -- where job number refers to the broken job.

b. unbreak the job:

EXECUTE DBMS_JOB.BROKEN(       JOB    => 10,       BROKEN => FALSE); -- in this example, the broken flag for job#10 is unset.

DLP uses DBMS_JOB to run refreshCBOStats_Job

As described in KB: 46974, refreshCBOStats gathers table and index statistics on a periodic basis to aid in the Cost Based Optimizer performance.  That period is dynamically changed dependent on the incident count in the incident table.  The when and how of refreshcbostats is managed by a single entry table called, "vontu_stats_collection_profile", which contains an incident count threshold of 5000 (when incident count reaches 5000, run refreshcbostats once every two hours), a flag for always_invalidate_cursor set to 'N' or no, and a flag from gather_all set to no.

When the dbms_job refreshcbostats_job is run, it kicks off the refrescbostats job using the parameters provided by the vontu_stats_collection_profile table, then recreates itself in dbms_jobs to run again at a later time.

Some Troubleshooting Tips (a non-exhaustive list):

1) Find out, using the scripts above, if the job is broken.  If broken, why is it broken?

2) Check for a single entry in the vontu_stats_collection_profile using:

select * from vontu_stats_collection_profile;

Should look like this:

INCIDENT_CNT_THRESHOLD A G
---------------------- - -
                  5000 N N

3) Make sure that the dbms_job is "owned" by protect.

4) Try running refreshcbostats from SQLPlus logged in as protect:

exec refreshcbostats;