gc index operation

From: manikandan <pvmanikandan_at_gmail.com>
Date: Thu, 9 Apr 2020 17:45:51 -0400
Message-ID: <CAB6JwgjgFwGt3o1BLGpuVYrHM+qJCOLF2MRde4CyR0=drs37ow_at_mail.gmail.com>



Hi ,

We have two node 12.2 RAC and 12.3 Golden gate set up and all the GG components running on node 1. We ran into a situation that one of the replicat was hung and the corresponding replicat apply slave session was waiting on even "gc index operation".
The session was trying to do the below Insert (trimming the full sql)

 INSERT /*+ restrict_all_ref_cons */

       INTO "RDSOWNER"."RXP_FILL_USER" ("FILL_USER_ID",
"PRESCRIPTION_FILL_ID",

"CREATED_DATE",

"CREATED_BY",

"USER_TYPE",

"PHARMACIST_LICENSE_NUMBER",

"LAST_NAME",

"FIRST_NAME",

"MIDDLE_NAME",

"ACTIVITY",

"TIMESTAMP",

"USER_CREDENTIALS",

"LOCAL_TIMESTAMP",

"EMPLOYEE_ID",

"LAST_UPDATED_DATE",

"LAST_UPDATED_BY",

"IS_AUTO_APPLIED")
     VALUES (:1,              :2,

At the same time, when we checked the second node, the global index maintenance job (PMO-DEFERRED_GIDX_MAINT_JOB) was running on the same table.

ALTER INDEX "RDSOWNER"."RXP_FILL_USER_PK"     MODIFY PARTITION "RXP_FILL_USER_P03"         COALESCE CLEANUP We tried few options like stopping the GIDX and restart replicat, but dint work. Finally we had to bounce second node for the replicat to move.

Can someone provide some details on why this issue is happening and what this wait event for and also does index coalesce hold any lock on the index during cleanup the orphan entries ?

Thanks,

Mani

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 09 2020 - 23:45:51 CEST

Original text of this message