goldengate实施文档

beijing:/u01/ogg$ unzip p18322848_1121020_Linux-x86-64.zip

Archive:  p18322848_1121020_Linux-x86-64.zip

  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar 

  inflating: Oracle-GoldenGate-11.2.1.0-README.txt 

  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.20.pdf 

beijing:/u01/ogg$ ls

beijing:/u01/ogg$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

bcpfmt.tpl

bcrypt.txt

beijing:/u01/ogg$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.20 18227972 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140304.2209_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Mar  5 2014 03:02:15

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

GGSCI (beijing) 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

node1:/u01/ogg$ unzip p18322848_1121020_Linux-x86-64.zip

Archive:  p18322848_1121020_Linux-x86-64.zip

  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar 

  inflating: Oracle-GoldenGate-11.2.1.0-README.txt 

  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.20.pdf 

node1:/u01/ogg$

node1:/u01/ogg$

node1:/u01/ogg$

node1:/u01/ogg$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

bcpfmt.tpl

bcrypt.txt

cachefiledump

node1:/u01/ogg$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.20 18227972 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140304.2209_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Mar  5 2014 03:02:15

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

GGSCI (node1) 4> 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

node1:/home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Oct 22 10:04:59 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

---归档是否打开

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/arch

Oldest online log sequence     151

Next log sequence to archive   153

Current log sequence           153

--源库添加最小日志

SQL> alter database add supplemental log data;

Database altered.

SQL> SQL>

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

----创建goldengate用户

SQL> create user goldengate identified by oracle;

User created.

grant connect,resource,unlimited tablespace to goldengate;

grant execute on utl_file to goldengate;

grant select any dictionary,select any table to goldengate;

grant alter any table to goldengate;

grant flashback any table to goldengate;

grant execute on DBMS_FLASHBACK to goldengate;

---给同步的表添加附加日志

node1:/u01/ogg$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.20 18227972 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140304.2209_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Mar  5 2014 03:02:15

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

GGSCI (node1) 1> dblogin userid goldengate,password oracle;

Successfully logged into database.

GGSCI (node1) 2> add trandata scott.emp1;

ERROR: No viable tables matched specification.

GGSCI (node1) 3> add trandata scott.emp1

2015-10-22 10:41:59  WARNING OGG-00869  No unique key is defined for table 'EMP1'. 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.EMP1.

GGSCI (node1) 4>

GGSCI (node1) 5> view params mgr

port 7839

DYNAMICPORTLIST 7840-7849

--AUTOSTART EXTRACT *

--AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/s1*, USECHECKPOINTS

--USERID goldengate, PASSWORD goldengate

--PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

--PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GGSCI (node1) 2> start mgr

Manager started.

GGSCI (node1) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

