一步一步配置 GoldenGate 数据同步：Extract、Pump、Replicat 和 initial load

最新推荐文章于 2024-05-11 09:48:02 发布

iteye_14608

最新推荐文章于 2024-05-11 09:48:02 发布

阅读量409

点赞数

文章标签：大数据数据库

环境准备：

Source：192.168.8.160 prod.oracle.com prod

target: 192.168.8.180 dss.oracle.com dss

source DB: prod

target DB: DSS

Database Version: 11.2.0.3.0

GoldenGate Version: 11.2.1.0.1

OS version:Enterprise LinuxEnterprise Linux Server release 5.6 (Carthage)

OS Architecture：i686

Character Set:AMERICAN_AMERICA.AL32UTF8

一、在 Linux 上安装 GoldenGate 11g

1. 在 source 和 target 上创建安装目录并解压缩安装文件

Source System

[oracle@prod ~]$mkdir -p /home/oracle/ggs

[oracle@prodggs]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

[oracle@prodggs]$ tar xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

Target System

[oracle@dss ~]$ mkdir -p/home/oracle/ggs

[oracle@dss ggs]$ unzipogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

[oracle@dss ggs]$ tar xvffbo_ggs_Linux_x86_ora11g_32bit.tar

2. 创建 sub workdirectories

Source System

[oracle@prod ggs]$ ggsci

Oracle GoldenGate CommandInterpreter for Oracle

Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized),Oracle 11g on Apr 23 2012 08:09:25

GGSCI (prod.oracle.com) 1>create subdirs

Creating subdirectories undercurrent directory /home/oracle/ggs

Parameter files /home/oracle/ggs/dirprm:already exists

Report files /home/oracle/ggs/dirrpt:created

Checkpoint files /home/oracle/ggs/dirchk: created

Process status files /home/oracle/ggs/dirpcs: created

SQL script files /home/oracle/ggs/dirsql: created

Database definitions files /home/oracle/ggs/dirdef: created

Extract data files /home/oracle/ggs/dirdat: created

Temporary files /home/oracle/ggs/dirtmp:created

Stdout files /home/oracle/ggs/dirout:created

Target System

[oracle@dss ggs]$ ggsci

Oracle GoldenGate CommandInterpreter for Oracle

Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized),Oracle 11g on Apr 23 2012 08:09:25

GGSCI (dss.oracle.com) 1>create subdirs

Creating subdirectories undercurrent directory /home/oracle/ggs

Parameter files /home/oracle/ggs/dirprm:already exists

Report files /home/oracle/ggs/dirrpt:created

Checkpoint files /home/oracle/ggs/dirchk: created

Process status files /home/oracle/ggs/dirpcs: created

SQL script files /home/oracle/ggs/dirsql: created

Database definitions files /home/oracle/ggs/dirdef: created

Extract data files /home/oracle/ggs/dirdat: created

Temporary files /home/oracle/ggs/dirtmp:created

Stdout files /home/oracle/ggs/dirout:created

二、配置 Oracle 数据库以配置 GoldenGate 同步

1.在 source 和 target 系统上创建 GoldenGate 用户及其表空间，并授予用户所需的权限。

Source System

17:27:58 sys@PROD> createtablespace ts_ggs datafile

'/u01/app/oracle/oradata/prod/ts_ggs_001.dbf'

size 50M autoextend on next 1m maxsize 200m;

Tablespace created.

17:31:28 sys@PROD> createuser ggs identified by register default tablespace ts_ggs temporary tablespaceTEMP quota unlimited on ts_ggs;

User created.

grant CONNECT, RESOURCE to ggs;

grant CREATE SESSION, ALTERSESSION to ggs;

grant SELECT ANY DICTIONARY,SELECT ANY TABLE to ggs;

grant ALTER ANY TABLE to ggs;

grant FLASHBACK ANY TABLE toggs;

grant EXECUTE on DBMS_FLASHBACKto ggs;

准备测试表

Source system

create table emp_ggs as select* from emp;

create table dept_ggs as select* from dept;

create table salgrade_ggs asselect * from salgrade;

