ORA-4020 in ADG Standby Database causing instance crash by LGWR
(Doc ID 2379024.1)
Last updated on JULY 06, 2021
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
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 |