Long Running Jobs In ODI, the Oracle Database session cannot be killed and the only way is to bounce the Oracle Database. (Doc ID 2003849.1)

Last updated on SEPTEMBER 27, 2016

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.8.1 and later
Information in this document applies to any platform.

Symptoms

Some of the ODI jobs are running for a very long time and the overall batch is not completing on time.  Most of the time, jobs will hang while collecting the statistics.

When the DBAs try to kill the session, they are unable to kill it and the only way is to bounce the database.

 

Environment Details

Oracle Database 11.2.0.4 (with Enterprise Manager 12c) with the OBI Applications 11.1.1.8.1 component which is ODI.

ODI works with an Oracle Database whilst doing a load.  The database hangs when performing this load.  This have just installed OBI Apps in the last week and this is the first time they are doing loads, so this has not worked in the past.

The customer stated that the workaround (unaccetpable) is as follows:

1. Stop Load Plan
2. Bounce database to remove session and clean the database.
3. Database Session cannot be killed on kill -9 and the machine has to be shutdown.
4. Restart ODI load – run through to completion

If the load is run again then the database is in a waiting state (stopped).

The machine has 20 cores and 96 GB of memory so is not likely to be suffering from memory/processor issues

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms