环境描述

      OS VERSION: aix 6.1 64bit
      DB VERSION: oracle 11.2.0.3.0 64bit
primary：
      双节点RAC （11.2.0.3.0+ASM）
standby
      单节点（11.2.0.3.0 +ASM）
主库和备库没有进行过swithover

问题描述
standby 以open read only方式打开，alert中发下ora-1555错误

Mon Mar 09 22:15:26 2015
Archived Log entry 3173 added for thread 2 sequence 3502 ID 0x3f6626e6 dest 1:
Mon Mar 09 22:54:14 2015
ORA-01555 caused by SQL statement below (SQL ID: an32pat0t9ayk, Query Duration=2 sec, SCN: 0x0c77.a70129b2):
SELECT \"BAZ001\",\"BAZ002\",\"AKB020\",\"AKC190\",\"AAE072\",\"BKA135\",\"BKC197\",\"AAE001\",\"AKA130\",\"BKC021\",\"AAC999\",\"AAE135\",\"AAC003\",\"AKE010\",\"AAE100\",\"AKC264\",\"AKC253\",\"AKE039\",\"BKE082\",\"BKE030\",\"BKE031\",\"BKE033\",\"BKE034\",\"BKE035\",\"BKE039\",\"AAY009\",\"AAY003\",\"AAY012\",\"AAY030\",\"BKC378\",\"BKC380\",\"AAY025\",\"BAE074\",\"BKC403\",\"BKC404\",
Mon Mar 09 23:52:29 2015
Media Recovery Log +DATA/dbjx/arch/1_5025_828974707.dbf
Media Recovery Log +DATA/dbjx/arch/2_3480_828974707.dbf
Media Recovery Delayed for 1440 minute(s) (thread 1 sequence 5043)

问题分析
primary的两个节点的undo_retention

节点1
SQL> sho parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
节点2
SQL> sho parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

standby的undo_retention

SQL> sho parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string #注意这里的undo表空间为空

standby端的undo表空间

SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2

起初怀疑是standby端的undo_tableapce没有设置造成，将standby的undo_tablespace设置为UNDOTBS1后，问题仍然存在。
继续分析alert日志，可以发现ora-1555错误的发生时间，既不是在接收archivelog时，也不是在appliy log时，而且Query Duration=2 sec。实际的standby的undo_retention为900，怀疑很可能是bug
mos上查到了bug 13814252 Spurious ORA-1555 in RAC Active Dataguard

解决方法

将数据库升级到11.2.0.4

关于bug 13814252，可以参考链接
http://blog.itpub.net/28697282/viewspace-1458089/

ITPub博客

physcial standby ora-1555 in RAC Active DataGuard

最新文章