GOLDENGATE DML单向同步

最新推荐文章于 2021-04-12 06:00:01 发布

wshxgxiaoli

最新推荐文章于 2021-04-12 06:00:01 发布

阅读量1.2k

点赞数

分类专栏： ORACLE GOLDENGATE 文章标签： Oracle goldengate

本文链接：https://blog.csdn.net/wshxgxiaoli/article/details/9701071

版权

ORACLE GOLDENGATE 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 环境介绍
ORACLE版本： 10.2.0.1
GG版本： 11.2.1

2.解压安装包。
3.配置环境变量。
源端：增加如下配置

export GG_HOME=/home/oracle/gg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib

目标端：增加如下配置

export GG_HOME=/home/oracle/gg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib

3. 创建目录
源端：

[oracle@GOLDENGATE1 gg]$ ./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 (GOLDENGATE1) 1> create subdirs

Creating subdirectories under current directory /home/oracle/gg

Parameter files                /home/oracle/gg/dirprm: already exists
Report files                   /home/oracle/gg/dirrpt: created
Checkpoint files               /home/oracle/gg/dirchk: created
Process status files           /home/oracle/gg/dirpcs: created
SQL script files               /home/oracle/gg/dirsql: created
Database definitions files     /home/oracle/gg/dirdef: created
Extract data files             /home/oracle/gg/dirdat: created
Temporary files                /home/oracle/gg/dirtmp: created
Stdout files                   /home/oracle/gg/dirout: created


GGSCI (GOLDENGATE1) 2> exit
[oracle@GOLDENGATE1 gg]$ mkdir trails

目标端：

[oracle@GOLDENGATE2 gg]$ ./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 (GOLDENGATE2) 1> create subdirs

Creating subdirectories under current directory /home/oracle/gg

Parameter files                /home/oracle/gg/dirprm: already exists
Report files                   /home/oracle/gg/dirrpt: created
Checkpoint files               /home/oracle/gg/dirchk: created
Process status files           /home/oracle/gg/dirpcs: created
SQL script files               /home/oracle/gg/dirsql: created
Database definitions files     /home/oracle/gg/dirdef: created
Extract data files             /home/oracle/gg/dirdat: created
Temporary files                /home/oracle/gg/dirtmp: created
Stdout files                   /home/oracle/gg/dirout: created


GGSCI (GOLDENGATE2) 2> exit
[oracle@GOLDENGATE2 gg]$ mkdir trails

4.创建GOLDENGATE用户
源端：

SQL> create tablespace goldengate datafile'/home/oracle/oradata/gg1/gg01.dbf' size 500m;

Tablespace created.

SQL> create user goldengate identified by goldengate default tablespace goldengate;

User created.

SQL> grant dba to goldengate;

Grant succeeded.

目标端：

SQL>  create tablespace goldengate datafile'/home/oracle/oradata/gg1/gg01.dbf' size 500m;

Tablespace created.

SQL> create user goldengate identified by goldengate default tablespace goldengate;

User created.

SQL> grant dba to goldengate;

Grant succeeded.

5. 源库修改归档模式

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218292 bytes
Variable Size              83888396 bytes
Database Buffers           75497472 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

6. 源库增加辅助日志

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL>  select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

7. GLOBALS文件配置
源端：

GGSCI (GOLDENGATE1) 1> edit params ./GLOBALS
GGSCHEMA goldengate

目标端：

GGSCI (GOLDENGATE2) 1> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint

目标端添加CHKPOINT表：

GGSCI (GOLDENGATE2) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.

GGSCI (GOLDENGATE2) 3> add checkpointtable goldengate.chkpoint

Successfully created checkpoint table goldengate.chkpoint.

8. MGR配置
源端：

GGSCI (GOLDENGATE1) 2> edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /opt/gg/trails/w1*, USECHECKPOINTS, MINKEEPFILES 10

目标端：

GGSCI (GOLDENGATE1) 2> edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /opt/gg/trails/w1*, USECHECKPOINTS, MINKEEPFILES 10

9. 源端extrail配置：
添加EXTRAIL： add extract w1ext, tranlog, begin now
增加配置文件：edit params w1ext

EXTRACT w1ext
USERID goldengate, PASSWORD goldengate
EXTTRAIL /home/oracle/gg/trails/w1
DISCARDFILE w1extdsc,APPEND,MEGABYTES 5
TRANLOGOPTIONS ALTARCHIVELOGDEST /home/oracle/archivelog
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE hr.*;

10. 添加该extract对应的trail文件，用来存储抽取的数据。单个文件大小设置为100MB

add exttrail /home/oracle/gg/trails/w1,extract w1ext, megabytes 100

11. 添加PUMP

