Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X'

Fix : Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X'

The issue:

I restored one of my prod database to one of pre-prod instance but I found frequently database was bouncing. But one change in my source and traget, that is Source is RAC and target is stand-alone database. I thought there may be issue with restore / recover. But after all cross verification, I found similar kind of issue in Oracle document as a bug (Bug 15850031)


From the ALERT log:
==============================

Fri Sep 04 14:09:47 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1036B65440000] [PC:0x1036B65440000, {empty}] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/PREPROD/trace/PREPROD_smon_6488324.trc (incident=164153):
ORA-07445: exception encountered: core dump [PC:0x1036B65440000] [SIGSEGV] [ADDR:0x1036B65440000] [PC:0x1036B65440000] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/PREPROD/incident/incdir_164153/PREPROD_smon_6488324_i164153.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 04 14:09:50 2015
Dumping diagnostic data in directory=[cdmp_20150904140950], requested by (instance=1, osid=6488324 (SMON)), summary=[incident=164153].
Fri Sep 04 14:09:51 2015
Sweep [inc][164153]: completed
Sweep [inc2][164153]: completed
Fri Sep 04 14:09:52 2015
PMON (ospid: 11075852): terminating the instance due to error 474

From the related INCIDENT FILE
==============================

Dump file /u01/app/oracle/diag/rdbms/PREPROD/incident/incdir_164153/PREPROD_smon_6488324_i164153.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home
System name:    AIX
Node name:    ehdb4
Release:    1
Version:    6
Machine:    00057919D600
Instance name: PREPROD
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 6488324, image: oracle@ehdb4 (SMON)


*** 2015-09-04 14:09:47.431
*** SESSION ID:(487.1) 2015-09-04 14:09:47.431
*** CLIENT ID:() 2015-09-04 14:09:47.431
*** SERVICE NAME:(SYS$BACKGROUND) 2015-09-04 14:09:47.431
*** MODULE NAME:() 2015-09-04 14:09:47.431
*** ACTION NAME:() 2015-09-04 14:09:47.431

Dump continued from file: /u01/app/oracle/diag/rdbms/PREPROD/trace/PREPROD_smon_6488324.trc
ORA-07445: exception encountered: core dump [PC:0x1036B65440000] [SIGSEGV] [ADDR:0x1036B65440000] [PC:0x1036B65440000] [Address not mapped to object] []

========= Dump for incident 164153 (ORA 7445 [PC:0x1036B65440000]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1036B65440000] [PC:0x1036B65440000, {empty}] [flags: 0x0, count: 1]
Registers:
iar: 0x0001036b65440000 lr: 0x0001036b65440000
msr: 0xa00000000000d032 cr: 0x00000000444a8824
r00: 0x0001036b65440000 r01: 0x0ffffffffffeefe0 r02: 0x0000000110659e40
r03: 0x0000000000000000 r04: 0x000000000000000e r05: 0x0000000000000000
r06: 0x0700009851025060 r07: 0x0700009c6c5a5338 r08: 0x0000000000000008
r09: 0x000000000000000e r10: 0x0700009c0234f6a8 r11: 0x0700009c6c5a5338
r12: 0x00000000484a8828 r13: 0x00000001106c2448 r14: 0x0000000000001fe8
r15: 0x0000000002680043 r16: 0x0000000110000298 r17: 0x0700000000013600
r18: 0x0700009c6c5a5a58 r19: 0x00000000444a8883 r20: 0x0000000000000000
r21: 0x0000000000000110 r22: 0x0000000000000000 r23: 0x0700009c6c5a5758
r24: 0x0000000000000003 r25: 0x0ffffffffffef8f0 r26: 0x0700009c6c5a5828
r27: 0x0ffffffffffefa78 r28: 0x0ffffffffffefe20 r29: 0x0700009bdfbed598
r30: 0x0700009c6c5a5338 r31: 0x0000000000000003

*** 2015-09-04 14:09:47.433
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5ansr7r9htpq3) -----
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1

----- Call Stack Trace -----

skdstdst()+40 bl 107c953f0 000000000 ? 000000001 ?
000000003 ? 000000001 ?
000000000 ? 000000001 ?
000000003 ? 000000001 ?
ksedst1()+112 call skdstdst() 1400B9A249DAA178 ?
484A384100000000 ?
110853E30 ? 000002004 ?
1106D0680 ? 10A72B2BC ?
000000000 ? 1106D0680 ?
ksedst()+40 call ksedst1() 30301CA5FE0 ? 002050033 ?
.....



============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | UPDATE STATEMENT | | | | 2 | |
| 1 | UPDATE | UNDO$ | | | | |
| 2 | INDEX UNIQUE SCAN | I_UNDO1 | 1 | 69 | 1 | 00:00:01 |
--------------------------------------+-----------------------------------+

----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
kxsbbbfp=700009c3fddb7da bln=32 avl=21 flg=09
value="_SYSSMU32_1350726082$"
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109c1fe8 bln=24 avl=02 flg=05
value=3
Bind#2
.....
.....

=========================
Workaround with solution
=========================

1) I removed thread 2 redo logs ( source 2 node RAC)
2) Analyzed structure undo table and index:

ANALYZE TABLE UNDO$ VALIDATE STRUCTURE CASCADE ONLINE;
ANALYZE INDEXI_UNDO1 VALIDATE STRUCTURE ONLINE;

3) I found similar issue in Oracle Doc ID : 15850031.8, The issue is related to "Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X' ". For this issue one patch I applied. i.e., patch - 15850031

Now the issue has been fixed.

Thanks you.