My Oracle Support Banner

ORA-4020 in ADG Standby Database causing instance crash by LGWR (Doc ID 2379024.1)

Last updated on NOVEMBER 11, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.2.0.1 [Release 11.2 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Object Name: SYS.TEST
Database Name: TEST

*****************

In ADG Standby Database, LGWR process crashes the instance with an ORA-4020 and the LGWR trace shows it was waiting for a "library cache lock".
The deadlock is on the instance lock:
Namespace=DBINSTANCE 

Alert.log shows.

Sat Mar 24 07:47:06 2018
Archived Log entry 1144 added for thread 4 sequence 12918 ID 0x614b7434 dest 1:
Sat Mar 24 07:56:32 2018
Errors in file /oracle/db_home/diag/rdbms/test_stb/TEST/trace/TEST_lgwr_24012.trc:
ORA-04020: deadlock detected while trying to lock object SYS.TEST
LGWR (ospid: 24012): terminating the instance due to error 4020
Sat Mar 24 07:56:32 2018
System state dump requested by (instance=1, osid=24012 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/db_home/diag/rdbms/test_stb2/TEST/trace/TEST_diag_23964_20180324075632.trc
Instance terminated by LGWR, pid = 24012
Sat Mar 24 08:28:17 2018
Starting ORACLE instance (normal)

 

The trace file (TEST_lgwr_24012.trc) shows:

*** 2018-03-24 07:56:32.114
*** SESSION ID:(5671.1) 2018-03-24 07:56:32.114
*** CLIENT ID:() 2018-03-24 07:56:32.114
*** SERVICE NAME:(SYS$BACKGROUND) 2018-03-24 07:56:32.114
*** MODULE NAME:() 2018-03-24 07:56:32.114
*** ACTION NAME:() 2018-03-24 07:56:32.114

A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SYS.TEST
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
0x411fe9220 0x41f5d09c8 0x41a4a2da0 S 0x41fc97ac8 0x413f54ea8 0
0x414ab13f0 0x405f109b0 0x4178ed230 S 0x41f5d09c8 0x41b53d848 X
0x411fe9220 0x41fc97ac8 0x413f54ea8 X 0x405f109b0 0x416a25850 S

------------- WAITING LOCK -------------
----------------------------------------
SO: 0x41a4a2da0, type: 78, owner: 0x3c2284b50, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x408fa3c68, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0

LibraryObjectLock: Address=0x41a4a2da0 Handle=0x411fe9220 RequestMode=S CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

User=0x41f5d09c8 Session=0x41f5d09c8 ReferenceCount=0 Flags=[0000] SavepointNum=267cc9
LibraryHandle: Address=0x411fe9220 Hash=2f89d9df LockMode=S PinMode=0 LoadLockMode=0 Status=0
ObjectName: Name=SYS.TEST
FullHashValue=6bd16b5a2a96e59b998013e72f89d9df Namespace=DBINSTANCE(74) Type=CURSOR(00) Identifier=1 OwnerIdn=0

------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x413f54ea8, type: 78, owner: 0x40ff53490, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x40ef84db8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0

LibraryObjectLock: Address=0x413f54ea8 Handle=0x411fe9220 RequestMode=X CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

User=0x41fc97ac8 Session=0x41fc97ac8 ReferenceCount=0 Flags=[0000] SavepointNum=2
LibraryHandle: Address=0x411fe9220 Hash=2f89d9df LockMode=S PinMode=0 LoadLockMode=0 Status=0
ObjectName: Name=SYS.TEST
FullHashValue=6bd16b5a2a96e59b998013e72f89d9df Namespace=DBINSTANCE(74) Type=CURSOR(00) Identifier=1 OwnerIdn=0

------------- WAITING LOCK -------------
----------------------------------------
SO: 0x413f54ea8, type: 78, owner: 0x40ff53490, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x40ef84db8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0

LibraryObjectLock: Address=0x413f54ea8 Handle=0x411fe9220 RequestMode=X CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

User=0x41fc97ac8 Session=0x41fc97ac8 ReferenceCount=0 Flags=[0000] SavepointNum=2
LibraryHandle: Address=0x411fe9220 Hash=2f89d9df LockMode=S PinMode=0 LoadLockMode=0 Status=0
ObjectName: Name=SYS.TEST
FullHashValue=6bd16b5a2a96e59b998013e72f89d9df Namespace=DBINSTANCE(74) Type=CURSOR(00) Identifier=1 OwnerIdn=0

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.