create table BONUS_ggs asselect * from bonus;

target system

create table emp_ggs as select* from emp where 1=2;

create table dept_ggs as select* from dept where 1=2;

create table salgrade_ggs asselect * from salgrade where 1=2;

create table BONUS_ggs asselect * from bonus where 1=2;

alter table EMP_GGS addconstraint pk_empno primary key(empno);

alter table DEPT_GGS addconstraint pk_deptno primary key(deptno);

ALTER TABLE EMP_GGS ADD CONSTRAINT FK_dno

FOREIGN KEY(deptno) REFERENCESdept(deptno);

创建target goldengate 用户并授权

Target System

17:34:34 sys@DSS> createtablespace ts_ggs datafile

'/u01/app/oracle/oradata/dss/ts_ggs_001.dbf'

size 50M autoextend on next 1mmaxsize 200m;

Tablespace created.

17:34:53 sys@DSS> 17:34:53sys@DSS> create user ggs identified by register default tablespace ts_ggstemporary tablespace TEMP quota unlimited on ts_ggs;

User created.

grant CONNECT, RESOURCE to ggs;

grant CREATE SESSION, ALTERSESSION to ggs;

grant SELECT ANY DICTIONARY,SELECT ANY TABLE to ggs;

grant CREATE TABLE to ggs;

grant INSERT, UPDATE, DELETE onscott.emp_ggs to ggs;

grant INSERT, UPDATE, DELETE onscott.dept_ggs to ggs;

三、启用源数据库的 supplemental log 模式、archivelog 模式和 force logging 模式

Source System

17:54:09 sys@PROD> selectSUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

17:54:11 sys@PROD> alterdatabase add supplemental log data;

Database altered.

Elapsed: 00:00:00.67

17:54:42 sys@PROD> altersystem switch logfile;

System altered.

17:54:50 sys@PROD> archivelog list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 35

Next log sequence toarchive 37

Current log sequence 37

18:26:43 sys@PROD> SELECTforce_logging FROM v$database;

FOR

---

18:37:09 sys@PROD> alterdatabase force logging;

Database altered.

四、在源数据库上为要同步的表启用 supplemental log

/home/oracle/ggs/ggsci<<EOF

dblogin userid ggs,passwordregister

add trandata scott.emp_ggs

add trandata scott.dept_ggs

add trandata scott.salgrade_ggs

add trandata scott.BONUS_ggs

exit

EOF

五、配置 GoldenGate Manager 进程

Source System

GGSCI (prod.oracle.com) 1>edit params mgr

PORT 7809

PURGEOLDEXTRACTS/dirdat, USECHECKPOINTS

GGSCI(prod.oracle.com) 6> start mgr

Managerstarted.

GGSCI(prod.oracle.com) 9> info mgr

Manager isrunning (IP port prod.oracle.com.7809).

Target System

GGSCI (dss.oracle.com) 2>edit params mgr

PORT 7809

PURGEOLDEXTRACTS/dirdat, USECHECKPOINTS

GGSCI(dss.oracle.com) 3> start mgr

Managerstarted.

GGSCI(dss.oracle.com) 6> info mgr

Manager isrunning (IP port dss.oracle.com.7809).

六、通过 direct load 初始化数据

1.配置 source system 的初始化 extract 进程

Source System

GSCI (prod.oracle.com) 11>add extract einit,sourceistable

EXTRACT added.

GSCI (prod.oracle.com) 13>info extract *,tasks

EXTRACT EINITInitialized 2013-03-0223:26 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI (prod.oracle.com) 14>edit params einit

EXTRACT EINIT

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ggs, PASSWORD register

RMTHOST 192.168.8.180, MGRPORT7809

RMTTASK REPLICAT, GROUP RINIT

TABLE scott.EMP_GGS;

TABLE scott.DEPT_GGS;

TABLE scott.BONUS_GGS;

TABLE scott.SALGRADE_GGS;

GGSCI (prod.oracle.com) 16>info extract *,tasks

EXTRACT EINITInitialized 2013-03-0223:26 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

2.在 target system 上配置初始化replicat 进程

