ETL Process Hangs When Using an Oracle 11g RAC Database
Last updated on APRIL 03, 2018
Applies to:Primavera Data Warehouse - Version R3.2 and later
Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2]
Information in this document applies to any platform.
- When running the staretl.sh(staretl.bat) to initiate STAR ETL process for Star, the ETL process hangs at Step 23, Part 2 and did not complete STARETL process. The staretlprocess.log file does not display any errors, but it does show that the process just stops at "Finished Script W_PROJECT_SECURITY_SS.sql" within Step 23, Part 2.
DIAGNOSING STEPS TAKEN TO ROOT CAUSE THIS ISSUE
1. Launch the staretl.sh(staretl.bat) in the etl_homes\staretl directory and note the actual behavior listed above.
2. From staretlprocess.log, we can find started script for W_ACTIVITYCODEASSIGNMENT_DS.sql, but no Finished statement for this script at step 23
3. Run linux command with user (who can run staretl)
oracle 3369 3316 0 Mar10 pts/6 00:00:00 sqlplus -s -L @/u01/app/star_1/star/scripts/source_extract/W_ACTIVITYCODEASSIGNMENT_DS.sql
Please note: This also confirmed that W_ACTIVITYCODEASSIGNMENT_DS.sql is in running status at step 23.
4. From AWR report of the second node, we noticed "BEGIN extract2staging('W_ACTIVITYCODEASSIGNMENT_DS')" in the top of "SQL ordered by Elapsed Time"
|per Exec (s)||%Total||%CPU||%IO||SQL Id||SQL Module||SQL Text|
5. From ASH report of the second node, we observed the W_ACTIVITYCODEASSIGNMENT_DS.sql in top SQL with Top Events with "gc current request". See below detail:
|SQL ID||Planhash||Sampled # of Executions||% Activity||Event||% Event||Top Row Source||% RwSrc||SQL Text|
|123||0232||1||35.39||gc current request||34.39||INSERT STATEMENT||34.39||INSERT /*+ APPEND */ INTO W_AC...|
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