OGG同步链路初始化方法

OGG同步链路初始化方法

问题背景

ogg trail文件序号快速增长，为防止达到阈值影响同步，需要提前将trail文件序号恢复至000000。

操作建议及过程

2.1 源端操作

1）长事务检查处理

select b.sid,b.username,xid,a.status,start_time,used_ublk,used_urec,log_io,phy_io from v$transaction a,v$session b where a.ses_addr = b.saddr;

    SID USERNAME        XID              Status START_TIME            USED_UBLK  USED_UREC     LOG_IO     PHY_IO
------- --------------- ---------------- ------ -------------------- ---------- ---------- ---------- ----------
   5319 USER            003D001D0039BA6C ACTIVE 08/26/22 15:57:50             1          1          3      11750
   5322 USER            00120012009855A3 ACTIVE 10/14/22 20:03:42             1          1          9        147
   5325 USER            001F001C00DFAE27 ACTIVE 08/26/22 16:05:24             1          1          3          1
   5328 USER            001E001F00DE22A3 ACTIVE 08/26/22 16:05:28             1          1          3          1
   5331 USER            001C000A00AA1EC1 ACTIVE 08/26/22 16:05:32             1          1          3          0
   5336 USER            0035001A00648E5F ACTIVE 08/26/22 16:05:42             1          1          3          0
   5338 USER            0030001300672C3F ACTIVE 08/26/22 16:05:46             1          1          3         23

select  'alter system kill session '''||sid||','||serial#||''' immediate;'   from v$session where sid in (5319,5322,5325,5328,5331,5334,5336,5338);

alter system kill session '5319,2' immediate;
alter system kill session '5322,2' immediate;
alter system kill session '5325,2' immediate;
alter system kill session '5328,2' immediate;
alter system kill session '5331,2' immediate;
alter system kill session '5334,2' immediate;
alter system kill session '5336,2' immediate;
alter system kill session '5338,2' immediate;

2）停止源端抽取进程

stop   extnew

3）记录停止seq号

info extnew showch

GGSCI > info extnew showch

EXTRACT EXTNEW Last Started 2022-08-24 16:28 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
                     2022-12-09 21:00:37 Seqno 465666, RBA 706396672
                     SCN 3444.2238364703 (14794105732127)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Redo Log

  Startup Checkpoint (starting position in the data source):
    Thread #: 1
    Sequence #: 449540
    RBA: 1042534928
    Timestamp: 2022-08-24 12:35:24.000000
    SCN: 3440.1821991109 (14776509489349)
    Redo File: /archlog/1_449540_665275242.dbf

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Thread #: 1
    Sequence #: 449822
    RBA: 1320789520
    Timestamp: 2022-08-26 15:57:50.000000
    SCN: 3440.1897166952 (14776584665192)
    Redo File: Not Available

  Current Checkpoint (position of last record read in the data source):
    Thread #: 1
    Sequence #: 465666
    RBA: 706396672
    Timestamp: 2022-12-09 21:00:37.000000
    SCN: 3444.2238364703 (14794105732127)
    Redo File: /dev/rredodbs41

  BR Previous Recovery Checkpoint:
    Thread #: 0
    Sequence #: 0
    RBA: 0
    Timestamp: 2022-08-24 16:28:39.530357
    SCN: Not available
    Redo File:

  BR Begin Recovery Checkpoint:
    Thread #: 0
    Sequence #: 465654
    RBA: 1530142224
    Timestamp: 2022-12-09 17:24:14.000000
    SCN: 3444.2234340948 (14794101708372)
    Redo File:

  BR End Recovery Checkpoint:
    Thread #: 1
    Sequence #: 465654
    RBA: 1743555552
    Timestamp: 2022-12-09 17:25:50.000000
    SCN: 3444.2234515374 (14794101882798)
    Redo File:

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 938023
    RBA: 7649670
    Timestamp: 2022-12-09 21:00:39.419938
    Extract Trail: /ogg/dirdat/cc

CSN state information:
  CRC: 9C-EF-CB-EF
  Latest CSN: 14794105732127
  Latest TXN: 29.9.13306304
  Latest CSN of finished TXNs: 14794105732127
  Completed TXNs: 29.9.13306304

Header:
  Version = 2
  Record Source = A
  Type = 10
  # Input Checkpoints = 1
  # Output Checkpoints = 1

File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0

Configuration:
  Data Source = 3
  Transaction Integrity = 1
  Task Type = 0

Status:
  Start Time = 2022-08-24 16:28:39
  Last Update Time = 2022-12-09 21:00:39
  Stop Status = A
  Last Result = 400

4）基于extseqno|extrba重建EXTRACT

cd /ogg/dirprm
cp extnew.prm extnew1.prm --修改参数文件中进程名称

ggsci
#添加进程
add ext extnew1, tranlog, begin now
add exttrail /ogg/dirdat/cc, ext extnew1, megabytes 500
#修改Current Checkpoint
alter extnew1 extseqno xxxx1, extrba xxxx2
#修改Recovery Checkpoint
alter extnew1 ioextseqno xxxx1, ioextrba xxxx2
清空/ogg/dirdat中遗留trail文件
rm /ogg/dirdat/cc*
启动进程
start extnew1

5）重建PUMP

cp   ppnew.prm   ppnew1.prm

注意：修改参数文件的进程名称。

#重新添加pump进程
./ggsci
add ext ppnew1,exttrailsource /ogg/dirdat/cc
add rmttrail /ogg/dirdat/cc,ext ppnew1,megabytes 100
alter ppnew1, extseqno 0, extrba 0

2.2 目标端操作

1）停止目标端Ogg进程，清空trail文件

cd $GG_HOME
./ggsci
stop REP1

cd /ogg/dirdat
rm cc*

2）重定位到000000开始

cd $GG_HOME
./ggsci
alter replicat REP1 ,extseqno 0,extrba 0

2.3 启动进程

1）源端启动pump进程

start ppnew1

注意：确认trail文件已在目标端/ogg/dirdat目录下生成以000000开头的文件。

2）目标端重启replicat进程

start REP1