GGSCI (dss.oracle.com) 7>add replicat rinit,specialrun

REPLICAT added.

GGSCI (dss.oracle.com) 8>INFO REPLICAT *, TASKS

REPLICAT RINITInitialized 2013-03-0223:33 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:16 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

GGSCI (dss.oracle.com) 9>edit params rinit

REPLICAT RINIT

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

ASSUMETARGETDEFS

USERID ggs, PASSWORD register

DISCARDFILE ./dirrpt/RINII.dsc,PURGE

MAP scott.*, TARGET scott.*;

GGSCI (dss.oracle.com)13> INFO REPLICAT *, TASKS

REPLICAT RINITInitialized 2013-03-0223:33 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:05:30 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

2.完成初始化数据加载

先后启动 source system 和 target system 上的 einit 和 rinit 进程完成初始化数据加载

Source System

GGSCI (prod.oracle.com) 4>start einit

Sending START request toMANAGER ...

EXTRACT EINIT starting

Target System

GGSCI (dss.oracle.com) 2>start rinit

Sending START request toMANAGER ...

REPLICAT RINIT starting

3.验证初始化加载结果和初始化进程状态

Source System

Processing table SCOTT.EMP_GGS

Processing table SCOTT.DEPT_GGS

***********************************************************************

* ** Run Time Statistics** *

***********************************************************************

Report at 2013-03-02 23:56:47(activity since 2013-03-02 23:56:31)

Output to RINIT:

From Table SCOTT.EMP_GGS:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.DEPT_GGS:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 3056

Target System

00:34:16 scott@DSS> select *from DEPT_GGS;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

00:34:35 scott@DSS> selectcount(*) from EMP_GGS;

COUNT(*)

----------

七、配置 GoldenGate 同步进程

1.在 Source System 上配置Primary Extract 进程

GGSCI (prod.oracle.com) 11>edit params escott

EXTRACT escott

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ggs, PASSWORD register

EXTTRAIL ./dirdat/aa

TABLE scott.EMP_GGS;

TABLE scott.DEPT_GGS;

GGSCI (prod.oracle.com) 12>ADD EXTRACT escott, TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (prod.oracle.com) 13>ADD EXTTRAIL ./dirdat/aa, EXTRACT escott, MEGABYTES 5

EXTTRAIL added.

GGSCI (prod.oracle.com) 15>start escott

Sending START request toMANAGER ...

EXTRACT ESCOTT starting

GGSCI (prod.oracle.com) 19>info escott

EXTRACT ESCOTTLast Started 2013-03-0300:55 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint Oracle Redo Logs

2013-03-03 00:55:14 Seqno 40, RBA 5691904

SCN 0.929341 (929341)

2.在 Source System 上配置 Pump 进程

GGSCI (prod.oracle.com) 20>edit params pscott

EXTRACT pscott

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

PASSTHRU

RMTHOST 192.168.8.180, MGRPORT7809

RMTTRAIL ./dirdat/pa

TABLE scott.EMP_GGS;

TABLE scott.DEPT_GGS;

GGSCI (prod.oracle.com) 1>ADD EXTRACT pscott, EXTTRAILSOURCE ./dirdat/aa

EXTRACT added.

GGSCI (prod.oracle.com) 4>ADD RMTTRAIL ./dirdat/pa, EXTRACT pscott, MEGABYTES 5

RMTTRAIL added.

GGSCI (prod.oracle.com) 12>info pscott

EXTRACT PSCOTTLast Started 2013-03-03 01:02Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint File ./dirdat/aa000000

First Record RBA 0

在 target system 上验证 remote trail 文件是否传递过来：

[oracle@dss dirdat]$ pwd

/home/oracle/ggs/dirdat

[oracle@dss dirdat]$ ls

pa000000

3.在 target system 上配置 replicat 进程

GGSCI (dss.oracle.com) 7>edit params ./GLOBALS

CHECKPOINTTABLE ggs.ggs_checkpoint

4.在 target system 上添加 replicat checkpointtable

GGSCI (dss.oracle.com) 9>exit

[oracle@dss ggs]$ ggsci

