oracle11g ORA-01555 ON ACTIVE DATA GUARD

布满荆棘的人生 2022-06-18 06:18 181阅读 0赞

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

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