Oracle 11g ogg单表初始化步骤

转载

mb5ff98246f093d 2019-04-25 11:41:00

ogg单表初始化步骤

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