ogg管理-抓取延迟处理

最新推荐文章于 2023-11-24 19:57:18 发布

执笔画情ora

最新推荐文章于 2023-11-24 19:57:18 发布

阅读量1.3k

点赞数

分类专栏： Goldgate管理与应用

本文链接：https://blog.csdn.net/oradbm/article/details/103954990

版权

Goldgate管理与应用专栏收录该内容

44 篇文章 7 订阅

订阅专栏

1 查看BR需要的归档日志

BR Begin Recovery Checkpoint:
Thread #: 1
Sequence #: 886689
RBA: 211208720
Timestamp: 2020-01-10 20:16:05.000000
SCN: 3009.3346484643 (12926903078307)
Redo File:

BR End Recovery Checkpoint:
Thread #: 1
Sequence #: 886715
RBA: 667506056
Timestamp: 2020-01-10 21:36:40.000000
SCN: 3009.3353922492 (12926910516156)
Redo File:

2 恢复归档日志

RUN {
# backup all archive logs
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=nbu01,NB_ORA_CLIENT=orcl';
restore archivelog from sequence 886689 until sequence 886730;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

RUN {
# backup all archive logs
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=nbu01,NB_ORA_CLIENT=orcl';
restore archivelog from sequence 886760 until sequence 886800;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

RUN {
# backup all archive logs
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=nbu01,NB_ORA_CLIENT=orcl';
restore archivelog from sequence 887001 until sequence 887043;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

3 启动抓取进程

开始BR恢复
bash-3.2$ fuser *
1_886689_817384695.dbf:
1_886690_817384695.dbf:
1_886691_817384695.dbf:
1_886692_817384695.dbf: 8438o
1_886693_817384695.dbf:
1_886694_817384695.dbf:
1_886695_817384695.dbf:
1_886696_817384695.dbf:
1_886697_817384695.dbf:
1_886698_817384695.dbf:
1_886699_817384695.dbf:

BR恢复完成后，开始抓新得事务，并将tail文件传输到目标端。
GGSCI (orcl) 30> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 36:57:39 00:00:07
EXTRACT RUNNING EXTorcl_A 36:57:02 00:00:01

GGSCI (orcl) 31> !
info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 36:57:39 00:00:09
EXTRACT RUNNING EXTorcl_A 36:57:02 00:00:03

GGSCI (orcl) 32>

bash-3.2$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.27 19591627 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_141006.1156_FBO
Solaris, sparc, 64bit (optimized), Oracle 11g on Oct 13 2014 12:52:21

GGSCI (orcl) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 24:22:37 00:00:08
EXTRACT RUNNING EXTorcl_A 24:22:23 00:00:06

GGSCI (orcl) 2> !
info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 24:22:37 00:00:10
EXTRACT RUNNING EXTorcl_A 24:22:23 00:00:08

GGSCI (orcl) 3>

4 删除归档日志

由于归档日志已经存在备份，将抓取进程使用过的归档删除后，及时释放空间。

delete archivelog from sequence 886721 until sequence 887094;

当删除掉最后的一个恢复过来的归档之后，对归档做一次备份，

但是不删除##，后面应用后逐步删除，并查看备份中最大的归档日志序列号：xxxxx。 input archived log thread=1 sequence=887304 RECID=890602 STAMP=1029542774

########################1_887044_817384695.dbf##################

5 过程分析

1 由于存在未知的长事务，BR会重构temp。

2 在抓取过程中，产生BR，将超过BR时间点内的事务缓存到dirtemp目录中，具体的现象看下面对应抓取进程的延迟以及走向。

2020-01-12 11:18:19 WARNING OGG-01027 Long Running Transaction: XID 95.46.803697, Items 0, Extract EXTorcl_A, Redo Thread 1, SCN 3009.61007598 (12923617601262), Redo Seq #880041, Redo RBA 191026704.
select b.sql_text,sid,blocking_session,a.program from v$session a, v$sql b where a.sql_id = b.sql_id
and a.sid=1217;

SQL> select t.addr,t.XIDUSN,t.XIDSLOT,t.XIDSQN,t.START_DATE from gv$transaction t order by t.START_DATE --where XIDUSN=95 and XIDSLOT=46;

ADDR XIDUSN XIDSLOT XIDSQN START_DATE
---------------- ---------- ---------- ---------- -------------------
0000004CF12D0A58 95 46 803697 2019-12-19 14:21:27

SQL> select sid,serial#,status,program from v$session where taddr='0000004CF12D0A58';

SID SERIAL# STATUS PROGRAM
---------- ---------- ---------------- --------------------
1217 46119 INACTIVE JDBC Thin Client

SET LINES 129
COL T.ADDR FOR A20
COL START_DATE FOR A30
select t.addr,t.XIDUSN,t.XIDSLOT,t.XIDSQN,t.START_DATE,T1.STATUS from gv$transaction t,V$SESSION T1 WHERE T1.TADDR=T.ADDR AND T1.STATUS='INACTIVE' order by t.START_DATE;
SQL> SET LINES 129
SQL> COL T.ADDR FOR A20
SQL> COL START_DATE FOR A30
SQL> select t.addr,t.XIDUSN,t.XIDSLOT,t.XIDSQN,t.START_DATE,T1.STATUS from gv$transaction t,V$SESSION T1 WHERE T1.TADDR=T.ADDR AND T1.STATUS='INACTIVE' order by t.START_DATE;

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D90E68478 109 16 615417 2019-09-25 01:25:08 INACTIVE
0000004D50BFDA68 67 10 4092420 2019-11-27 10:06:50 INACTIVE
0000004CF12D0A58 95 46 803697 2019-12-19 14:21:27 INACTIVE
0000004D10C434B0 98 26 847097 2019-12-19 14:23:48 INACTIVE
0000004D3120AE98 13 9 3191303 2019-12-19 14:23:48 INACTIVE
0000004D10C40260 123 42 382341 2019-12-23 16:34:33 INACTIVE
0000004E949794F0 28 10 3788312 2019-12-25 11:09:35 INACTIVE
0000004D50C002A8 81 28 1334123 2019-12-25 11:09:51 INACTIVE
0000004CF1371EF8 75 2 1485228 2019-12-25 11:10:26 INACTIVE
0000004D311E6408 85 30 1208908 2019-12-25 11:50:46 INACTIVE
0000004D10CBE078 34 5 3625218 2019-12-27 05:10:46 INACTIVE

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D90E2AE88 64 38 2341431 2019-12-27 08:11:06 INACTIVE
0000004D50C1E898 68 21 3156759 2019-12-27 14:11:13 INACTIVE
0000004E949762A0 99 14 761335 2019-12-28 02:10:45 INACTIVE
0000004D90F27EF0 1 13 3862217 2020-01-06 13:39:25 INACTIVE
0000004E94937890 45 7 3681938 2020-01-06 17:36:01 INACTIVE
0000004D71345AD8 43 45 3247495 2020-01-06 20:55:40 INACTIVE
0000004E94918890 91 33 941982 2020-01-06 23:44:44 INACTIVE
0000004CF130E048 53 42 3439295 2020-01-07 09:28:50 INACTIVE
0000004D50BBC000 84 22 1235759 2020-01-07 11:10:46 INACTIVE
0000004E949B6AE0 61 3 2637957 2020-01-07 14:44:44 INACTIVE
0000004D71404B70 78 0 1291769 2020-01-10 23:21:52 INACTIVE

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D50C7C2A8 73 28 1629231 2020-01-10 23:21:53 INACTIVE
0000004D10CBB838 105 17 670622 2020-01-12 18:54:56 INACTIVE
0000004D10C7D838 92 22 884879 2020-01-12 19:01:51 INACTIVE
0000004D10C7E248 20 44 3213125 2020-01-12 19:04:17 INACTIVE
0000004E9491B0D0 47 45 3785951 2020-01-12 19:07:05 INACTIVE
0000004D50C7A478 96 14 974484 2020-01-12 19:21:57 INACTIVE
0000004CF1294870 27 34 3182207 2020-01-12 19:25:37 INACTIVE
0000004D713E1F10 21 43 3266439 2020-01-12 19:38:48 INACTIVE
0000004E94957CB0 25 19 3655170 2020-01-12 19:38:50 INACTIVE
0000004D50BDD648 101 45 791129 2020-01-12 19:43:37 INACTIVE
0000004D312674A0 3 6 3490734 2020-01-12 20:15:42 INACTIVE

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D713A3F10 46 7 3223139 2020-01-12 20:16:51 INACTIVE
0000004D50C02AE8 58 1 2752816 2020-01-12 20:16:53 INACTIVE
0000004D10CFB668 2 18 3985019 2020-01-12 20:33:36 INACTIVE
0000004D90EE80C0 52 19 3747232 2020-01-12 21:12:51 INACTIVE
0000004D50C7CCB8 57 19 3759492 2020-01-12 21:18:37 INACTIVE
0000004D10C7F668 6 6 3009046 2020-01-12 21:22:47 INACTIVE
0000004D10C1E010 80 44 1096719 2020-01-12 21:25:29 INACTIVE
0000004D71384500 60 34 3343225 2020-01-12 21:26:47 INACTIVE
0000004D311E6E18 70 47 1772563 2020-01-12 21:27:36 INACTIVE
0000004E948D8068 100 24 781222 2020-01-12 21:27:36 INACTIVE
0000004D713260C8 33 44 3613610 2020-01-12 21:28:00 INACTIVE

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D713654E8 10 24 3494476 2020-01-12 21:28:03 INACTIVE
0000004D50B7EA10 71 4 2050864 2020-01-12 21:28:35 INACTIVE
0000004D713826D0 24 2 3437676 2020-01-12 21:28:35 INACTIVE
0000004D50C5DCB8 26 8 3347731 2020-01-12 21:28:46 INACTIVE
0000004D50B9F840 94 37 878607 2020-01-12 21:29:15 INACTIVE
0000004D10BFF010 88 10 1020826 2020-01-12 21:30:19 INACTIVE
0000004D50C1B648 9 46 3358983 2020-01-12 21:40:32 INACTIVE
0000004CF136D888 4 26 3218317 2020-01-12 21:42:05 INACTIVE
0000004D50BFE478 59 20 3241083 2020-01-12 21:42:18 INACTIVE
0000004CF12B1A40 32 3 3275213 2020-01-12 22:09:08 INACTIVE
0000004D312E48C0 5 2 3216512 2020-01-12 22:11:37 INACTIVE

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D50C3C478 63 42 3075106 2020-01-12 22:12:11 INACTIVE
0000004E949192A0 55 25 3661020 2020-01-12 22:16:33 INACTIVE
0000004D10C9B418 41 6 3970317 2020-01-12 22:16:33 INACTIVE
0000004CF134F298 69 32 2364417 2020-01-12 22:19:10 INACTIVE
0000004D50C5FAE8 107 27 705247 2020-01-12 22:19:11 INACTIVE
0000004D50C5B478 35 19 3928900 2020-01-12 22:19:11 INACTIVE
0000004CF13130C8 62 33 3091463 2020-01-12 22:19:11 INACTIVE
0000004D10C5BFF8 16 22 3350927 2020-01-12 22:19:11 INACTIVE
0000004D90EE8AD0 82 5 1359303 2020-01-12 22:19:11 INACTIVE
0000004D50C3CE88 31 45 3394172 2020-01-12 22:19:42 INACTIVE
0000004D50C206C8 79 0 1374482 2020-01-12 22:19:46 INACTIVE

ADDR XIDUSN XIDSLOT XIDSQN START_DATE STATUS
---------------- ---------- ---------- ---------- ------------------------------ ----------------
0000004D90E4A898 38 7 4169142 2020-01-12 22:20:02 INACTIVE

###################在写入temp时###########################

开始缓存超过BR时间点的事务
bash-3.2$ ls -lrth
total 247264
-rw-r----- 1 orcl oinstall 17M Jan 12 23:10 extorcl_a_p16399_Redo Thread 1_10514a590_00000.cm
-rw-r----- 1 orcl oinstall 38M Jan 12 23:10 extorcl_a_p16399_Redo Thread 1_1052e4870_00000.cm
-rw-r----- 1 orcl oinstall 39M Jan 12 23:10 extorcl_a_p16399_Redo Thread 1_105154080_00000.cm
-rw-r----- 1 orcl oinstall 27M Jan 12 23:10 extorcl_a_p16399_Redo Thread 1_1051cfc00_00000.cm
bash-3.2$

GGSCI (orcl) 27> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 00:00:00 00:00:02
EXTRACT RUNNING EXTorcl_A 22:26:49 00:00:03

GGSCI (orcl) 28> !
info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 00:00:00 00:00:03
EXTRACT RUNNING EXTorcl_A 22:25:18 00:00:04

##############################处理temp时###################################################

事务已经提交开始处理缓存的temp文件。
bash-3.2$ ls -lrth
total 122096
-rw-r----- 1 orcl oinstall 17M Jan 12 22:09 extorcl_a_p16399_Redo Thread 1_105309870_00000.cm
-rw-r----- 1 orcl oinstall 43M Jan 12 22:09 extorcl_a_p16399_Redo Thread 1_1051c06e0_00000.cm ----超过BR写入dirtmp

bash-3.2$ fuser *
extorcl_a_p16399_Redo Thread 1_10517a9f0_00000.cm:
extorcl_a_p16399_Redo Thread 1_105194c80_00000.cm:
extorcl_a_p16399_Redo Thread 1_105205840_00000.cm:
extorcl_a_p16399_Redo Thread 1_1052105b0_00000.cm:
extorcl_a_p16399_Redo Thread 1_10529af60_00000.cm: 8185m
bash-3.2$ fuser *
extorcl_a_p16399_Redo Thread 1_10517a9f0_00000.cm:
extorcl_a_p16399_Redo Thread 1_105194c80_00000.cm:
extorcl_a_p16399_Redo Thread 1_105205840_00000.cm:
extorcl_a_p16399_Redo Thread 1_1052105b0_00000.cm:
extorcl_a_p16399_Redo Thread 1_10529af60_00000.cm: 8185m

GGSCI (orcl) 2> !
info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPEorcl_A 24:22:37 00:20:10
EXTRACT RUNNING EXTorcl_A 24:22:23 00:20:08 ----在处理长交易

bash-3.2$ cd dirtmp/
bash-3.2$ ls
bash-3.2$ ls

执笔画情ora

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ogg管理-抓取延迟处理

1查看BR需要的归档日志BR Begin Recovery Checkpoint: Thread #: 1 Sequence #: 886689 RBA: 211208720 Timestamp: 2020-01-10 20:16:05.000000 SCN: 3009.3346484643 (12926903078307) Redo File...
复制链接

扫一扫

专栏目录