Solution ID: prim78003
Oracle Background Jobs are Pausing Randomly.
Status: Reviewed
Version(s): 6.1, 6.2, 6.2.1

Fact: Background jobs are configured for the Oracle database and the job_queue_process is greater than 0.
Problem: Oracle Background Jobs are Pausing Randomly.
Problem: Changes to EPS/OBS security are not updating or commiting to the Project/Database.
Problem: Error message from the client: “The database has not been configured to run background jobs.”
Problem: When logging into Project Management: “This user is already logged in.  Please use another login name.”
Problem: Background jobs runs successfully when manually executed: exec system_monitor.
Problem: Sporadic P6 performance issues running under Oracle.
Cause:

The code in the procedure add_database_job that creates the database jobs lists the following for the interval of the database jobs:


elsif time_code = ‘M’ then


    diff_str := ‘TRUNC(SYSDATE+(‘|| to_char(time_int) || ‘/1400),”MI”)’;


    diff_int := time_int/1400;


    trunc_str := ‘MI’;


This creates the dbms_job to look like TRUNC(SYSDATE+(5/1400),’MI’).  The problem is that there are 1440 minutes in a day, not 1400.
Fix: This known problem in P6 has been corrected in 6.2.1 Service Pack 1. To download the latest Service Pack for P6 6.2.1, see: 

Solution ID: prim82785 ”What are the latest Service Packs for Primavera version 6.2.1?”
Fix: The code should be written like trunc(sysdate,’MI’)+(5/1440).

DECLARE jobno number;
BEGIN
    DBMS_JOB.SUBMIT(jobno,
    ’ppm.system_monitor;’,
    TO_DATE(‘Friday, March 13, 2009 08 53 00 AM’, ‘DAY, MONTH DD, YYYY HH:MI:SS AM’),
    ‘TRUNC(SYSDATE,’MI’) +(1/1400)’);
COMMIT;
END;
/ 


To fix, either manually update the ‘Add_database_Job’ procedure OR Run the attached scripts against your P6 6.1, P6 6.2 and P6 6.2.1 schema to correct this timestamp:


Add_database_procedure –> Add_Database_Job.SQL


NOTE: It is strongly recommended to backup the database before running scripts against the data. DISCLAIMER: The sample code listed above is provided for the convenience of the User and is not provided under warranty or supported as part of Primavera System Inc.’s Maintenance and Support Program. Users who implement the code do so at their own risk. Primavera Systems Inc. will not be responsible for any problems resulting from the use of this code. Additionally, most license agreements prohibit the direct modification of Data in the database, and as such, direct modification may void your warrantee.