ogg单表初始化步骤
2018-06-08 14:55 296 0 原创 GoldenGate
1.ogg单表初始化步骤
1.1.目标端数据被误删除
SQL> conn test/test
Connected.
SQL> select count(*) from test1;
COUNT(*)
----------
100000
SQL> delete from test1;
100000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test1;
COUNT(*)
----------
0
1.2.先停掉replicat进程
GGSCI (cndba) 6> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (cndba) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:06
1.3.获取当前源端的scn号
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1184010
1.4.源端增量数据在产生
SQL> conn test/test
Connected.
SQL> insert into test values(6,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- --------
1 zhangsan
2 lisi
3 wanger
4 test
5 test
6 test
1.5.导出要同步的表、重新导入
在数据实时性要求较高的系统，首先确定问题表，然后只在目标端配置文件中将问题表剔除：MAPEXCLUDE test.test1;然后启动进程即可。(此过程没在此篇文章没体现)
--源端导出需要同步的表
expdp system/oracle directory=dump_dir dumpfile=test1.dmp logfile=test1.log tables=test.test1 flashback_scn=1184010
--将dmp文件传送到目标端
[root@cndba backup]# scp test1.dmp 192.168.1.86:/backup
--在目标端给dmp 授oracle访问权限
[root@cndba ~]# cd /backup/
[root@cndba backup]# chown oracle:oinstall test1.dmp
--目标端导入数据
impdp system/oracle directory= dump_dir dumpfile=test1.dmp tables=test.test1 table_exists_action=replace
SQL> select count(*) from test1;
COUNT(*)
----------
100001
1.6.修改replicat参数文件
GGSCI (cndba) 20> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=cndba)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
--HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
--HANDLECOLLISIONS
map test.test1, target test.test1, filter (@GETENV("transaction","csn") > 1181589);
--对于这条语句，如果使用的11g的ogg，需要使用双引号"transaction"，"csn"，如果使用的是12c的ogg使用单引号
--否则报错：OGG-01298 Oracle GoldenGate Delivery for Oracle, rep1.prm: Column function diagnostic message: could not find column 'transaction'.
MAP test.*, TARGET test.*;
--ddl include all
--ddlerror default ignore retryop maxretries 3 retry delay 5
1.7.启动抽取进程
GGSCI (cndba) 18> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (cndba) 19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
1.8.测试数据同步
--源端插入数据
SQL> insert into test1 values(100002,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from test1;
COUNT(*)
----------
100002
--目标端查看数据
增量数据也同步过来
SQL> select * from test;
ID NAME
---------- --------
5 test
1 zhangsan
2 lisi
3 wanger
4 test
6 test
误删除的表数据数据也同步过来了
SQL> select count(*) from test1;
COUNT(*)
----------
100002