ORA-0402 causes ORACLE11GADG Standby problem processing

Find the database alarm, check the alert log, found the following error
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL1/TRACE/ORCL1_LGWR_26383.TRC:
Ora-04021:timeout occurred while waiting to lock object
LGWR (ospid:26383): Terminating the instance due to error 4021
Sun Mar 25 03:29:07 2018
System State Dump requested by (Instance=1, osid=26383 (LGWR)), summary=[abnormal instance termination].
System state dumped to trace FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL1/TRACE/ORCLE1_DIAG_26321_20180325032907.TRC
Instance terminated by LGWR, PID = 26383
Sun Mar 25 03:29:20 2018
Starting ORACLE instance (normal)

To deal with the DG standby problem, check the State Discovery Library is the Mount state, start the database first.
sql> ALTER DATABASE open;
sql> ALTER DATABASE recover managed standby database using current logfile disconnect;
Sql> select Open_mode from V$database;

Open_mode

READ only with APPLY

To view the issue again, MoS explains the problem as follows:
Applies To:

Oracle database-enterprise edition-version 11.2.0.3 and later
Information in this document applies to any platform.
Symptoms

DR database crashed with below errors.

Client Address: (address= (PROTOCOL=TCP) (host=xx.xxx.xxx. XX) (port=54537))
Warning:inbound Connection timed out (ORA-3136)
Wed Jul 13 13:43:24 2016
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/RXEPRR_DR/RXEPRR1/TRACE/RXEPRR1_LGWR_31312.TRC:
Ora-04021:timeout occurred while waiting to lock object
LGWR (ospid:31312): Terminating the instance due to error 4021
Wed Jul 13 13:43:24 2016
System State Dump requested by (Instance=1, osid=31312 (LGWR)), summary=[abnormal instance termination].
System state dumped to trace FILE/U01/APP/ORACLE/DIAG/RDBMS/RXEPRR_DR/RXEPRR1/TRACE/RXEPRR1_DIAG_31221.TRC
Wed Jul 13 13:43:25 2016
License High Water mark = 318
Instance terminated by LGWR, PID = 31312
USER (ospid:20898): terminating the instance
Instance terminated by USER, PID = 20898
Wed Jul 13 13:43:39 2016
Starting ORACLE instance (normal)

Changes

No changes

Cause

Bug 16717701-ADG should GET the INSTANCE PARSE LOCK with A TIMEOUT

Bug 11712267-active DATA GUARD DATABASE HUNG on ' LIBRARY cache:mutex X ' WAIT EVENT

LGWR trace file (rxeprr1_lgwr_31312.trc)

2016-07-13 13:43:24.498
SESSION ID: (6709.1) 2016-07-13 13:43:24.498
CLIENT ID: () 2016-07-13 13:43:24.498
SERVICE NAME: (sys$background) 2016-07-13 13:43:24.498
MODULE NAME: () 2016-07-13 13:43:24.498
ACTION NAME: () 2016-07-13 13:43:24.498

Error 4021 detected in background process
Ora-04021:timeout occurred while waiting to lock object
Kjzduptcctx:notifying DIAG for Crash event
-----Abridged call Stack Trace-----
Ksedsts () +1296<-kjzdicrshnfy () +364<-ksuitm () +1688<-ksbrdp () +4296<-opirip () +1680<-opidrv (+748) <-sou2o () +88<-opimai_real () +276<-ssthrdmain () +316<-main () +316<-_start (+380)
-----End of Abridged call Stack Trace-----

Solution

Issue matches with bug 11712267 and bug 16717701

Since Bugs is matching with the case,

You can try with option (1). As per Bug 11712267

Change the cursor_sharing-Force on Active Dataguard (ADG).

Monitor your environment for sometime.

If it crashes again then follow with the option (2)
Option (2):

As per bug description

LGWR can request DBInstance lock in X mode without any timeout which can leads to a hang/deadlock.

Both fixes was already included in 11.2.0.4 but the fix was DISABLED by default.
= = > To ENABLE The fix one have to set = = > "_adg_parselock_timeout" > to the number of Centi-seconds = = > LGWR Should wait before backing off and retrying the request.

Value should is in centi seconds. = = > I Don ' t think there is really any hard fast rule for a value-at default (0) it would not timeout.
A value representing a few seconds seems reasonable-if LGWR have been stuck for say 5 seconds waiting it seems reasonable Guess it isn't going to get the lock.

The param just causes it to abort the current attempt and retry If you want to play safe can start with a higher value the n Decrease later.
A higher value would just mean more sessions blocked for longer in case of the deadlock situation.
Seems reasonable, but I had no data to base it on.

There should be a statistic "ADG parselock X get attempts" If it gets set too small this value would likely increase a lot Due to keep timing out and retrying.

This is a dynamic parameter

Follow option (1).

Change the cursor_sharing-force on ADG

If issue re-appears then follow option (2) as below

Please set "_adg_parselock_timeout" to + = >

SQL > Alter system set "_adg_parselock_timeout" =500 scope=both sid= ' * ';

The simple translation is as follows:
1. Change the cursor_sharing parameter to force
2. Set "_adg_parselock_timeout" to 500
SQL > Alter system set "_adg_parselock_timeout" =500 scope=both sid= ' * ';

ORA-0402 causes ORACLE11GADG Standby problem processing