ORA-01555 on Active Data Guard Standby Database

靜以致遠√團團發表於2017-04-05
原文網址 : http://blog.itpub.net/29320885/viewspace-2136580/
Database
ORA-01555 on Active Data Guard Standby Database [ID 1273808.1]
  修改時間 18-APR-2011     型別 PROBLEM     狀態 PUBLISHED  

In this Document
  
  
  
  

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
: QUERY IN ACTIVE DATA GUARD RETURNS ORA-1555
which is duplicate from
: ACTIVE DATAGUAD INSTANCE HANGS DUE TO A LIBRARY CACHE LOCK HOLDER
This is resolved by applying the fix for
Internal :  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 ()

References

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29320885/viewspace-2136580/，如需轉載，請註明出處，否則將追究法律責任。

相關文章