Patch#33377874 hanging on xla_distribution_links_n1.xdf

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

1. Check the DB session details associated with running worker and capture SID, Serial#
2. Using adctrl make the concerned running worker failed
3. 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
4. Ensure xla_distribution_links_n1.xdf gets picked up by another worker and gets completed(Validate from worker logs).