19 Troubleshooting

See this section to locate information on common issues and any available workarounds.


  • See also "Troubleshooting Oracle Applications Performance Issues," Note 169935.1 on My Oracle Support.
  • During its development, the Oracle Life Sciences Data Hub 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.
This chapter includes the following topics:

Job Execution Problems

This section includes details on job execution issues and any possible workarounds.

It contains the following topics:

Job Remains at Submitted or Executing

If a job does not progress beyond a status of Submitted or Executing, try the steps in this section.

  1. Log in as Apps.
  2. Run the following SQL script:
  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. (For more information, see Stopping the OWB Service.)
  5. Stop the job queue.

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

  6. Start the OWB service.

    To do this, connect as cdr_rtrepos and run start_service.sql. (For more information, 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. (For more information, 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 Life Sciences Data Hub, 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:


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:


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 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 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 Life Sciences Data Hub 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.

  1. In the Output Description Suffix field of the Submission screen for the job in the Oracle Life Sciences Data Hub 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'