ogg同步部分列配置

时间：2020-05-21 14:33:54 阅读：50 评论：0 收藏：0 [点我收藏+]

--原文：http://www.xifenfei.com/2015/09/ogg%E5%90%8C%E6%AD%A5%E9%83%A8%E5%88%86%E5%88%97%E9%85%8D%E7%BD%AE.html

最近有客户需求,a库在内网,b库在外网,希望同步a库中几个基础业务表的每个表的几个字段同步到b库中,采用a–>c–>b的方式来实现同步(c同时接通内外网)

源端数据库准备
启动归档模式，开启强制日志和辅助日志，创建测试用户/表，ogg用户

SQL> create user xifenfei identified by xifenfei;
 
User created.
 
SQL> grant dba to xifenfei;
 
Grant succeeded.
 
SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
 
Table created.
 
SQL> alter table t_xifenfei add constraint pk_t_xifenfei primary key(object_id); 
 
Table altered.
 
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Current log sequence           11
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  901775360 bytes
Fixed Size                  2024944 bytes
Variable Size             239077904 bytes
Database Buffers          658505728 bytes
Redo Buffers                2166784 bytes
Database mounted.
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> alter database force logging;
 
Database altered.
 
SQL>  alter database add supplemental log data;
 
Database altered.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> create user ogg identified by oracle;
 
User created.
 
SQL> grant dba to ogg;
 
Grant succeeded.

配置mgr进程

[oracle@xffdbrh5 ogg]$ export PATH=/u01/ogg:$PATH
[oracle@xffdbrh5 ogg]$ export LD_LIBRARY_PATH=/u01/ogg:$ORACLE_HOME/lib
 
[oracle@xffdbrh5 ogg]$ ./ggsci
 
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_02 16363018 OGGCORE_11.2.1.0.6_PLATFORMS_130301.1500_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Mar  1 2013 19:04:05
 
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
 
 
GGSCI (xffdbrh5) 1> create subdirs
 
Creating subdirectories under current directory /u01/ogg
 
Parameter files                /u01/ogg/dirprm: already exists
Report files                   /u01/ogg/dirrpt: created
Checkpoint files               /u01/ogg/dirchk: created
Process status files           /u01/ogg/dirpcs: created
SQL script files               /u01/ogg/dirsql: created
Database definitions files     /u01/ogg/dirdef: created
Extract data files             /u01/ogg/dirdat: created
Temporary files                /u01/ogg/dirtmp: created
Stdout files                   /u01/ogg/dirout: created
 
GGSCI (xffdbrh5) 2> edit param mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
 
GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle
Successfully logged into database.
 
GGSCI (xffdbrh5) 4> add checkpointtable ogg.ggs_checkpoint
 
Successfully created checkpoint table ogg.ggs_checkpoint.
 
GGSCI (xffdbrh5) 5> EDIT PARAMS ./GLOBALS
ogg.ggs_checkpoint
 
 
GGSCI (xffdbrh5) 6> start mgr
 
Manager started.
 
 
GGSCI (xffdbrh5) 7> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           

配置extract进程

GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle
Successfully logged into database.
 
GGSCI (xffdbrh5) 4> add trandata xifenfei.t_xifenfei
 
Logging of supplemental redo data enabled for table XIFENFEI.T_XIFENFEI.
 
GGSCI (xffdbrh5) 5>  add extract ext_1, tranlog, begin now, threads 1
EXTRACT added.
 
 
GGSCI (xffdbrh5) 6>  add EXTTRAIL ./dirdat/r1, extract ext_1,megabytes 100
EXTTRAIL added.
 
GGSCI (xffdbrh5) 7> edit param ext_1
EXTRACT ext_1
userid ogg,password oracle
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext_1.dsc,APPEND,MEGABYTES 1024
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS DISABLESUPPLOGCHECK   --bug 16857778
TABLE xifenfei.t_xifenfei, COLS (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID);
 
GGSCI (xffdbrh5) 8> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXT_1       00:00:00      00:00:22    
 
GGSCI (xffdbrh5) 9> start ext_1
 
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
 
 
GGSCI (xffdbrh5) 10> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:01:18      00:00:00    

配置pump data进程

GGSCI (xffdbrh5) 1> edit param dpe_1
 
 
extract dpe_1
dynamicresolution
passthru
rmthost 192.168.137.251, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
TABLE xifenfei.t_xifenfei;
 
 
GGSCI (xffdbrh5) 2> start dpe_1
 
Sending START request to MANAGER ...
EXTRACT DPE_1 starting
 
 
GGSCI (xffdbrh5) 3> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPE_1       00:00:00      00:16:47    
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:07    

目标端数据库准备

[oracle@xifenfei ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 13 00:40:19 2014
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> create user ogg identified by oracle;
 
User created.
 
SQL> grant dba to ogg;
 
Grant succeeded.
 
SQL> create user xff identified by xifenfei;
 
User created.
 
SQL> grant dba to xff;
 
Grant succeeded.
 
SQL> conn xff/xifenfei
Connected.
SQL> create  database link syc_data
  2    connect to ogg identified by oracle
  3    using ‘(DESCRIPTION =
  4    (ADDRESS_LIST =
  5    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.252)(PORT = 1521))
  6    )
  7    (CONNECT_DATA =
  8    (SERVER = DEDICATED)
  9    (SERVICE_NAME = ora10g)
 10    )
 11    )‘;
 
