19 Troubleshooting

This section contains the following topics:

• Job Execution Problems

• Database Core Dumps during Object Creation

• Query Exceeds 200 Rows

• Table Auditing (Journaling) Not Working

• Enabling a Trace on a Job

Note:

See also "Troubleshooting Oracle Applications Performance Issues," Note 169935.1 on My Oracle Support.

Note:

During its development, the Oracle Life Sciences Data Hub (Oracle LSH) was known internally as CDR. Therefore many Oracle LSH-related directories, files, scripts, parameters, and so on are named CDR or contain the string cdr. Please think of CDR as a synonym for Oracle LSH.

Job Execution Problems

This section includes the following topics:

• Job Remains at Submitted or Executing

• Oracle Warehouse Builder Processes Do Not Start

• Job Runs But Does Not Upload Outputs

• Job Log File Does Not Contain Info after a Long or Failed Execution

• Job Fails to Obtain Service

• Report Set Jobs Do Not Produce Any Output when using SAS 9.2

Job Remains at Submitted or Executing

If a job does not progress beyond a status of Submitted or Executing, try the following procedure:

1. Log in as Apps.

2. Run the following SQL script:

   SELECT  q.JOB_PROCESSING_FLAG_RC,  q.LOG_MESSAGE_ID,TO_CHAR(q.LOG_STAMP),q.message  FROM cdr_job_queues_log q ORDER BY q.LOG_STAMP DESC
   

3. Check the resulting message. If the message is similar to the following, proceed to Step 4:

   • dequeued from control Q: _JOBCONTROL_STOP

   • Caught Exception (-20200) (ORA-20200: *** ERROR: cdr_exe_owb_execution.startedJob: SQL error: ORA-06508: PL/SQL: could not find program unit being called ORA-25228: timeout or end-of-fetch during message

4. Stop the OWB service.

   To do this, connect as cdr_rtrepos and run stop_service.sql (see "Stopping the OWB Service").

5. Stop the job queue.

   To do this, connect as apps and run stopq.sql (see "Stopping the Job Queue").

6. Start the OWB service.

   To do this, connect as cdr_rtrepos and run start_service.sql (see "Starting the OWB Service").

   You should get a message that the service is available.

7. Start the job queue.

   To do this, connect as apps and run startq.sql (see "Starting the Job Queue").

8. Rerun the query given in Step 2.

   You should get a message:

   dequeued from control Q: _JOBCONTROL_ENABLE

   It may take some time for the message to appear.

9. Rerun the job.

Oracle Warehouse Builder Processes Do Not Start

If Oracle Warehouse Builder (OWB) processes do not start, check the init.ora file for the setting of the job_queue_processes parameter. Oracle recommends a setting of 10, but you may require more. If you have too few job queue processes started, OWB processes may not be able to start.

Job Runs But Does Not Upload Outputs

If a job runs on an external engine (SAS, Oracle Reports, or XML Publisher) and generates outputs but doesn't upload the outputs to Oracle LSH, check the DP Server log. The DP Server log directory is on the computer where the DP Server is installed, usually in the following location:

$CDR_HOME/<DBName>/log

Job Log File Does Not Contain Info after a Long or Failed Execution

If a job executes for a long time or fails and the job log does give enough information, check the Oracle Warehouse Builder (OWB) log.

The OWB log directory is located on the machine where the database is installed, usually in the following location:

$OWB_HOME/log

Job Fails to Obtain Service

If a job that requires an external engine (SAS, Oracle Reports, or XML Publisher) fails at Obtainservice (or you get a No Data Found error at this point) run the script checkJSapps to see if the DP Server is running; see "Checking the Distributed Processing Server Service".

If the DP Server is not running, follow instructions at "Starting the Job Queue".

Report Set Jobs Do Not Produce Any Output when using SAS 9.2

If you are using SAS 9.2 and find that your Report Set jobs fail and do not generate any output, you must install the one-off patch 6993271. See "Apply Required Oracle Applications Patches" in the Oracle Life Sciences Data Hub Installation Guide for details on how to install the patch.

Database Core Dumps during Object Creation

If your database core dumps during the creation of Domains and other objects, the cause may be a bug in Oracle Text (bug number 5587976). At the time of publication of this document, patch 5587976 fixes this issue on Linux x86. If you are running on a different platform, work with Oracle Support to get a backport to RDBMS 10.1.0.4 for your platform.

Query Exceeds 200 Rows

If you get the error message "Query has exceeded 200 rows. Potentially more rows exist, please restrict your query" in a situation where you cannot restrict your query—for example, you have more than 200 Domains created, or more than 200 Applications in a Domain, or more than 200 Work Areas in an Application Area, or more than 200 object instances in a Work Area—then you may want to reset the Oracle Applications profile FND: View Object Max Fetch Size. This profile is set to 200 by default. See Chapter 3, "Setting Profile Values" for further information.

However, use caution because changing this value affects all your Oracle Applications queries as well as all your Oracle LSH queries.

Table Auditing (Journaling) Not Working

Document number 105624.1 on My Oracle Support is a troubleshooting guide for auditing (also called journaling).

Enabling a Trace on a Job

To help diagnose performance issues, you can create a trace file for any job. Do the following:

1. In the Output Description Suffix field of the Submission screen for the job in the Oracle LSH user interface, enter the following string: !enable_trace!

   When you submit the job, the system creates a trace file for the job. The trace file name contains the identifier LSHJOBT.

2. (Optional) Enter a string in the Output Title Suffix field. The system adds that string to the trace file name immediately before the .trc extension.

3. Run the job.

4. Use the following query to find the location of the file:

   select value from v$parameter where name='user_dump_dest'