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.
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.
ps: Thanks to Simone Di Somma and Jude Healy from HP for working this case to resolution...