GGSCI (node1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

GSCI (node1) 7> add extract extnd,tranlog,begin now

EXTRACT added.

GGSCI (node1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     EXTND       00:00:00      00:00:02   

--设置抽取进程

GGSCI (node1) 9> add exttrail ./dirdat/nd,extract extnd,megabytes 100

EXTTRAIL added.

GGSCI (node1) 7> add extract extnd,tranlog,begin now

EXTRACT added.

GGSCI (node1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     EXTND       00:00:00      00:00:02   

GGSCI (node1) 9> add exttrail ./dirdat/nd,extract extnd,megabytes 100

EXTTRAIL added.

配置抽取进程参数

GGSCI (node1) 10> edit params extnd

EXTRACT EXTND

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_SID=prod)

USERID goldengate, PASSWORD oracle

REPORTCOUNT EVERY 1 HOURS, RATE

DISCARDFILE ./dirrpt/extnd.dsc, APPEND, MEGABYTES 500

DISCARDROLLOVER AT 3:00 ON SUNDAY

EXTTRAIL ./dirdat/nd

GETTRUNCATES

DYNAMICRESOLUTION

NUMFILES 5000

--TCPSOURCETIMER

--CHECKPARAMS

----- DDL -----

--DDL INCLUDE MAPPED

--DDLOPTIONS ADDTRANDATA

--DDLOPTIONS ADDTRANDATA RETRYOP MAXRETRIES 10 RETRYDELAY 10

--DDLOPTIONS REPORT

--TRANLOGOPTIONS rawdeviceoffset 0

TRANLOGOPTIONS EXCLUDEUSER goldengate

TRANLOGOPTIONS convertucs2clobs

--TRANLOGOPTIONS ASMUSER SYS@NCIIS_ASM, ASMPASSWORD oracle

FETCHOPTIONS NOUSESNAPSHOT

FETCHOPTIONS SUPPRESSDUPLICATES

CACHEMGR CACHESIZE 200M

----- TABLES -----

TABLE scott.emp1;

~~

GGSCI (node1) 12> start extnd

Sending START request to MANAGER ...

启动抽取进程报错处理

ERROR: opening port for MGR MGR (Connection timed out).

GGSCI (node1) 13>

由于ip和host名不匹配导致

[root@node1 ~]# vi /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1                     localhost6.localdomain6 localhost6

192.168.10.23            node1

GGSCI (node1) 17> add extract dpend,exttrailsource ./dirdat/nd

EXTRACT added.

GGSCI (node1) 44> add RMTTRAIL ./dirdat/nd,extract dpend

RMTTRAIL added.

GGSCI (node1) 25> view params dpend

EXTRACT dpend

PASSTHRU

DYNAMICRESOLUTION

RMTHOST 192.168.10.21, MGRPORT 7839, COMPRESS

RMTTRAIL ./dirdat/nd

NUMFILES 5000

----- TABLES -----

TABLE scott.emp1;

TABLE scott.emp2;

GGSCI (node1) 45> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     ABENDED     DPEND       00:00:00      02:15:14   

EXTRACT     RUNNING     EXTND       00:00:00      00:00:07   

GGSCI (node1) 46> start dpend

Sending START request to MANAGER ...

EXTRACT DPEND starting

GGSCI (node1) 47> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     DPEND       00:00:00      02:15:19   

EXTRACT     RUNNING     EXTND       00:00:00      00:00:02   

SQL> create user goldengate identified by oracle;

User created.

grant connect,resource,unlimited tablespace to goldengate;

grant execute on utl_file to goldengate;

grant select any dictionary,select any table to goldengate;

grant alter any table to goldengate;

grant flashback any table to goldengate;

grant execute on DBMS_FLASHBACK to goldengate;

GGSCI (beijing) 4> edit params globals

CHECKPOINTTABLE goldengate.checkpoint

beijing:/u01/ogg$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.20 18227972 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140304.2209_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Mar  5 2014 03:02:15

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

GGSCI (beijing) 1> dblogin userid goldengate,password oracle

Successfully logged into database.

GGSCI (beijing) 2> add checkpointtable goldengate.checkpoint

Successfully created checkpoint table goldengate.checkpoint.

GGSCI (beijing) 3> edit params mgr

port 7839

DYNAMICPORTLIST 7840-7849

--AUTOSTART EXTRACT *

--AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/nd*, USECHECKPOINTS

--USERID goldengate, PASSWORD goldengate

--PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

--PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GGSCI (beijing) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                          

GGSCI (beijing) 5> start mgr

Manager started.

GGSCI (beijing) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

GGSCI (beijing) 8> edit params rsnd

REPLICAT rsnd

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_SID=prod)

USERID goldengate, PASSWORD oracle

REPORT AT 01:59

REPORTCOUNT EVERY 1 MINUTES, RATE

NUMFILES 5000

DISCARDFILE ./dirrpt/rsnd.dsc, APPEND, MEGABYTES 500

DISCARDROLLOVER AT 3:00

GETTRUNCATES

ALLOWNOOPUPDATES

--DBOPTIONS DEFERREFCONST

--DBOPTIONS SUPPRESSTRIGGERS for oracle 11G

----- DDL -----

--DDL INCLUDE MAPPED

--ddloptions report

--DDLERROR 14074 IGNORE

REPERROR DEFAULT, ABEND

--REPERROR 1403 TRANSDISCARD

--REPERROR (-1, IGNORE)

NOHANDLECOLLISIONS

ASSUMETARGETDEFS

BATCHSQL

MAXTRANSOPS 10000

GROUPTRANSOPS 2000

----tables---

MAP scott.emp1, TARGET scott.emp1, FILTER(@RANGE(1, 4));

GGSCI (beijing) 9> add replicat rsnd,exttrail ./dirdat/nd,checkpointtable goldengate.checkpoint

REPLICAT added.

GGSCI (beijing) 10>

GGSCI (beijing) 10>

GGSCI (beijing) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    STOPPED     RSND        00:00:00      00:00:03   

GGSCI (node1) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     DPEND       00:00:00      00:00:05   

EXTRACT     RUNNING     EXTND       00:00:00      00:00:00   

GGSCI (node1) 24> stop e*

Sending STOP request to EXTRACT EXTND ...

Request processed.

GGSCI (node1) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     DPEND       00:00:00      00:00:09   

EXTRACT     STOPPED     EXTND       00:00:00      00:00:02   

GGSCI (node1) 26> stop d*

Sending STOP request to EXTRACT DPEND ...

Request processed.

GGSCI (node1) 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     DPEND       00:00:00      00:00:01   

EXTRACT     STOPPED     EXTND       00:00:00      00:00:08   

GGSCI (beijing) 8> stop r*

Sending STOP request to REPLICAT RSND ...

Request processed.

GGSCI (beijing) 9>

GGSCI (beijing) 9>

GGSCI (beijing) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    STOPPED     RSND        00:00:00      00:00:52   

GGSCI (node1) 22> view params mgr

port 7839

DYNAMICPORTLIST 7840-7849

--AUTOSTART EXTRACT *

--AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/s1*, USECHECKPOINTS

--USERID goldengate, PASSWORD goldengate

--PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

--PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GGSCI (node1) 23> view params extnd

EXTRACT EXTND

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_SID=prod)

