GoldenGate双向复制

双向复制实际上是在单项复制的基础上,

在原target端,      添加Trandata,EXTRACT进程,Pump进程,

在原source端,     添加checkpoint table,Replicat进程

从而实现双向复制

 

1.Configure Oracle Database for Goldengate

 

1.1Source端(原Target端)开启supplemental log

 

查看suplemental log 是否开启：

SQL> select supplemental_log_data_min from v$database;

 

SUPPLEME

--------

NO

 

开启：

SQL> alter database add supplemental log data;

 

Database altered.

 

1.2开启force logging

 

查看force logging是否开启：

SQL> select force_logging from v$database;

 

FOR

---

NO

 

开启：

SQL> alter database force logging;

 

Database altered.

 

1.3开启归档

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  732352512 bytes

Fixed Size                    1339036 bytes

Variable Size                  432013668 bytes

Database Buffers          293601280 bytes

Redo Buffers                    5398528 bytes

Database mounted.

 

 

SQL> alter database archivelog;

 

Database altered.

 

SQL> archive log list

Database log mode               Archive Mode

Automatic archival               Enabled

Archive destination               /u01/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online log sequence     7

Next log sequence to archive   9

Current log sequence               9                

 

1.4赋予权限

 

赋予权限gc2

SQL> grant ALTER ANY TABLE to ogg;                

 

否则add trandata时会报错

GGSCI (gc2) 20> ADD TRANDATA scott.emp_ogg

 

2014-06-18 12:28:12  GGS WARNING     109  No unique key is defined for table EMP_OGG. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

 

2014-06-18 12:28:13  GGS WARNING     301  Failed to add supplemental log group on table SCOTT.EMP_OGG due to ORA-01031: insufficient privileges, SQL ALTER TABLE "SCOTT"."EMP_OGG" ADD SUPPLEMENTAL LOG GROUP "GGS_EMP_OGG_73959" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.

 

SQL>grant FLASHBACK ANY TABLE to ogg;

SQL>grant EXECUTE on DBMS_FLASHBACK to ogg;

 

赋予权限gc1

sys@PROD>grant INSERT, UPDATE, DELETE on scott.emp_ogg to ogg;

 

sys@PROD>grant INSERT, UPDATE, DELETE on scott.dept_ogg to ogg;

 

1.5Enable transaction data change capture for these two tables in Source system:

 

GGSCI (gc2) 21> ADD TRANDATA scott.emp_ogg

 

2014-06-18 12:28:56  GGS WARNING     109  No unique key is defined for table EMP_OGG. 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 SCOTT.EMP_OGG.        

 

GGSCI (gc2) 22> ADD TRANDATA scott.DEPT_OGG

 

2014-06-18 13:24:45  GGS WARNING     109  No unique key is defined for table DEPT_OGG. 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 SCOTT.DEPT_OGG.

 

Verify that supplemental logging has been turned on for these tables：

 

GGSCI (gc2) 23> info trandata scott.emp*

 

Logging of supplemental redo log data is disabled for table SCOTT.EMP.

 

Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG

 

GGSCI (gc2) 24> info trandata scott.dept*

 

Logging of supplemental redo log data is disabled for table SCOTT.DEPT.

 

Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG

 

2.Configure Extract Process in Source system(原Target端)

 

2.1Edit extract process parameter

 

GGSCI (gc2) 26> EDIT PARAMS EORA_1

 

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL ./dirdat/aa

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

 

2.2创建EXTRACT进程和本地tail文件,Extract组负责写这份文件,Pump进程组负责读取它

 

GGSCI (gc2) 27> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

EXTRACT added.

 

GGSCI (gc2) 28> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

 

2.3Start primary Extract process

 

GGSCI (gc2) 29> START EXTRACT EORA_1

 

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

 

3.Configure pump process in source system(原Target端配置Pump投递进程组)

 

3.1Edit data pump process parameter

 

GGSCI (gc2) 30> EDIT PARAMS PORA_1

 

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc1, MGRPORT 7788

RMTTRAIL ./dirdat/pa

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

 

添加Pump进程

 

GGSCI (gc2) 33> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa

EXTRACT added.

 

3.2Add GoldenGate remote trail in Source system(创建远程trail文件,这个文件在source端通过命令创建到target端)

 

GGSCI (gc2) 34> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

 

启动Pump process

GGSCI (gc2) 35>  START EXTRACT PORA_1

 

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

 

4.Configure replicat process in target system(原SOURCE端)

 

4.1Create GLOBALS parameter in target system(用于添加checkpoint表)

 

GGSCI (gc1) 29> EDIT PARAMS ./GLOBALS

添加：

CHECKPOINTTABLE ogg.ggschkpt

 

For GLOBALS configuration take effect, we must exit GGSCI session:

GGSCI (gc1) 30> exit

[oracle@gc1:/u01/app/ogg]$ ./ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42

 

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

 

GGSCI (gc1) 1> dblogin userid ogg,password ogg

Successfully logged into database.

 

GGSCI (gc1) 2> ADD CHECKPOINTTABLE

 

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

 

Successfully created checkpoint table OGG.GGSCHKPT.

 

4.2Edit Delivery process parameter(开始配置Replicat复制进程组)

 

编辑参数：

GGSCI (gc1) 3> EDIT PARAMS RORA_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

MAP scott.*, TARGET scott.*;

 

添加Replicat复制进程：

GGSCI (gc1) 4> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa

REPLICAT added.

 

启动：

GGSCI (gc1) 5> START REPLICAT RORA_1

 

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

 

5.测试双向复制可用性

 

4.1测试insert

 

gc2:

SQL> insert into dept_ogg values(55,'papa','biaji');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

gc1:

scott@PROD>select * from dept_ogg;

 

    DEPTNO DNAME          LOC

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

10 ACCOUNTING          NEW YORK

20 RESEARCH          DALLAS

30 SALES          CHICAGO

40 OPERATIONS          BOSTON

50 Ministry          BJ

55 papa           biaji

 

 

 

4.2测试delete

 

gc2：

SQL> select * from dept_ogg;

 

         DEPTNO DNAME                 LOC

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

10 ACCOUNTING          NEW YORK

20 RESEARCH          DALLAS

30 SALES          CHICAGO

40 OPERATIONS          BOSTON

50 Ministry          BJ

80 test           test

 

SQL> delete from dept_ogg where deptno=80;

 

1 row deleted.

 

SQL> commit;

 

gc1:

scott@PROD>select * from dept_ogg;

 

        DEPTNO   DNAME                 LOC

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

10 ACCOUNTING          NEW YORK

20 RESEARCH          DALLAS

30 SALES          CHICAGO

40 OPERATIONS          BOSTON

50 Ministry          BJ

 

 

4.3测试update

 

gc2:

SQL> update dept_ogg set deptno=57 where dname='papa';

 

1 row updated.

 

SQL> commit;

 

gc1:

scott@PROD>select * from dept_ogg;

 

    DEPTNO DNAME          LOC

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

10 ACCOUNTING          NEW YORK

20 RESEARCH          DALLAS

30 SALES          CHICAGO

40 OPERATIONS          BOSTON

50 Ministry          BJ

57 papa           biaji

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29492784/viewspace-1208953/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29492784/viewspace-1208953/