oracle11g ORA-01555 ON ACTIVE DATA GUARD

author：skate
time：2011/10/12

 

在oracle11g的active dataguard中，在备库运行一段时间，一般为几天，然后查询数据视图，数据库就hang很长时间，然后抛出错误：

ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 21 with name
"_SYSSMU21_3288119158$" too small

 

os的版本：Linux x86-64
oracle版本：
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

 

错误情况如下：
[root@db01 ~]# su - oracle
[oracle@db01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 12 11:51:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
SLAVEDB   READ ONLY WITH APPLY

上面这个sql运行了好长时间才出来

SQL> show parameter mem

ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 21 with name
"_SYSSMU21_3288119158$" too small

SQL> SQL>
SQL>
SQL> show parameter log
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 21 with name
"_SYSSMU21_3288119158$" too small

SQL> select * from tab;
select * from tab
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 21 with name
"_SYSSMU21_3288119158$" too small

到oracle的metlink中查，发现是11.2.0.2.0 的bug : 10018789,下载p10018789_112020_Linux-x86-64.zip（424K）补丁修复。

 

官方文档如下：

 

 

Bug 11819223: ORA-01555 ON ACTIVE DATA GUARD 

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

Bug 属性

 

--------------------------------------------------------------------------------
类型 B - Defect 已在产品版本中修复 -
严重性 2 - Severe Loss of Service 产品版本 11.2.0.2.0
状态 36 - Duplicate Bug. To Filer 平台 226 - Linux x86-64
创建时间 28-Feb-2011 平台版本 NO DATA
更新时间 09-Mar-2011 基本 Bug 10018789
数据库版本 11.2.0.2.0  
影响平台  Generic  
产品源 Oracle  

 

相关产品

 

--------------------------------------------------------------------------------
产品线 Oracle Database Products 系列 Oracle Database
区域 Oracle Database 产品 5 - Oracle Server - Enterprise Edition

 

Hdr: 11819223 11.2.0.2.0 RDBMS 11.2.0.2.0 DATAGUARD_ACTV PRODID-5 PORTID-226 ORA-1555 10018789
Abstract: ORA-1555 ON ACTIVE DATA GUARD

*** 02/28/11 12:37 pm ***

PROBLEM:
--------
Cusotmer gets ORA-1555 on Active data Guard when querying dictionary views

SQL> select count(*) from sysadm.ps_action_tbl;
select count(*) from sysadm.ps_action_tbl
                           *
ERROR at line 1:
ORA-604: error occurred at recursive SQL level 1
ORA-1555: snapshot too old: rollback segment number 472 with name
"_SYSSMU472_473248557$" too small

11:21:23 SQL> l1
 1* select count(*) from sysadm.ps_action_tbl
11:21:24 SQL> /
select count(*) from sysadm.ps_action_tbl
                           *
ERROR at line 1:
ORA-604: error occurred at recursive SQL level 1
ORA-1555: snapshot too old: rollback segment number 472 with name
"_SYSSMU472_473248557$" too small

SQL> select tablespace_name,bytes/1024/1024/1024 from dba_data_files;
select tablespace_name,bytes/1024/1024/1024 from dba_data_files
                                                *
ERROR at line 1:
ORA-604: error occurred at recursive SQL level 1
ORA-1555: snapshot too old: rollback segment number 54 with name
"_SYSSMU54_3751033121$" too small

these queries fail within 30 seconds, they dont cross the undo_retention
period

DIAGNOSTIC ANALYSIS:
--------------------
Per following document there is a bug on Active Data Guard in 11.2.0.2.0

ORA-1555 on Active Data Guard Standby Database (Doc ID 1273808.1)

I am uploading the trace files with the stack trace, please confirm.

WORKAROUND:
-----------
No workaround

RELATED BUGS:
-------------
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

 

 

 

-----------------end--------------