DataGuard REDO发生跳跃的问题
Windows 2003 + Oracle 10.2.0.4 异地DG，网络经常不稳定，结果导致逻辑备库和物理备库的REDO都发生跳跃现象，但逻辑备库的跳跃现象比物理备库严重很多。以下是逻辑备库上的：
SQL> set pagesize 1000;
SQL>
SQL> select
2 (case
3 when newest_scn = applied_scn then 'Done'
4 when newest_scn <= applied_scn + 9 then 'Done?'
5 when newest_scn > (select max(next_change#) from dba_logstdby_log)
6 then 'Near done'
7 when (select count(*) from dba_logstdby_log
8 where (next_change#, thread#) not in
9 (select first_change#, thread# from dba_logstdby_log)) > 1
10 then 'Gap'
11 when newest_scn > applied_scn then 'Not Done'
12 else '---' end) "Fin?",
13 newest_scn, applied_scn, read_scn from dba_logstdby_progress;
Fin? NEWEST_SCN APPLIED_SCN READ_SCN
--------- ---------- ----------- ----------
Near done 2236293689 2230551363 2230528953
SQL> select newest_time, applied_time, read_time from dba_logstdby_progress;
NEWEST_TIME APPLIED_TIME READ_TIME
----------- ------------ -----------
2011/10/24 2011/10/24 1 2011/10/24
SQL> -- Determine if apply is lagging behind and by how much. Missing
SQL> -- sequence#'s in a range indicate that a gap exists.
SQL> set numwidth 15
SQL> column trd format 99
SQL> select thread# trd, sequence#,
2 first_change#, next_change#,
3 dict_begin beg, dict_end end,
4 to_char(timestamp, 'hh:mi:ss') timestamp,
5 (case when l.next_change# < p.read_scn then 'YES'
6 when l.first_change# < p.applied_scn then 'CURRENT'
7 else 'NO' end) applied
8 from dba_logstdby_log l, dba_logstdby_progress p
9 order by thread#, first_change#;
TR SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# BEG END TIMESTAMP APPLIED
-- --------------- --------------- --------------- --- --- --------- -------
1 31911 2230481985 2230533991 NO NO 12:35:31 CURRENT
1 31913 2230583991 2230634750 NO NO 01:03:44 NO
1 31914 2230634750 2230686751 NO NO 01:11:18 NO
1 31915 2230686751 2230740577 NO NO 01:15:17 NO
1 31917 2230837048 2230892251 NO NO 02:01:34 NO
1 31919 2230947150 2230999973 NO NO 02:48:42 NO
1 31921 2231054591 2231108097 NO NO 03:37:24 NO
1 31922 2231108097 2231162564 NO NO 03:41:09 NO
1 31923 2231162564 2231213751 NO NO 04:01:56 NO
1 31925 2231265829 2231317306 NO NO 04:56:39 NO
1 31926 2231317306 2231368742 NO NO 05:02:12 NO
1 31927 2231368742 2231422597 NO NO 05:20:34 NO
1 31929 2231473892 2231526704 NO NO 06:02:15 NO
1 31930 2231526704 2231577061 NO NO 06:15:36 NO
1 31931 2231577061 2231625643 NO NO 06:30:10 NO
1 31932 2231625643 2231674379 NO NO 06:36:25 NO
1 31934 2231725519 2231775574 NO NO 06:53:56 NO
1 31936 2231825655 2231875104 NO NO 07:11:25 NO
1 31937 2231875104 2231924826 NO NO 07:22:03 NO
.....（中间省略）
SQL>
SQL> select to_char(event_time, 'MM/DD HH24:MI:SS') time,
2 commit_scn, current_scn, event, status
3 from dba_logstdby_events
4 order by event_time, commit_scn, current_scn;
TIME COMMIT_SCN CURRENT_SCN EVENT STATUS
-------------- --------------- --------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
.....（中间省略掉以上时间点之前的）
10/20 00:15:09 2210031391 2210031389 alter sequence seq_fillid increment by 1 ORA-04021: timeout occurred while waiting to lock object TEK.SEQ_FILLID
10/20 00:15:09 ORA-16222: automatic Logical Standby retry of last action
10/20 00:15:10 ORA-16111: log mining and apply setting up
10/21 00:15:08 2214102761 2214102759 alter sequence seq_fillid increment by 1 ORA-04021: timeout occurred while waiting to lock object TEK.SEQ_FILLID
10/21 00:15:08 ORA-16222: automatic Logical Standby retry of last action
10/21 00:15:09 ORA-16111: log mining and apply setting up
10/22 00:15:10 2218796512 2218796510 alter sequence seq_fillid increment by 1 ORA-04021: timeout occurred while waiting to lock object TEK.SEQ_FILLID
10/22 00:15:10 ORA-16222: automatic Logical Standby retry of last action
10/22 00:15:11 ORA-16111: log mining and apply setting up
10/23 00:15:23 2223956958 2223956956 alter sequence seq_fillid increment by 1 ORA-04021: timeout occurred while waiting to lock object TEK.SEQ_FILLID
10/23 00:15:23 ORA-16222: automatic Logical Standby retry of last action
10/23 00:15:24 ORA-16111: log mining and apply setting up
10/24 02:00:16 2229303401 2229303399 alter sequence seq_fillid increment by 1 ORA-04021: timeout occurred while waiting to lock object TEK.SEQ_FILLID
10/24 02:00:16 ORA-16222: automatic Logical Standby retry of last action
10/24 02:00:19 ORA-16111: log mining and apply setting up
说明：ORA-04021的问题与REDO跳跃无关。 附件中为10月24日当天的ALERT： 你好 ， 请运行http://www.oracledatabase12g.com/archives/script-to-collect-data-guard-diagnostic-information.html
这里的完整脚本
主要对primary 和logical standby都要 已经运行完，在附件中，多谢！ firewall的设置没有问题，大部分时间中REDO传送正常，但主备库在异地，之间的网络经常出问题。
现在的问题就在于，网络恢复正常后，中间有些REDO不会重传，而直接传后面的REDO，导致REDO跳跃。
[ 本帖最后由 深圳－刀 于 2011-10-27 23:34 编辑 ] 经各位DX指点，原来是FAL设错了，粗心呀。。。多谢各位！ FAL设错了?结贴！ 通过这2天的观察，改了FAL参数之后，仍然有问题。不过，考虑到逻辑备库的情况比物理备库复杂，我另开一贴，关于物理备库GAP的，解决了物理备库GAP，再回头来解决逻辑备库GAP的问题。
ding ding ding
页:
[1]