Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

PPM 9.1.1 Import Users report fails with ORA-03111 break received on communication channel

SOLVED
Go to solution
Highlighted
EricLEdberg
Super Collector

PPM 9.1.1 Import Users report fails with ORA-03111 break received on communication channel

I am writing the user account creation/update interface for our new PPM instance and have a update/create script using the KNTA_USERS_INT method that has worked ok for up to about 1000 users during development this last week.

 

Today, I scaled it up update/create all 9,000 users that will be using PPM and it failed with a:

  • java.sql.SQLException: ORA-03111: break received on communications channel

I'm wondering if this is a TIMEOUT issue with long-running scripts (it failed after about 5 minutes).

Could not locate any worthwhile error messages other than the Execution Log History

 

Have not tried to repeat the script as I want to identify the root cause of this failure first.

 

When reviewing the Oracle KNTA_USERS_INT table most rows are in PROCESS_PHASE=4 and PROCESS_STATUS=2 if that means anything....

 

Is this a HP PPM trouble support ticket, a possible issue with data integrity, or possibly an Oracle issue?

 

Our HP PPM architect and programmer are gone for a time.

 

Eric Edberg

3 REPLIES
dirkf
Honored Contributor

Re: PPM 9.1.1 Import Users report fails with ORA-03111 break received on communication channel

Hi Eric,

 

it seems to me that a reasonable assumption would be that the connection is timing out due to the volume of the results returned. A couple of points:

- I have checked the knowledge base and found that in the tools available to me, no ORA-03111 has been reported, so this seems to be a new incident to be classified.

- I have googled the error and found an interesting thread here: http://royontechnology.blogspot.com/2009/06/mysterious-ora-03111-error.html

- have you tested with the regular User interface report without the script to see the results there? If that is the case, that would be a valid support case.

 

I hope that the one or other hint may help.

 

Best regards, have a great day,

Dirk

 

EricLEdberg
Super Collector

Re: PPM 9.1.1 Import Users report fails with ORA-03111 break received on communication channel

I submitted a case to HP and am working to discover a resolution with a support person.

When I obtain a solution I'll post a summary back on this thread.

 

Eric

 

 

 

EricLEdberg
Super Collector
Solution

Re: PPM 9.1.1 Import Users report fails with ORA-03111 break received on communication channel

This problem has been solved after much debugging on the PPM and Oracle database servers.

 

The KNTA_USERS_INT table was being populated with 9K users updated using nightly corporate LDAP synchronization.  A similar number of records was in the KNTA_USER_SECURITY_INT table.

 

When the report ran it timed out with an ORA-0311 error

 

Initially, HP Support had me trying to trap the program by adding debugging commands and modifying timers, etc...  Eventually, a local Oracle DBA analyzed the issue and trace files.  He updated various memory and buffer settings to tune the system and created an index on KNTA_USERS_INT.  This change help speed up the Import Users "validation" phase, but it still faulted in the Run_Import phase.

 

Finally, our on-site HP PPM contractor Simone Di Somma, thought of the "easy solution" which was that the Import User Report has TIMERS for each of the command/phases  executed in the report.  The two critical commands only had default timer of 180 seconds.  This was not enough time to process the report.

 

We had to increased the TIMER of the validation and import phases to 10 & 20 minutes respectively for the report to success.....

 

These comments are applicable:

 

  • The default timers in the Import Users report phases are not adequate to import large numbers of users on a slowish PPM CPU.
  • HP stated they will NOT be adjusting these timers in future releases
  • Creating an INDEX on the KNTA_USERS_INT table greatly speed up the validation task
  • HP will be delivering a new index on this table in future releases
  • Other indexes were suggested that may be applied to other tables based on review next week

Note:  the ORA-03111 error is a side-affect of the report timer killing the report abnormally.  Oracle obtains a kill signal (I suppose) and generates a break similar to when a network connection is broke.  Since this was the last error in our log it led us to look a the wrong issue for 1 month!

 

Since updating the timers the import has succeeded, abeit slowly.  Our report takes over 20 minutes to process 9K users.

 

Eric Edberg

 

ps:  Thanks to Simone Di Somma and Jude Healy from HP for working this case to resolution...

 

 

//Add this to "OnDomLoad" event