oracle单表实时同步,添加单表在线同步ogg

1由于源端抽取是xxx.*,投递也是xxx.*，所以目队列包含需要同步的表

2源端数据库获取当前最新scn

SQL> select

to_char(dbms_flashback.get_system_change_number()) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

15616139721983

SQL>

3源端数据库按照指定的scn导出表数据

exp xxx/xxx direct=y rows=y buffer=64000000

flashback_scn=15616139721983 constraints=n grants=n triggers=n

STATISTICS=none file=/zyhome/oracle/cml/BUILDING.dmp

log=/zyhome/oracle/cml/exp_BUILDING.log tables=BUILDING

exp xxx/xxx direct=y rows=y buffer=64000000

flashback_scn=15616139721983 constraints=n grants=n triggers=n

STATISTICS=none file=/zyhome/oracle/cml/BREAKPOINT.dmp

log=/zyhome/oracle/cml/exp_BREAKPOINT.log tables=BREAKPOINT

exp xxx/xxx direct=y rows=y buffer=64000000

flashback_scn=15616139721983 constraints=n grants=n triggers=n

STATISTICS=none file=/zyhome/oracle/cml/POLE.dmp

log=/zyhome/oracle/cml/exp_POLE.log tables=POLE

exp xxx/xxx direct=y rows=y buffer=64000000

flashback_scn=15616139721983 constraints=n grants=n triggers=n

STATISTICS=none file=/zyhome/oracle/cml/MANHOLE.dmp

log=/zyhome/oracle/cml/exp_MANHOLE.log tables=MANHOLE

4 停止复制进程180

Stop r_ossdb1

5 将导出的数据导入到目标端数据库

imp xxx/xxx file=/home/oracle/cml/BUILDING.dmp

log=/home/oracle/cml/IMP_BUILDING.LOG full=y ignore=y

buffer=64000000

imp xxx/xxx file=/home/oracle/cml/BREAKPOINT.dmp

log=/home/oracle/cml/IMP_BREAKPOINT.LOG full=y ignore=y

buffer=64000000

imp xxx/xxx file=/home/oracle/cml/POLE.dmp

log=/home/oracle/cml/IMP_POLE.LOG full=y ignore=y

buffer=64000000

imp xxx/xxx file=/home/oracle/cml/MANHOLE.dmp

log=/home/oracle/cml/IMP_MANHOLE.LOG full=y ignore=y

buffer=64000000

6目标端数据库查看表上是否有触发器，禁掉新增表上的trigger与有级联删除的约束

7修改复制进程配置，增加复制表map关系，用filter指定复制scn

MAP xxx.BUILDING,TARGET xxx.BUILDING filter (

@getenv("TRANSACTION", "CSN") > 15616139721983);

MAP xxx.BREAKPOINT,TARGET xxx.BREAKPOINT filter (

@getenv("TRANSACTION", "CSN") > 15616139721983);

MAP xxx.POLE,TARGET xxx.POLE filter ( @getenv("TRANSACTION",

"CSN") > 15616139721983);

MAP xxx.MANHOLE,TARGET xxx.MANHOLE filter (

@getenv("TRANSACTION",

"CSN") > 15616139721983);

8启动复制进程

Start r_ossdb1

9 等状态同步完成，去掉注释掉7中参数配置,添加map重启复制进程