xla_distribution_links_n1.xdf worker is running long without any error in the log file, it is trying to create index and taking long time.
Reading language information from applUS.txt …
Temporarily resetting CLASSPATH to:
“/u01/install/APPS/fs1/EBSapps/appl/ad/12.0.0/java/adjava.zip:/u01/install/APPS/fs1/EBSapps/comn/util/jdk32/lib/dt.jar:/u01/install/APPS/fs1/EBSapps/comn/util/jdk32/lib/tools.jar:/u01/install/APPS/fs1/EBSapps/comn/util/jdk32/jre/lib/rt.jar:/u01/install/APPS/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/install/APPS/fs1/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u01/install/APPS/fs1/EBSapps/comn/java/classes:/u01/install/APPS/fs1/EBSapps/10.1.2/forms/java:/u01/install/APPS/fs1/EBSapps/10.1.2/forms/java/frmall.jar:/u01/install/APPS/fs1/EBSapps/10.1.2/jlib/ewt3.jar:/u01/install/APPS/fs1/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar”
Calling /u01/install/APPS/fs1/EBSapps/comn/util/jdk32/jre/bin/java …
Invoking Utility FndXdfCmp …
Class: oracle.apps.fnd.odf2.FndXdfCmp
Method: applyXDF
Arguments: &un_xla &pw_xla &un_apps &pw_apps &jdbc_protocol &jdbc_db_addr index &fullpath_xla_patch/115/xdf_xla_distribution_links_n1.xdf &fullpath_fnd_patch/115/xdf_xsl
**** Reseting package states in the current session ****
TimeStamp : [Thu Sep 01 22:04:31 PDT 2022]
Session details
=========
Its ACTIVE and creating INDEX.
=========================================================
Sid , Serial# : 1617 , 35952
Server/Shadow : 18437
Client/Foreground : 1234
Terminal / Machine: unknown / calraptst03.harborfreight.com
Username……..: APPS
Osuser……….: applmgr
Program………: JDBC Thin Client
Module……….: JDBC Thin Client
Status……….: ACTIVE
Action……….:
Wait_time…….: 0
State ……….: WAITING
Wait Event …..: db file scattered read
Seconds_in_wait.: 0
Pga_alloc….: 432.60 Mb
Pga_used…..: 427.39 Mb
Pga_free…..: 4.63 Mb
Pga_max……: 432.61 Mb
Lock / Latch.: None/ None
Blocking Sessi=on:
Blocking Session Status: NO HOLDER
Latch Spin…: None
Logon Time…: Tue 06-Sep-22 13:38:13
Last Call….: Tue 06-Sep-22 13:38:13 -> 277.2 Mins 4.6 Hours .2 Days
Sql Address. : 00000000716A5A50
Sql Hash…. : 2915616157
Prev Sql Hash: 3569781485
Trans Status : ACTIVE
Trans Active : 00000001EE5EB2F0
Undo Generation: 1 Blocks
Changed Blocks : 433613 Blocks
………… Current Sql Statment ……………..:
=========================================================
CREATE INDEX “XLA”.”XLA~DISTRIBUTION~LINKS~N1″ ON “XLA”.”XLA_DISTRIBUTION_LINKS”
(“SOURCE_DISTRIBUTION_TYPE”, “SOURCE_DISTRIBUTION_ID_NUM_1”, NVL(“SOURCE_DISTRI
BUTION_ID_NUM_2″,(-99)), “SOURCE_DISTRIBUTION_ID_NUM_2”) PCTFREE 10 INITRANS
11 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT) LOCAL ( PARTITION AP TABLESP
ACE APPS_TS_TX_IDX , PARTITION AR TABLESPACE APPS_TS_TX_IDX , PARTITION PA TAB
LESPACE APPS_TS_TX_IDX , PARTITION PO TABLESPACE APPS_TS_TX_IDX , PARTITION PN
TABLESPACE APPS_TS_TX_IDX , PARTITION OFA TABLESPACE APPS_TS_TX_IDX , PARTITI
ON OKL TABLESPACE APPS_TS_TX_IDX , PARTITION OZF TABLESPACE APPS_TS_TX_IDX , P
ARTITION CST TABLESPACE APPS_TS_TX_IDX , PARTITION PSB TABLESPACE APPS_TS_TX_ID
X , PARTITION CE TABLESPACE APPS_TS_TX_IDX , PARTITION FUN TABLESPACE APPS_TS_
TX_IDX , PARTITION FV TABLESPACE APPS_TS_TX_IDX , PARTITION GMF TABLESPACE APP
S_TS_TX_IDX , PARTITION LNS TABLESPACE APPS_TS_TX_IDX , PARTITION PAY TABLESPA
CE APPS_TS_TX_IDX , PARTITION IGI TABLE
=========================================================
……………. Prev Sql Statment ………………:
=========================================================
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,t
erminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, n
ew$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,co
mment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, in
stance#,process#,xid,scn,auditid, sqlbind,sqltext,obj$edition,dbid,rls$info,cur
rent_user) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8,
:9,:10,:11,:12, :13,:14,:15,:16,:17, :18,:19,:20,:21,:22, :23,:24
,:25,:26,:27, :28,:29,:30,:31,:32, :33,:34,:35,:36,:37,:38)
=========================================================
1 row selected.
SQL>
Cause
As per R12: AP: Application of Patch 29499116 is Hanging While Creating Index XLA~DISTRIBUTION~LINKS~N1. (Doc ID 2649876.1) , The problem index is NOT a standard one. Tilde character (~) is not standard character for Oracle object names.
The index may have been created incorrectly via standard means, according to similar documents found, like Note 2513648.1 – Loader Worker Failing With ORA-00911 On MSC_SYSTEM_ITEMS
During on-line patching cycle, the indexes (new or modified) are getting changed as follows:
the “_” (underscores) are replaced by “~” (tilde) characters.
Bug 29376650 – MSCSLD FAILED WITH ORA-00911: INVALID CHARACTER ORA-06512, ERROR IN MAIN 103
Fix
- Check the DB session details associated with running worker and capture SID, Serial#
- Using adctrl make the concerned running worker failed
- If the workers goes to “wait” status (meaning patch is not moving forward) after failing the worker in #2, check if the SID in#1 is still ACTIVE and kill from background
- Ensure xla_distribution_links_n1.xdf gets picked up by another worker and gets completed(Validate from worker logs).