oracle ogg filter,Oracle Goldengate使用filter+@GETENV在线重新初始化指定的table

在Oracle-Oracle

Goldengate的复制环境中，为了避免影响整个复制环境的复制进度，采取跳过错误事务或者跳过特定对象的办法使得goldengate继续同步；如果后续某个表不得不需要重新同步，而且应用是不间断进行事务操作的，在不停止应用和重建整个复制环境的情况下，为了保证数据的一致性，处理的办法还是不少的，下面给出一个在replicat端过滤SCN事务的办法，来实现数据的一致同步。

处理的思路就是首先在target上获得该表上某个特定SCN版本上的数据(比如使用导入导出或者数据泵)，然后通过filter功能来筛选出该表上大于该commit

scn的事务，从而确保事务的一致性。在goldengate v10之后，可以通过@GETENV函数直接获得事务的CSN。

简单看一下处理步骤，假设目前gg环境中hr.tbtest表需要重新初始化，由source端hr用户同步到target端hr2用户下。

1.停止target上的replicat进程

GGSCI (sh) 44> stop rephr

Sending STOP request to REPLICAT REPHR …

Request processed.

2.获得source上hr.tbtest表的特定SCN一致性版本

SQL> select dbms_flashback.get_system_change_number from

dual;

9543774

使用exp进行一致性版本导出

[oracle@gz ~]$ exp hr/hr tables=translog

file=translog.dmpflashback_scn=9543774

Export: Release 10.2.0.1.0 – Production on Tue Jan 9 00:27:42

2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release

10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in US7ASCII character set and AL16UTF16 NCHAR character

set

server uses ZHS16GBK character set (possible charset

conversion)

About to export specified tables via Conventional Path …

. . exporting table TRANSLOG 5062409 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

3. target上导入一致性版本

[oracle@gz ~]$ imp hr2/hr2@sh

tables=translog file=translog.dmp ignore=y

Import: Release 10.2.0.1.0 – Production on Tue Jan 9 00:28:36

2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release

10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional

path

Warning: the objects were exported by HR, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR

character set

import server uses ZHS16GBK character set (possible charset

conversion)

. importing HR’s objects into HR2

. importing HR’s objects into HR2

. . importing table "TRANSLOG" 50624 rows imported

IMP-00017: following statement failed with ORACLE error

30567:

"ALTER TABLE "TRANSLOG" ADD SUPPLEMENTAL LOG GROUP

"GGS_TRANSLOG_56459" ("OB"

"JECT_ID") ALWAYS"

IMP-00003: ORACLE error 30567 encountered

ORA-30567: name already used by an existing log group

Import terminated successfully with warnings.

4. 修改replicat参数，过滤hr.tbtest上小于SCN 9543774上的事务。

GGSCI (sh) 43> view param rephr

replicat rephr

USERID gg, PASSWORD AACAAAAAAAAAAACANJPHHDSJCIYCFCZB,ENCRYPTKEY

DEFAULT

assumetargetdefs

map hr.tbtest, target hr2.translog,filter (@GETENV ("transaction",

"csn") > 9

543774);

map hr.org, target hr2.org;

map hr.unp_file, target hr.unp_file;

5. 启动replicat进程

GGSCI (sh) 2> start rephr

Sending START request to MANAGER …

REPLICAT REPHR starting

采用这种办法，即使source上hr.tbtest表不断被DML，因为SCN过滤的存在，保证了复制过程中的数据一致性。对source上的应用来讲，也无须额外的停机时间。在初始化过程中，因为target上复制进程的暂停，会影响到复制的实时性，为减少target上replicat的暂停时间，可以采用分割datapump和replicate

group的方法，也可实现特定对象的在线初始化