ODI jobs gets errored with "ORA-20000: Error creating indexes in parallel"

3546557

I am getting below error and it's random and happens for different tables in different days. any help would be highly appreciated.  example  :

ODI-1519: Serial step "Start Load Plan (InternalID:53661525)" failed because child step "Global Variable Refresh (InternalID:53662525)" is in error.

ODI-1519: Serial step "Global Variable Refresh (InternalID:53662525)" failed because child step "Warehouse Load Phase (InternalID:57741525)" is in error.

ODI-1519: Serial step "Warehouse Load Phase (InternalID:57741525)" failed because child step "1 SIL Load (InternalID:57742525)" is in error.

ODI-1519: Serial step "1 SIL Load (InternalID:57742525)" failed because child step "2 SIL Fact Group (InternalID:59316525)" is in error.

ODI-1519: Serial step "2 SIL Fact Group (InternalID:59316525)" failed because child step "Parallel (InternalID:59322525)" is in error.

ODI-1518: Parallel step "Parallel (InternalID:59322525)" failed; 1 child step(s) in error, which is more than the maximum number of allowed errors (0) defined for the parallel step.  Failed child steps: 3 SIL Fact SLAJRNLS_FG (InternalID:59623525)

ODI-1519: Serial step "3 SIL Fact SLAJRNLS_FG (InternalID:59623525)" failed because child step "SLAJRNLS_FG (InternalID:59624525)" is in error.

ODI-1519: Serial step "SLAJRNLS_FG (InternalID:59624525)" failed because child step "SIL_SLAJOURNALFACT (InternalID:59625525)" is in error.

ODI-1217: Session SILOS_SIL_SLAJOURNALFACT (3096631525) fails with return code 20000.

ODI-1226: Step SIL_SLAJournalFact.W_SLA_XACT_F fails after 1 attempt(s).

ODI-1240: Flow SIL_SLAJournalFact.W_SLA_XACT_F fails while performing a Integration operation. This flow loads target table W_SLA_XACT_F.

ODI-1228: Task SIL_SLAJournalFact.W_SLA_XACT_F (Integration) fails on the target ORACLE connection BIAPPS_DW.

Caused By: java.sql.SQLException: ORA-20000: Error creating indexes in parallel, see W_ETL_EVENT_LOG for details

ORA-06512: at line 121

Charles M

Do you have any partitioned tables in your data warehouse?

Also, just a quick description of your environment ... version numbers, etc.

Regards,

Charles

Charles M

... also, look in that W_ETL_EVENT_LOG table. That will give you some details on the error.

Maybe try soemthing like this:

select * from w_etl_event_logwhere event_result like '%ORA%'--where event_result like '%eadlock%'order by event_timestamp desc;

You may also need to take a look at the database alert log, if you have access.

I have encountered this error. In my case, it was a deadlocking situation on a partitioned table. We need to understand more specifically what is happening with you.

Charles

3546557

Hi Charles,

Our is OBIA 11.1.1.8.1 version with ODI 11G. DB is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.  When I check the ETL event log table, the event result is "ORA-12805: parallel query server died unexpectedly"

Thanks,

Charles M

Ok, thanks for the additional information. You'll probably need to go at this from the database tier to start with - do you have access to the database to look at parameters? (you may want to review all the parallel settings -> SQL> show parameter parallel); and, are you able to look at the alert log from the file system? You'll need to see if anything else is in the alert log at the time of the ORA-12805 errors. Also, when did this start happening (any OBIA changes ...)? Did you recently upgrade to that 12c version?

If you have access to MOS, take a look at these notes:

Master Note for Parallel Execution 'ORA- ' Error Diagnosis (Doc ID 1348342.1) [Error:  ORA-12805 section] ~ explains the nature of the error

How to Diagnose ORA-12805 Parallel Query Server Died Unexpectedly Error (Doc ID 1348002.1) ~ shows how to trace this

Oracle Business Intelligence Applications Version 11g Performance Recommendations (Doc ID 1963225.1) [BI_Apps11g_Perf_Tech_Note V1.pdf] ~ shows recommendations for parallel settings in the db

You can do a lot of this if you have access and are comfortable with the database-side. But, you still may need to open a ticket with the db team to review/confirm the alert log and/or traces.

I'm happy to help if you want to troubleshoot further.

Regards,

Charles

Charles M

Hi - How are you doing with this?

Any questions/updates?

Charles

Charles M

Hi -

How'd everything work out with this? You get it resolved?

You can share some of the outcomes, if you think they will be useful for others. Also, mark helpful to anything here if it made a difference ... and close it out, if you've been able to fix it.

Regards,

Charles

3546557

Hi Charles,

Not yet. Our DB Administrators are a bit occupied. I'm following up with them. I'll get back you once I have an update.

Thanks..

Charles M

Ok, no worries. Thanks for the update!

Charles