Database link created.
 
SQL> select count(*) from xifenfei.t_xifenfei@syc_data;
 
  COUNT(*)
----------
      9917
 
SQL> SELECT CURRENT_SCN FROM V$DATABASE@syc_data;
 
CURRENT_SCN
-----------
     793069
 
SQL> create table xff.t_xff as select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 2 > OBJECT_ID from xifenfei.t_xifenfei@syc_data AS OF SCN  793069;
 
Table created.
 
SQL> alter table xff.t_xff add constraint pk_t_xff primary key(object_id); 
 
Table altered.

目标端mgrp配置

[oracle@xifenfei ogg]$export LD_LIBRARY_PATH=/home/oracle/amdu:$ORACLE_HOME/lib:/u01/oracle/oradata/ogg
[oracle@xifenfei ogg]$ ./ggsci
 
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
 
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (xifenfei) 8> edit param mgr
port 7839
DYNAMICPORTLIST 7840-7850
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
autorestart extract *, waitminutes 1, retries 60
autorestart replicat *, waitminutes 1, retries 60
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
 
GGSCI (xifenfei) 12> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     STOPPED                                           
 
 
GGSCI (xifenfei) 13> create subdirs
 
Creating subdirectories under current directory /u01/oracle/oradata/ogg
 
Parameter files                /u01/oracle/oradata/ogg/dirprm: already exists
Report files                   /u01/oracle/oradata/ogg/dirrpt: already exists
Checkpoint files               /u01/oracle/oradata/ogg/dirchk: already exists
Process status files           /u01/oracle/oradata/ogg/dirpcs: already exists
SQL script files               /u01/oracle/oradata/ogg/dirsql: already exists
Database definitions files     /u01/oracle/oradata/ogg/dirdef: already exists
Extract data files             /u01/oracle/oradata/ogg/dirdat: already exists
Temporary files                /u01/oracle/oradata/ogg/dirtmp: already exists
Stdout files                   /u01/oracle/oradata/ogg/dirout: already exists
 
GGSCI (xifenfei) 2> dblogin userid ogg, password oracle
Successfully logged into database.
 
GGSCI (xifenfei) 3> add checkpointtable ogg.ggs_checkpoint  
 
Successfully created checkpoint table ogg.ggs_checkpoint.
 
GGSCI (xifenfei) 4> EDIT PARAMS ./GLOBALS
 
checkpointtable ogg.ggs_checkpoint
 
GGSCI (xifenfei) 5> start mgr
 
Manager started.
 
GGSCI (xifenfei) 6> add replicat rep_1,exttrail ./dirdat/t1,checkpointtable ogg.ggs_checkpoint
REPLICAT added.
 
 
GGSCI (xifenfei) 7> edit params rep_1    
 
 
REPLICAT rep_1
USERID ogg,PASSWORD oracle
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
assumetargetdefs
DISCARDFILE ./dirrpt/rep_1.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 3:00
ALLOWNOOPUPDATES
MAP xifenfei.t_xifenfei, TARGET xff.t_xff;
 
GGSCI (xifenfei) 8> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
REPLICAT    STOPPED     REP_1       00:00:00      00:01:45    
 
 
GGSCI (xifenfei) 9> start rep_1,aftercsn 793069
 
Sending START request to MANAGER ...
REPLICAT REP_1 starting
 
 
GGSCI (xifenfei) 10> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP_1       00:00:00      00:00:01    

测试数据库同步

--源端库
SQL> desc t_XIFENFEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> update t_XIFENFEI set owner=‘www.xifenfei.com‘ where rownum<100;
 
99 rows updated.
 
SQL> commit;
 
Commit complete.
 
--目标端库
SQL> desc xff.t_xff
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 
SQL> select count(*) from xff.t_xff where owner=‘www.xifenfei.com‘;
 
  COUNT(*)
----------
        99
 
--源端库
SQL> delete from t_XIFENFEI where  owner=‘www.xifenfei.com‘;
 
99 rows deleted.
 
SQL> commit;
 
Commit complete.
 
--目标端
SQL> select count(*) from xff.t_xff where owner=‘www.xifenfei.com‘;
 
  COUNT(*)
----------
         0
 
--源端库
SQL> insert into xifenfei.t_xifenfei(owner,object_id) values(‘www.xifenfei.com‘,1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
--目标端库
SQL>  select count(*) from xff.t_xff where owner=‘www.xifenfei.com‘;
 
  COUNT(*)
----------
         1
 
SQL> select * from xff.t_xff where owner=‘www.xifenfei.com‘;
 
OWNER                OBJECT_NAME         SUBOBJECT_NAME                  OBJECT_ID
-------------------- ------------------- ------------------------------ ----------
www.xifenfei.com                       

这里实现部分列同步,主要在extract端使用COLS捕获需要列,使用ctas结合dblink,flashback query实现表测试后.

ogg同步部分列配置

原文：https://www.cnblogs.com/ss-33/p/12930363.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)