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

tohjb

OS: centos 6.0verison: oracle 11.2.0.1

一台server上，安装了两个数据库：ＤＢ1，ＤＢ2

现象：

使用sql developer或toad，
第一次 登陆到ＤＢ1，用DB1 的用户，通过 DBlink  访问 DB2 的ｔａｂｌｅ2
都会报 ora-01555错误，类似于：
ORA-01555 caused by SQL statement below (SQL ID: 3cd128j4dxvun, Query Duration=0 sec, SCN: 0x0000.817a0ce3):

ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_3103383136$") 过小
ORA-02063: 紧接着 line (起自 DBLINK_DB1_TO_DB2)


此时，只要在DB1上， commit 或 rollback，就再也不报错了！

此外，当时直接在DB2上，访问DB2 的table2，不会报错。

在DB1上，直接访问DB1的其他表，也不会报错

但就是在DB1上，用DB1的用户，  select  *  from  table2@dblink_db1_to_db2  就会报错。


请教是怎么回事？ 谢谢！

[ 本帖最后由 tohjb 于 2012-6-11 20:45 编辑 ]

tohjb

好的，下次提问题的经验会更丰富一些！

非常感谢！！～～～

Maclean Liu(刘相兵

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

tohjb

你好，因为表是商业数据库，由于保密协议，我无法直接将结果贴出。还请见谅。

环境补充：

DB2 为  Active data guard database   DB1 为 普通的database。

在网上找到这篇文章，跟我的问题一模一样。
https://cn.forums.oracle.com/forums/thread.jspa?threadID=2337478

Maclean Liu(刘相兵

请不要 给action plan 打折扣， desc 就把结果贴出来

tohjb

DB2 上：

SQL> select current_scn,systimestamp from v$database;

CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
2176883527
11-JUN-12 10.25.43.637479 PM +08:00

在DB1上，
SQL> select current_scn,systimestamp from v$database;

CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
2176571277
11-JUN-12 10.28.29.293950 PM +08:00


执行  describe table2  结果一模一样。

Maclean Liu(刘相兵

action plan:


在db1 和 db2 上分别执行以下SQL 并贴出结果：

alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';

select current_scn,systimestamp from  v$database;

desc table2