oracle 01555,通过dblink访问表时，总是报 ora-01555错误

DB 2 是 ADG  这是很重要的 诊断信息 应当在 一开始就说明

你举出的 oracle forums上的文章指出了以下metalink note，但是该note 介绍的信息与 dblink无关

ORA-01555 on Active Data Guard Standby Database [ID 1273808.1]

你的问题和 BUG Hdr: 12708071 吻合

Hdr: 12708071 11.2.0.1 RDBMS 11.2.0.1 DATAGUARD_ACTV PRODID-5 PORTID-212 ORA-1555

Abstract: ORA-1555 ACROSS DBLINK TO ACTIVE PHYSICAL STANDBY(ADG)

BUG TYPE CHOSEN

===============

Code

SubComponent: Data Guard Active Physical Standby

================================================

DETAILED PROBLEM DESCRIPTION

============================

Long-running queries from (non-standby) database, across db link to physical

standby database running ADG, throws ORA-1555 errors

Filing bug because ct does not have 9214531 applied and therefore BDE

said bug#10018789 is not applicable(per BLR 12335543) for this case. BDE

requested

for bug to be filed to determine the cause.

Customer is seeing ora-1555 on ADG standby and increasing undo_retention

on the primary and standby will not help since queries fail within seconds.

The queries that fall victim to these ORA-1555 errors, execute every

morning around 5AM.

Sometimes the queries succeed, sometimes they abort with the ORA-1555.

If we attempt to run the queries adhoc/manually, sometimes the queries

succeed, sometimes they throw the ORA-1555.

The ora-1555 occurs within seconds.

Tue Apr 19 14:36:08 2011

ORA-1555 caused by SQL statement below (SQL ID: 2jyqq773kh6gj, Query

Duration=1 sec, SCN: 0x095a.094d4d22):

SELECT "A1"."OBJECT_TYPE",COUNT("A1"."OBJECT_NAME") FROM "DBA_OBJECTS" "A1"

WHERE "A1"."OWNER"='APM' GROUP BY "A1"."OBJECT_TYPE"

Tue Apr 19 14:44:54 2011

RFS[2]: Completed archive primary log 2 thread 1 sequence 60530 (latpsbp1)

DIAGNOSTIC ANALYSIS

===================

Stack matches close to bug:Bug 10320455: QUERY IN ACTIVE DATA GUARD RETURNS

ORA-1555 closed as dup of 10018789

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

skdstdst ksedst1 ksedst dbkedDefDump ksedmp dbkdaKsdActDriver

dbgdaExecuteAction dbgdaRunAction dbgdRunActions dbgdProcessEventActions

dbgdChkEventKgErr dbkdChkEventRdbmsErr dbkdChkEventRdbmsErr ksfpec

dbgePostErrorKGE

dbkePostKGE_kgsf kgeade kgerev kserec2 ktussto kturCRBackoutOneChg

qertbFetchByRowID

qertbFetchByRowID qerjotFetch qerjotFetch qerjotFetch qerjotFetch qergsFetch

qerflFetchOutside

qervwFetch qerjoFetch  qergsFetch opifch2 opiall0 opial7 opiodr ttcpip

opitsk opiino

opidrv sou2o opimai_real

ssthrdmain main _start

-- increasing undo_retention would not help since queries fail within

seconds on ADG

WORKAROUND?

===========

No

TECHNICAL IMPACT

================

Nightly job queries are failing

RELATED ISSUES (bugs, forums, RFAs)

===================================

10018789

10320455

10419701

Hdr: 10419701 11.2.0.2 RDBMS 11.2.0.2 DATAGUARD_ACTV PRODID-5 PORTID-226 ORA-1555 10018789

Abstract: ORA-1555 ON ADG WITH NO APPLY LAG

*** 12/21/10 11:46 am *** (ADD: Impact/Symptom->FEATURE UNUSABLE )

*** 12/21/10 11:46 am ***

BUG TYPE CHOSEN

===============

Code

Component: RDBMS

================

DETAILED PROBLEM DESCRIPTION

============================

Primary database is PS1DMART (single instance) which has physical standby

database PS2DMART. It is a active data guard setup ( standby is open in read

only mode)

On standby PS2DMART,we are getting "ORA-1555: snapshot too old:" error on

standby even when we are trying to query data dictionary views.

DIAGNOSTIC ANALYSIS

===================

It starts, per customer's feedback, after ADG stop responding to some query

and performance is affected for SQLs.

We cannot justify as there is no GAP. Need assistance from BDE to

troubleshoot the root cause.

Setup the following events and reproduced the issue.

event='1555 trace name errorstack level 1; name systemstate level 10'

event='10442 trace name context forever, level 1'

event='10475 trace name context forever, level 1'

SELECT name, value, datum_time, time_computed

FROM V$DATAGUARD_STATS

WHERE name like 'apply lag';

06:49:29 SQL> 06:49:29 SQL> 06:49:29 SQL> 06:49:29 SQL> 06:49:29   2

06:49:29   3

NAME         VALUE                DATUM_TIME

TIME_COMPUTED

------------ -------------------- ------------------------------

------------------------------

apply lag    +00 00:00:00         12/20/2010 06:51:00            12/20/2010

06:51:00

06:51:37 SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply

lag' AND COUNT > 0;

SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT >

0

*

ERROR at line 1:

ORA-604: error occurred at recursive SQL level 2

ORA-1555: snapshot too old: rollback segment number 10 with name

"_SYSSMU10_2906602638$" too small

WORKAROUND?

===========

Yes

WORKAROUND INFORMATION

======================

Restart the ADG

TECHNICAL IMPACT

================

It requires manual intervention and it also breaks the ADG funcionality

RELATED ISSUES (bugs, forums, RFAs)

===================================

Bug 10219922 - ORA-1555: SNAPSHOT TOO OLD: ON THE ADG STANDBY

Bug 10039896 - PHSB: ORA-1555 INSTANCE CAN'T START UP

对于以上问题 metalink提供了一个workaround 是重启 physical standby ，实际上不是很有可行性

建议

1.建议升级到 latest patch 11.2.0.3.2

2. 不要对ADG 使用 dblink