oracle11g从库sqlplus hang

In this Document

APPLIES TO:

SYMPTOMS

Periodically on the standby database, sqlplus sessions will hang and cannot log into sqlplus to check the status.

Must connect using prelim option and shutdown abort, then restart the standby and restart recovery.

We see below in the hang analyzer trace:

Occurence  Wait Event                          Blocker                         
---------     ----------                             -------                         
68           cursor: pin S wait on X            inst: 1, sid: 9421, ser: 181    
1             library cache load lock             inst: 1, sid: 9421, ser: 181    
1             library cache lock                    inst: 1, sid: 9421, ser: 181    
1             library cache: mutex X            inst: 1, sid: 9421, ser: 181    

 Current SQL:           0

   Previous SQL:  7000160f66dbf78
       ObjectName:  Name=
SELECT  1, status, '',archiver, database_status, active_state FROM v$instance


   Short stack dump: 
ksedsts()+240<-ksdxfstk()+44<-ksdxcb()+3432<-sspuser()+116<-__sighandler()<-kglrdtin()+656<-kglrdti()+36<-kksAllocCursorStat()+664<-kksLoadChild()+17744<-kkslod()+40<-kglobld()+848<-kglobpn()+1400<-kglpim()+972<-IPRA.$kglpin()+1292<-kglpin()+96<-kxsGetRuntimeLock()+1540<-kksfbc()+12860<-kkspsc0()+1188<-kksParseCursor()+116<-opiosq0()+2072<-opiall0()+7352<-opikpr()+460<-opiodr()+908<-rpidrus()+216<-skgmstack()+160<-rpidru()+132<-rpiswu2()+520<-kprball()+1044<-kprbbnd0()+1168<-kprbbnda()+948<-kprbbnd()+124<-kqldtstr()+460<-kqldftar()+512<-kqldvir()+128<-kqldtr()+152<-kglspld()+56<-kqlobjlod()+7076<-kqllod()+640<-kglobld()+848<-kglobpn()+1400<-kglpim()+972<-IPRA.$kglpin()+1292<-kglgob()+456<-qcdlgbo()+476<-qcdlgob()+664<-qcsfgob()+256<-qcsprfro()+500<-qcsprfro_tree()+316<-qcsprfro_tree()+1336<-qcspafq()+292<-qcspqbDescendents()+260<-qcspqb()+244<-qcsevw()+444<-kkmevw()+3904<-kkmfcbvw()+844<-kkmfcblo()+5616<-kkmpfcbk()+864<-qcsprfro()+580<-qcsprfro_tree()+316<-qcsprfro_tree()+1336<-qcspafq()+292<-qcspqbDescendents()+260<-qcspqb()+244<-kkmdrv()+64<-opiSem()+1956<-opiDeferredSem()+376<-opitca()+15376<-kksFullTypeCheck()+76<-rpiswu2()+520<-kksLoadChild()+7016<-kxsGetRuntimeLock()+2060<-kksfbc()+12860<-kkspsc0()+1188<-kksParseCursor()+116<-opiosq0()+2072<-kpooprx()+316<-kpoal8()+884<-opiodr()+908<-ttcpip()+1028<-opitsk()+1612<-opiino()+940<-opiodr()+908<-opidrv()+1132<-sou2o()+136<-opimai_real()+560<-ssthrdmain()+276<-main()+204<-__start()+112

   Current Wait Stack:
     Not in wait; last wait ended 654 min 14 sec ago 
   There are 71 sessions blocked by this session.
   Dumping one waiter:
     inst: 1, sid: 11619, ser: 21
     wait event: 'library cache load lock'
       p1: 'object address'=0x7000160f4f72f80
       p2: 'lock address'=0x7000160fdbc5d70
     min_blocked_time: 36148 secs, waiter_cache_ver: 51654
   Wait State:
     fixed_waits=0 flags=0x20 boundary=0x0/-1

CHANGES

CAUSE

 The issue matching Bug 18094722 - HANGS ON ACTIVE DATAGUARD - BLOCKER SPINNING IN KGLRDTIN() which closed as a duplicate of Bug 19684789 - A GLOBAL FLUSH UNDER ADG SHOULD OBSOLETE CURSOR STATISTICS HANDLES

SOLUTION

Download and apply patch 19684789

REFERENCES