LGWR (ospid: 29534): terminating the instance due to error 4021

LGWR (ospid: 29534): terminating the instance due to error 4021

ksuitm: waiting up to [5] seconds before killing DIAG(29522)

公司備庫ADG意外當機，alert日誌顯示LGWR程式終止了DB instance

Errors in file /app/oracle_11g/diag/rdbms/hkadg/hkadg/trace/hkadg_lgwr_29534.trc:

ORA-04021: timeout occurred while waiting to lock object 

Sun Mar 24 05:53:27 2019

System state dump requested by (instance=1, osid=29534 (LGWR)), summary=[abnormal instance termination].

LGWR (ospid: 29534): terminating the instance due to error 4021

System State dumped to trace file /app/oracle_11g/diag/rdbms/hkadg/hkadg/trace/hkadg_diag_29522_20190324055327.trc

Dumping diagnostic data in directory=[cdmp_20190324055327], requested by (instance=1, osid=29534 (LGWR)), summary=[abnormal instance termination].

Instance terminated by LGWR, pid = 29534

錯誤原因：

1、發生ORA-4021 錯誤的原因

獲取library cache lock/pin 超時

2、為什麼是 LGWR 終止了db instance

根據 《Bug 18242740 : ACTIVE STANDBY DATABASE CRASHED WITH ORA-4021》整理：

LGWR 需要獲取到解析鎖（parse lock）才能重新整理，可能這個會話話費了大量時間解析。超過了設定時間，依然沒有獲取到解析鎖，就發生了ORA-4021崩潰。

解決辦法：

MOS上找到了相同案例的解決方案《LGWR (ospid: Xxx): Terminating The Instance Due To Error 4021 (文件 ID 2177473.1)》，BUG導致ADG當機。設定隱含引數 _adg_parselock_timeout 可解決該bug，

ALTER SYSTEM set "_adg_parselock_timeout"=550 scope=both;

 

參考資料

LGWR (ospid: Xxx): Terminating The Instance Due To Error 4021 (文件 ID 2177473.1)

CAUSE

Bug 18242740 ACTIVE STANDBY DATABASE CRASHED WITH ORA-4021 closed as duplicate of Bug 17018214 – ORA-600 [KRDRSB_END_QSCN_2]

SOLUTION

Fix is already included in 11.2.0.4 but the fix is DISABLED by default.

To enable the fix  set “_adg_parselock_timeout” to the number of centi-seconds LGWR should wait before backing off and retrying the request.

Set  the parameter “_adg_parselock_timeout”

On the Active Data Guard Standby set below parameter and restart standby:

ALTER SYSTEM set “_adg_parselock_timeout”=550 scope=both;

-----------------------------------End By  2019-03-28--------------------------------------------------------