GGSCI (dss.oracle.com) 2>dblogin userid ggs,password register

Successfully logged intodatabase.

GGSCI (dss.oracle.com) 3>ADD CHECKPOINTTABLE

Nocheckpoint table specified, using GLOBALS specification (ggs.ggs_checkpoint)...

Successfullycreated checkpoint table ggs.ggs_checkpoint.

5.配置 replicat 进程

GGSCI (dss.oracle.com) 4>ADD REPLICAT rscott, EXTTRAIL ./dirdat/pa

REPLICAT added.

REPLICAT rscott

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ggs, PASSWORD register

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE./dirrpt/rscott.DSC, PURGE

MAP scott.EMP_GGS, TARGETscott.EMP_GGS;

MAP scott.DEPT_GGS, TARGETscott.DEPT_GGS;

GGSCI (dss.oracle.com) 7>start RSCOTT

Sending START request toMANAGER ...

REPLICAT RSCOTT starting

GGSCI (dss.oracle.com) 12>info rscott

REPLICAT RSCOTTLast Started 2013-03-03 01:14Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

八、DML 测试同步配置

1.检查源端和目标端OGG进程状态

GGSCI (prod.oracle.com) 13>info all

Program StatusGroup Lag at ChkptTime Since Chkpt

MANAGER RUNNING

EXTRACT RUNNINGESCOTT 00:00:00 00:00:09

EXTRACT RUNNINGPSCOTT 00:00:00 00:00:02

GGSCI (dss.oracle.com) 13>info all

Program StatusGroup Lag at ChkptTime Since Chkpt

MANAGER RUNNING

REPLICAT RUNNINGRSCOTT 00:00:00 00:00:10

2.检查源端和目标端表记录数

01:19:02 scott@PROD> select count(*)from emp_ggs;

COUNT(*)

----------

01:19:57 scott@DSS> select count(*) fromemp_ggs;

COUNT(*)

----------

4.在源端执行 inset 操作

insert into scott.emp_ggs(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

01:33:14 2values (7379, 'MAOMI', 'CLERK', 7902, to_date('17-12-1987','dd-mm-yyyy'), 800.00, null, 20);

1 row created.

Elapsed: 00:00:00.12

01:33:16 scott@PROD> commit;

Commit complete.

在源端验证：

GGSCI (prod.oracle.com) 2>stats ESCOTT

Sending STATS request toEXTRACT ESCOTT ...

Start of Statistics at2013-03-03 01:34:07.

Output to ./dirdat/aa:

Extracting from SCOTT.EMP_GGSto SCOTT.EMP_GGS:

*** Total statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (prod.oracle.com) 3>stats PSCOTT

Sending STATS request toEXTRACT PSCOTT ...

Start of Statistics at2013-03-03 01:34:12.

Output to ./dirdat/pa:

Extracting from SCOTT.EMP_GGSto SCOTT.EMP_GGS:

*** Total statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since2013-03-03 01:33:24 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

在目标端验证：

GGSCI (dss.oracle.com) 2>stats RSCOTT

Sending STATS request toREPLICAT RSCOTT ...

Start of Statistics at 2013-03-0301:36:05.

Replicating from SCOTT.EMP_GGSto SCOTT.EMP_GGS:

*** Total statistics since2013-03-03 01:33:26 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since2013-03-03 01:33:26 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since2013-03-03 01:33:26 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since2013-03-03 01:33:26 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

01:20:06 scott@DSS> select *from emp_ggs where empno = '7379';

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7379 MAOMI CLERK 7902 1987-12-17 00:00:00 800 20

Update 和 delete 操作测试省略

转载请注明出处及源文链接：

http://blog.csdn.net/xiangsir/article/details/8635870

iteye_14608

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一步一步配置 GoldenGate 数据同步：Extract、Pump、Replicat 和 initial load

环境准备：Source：192.168.8.160 prod.oracle.com prodtarget: 192.168.8.180 dss.oracle.com dsssource DB: prod target DB: DSSDatabase Version: 11.2.0.3.0GoldenGate Version: 11.2.1.0.1OS...
复制链接

扫一扫