- 最后登录
- 2014-3-8
- 在线时间
- 38 小时
- 威望
- 121
- 金钱
- 945
- 注册时间
- 2011-10-26
- 阅读权限
- 50
- 帖子
- 38
- 精华
- 1
- 积分
- 121
- UID
- 70
|
1#
发表于 2011-10-26 23:39:39
|
查看: 8368 |
回复: 8
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跳跃无关。 |
|