add extract w1extdp, exttrailsource /home/oracle/gg/trails/w1, begin now

修改PUMP配置：

GGSCI (GOLDENGATE1) 8> edit params w1extdp
EXTRACT w1extdp
USERID goldengate, PASSWORD goldengate
RMTHOST 192.168.102.157, MGRPORT 7809
RMTTRAIL /home/oracle/gg/trails/w1
DISCARDFILE w1extdpdsc,APPEND,MEGABYTES 5
TABLE hr.*;

12. 添加远程TRAIL文件：

add rmttrail /home/oracle/gg/trails/w1, extract w1extdp, megabytes 100

13. 添加目标端REP进程

add replicat w1rep, exttrail /home/oracle/gg/trails/w1, checkpointtable goldengate.chkpoint

修改参数

GGSCI (GOLDENGATE2) 6> edit params w1rep
REPLICAT w1rep
ASSUMETARGETDEFS
USERID goldengate, PASSWORD goldengate
DISCARDFILE w1repdsc,APPEND,MEGABYTES 5
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
MAP hr.* , TARGET hr.* ;

14. 添加传输表：

GGSCI (GOLDENGATE1) 10> dblogin userid goldengate, password goldengate
Successfully logged into database.

GGSCI (GOLDENGATE1) 3> add trandata hr.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

Logging of supplemental redo data enabled for table HR.JOBS.

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

Logging of supplemental redo data enabled for table HR.LOCATIONS.

Logging of supplemental redo data enabled for table HR.REGIONS.

2012-09-20 21:50:57  WARNING OGG-00869  No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table HR.T.

15. 初始化数据到目标库：
查询原库SCN：

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     511128

DUMP导出数据：

[oracle@GOLDENGATE1 gg]$ exp hr/hr owner=hr file=hr.dmp flashback_scn=511128 log=log.log

Export: Release 10.2.0.1.0 - Production on Thu Sep 20 21:53:54 2012

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR 
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table                      COUNTRIES         25 rows exported
. . exporting table                    DEPARTMENTS         27 rows exported
. . exporting table                      EMPLOYEES        107 rows exported
. . exporting table                           JOBS         19 rows exported
. . exporting table                    JOB_HISTORY         10 rows exported
. . exporting table                      LOCATIONS         23 rows exported
. . exporting table                        REGIONS          4 rows exported
. . exporting table                              T          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

导入目标数据库

[oracle@GOLDENGATE2 ~]$ imp hr/hr fromuser=hr touser=hr file=hr.dmp ignore=y log=log.log

Import: Release 10.2.0.1.0 - Production on Fri Sep 21 00:03:47 2012

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. . importing table                    "COUNTRIES"         25 rows imported
. . importing table                  "DEPARTMENTS"         27 rows imported
. . importing table                    "EMPLOYEES"        107 rows imported
. . importing table                         "JOBS"         19 rows imported
. . importing table                  "JOB_HISTORY"         10 rows imported
. . importing table                    "LOCATIONS"         23 rows imported
. . importing table                      "REGIONS"          4 rows imported
. . importing table                            "T"          0 rows imported
About to enable constraints...
Import terminated successfully without warnings.

16，启动GG各进程
源端：

GGSCI (GOLDENGATE1) 44> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     W1EXT       00:00:00      00:00:02    
EXTRACT     RUNNING     W1EXTDP     00:00:00      00:00:08

目标端：这里在启动REP的时候要以源库导出时所查询的SCN值启动

GGSCI (GOLDENGATE2) 7> start w1rep, aftercsn 511128 

Sending START request to MANAGER ...
REPLICAT W1REP starting


GGSCI (GOLDENGATE2) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     W1REP       00:00:00      00:00:06

17 验证数据同步
源库：

SQL> insert into t(id) values(88);

1 row created.

SQL> commit;

Commit complete.

目标端：

SQL> select * from t;

        ID
----------
        88

小记：在遇到错误时可查看GGSERR.LOG 日志，也可用view report 进程名来查看错误的具体原因。复制情况可用 stats w1rep

GGSCI (GOLDENGATE2) 3> stats w1rep

Sending STATS request to REPLICAT W1REP ...

Start of Statistics at 2012-09-21 00:19:16.

Replicating from HR.T to HR.T:

*** Total statistics since 2012-09-21 00:17:59 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2012-09-21 00:17:59 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2012-09-21 00:17:59 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2012-09-21 00:17:59 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.

wshxgxiaoli

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
GOLDENGATE DML单向同步

1. 环境介绍ORACLE版本： 10.2.0.1GG版本： 11.2.12.解压安装包。3.配置环境变量。源端：增加如下配置export GG_HOME=/home/oracle/ggexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib目标端：增加如下配置export GG_HOME
复制链接

扫一扫