logical standby sql apply 挂起

miloluo

各位好，
我们有一个DG环境:
OS: AIX 5.2 64bit,
DB version: 10.2.0.4 64bit,
1 primary + 1 logical standby,


一星期前，发现有几个日志一直在current状态，但是过了一周了mining_scn都未改变。logical standby自己的归档日志40s大概生成一个。经过日志挖掘，发现这个日志里面几乎所有都是对某几个序列的更新。这个更新比较奇怪，他会把sequence的start with的值，以及cache的值做一些修改。


这几个scn的值在这一周都没有变化过：

1. SQL> SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;
   
2. 
   
3. APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
   
4. ------------- -------------- -------------- --------------
   
5. 1844801217 1857454587 1844801222 1844637788

复制代码

这个内部序列update的语句形式这样：

1. UPDATE "SYS"."seq$"
   
2. SET    "increment$" = '1',
   
3.        "minvalue" = '1',
   
4.        "maxvalue" = '99999',
   
5.        "cycle#" = '1',
   
6.        "order$" = '0',
   
7.        "cache" = '20',
   
8.        "highwater" = '16701',
   
9.        "audit$" = '--------------------------------',
   
10.        "flags" = '8'
    
11. WHERE  "obj#" = '38038'
    
12.        AND "increment$" = '1'
    
13.        AND "minvalue" = '1'
    
14.        AND "maxvalue" = '99999'
    
15.        AND "cycle#" = '1'
    
16.        AND "order$" = '0'
    
17.        AND "cache" = '20'
    
18.        AND "highwater" = '16681'
    
19.        AND "audit$" = '--------------------------------'
    
20.        AND "flags" = '8'
    
21.        AND rowid = 'AAAABDAABAAAAHzAA5';  
    
22.   

复制代码

Object的具体信息：

1. SQL> r
   
2.   1* select object_id, object_name, object_type from dba_objects where object_id=44703
   
3. 
   
4. OBJECT_ID OBJECT_NAME                              OBJECT_TYPE
   
5. ---------- ---------------------------------------- -------------------
   
6.      44703 OUTP_ANAESTHESIA_SEQUENCE_NO             SEQUENCE

复制代码

Primary上这个序列的定义：

1. SQL> set long 1000;
   
2. SQL> r
   
3.   1* select dbms_metadata.get_ddl('SEQUENCE','OUTP_ANAESTHESIA_SEQUENCE_NO','OUTPDOCT') from dual
   
4. 
   
5. DBMS_METADATA.GET_DDL('SEQUENCE','OUTP_ANAESTHESIA_SEQUENCE_NO','OUTPDOCT')
   
6. --------------------------------------------------------------------------------
   
7. 
   
8.    CREATE SEQUENCE  "OUTPDOCT"."OUTP_ANAESTHESIA_SEQUENCE_NO"  MINVALUE 1 MAXVAL
   
9. UE 999999999999 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER  CYCLE

复制代码

而Standby上这个序列的定义却是这样：

1. SQL> set long 100000;
   
2. SQL> r
   
3.   1* select dbms_metadata.get_ddl('SEQUENCE','OUTP_ANAESTHESIA_SEQUENCE_NO','OUTPDOCT') from dual
   
4. 
   
5. DBMS_METADATA.GET_DDL('SEQUENCE','OUTP_ANAESTHESIA_SEQUENCE_NO','OUTPDOCT')
   
6. --------------------------------------------------------------------------------
   
7. 
   
8.    CREATE SEQUENCE  "OUTPDOCT"."OUTP_ANAESTHESIA_SEQUENCE_NO"  MINVALUE 1 MAXVAL
   
9. UE 999999999999 INCREMENT BY -60 START WITH 920434195299 NOCACHE  NOORDER  CYCLE

复制代码

尝试过重启sql apply, 重启logical standby 数据库。
但是都不能解决问题。

最后发现有个bug和目前的想想较为匹配<Bug 9486060  Many "update seq$" SQLs generated / spin on logical standby - superceded>

这个bug最后指向的workaround是<Bug 9906543  Logical standby Apply lag due to apply slaves synchronizing a sequence>提到的。

描述如下：

1. Workaround:
   
2. Drop the guard, and then recreate the sequence at the standby with the
   
3. correct START WITH value (corresponding to the HIGHWATER at the primary).

复制代码

我执行的过程：

1. alter session disable guard;
   
2. 
   
3. -- 因为正常执行关闭不成功，会挂起很长时间没反应，使用了abort的方法：
   
4. ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
   
5. 
   
6. drop sequence OUTPDOCT.OUTP_ANAESTHESIA_SEQUENCE_NO;
   
7. 
   
8. CREATE SEQUENCE "OUTPDOCT"."OUTP_ANAESTHESIA_SEQUENCE_NO" MINVALUE 1 MAXVAL
   
9. UE 999999999999 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER CYCLE;
   
10. 
    
11. 
    
12. ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
    
13. 
    
14. 
    
15. alter session enable guard;
    
16. 
    
17. 
    
18. 过了一阵，在logical standby的对应sequence里，发现又被改变了：
    
19. 
    
20. SQL> select dbms_metadata.get_ddl('SEQUENCE','OUTP_ANAESTHESIA_SEQUENCE_NO','OUTPDOCT') from dual;
    
21. 
    
22. DBMS_METADATA.GET_DDL('SEQUENCE','OUTP_ANAESTHESIA_SEQUENCE_NO','OUTPDOCT')
    
23. --------------------------------------------------------------------------------
    
24. 
    
25. CREATE SEQUENCE "OUTPDOCT"."OUTP_ANAESTHESIA_SEQUENCE_NO" MINVALUE 1 MAXVAL
    
26. UE 999999999999 INCREMENT BY 1 START WITH 1520821 CACHE 20 NOORDER CYCLE

复制代码

DG alert log和 DG diag脚本跑出的信息已经上传。

Primary_Standby_alert_log.zip (530.36 KB, 下载次数: 1103)

2012-7-4 01:39 上传

点击文件名下载附件
Primary & Standby alert log

DG_diag_info.zip (15.59 KB, 下载次数: 1280)

2012-7-4 01:39 上传

点击文件名下载附件
Primary & Standby diag info

请大家帮忙看看，有没什么好办法。
谢谢。

[ 本帖最后由 miloluo 于 2012-7-5 11:13 编辑 ]

Maclean Liu(刘相兵

先尝试把 cache调大成10000， 现在要出门了 晚上在帮你看

Maclean Liu(刘相兵

目前找到的一个案例：

ODM FINDING:

Switchover to logical standby

This morning we did a switchover between our primary database and one of the two logical standby databases.

The switchover went smoonthly. When the data began to be replicated from primary, however, the standby database suddenly did successive log switch at very high frequency. This is a bit puzzling since the primary database was not too busy.
Loads of waiting events from “SYS” user:

LogMiner builder: DDL                    oracle@si-sbr-db (MS01)  
LogMiner reader: buffer                  oracle@si-sbr-db (MS00)
Streams apply: waiting for dependency    oracle@si-sbr-db (AS05)                     
Streams apply: waiting for dependency    oracle@si-sbr-db (AS04)   

The frequent updating SQL was:

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10
where obj#=:1

We started to replicate a big log table a couple of days ago. That table was previous skipped in log apply. The old logical standby coped the change very well. Did this change cause problem on the new standby?
I increased the cache size of hibernate sequence to 10,000 from default value 20.

alter sequence hitlog.hibernate_sequence cache 10000

The log switch immediately stopped. I was surprised how effective the SQL was.

ORA-16282
At developer’s request, I did switchover again. Went smoothly. Then I was asked to switchover again ( for some reason of course), I encountered an error on the current standby database:

SYS@standby SQL>ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16282: operation not permitted during rolling upgrade

After I faffed around on google, then I recalled I forgot to startup the log apply on the current standby.
Well, the “PREPARE TO SWITCHOVER TO PRIMARY” succeeded after I started the log apply.

miloluo

Hi, Maclean
Primary和Standby之间从搭建完成到现在一直没有发生过切换， 并且不知道你是否注意到，前面提到的sequence的ddl， 我尝试过重建sequence，但是马上不到几秒钟，序列的属性就会被改变，包括cache值，因为现在不在现场，也不好尝试更改，明天我有时间去试试，但是感觉希望不大。

miloluo

试了下扩大sequence，仍然没有过去，而且一下定义就被疯狂的update seq$给更改。。。。

miloluo

可能是碰到了Oracle的一个新bug，目前已提交Oracle开发，但是oracle的这个开发突然要休2 weeks的holidays。 伤不起，等不起啊，只能重搭了。。。

Maclean Liu(刘相兵

logical standby伤不起啊， 升级11g 用ADG吧

miloluo

客户那边是估计不愿升级了。 后续bug的信息会及时跟进来。遇到相同的情况的朋友，果断重搭吧。。。