ORA-01555 on Active Data Guard Standby Database

ORA-01555 on Active Data Guard Standby Database [ID 1273808.1]
		修改时间 18-APR-2011     类型 PROBLEM     状态 PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2.0 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.
Primary and Standby databases at 11.2.0.2

Symptoms

After upgrading to 11.2.0.2 we see this ORA-1555 on the Standby Database. It is not possible to logon with system, dbsnmp or other db-users. Only sys as sysdba is working.
It's like we are running on system's undo Tablespace, - and not the one which has been restored..

Alert log file from Standby database

Wed Oct 27 14:48:22 2010
ORA-01555 caused by SQL statement below (SQL ID: dskjv8dgqdax0, Query Duration=8519 sec, SCN: 0x03cd.7fb9ca61):
SELECT TRANSMAIN.RECSEQNO,TRANSMAIN.TRANSIK,TRANSMAIN.TRAUNIEX,TRANSMAIN.TRANSEX,TRANSMAIN.DEPKEY,TRANSMAIN.SECIK,TRANSMAIN.PORIK,TRANSMAIN.CTP,TRANSMAIN.DEALER,TRANSMAIN.TRASPECIK,TRANSMAIN.CDYIK,TRANSMAIN.BLKIK,TRANSMAIN.TRCELMNO,TRANSMAIN.TRCBUSNO,TRANSMAIN.TRASTATREQ,TRANSMAIN.TRASTATACT,TRANSMAIN.TRASTATREV,TRANSMAIN.TRACAN,TRANSMAIN.BLKDATE,TRANSMAIN.TRANSORG,TRANSMAIN.TRANOLINK,TRANSMAIN.XI_TRANSCOSTTAX,TRANSMAIN.XI_TRANSSETTLE,TRANSMAIN.XI_TRANSINSTRUC,TRANSMAIN.XI_BALBOOKPFC,TRANSMAIN.NOMVAL,TRANSMAIN.NOMVALEX,TRANSMAIN.AGRDATE,TRANSMAIN.INTBEGDATE,TRANSMAIN.STLMDATE,TRANSMAIN.PMTDATE,TRANSMAIN.TRAPRICE,TRANSMAIN.TRAYIELD,TRANSMAIN.CURVALQC,TRANSMAIN.CURVALPC,TRANSMAIN.ACRINTQC,TRANSMAIN.ACRINTPC,TRANSMAIN.STLMAMTSC,TRANSMAIN.FXRATEQP,TRANSMAIN.FXRATEQS,TRANSMAIN.ACRINTDAYS,TRANSMAIN.STLMCUR,TRANSMAIN.EXTNOTEDATE,TRANSMAIN.EXTNOTEID,TRANSMAIN.BOOKMONTH,TRANSMAIN.FXRATEIP,TRANSMAIN.ACRINTSC,TRANSMAIN.AGIODISASC,TRANSMAIN.CAPCHGID,TRANSMAIN.REFIRATE,TRANSMAIN.FROMDATE,TRANSMAIN.TODATE,TRANSMAIN.EXCTYPE
Wed Oct 27 14:59:29 2010
Thread 1 cannot allocate new log, sequence 17781
Private strand flush not complete
Current log# 4 seq# 17780 mem# 0: /u1041105/redo/redo0401.log
Thread 1 advanced to log sequence 17781 (LGWR switch)
Current log# 1 seq# 17781 mem# 0: /u1041105/redo/redo0101.log
Wed Oct 27 14:59:32 2010
LNS: Standby redo logfile selected for thread 1 sequence 17781 for destination LOG_ARCHIVE_DEST_2
Wed Oct 27 14:59:34 2010
Archived Log entry 35541 added for thread 1 sequence 17780 ID 0x21653caa dest 1:
Wed Oct 27 15:02:20 2010
ALTER SYSTEM ARCHIVE LOG
Wed Oct 27 15:02:20 2010
Thread 1 cannot allocate new log, sequence 17782
Private strand flush not complete
Current log# 1 seq# 17781 mem# 0: /u1041105/redo/redo0101.log
Thread 1 advanced to log sequence 17782 (LGWR switch)
Current log# 2 seq# 17782 mem# 0: /u1041105/redo/redo0201.log
Archived Log entry 35543 added for thread 1 sequence 17781 ID 0x21653caa dest 1:

Cause

This is described in
Bug 10320455: QUERY IN ACTIVE DATA GUARD RETURNS ORA-1555
which is duplicate from
Bug 10092353: ACTIVE DATAGUAD INSTANCE HANGS DUE TO A LIBRARY CACHE LOCK HOLDER
This is resolved by applying the fix for
Internal Bug 10018789:  DBMV2-BIGBH:SPIN IN KGLLOCK CAUSED DB HUNG AND HIGH LIBRARY CACHE LOCK

Solution

On Unix Platforms:

Apply this Patch 10018789 or request corresponding Patch if not available for your Platform. on Top of 11.2.0.2

On Windows Platform.:

Please apply 11.2.0.2.0 Patch 3 (11.2.0.2.3P)
32-Bit Patch:11731183
64-Bit (x64) Patch:11731184
For more info please check this note:
11.2.0.x Oracle Database and Networking Patches for Microsoft Platforms ( Note 1114533.1)

References

BUG:10092353 - ACTIVE DATAGUAD INSTANCE HANGS DUE TO A LIBRARY CACHE LOCK HOLDER
BUG:10320455 - QUERY IN ACTIVE DATA GUARD RETURNS ORA-1555
NOTE:1114533.1 - 11.2.0.x Oracle Database and Networking Patches for Microsoft Platforms

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10640532/viewspace-701568/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10640532/viewspace-701568/