USERID goldengate, PASSWORD oracle

REPORTCOUNT EVERY 1 HOURS, RATE

DISCARDFILE ./dirrpt/extnd.dsc, APPEND, MEGABYTES 500

DISCARDROLLOVER AT 3:00 ON SUNDAY

EXTTRAIL ./dirdat/nd

GETTRUNCATES

DYNAMICRESOLUTION

NUMFILES 5000

--TCPSOURCETIMER

--CHECKPARAMS

----- DDL -----

--DDL INCLUDE MAPPED

--DDLOPTIONS ADDTRANDATA

--DDLOPTIONS ADDTRANDATA RETRYOP MAXRETRIES 10 RETRYDELAY 10

--DDLOPTIONS REPORT

--TRANLOGOPTIONS rawdeviceoffset 0

TRANLOGOPTIONS EXCLUDEUSER goldengate

TRANLOGOPTIONS convertucs2clobs

--TRANLOGOPTIONS ASMUSER SYS@NCIIS_ASM, ASMPASSWORD oracle

FETCHOPTIONS NOUSESNAPSHOT

FETCHOPTIONS SUPPRESSDUPLICATES

CACHEMGR CACHESIZE 200M

----- TABLES -----

TABLE scott.emp1;

TABLE scott.emp2;

GGSCI (node1) 25> view params dpend

EXTRACT dpend

PASSTHRU

DYNAMICRESOLUTION

RMTHOST 192.168.10.21, MGRPORT 7839, COMPRESS

RMTTRAIL ./dirdat/nd

NUMFILES 5000

----- TABLES -----

TABLE scott.emp1;

TABLE scott.emp2;

GGSCI (beijing) 42> view params mgr

port 7839

DYNAMICPORTLIST 7840-7849

--AUTOSTART EXTRACT *

--AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/nd*, USECHECKPOINTS,minkeepdays 3

--USERID goldengate, PASSWORD goldengate

--PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

--PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GGSCI (beijing) 44> view params rsnd

REPLICAT rsnd

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_SID=prod)

USERID goldengate, PASSWORD oracle

REPORT AT 01:59

REPORTCOUNT EVERY 1 MINUTES, RATE

NUMFILES 5000

DISCARDFILE ./dirrpt/rsnd.dsc, APPEND, MEGABYTES 500

DISCARDROLLOVER AT 3:00

--GETTRUNCATES

ALLOWNOOPUPDATES

--DBOPTIONS DEFERREFCONST

--DBOPTIONS SUPPRESSTRIGGERS for oracle 11G

----- DDL -----

--DDL INCLUDE MAPPED

--ddloptions report

--DDLERROR 14074 IGNORE

REPERROR DEFAULT, ABEND

--REPERROR 1403 TRANSDISCARD

--REPERROR (-1, IGNORE)

NOHANDLECOLLISIONS

ASSUMETARGETDEFS

--BATCHSQL

MAXTRANSOPS 10000

GROUPTRANSOPS 2000

----tables---

MAP scott.emp1, TARGET scott.emp1;

MAP scott.emp2, TARGET scott.emp2;