oracle goldengate 必须追加日志,配置Oracle GoldenGate支持DDL复制2--实际配置记录

where is the user assigned to the GoldenGate processes.

SQL> GRANT GGS_GGSUSER_ROLE TO ggsusr;

Grant succeeded.

SQL>

SQL> @ddl_enable.sql

Trigger altered.

SQL> @ddl_pin ggsusr

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

5.配置manager管理进程：

GGSCI (nicoa.localdomain) 9> edit params mgr

GGSCI (nicoa.localdomain) 10> view params mgr

port 7809

DYNAMICPORTLIST 7840-7850

purgeoldextracts /home/oracle/ggs/dirdat/*,usecheckpoints, minkeepdays 2

GGSCI (nicoa.localdomain) 11> start manager

Manager started.

GGSCI (nicoa.localdomain) 12> status manager

Manager is running (IP port nicoa.localdomain.7809).

GGSCI (nicoa.localdomain) 13>

6.添加extract进程

首先要添加primary extract group

(1).添加进程组

add extract exscott, tranlog, begin 2011-02-14 13:00:00

(2).绑定local trail文件

add exttrail /home/oracle/ggs/dirdat/ex, extract exscott

(3).编辑exscott的参数文件：

extract exscott

userid ggsusr@nicoadb, password oracleggs

exttrail /home/oracle/ggs/dirdat/ex

discardfile  ./dirrpt/extu1.dsc,append

numfiles 3000

EOFDELAYCSECS 10

dynamicresolution

DDL INCLUDE OBJNAME "scott.*"

table scott.*;

日志记录：

[oracle@nicoa ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.0.0 Build 078

Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

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

GGSCI (nicoa.localdomain) 1> add extract exscott, tranlog, begin 2011-02-14 13:00:00

EXTRACT added.

GGSCI (nicoa.localdomain) 2> add exttrail /home/oracle/ggs/dirdat/ex, extract exscott

EXTTRAIL added.

GGSCI (nicoa.localdomain) 3> edit params exscott

GGSCI (nicoa.localdomain) 4> view params exscott

extract exscott

userid ggsusr@nicoadb, password oracleggs

exttrail /home/oracle/ggs/dirdat/ex

discardfile  ./dirrpt/extu1.dsc,append

numfiles 3000

EOFDELAYCSECS 10

dynamicresolution

DDL INCLUDE OBJNAME "scott.*"

table scott.*;

7.添加data pump进程

再添加data pump extract group

(1).添加进程组

add extract expm exttrailsource /home/oracle/ggs/dirdat/ex, begin 2011-02-14 13:00:00

(2).绑定trail文件

add rmttrail /home/oracle/ggs/dirdat/re, extract expm

(3).编辑expm参数文件

extract expm

passthru

userid ggsusr@nicoadb, password oracleggs

RMTHOST nicob,MGRPORT 7809, compress

RMTTRAIL /home/oracle/ggs/dirdat/re

EOFDELAYCSECS 10

dynamicresolution

numfiles 3000

table scott.*;

日志记录：

GGSCI (nicoa.localdomain) 5> add extract expm exttrailsource /home/oracle/ggs/dirdat/ex, begin 2011-02-14 13:00:00

EXTRACT added.

GGSCI (nicoa.localdomain) 6>  add rmttrail /home/oracle/ggs/dirdat/re, extract expm

RMTTRAIL added.

GGSCI (nicoa.localdomain) 7> edit params expm

GGSCI (nicoa.localdomain) 8> view params expm

extract expm

passthru

userid ggsusr@nicoadb, password oracleggs

RMTHOST nicob,MGRPORT 7809, compress

RMTTRAIL /home/oracle/ggs/dirdat/re

EOFDELAYCSECS 10

dynamicresolution

numfiles 3000

table scott.*;

GGSCI (nicoa.localdomain) 9>

8.再targe DB上添加replicat进程

(1).首先登录数据库

dblogin userid ggsusr ,password oracleggs

(2).创建checkpoint table

add checkpointtable ggsusr.chkpoint_tbl

(3).添加replicat进程

add replicat repscott, exttrail /home/oracle/ggs/dirdat/re, checkpointtable ggsusr.chkpoint_tbl, begin 2011-02-14 13:00:00

(4).编辑参数文件

replicat repscott

userid ggsusr, password oracleggs

reperror default,ignore

discardfile ./dirrpt/repu1.dsc,append,megabytes 2000m

EOFDELAYCSECS 10

assumetargetdefs

allownoopupdates

dynamicresolution

numfiles 3000

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLOPTIONS REPORT

MAP scott.* ,TARGET scott.*;

日志记录：

注意：在target DB上，ggsusr必须授予DBA的角色给他，否则dblogin登录不了数据库：

GGSCI (nicob.localdomain) 7> dblogin userid ggsusr@nicobdb, password oracleggs

ERROR: Failed to open data source for user GGSUSR@NICOBDB.

GGSCI (nicob.localdomain) 8> dblogin userid system, password oracle

Successfully logged into database.

GGSCI (nicob.localdomain) 9> exit

[oracle@nicob ggs]$

[oracle@nicob ggs]$

[oracle@nicob ggs]$

[oracle@nicob ggs]$ exit

exit

SQL> grant dba to ggsusr;

Grant succeeded.

SQL> !

[oracle@nicob ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.0.0 Build 078

Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

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

GGSCI (nicob.localdomain) 1> dblogin userid ggsusr, password oracleggs

Successfully logged into database.

GGSCI (nicob.localdomain) 3>  add checkpointtable ggsusr.chkpoint_tbl

Successfully created checkpoint table GGSUSR.CHKPOINT_TBL.

GGSCI (nicob.localdomain) 4> add replicat repscott, exttrail /home/oracle/ggs/dirdat/re, checkpointtable ggsusr.chkpoint_tbs, begin 2011-02-14 13:00:00

REPLICAT added.

GGSCI (nicob.localdomain) 5> edit params repscott

GGSCI (nicob.localdomain) 6> status manager

Manager is DOWN!

GGSCI (nicob.localdomain) 9> edit params mgr

GGSCI (nicob.localdomain) 11> start manager

Manager started.

GGSCI (nicob.localdomain) 12> status *

REPLICAT REPSCOTT: STOPPED

GGSCI (nicob.localdomain) 13> status manager

Manager is running (IP port nicob.localdomain.7809).

GGSCI (nicob.localdomain) 14> start *

Sending START request to MANAGER ...

REPLICAT REPSCOTT starting

GGSCI (nicob.localdomain) 15> status *

REPLICAT REPSCOTT: STOPPED

GGSCI (nicob.localdomain) 16> view params repscott

replicat repscott

userid ggsusr, password ggsusr

reperror default,ignore

discardfile ./dirrpt/repu1.dsc,append,megabytes 2000m

EOFDELAYCSECS 10

assumetargetdefs

allownoopupdates

dynamicresolution

numfiles 3000

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLOPTIONS REPORT

MAP scott.* ,TARGET scott.*;

GGSCI (nicob.localdomain) 17> edit params repscott

GGSCI (nicob.localdomain) 18> start *

Sending START request to MANAGER ...

REPLICAT REPSCOTT starting

GGSCI (nicob.localdomain) 19> status *

REPLICAT REPSCOTT: STOPPED

检查日志：

2011-02-14 12:46:38  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT REPSCOTT starting.

2011-02-14 12:46:38  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, repscott.prm:  REPLICAT REPSCOTT starting.

2011-02-14 12:46:38  ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, repscott.prm:  Checkpoint table GGSUSR.CHKPOINT_TBS does not exist.  Please create the table or recreate the REPSCOTT group using the correct table.

2011-02-14 12:46:38  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, repscott.prm:  PROCESS ABENDING.

发现我是在add replicat repscott的时候写错了，本来是tbl,写成tbs了，先创建GGSUSR.CHKPOINT_TBS再说吧：

GGSCI (nicob.localdomain) 22> dblogin userid ggsusr, password oracleggs

Successfully logged into database.

GGSCI (nicob.localdomain) 23> add checkpointtable ggsusr.chkpoint_tbs

Successfully created checkpoint table GGSUSR.CHKPOINT_TBS.

GGSCI (nicob.localdomain) 24> start *

Sending START request to MANAGER ...

REPLICAT REPSCOTT starting

可以看到，replicat进程启动好了：

GGSCI (nicob.localdomain) 25> status *

REPLICAT REPSCOTT: RUNNING

将replicat进程的checkpoint table改国来吧：

注意：是不可以通过alter replicat的方式来修改的：

GG的文档如是说：

An ADD REPLICAT option. You can change the description or any service option that

was configured with the ADD REPLICAT command, except for the CHECKPOINT and

NODBCHECKPOINT options.

要先停止replicat进程，然后删除，重建：

GGSCI (nicob.localdomain) 32> delete replicat repscott

ERROR: REPLICAT REPSCOTT is running and cannot be altered (1,2,No such file or directory).

GGSCI (nicob.localdomain) 33> stop *

Sending STOP request to REPLICAT REPSCOTT ...

Request processed.

GGSCI (nicob.localdomain) 34> status *

REPLICAT REPSCOTT: STOPPED

GGSCI (nicob.localdomain) 35> delete replicat repscott

Deleted REPLICAT REPSCOTT.

GGSCI (nicob.localdomain) 36> add replicat repscott, exttrail /home/oracle/ggs/dirdat/re, checkpointtable ggsusr.chkpoint_tbl, begin 2011-02-14 13:00:00

REPLICAT added.

--注意：删除的时候不会删除参数文件的，所以，我们直接使用之前创建的参数文件：

GGSCI (nicob.localdomain) 37> view params repscott

replicat repscott

userid ggsusr, password oracleggs

reperror default,ignore

discardfile ./dirrpt/repu1.dsc,append,megabytes 2000m

EOFDELAYCSECS 10

assumetargetdefs

allownoopupdates

dynamicresolution

numfiles 3000

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLOPTIONS REPORT

MAP scott.* ,TARGET scott.*;

GGSCI (nicob.localdomain) 38> start *

Sending START request to MANAGER ...

REPLICAT REPSCOTT starting

GGSCI (nicob.localdomain) 39> status *

REPLICAT REPSCOTT: RUNNING

9.在source DB上启动extract进程

GGSCI (nicoa.localdomain) 1> status manager

Manager is running (IP port nicoa.localdomain.7809).

GGSCI (nicoa.localdomain) 2> start *

Sending START request to MANAGER ...

EXTRACT EXPM starting

Sending START request to MANAGER ...

EXTRACT EXSCOTT starting

GGSCI (nicoa.localdomain) 3> status *

EXTRACT EXPM: RUNNING

EXTRACT EXSCOTT: STOPPED

exscott进程没有起来，检查ggserror.log日志：

2011-02-14 12:50:40  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT EXSCOTT starting.

2011-02-14 12:50:40  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, expm.prm:  EXTRACT EXPM starting.

2011-02-14 12:50:40  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, exscott.prm:  EXTRACT EXSCOTT starting.

2011-02-14 12:50:41  INFO    OGG-01635  Oracle GoldenGate Capture for Oracle, exscott.prm:  BOUNDED RECOVERY: reset to initial or altered checkpoint.

2011-02-14 12:50:41  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, expm.prm:  EXTRACT EXPM started.

2011-02-14 12:50:41  ERROR   OGG-00730  Oracle GoldenGate Capture for Oracle, exscott.prm:  No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key column is not in first row piece.

2011-02-14 12:50:41  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, exscott.prm:  PROCESS ABENDING.

发现数据库忘了追加附加日志：

修改数据库，追加附加日志：

SQL> conn /as sysdba

Connected.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> alter system switch logfile;

System altered.

然后重新启动extract进程：

GGSCI (nicoa.localdomain) 5> stop *

Sending STOP request to EXTRACT EXPM ...

Request processed.

EXTRACT EXSCOTT is already stopped.

GGSCI (nicoa.localdomain) 6> start *

Sending START request to MANAGER ...

EXTRACT EXPM starting

Sending START request to MANAGER ...

EXTRACT EXSCOTT starting

GGSCI (nicoa.localdomain) 7> status *

EXTRACT EXPM: RUNNING

EXTRACT EXSCOTT: RUNNING

10.测试：

安装好后的测试

在source数据库上：

SQL> conn scott/tiger

Connected.

SQL> set linesize 120

SQL> select * from dept;

DEPTNO DNAME      LOC

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

10 ACCOUNTING      NEW YORK

20 RESEARCH      DALLAS

30 SALES      CHICAGO

40 OPERATIONS      BOSTON

添加一行记录，测试DML是否工作正常：

SQL> insert into dept values(50,'nico','swg');

1 row created.

SQL> commit;

Commit complete.

登录target数据库：

[oracle@nicob ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Feb 14 13:06:20 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn scott/tiger

Connected.

SQL> set linesize 120

SQL> select * from dept;

DEPTNO DNAME      LOC

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

50 nico       swg

10 ACCOUNTING      NEW YORK

20 RESEARCH      DALLAS

30 SALES      CHICAGO

40 OPERATIONS      BOSTON

我们可以看到刚刚insert的数据已经传输到target端了。

测试一下DDL是否工作正常：

在source DB上创建一个nicotest的表

SQL> create table nicotest as select * from dept;

Table created.

再到target端看看：

SQL> desc nicotest

Name                                   Null?    Type

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

DEPTNO                                     NUMBER(2)

DNAME                                        VARCHAR2(14)

LOC                                        VARCHAR2(13)

SQL> select * from nicotest;

DEPTNO DNAME      LOC

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

50 nico       swg

10 ACCOUNTING      NEW YORK

20 RESEARCH      DALLAS

30 SALES      CHICAGO

40 OPERATIONS      BOSTON

可以看到已经有这个表了，并且数据也有了!

再试试truncate table看看:

source DB上执行：

SQL> truncate table nicotest;

Table truncated.

再在target端上看看数据是否如期望的那样没了：

SQL> select * from nicotest;

no rows selected

正如我们期望的那样表被truncate了

在source上删除该表：

SQL> drop table nicotest;

Table dropped.

SQL>

在target端看看：

SQL> desc nicotest

ERROR:

ORA-04043: object nicotest does not exist

SQL>

我们可以看到表已经被删除了！

我们再来看看replicat的日志里记录了写啥呢：

2011-02-14 13:07:04  INFO    OGG-00482  DDL found, operation [create table nicotest as select * from dept  (size 44)].

2011-02-14 13:07:04  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [create table "SCOTT"."NICOTEST" as select * from dept  (size 54)].

2011-02-14 13:07:04  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [NICOTEST].

2011-02-14 13:07:04  INFO    OGG-01407  Setting current schema for DDL operation to [SCOTT].

2011-02-14 13:07:04  INFO    OGG-00484  Executing DDL operation.

2011-02-14 13:07:04  INFO    OGG-00483  DDL operation successful.

2011-02-14 13:07:04  INFO    OGG-01408  Restoring current schema for DDL operation to [GGSUSR].

2011-02-14 13:07:29  INFO    OGG-00482  DDL found, operation [truncate table nicotest  (size 24)].

2011-02-14 13:07:29  INFO    OGG-00540  Metadata not cleared for [SCOTT.NICOTEST] because of TRUNCATE.

2011-02-14 13:07:29  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [truncate table "SCOTT"."NICOTEST"  (size 34)].

2011-02-14 13:07:29  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], objowner [SCOTT], objname [NICOTEST].

2011-02-14 13:07:29  INFO    OGG-01407  Setting current schema for DDL operation to [SCOTT].

2011-02-14 13:07:29  INFO    OGG-00484  Executing DDL operation.

2011-02-14 13:07:30  INFO    OGG-00483  DDL operation successful.

2011-02-14 13:07:30  INFO    OGG-01408  Restoring current schema for DDL operation to [GGSUSR].

2011-02-14 13:08:19  INFO    OGG-00482  DDL found, operation [drop table nicotest  (size 20)].

2011-02-14 13:08:19  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [drop table "SCOTT"."NICOTEST"  (size 30)].

2011-02-14 13:08:19  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [DROP], objtype [TABLE], objowner [SCOTT], objname [NICOTEST].

2011-02-14 13:08:19  INFO    OGG-01407  Setting current schema for DDL operation to [SCOTT].

2011-02-14 13:08:19  INFO    OGG-00484  Executing DDL operation.

2011-02-14 13:08:20  INFO    OGG-00483  DDL operation successful.

2011-02-14 13:08:20  INFO    OGG-01408  Restoring current schema for DDL operation to [GGSUSR].

2011-02-14 13:16:39  INFO    OGG-01021  Command received from GGSCI: STATS.