Oracle DB 12.1.0.2 RAC 2 nodes
Solaris 11
OGG 18.1.0
Hello Team,
recently I noticed the lag was increasing due to 1 table.
When I checked the alert log I saw there was a lock on this table. Kindly advise how we can prevent this and what can be remedy ?
T_N"=:23 ,"CHARGE_FROM_DATE_D"=:24 ,"CHARGE_TILL_DATE_D"=:25 ,"ERR_REF_N"=:26 ,"INVOICE_NUM_V"=:27 ,"RELEASE_FLG_V"=:28
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[355.28909] on resource TX-00940017-0001CB59-00000000-00000002
2022-05-06 09:35:51.118635 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
========================================================================
Global Wait-For-Graph(WFG) for GES Deadlock ID=[48_0_259]
------------------------------------------------------------------------
Victim : (instance=1, lock=0xb19ceb4c0)
Start (master) Instance : 1
Number of Locks involved : 4
Number of Sessions involved : 2
User session identified by:
{
User Name : ora12c
User Machine : dware1
OS Terminal Name : UNKNOWN
OS Process ID : 28909
OS Program Name : oracle@dware1 (AS04)
Application Name : GoldenGatev@rh-tableau-06.mtg.local (TNS V1-V3)
Action Name : OGG$REPNEW - Apply Serverernotes
Current SQL : UPDATE /*+ restrict_all_ref_cons */ "CBSOGG"."CB_BILL_INV_ACC_LIST" SET "ACCOUNT_CODE_N"=:1 ,"ACCOUNT_LINK_CODE_N"=:2 ,"SUBSCRIBER_CODE_N"=:3 ,"SUBSCRIBER_CATEGORY_V"=:4 ,"SUBSCRIBER_SUB_CATEGORY_V"=:5 ,"SERVICE_CODE_V"=:6 ,"BILL_CYCLE_FULL_CODE_N"=:7 ,"BATCH_ID_V"=:8 ,"SUB_BATCH_ID_N"=:9 ,"HEIRARCHIAL_OPTION_V"=:10 ,"STATEMENT_GROUP_CODE_N"=:11 ,"PARENT_GROUP_CODE_N"=:12 ,"GROUP_DISCOUNT_CODE_N"=:13 ,"BILLING_REGION_V"=:14 ,"CURRENCY_CODE_V"=:15 ,"PROCESSED_STAGE_ID_V"=:16 ,"ERROR_STAGE_ID_V"=:17 ,"PROCESSED_STAGE_SEQ_N"=:18 ,"ERROR_STAGE_SEQ_N"=:19 ,"CONFIRM_ROLBK_OPTN_V"=:20 ,"CREDIT_NOTE_OPTN_V"=:21 ,"PRINT_OPTN_V"=:22 ,"PRINT_CNT_N"=:23 ,"CHARGE_FROM_DATE_D"=:24 ,"CHARGE_TILL_DATE_D"=:25 ,"ERR_REF_N"=:26 ,"INVOICE_NUM_V"=:27 ,"RELEASE_FLG_V"=:28
Session Number : 439
Session Serial Number : 60057
Server Process ORAPID : 355
Server Process OSPID : 28909
Instance : 1
}
waiting for Lock 0xb19ceb4c0 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x650044.0xabff5(ext 0x0,0x2)
GES Transaction ID : 1016-0163-000004BD
}
which is blocked by Lock 0xb19ceb8a0 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x650044.0xabff5(ext 0x0,0x2)
GES Transaction ID : 1012-0125-00001E53
}
owned by the
User session identified by:
{
User Name : ora12c
User Machine : dware1
OS Terminal Name : UNKNOWN
OS Process ID : 29516
OS Program Name : oracle@dware1 (AS07)
Application Name : GoldenGatedb1 (TNS V1-V3)l (TNS V1-V3)NS V1-V3)
Action Name : OGG$REPNEW - Apply Servernatoresmation
Current SQL : UPDATE /*+ restrict_all_ref_cons */ "CBSOGG"."CB_BILL_INV_ACC_LIST" SET "ACCOUNT_CODE_N"=:1 ,"ACCOUNT_LINK_CODE_N"=:2 ,"SUBSCRIBER_CODE_N"=:3 ,"SUBSCRIBER_CATEGORY_V"=:4 ,"SUBSCRIBER_SUB_CATEGORY_V"=:5 ,"SERVICE_CODE_V"=:6 ,"BILL_CYCLE_FULL_CODE_N"=:7 ,"BATCH_ID_V"=:8 ,"SUB_BATCH_ID_N"=:9 ,"HEIRARCHIAL_OPTION_V"=:10 ,"STATEMENT_GROUP_CODE_N"=:11 ,"PARENT_GROUP_CODE_N"=:12 ,"GROUP_DISCOUNT_CODE_N"=:13 ,"BILLING_REGION_V"=:14 ,"CURRENCY_CODE_V"=:15 ,"PROCESSED_STAGE_ID_V"=:16 ,"ERROR_STAGE_ID_V"=:17 ,"PROCESSED_STAGE_SEQ_N"=:18 ,"ERROR_STAGE_SEQ_N"=:19 ,"CONFIRM_ROLBK_OPTN_V"=:20 ,"CREDIT_NOTE_OPTN_V"=:21 ,"PRINT_OPTN_V"=:22 ,"PRINT_CNT_N"=:23 ,"CHARGE_FROM_DATE_D"=:24 ,"CHARGE_TILL_DATE_D"=:25 ,"ERR_REF_N"=:26 ,"INVOICE_NUM_V"=:27 ,"RELEASE_FLG_V"=:28
Session Number : 718
Session Serial Number : 51310
Server Process ORAPID : 293
Server Process OSPID : 29516
Instance : 1
}
waiting for Lock 0xb11872f98 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x940017.0x1cb59(ext 0x0,0x2)
GES Transaction ID : 1012-0125-00001E53
}
which is blocked by Lock 0xb0d87c790 (Transaction):
Thanks,
Roshan