goldengate實施文件

fei890910發表於2016-03-10

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

 

 

二、源端配置

2.1源端資料庫配置

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;

 

2.2源庫配置同步表

---給同步的表新增附加日誌

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>

 

 

2.3配置管理程式

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                                          

 

2.4源庫配置抽取程式

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>

 

由於iphost名不匹配導致

[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

~

 

2.5源庫配置傳輸程式

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   

 

 

三、目標庫配置

3.1目標庫配置

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;

 

3.2建立checkpoint

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>

 

3.3配置管理程式

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                                          

 

3.4配置應用程式

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   

 

 

 

 

四、ogg新增表

4.1停抽取 傳輸 應用程式

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;

 

六、配置sequence

在主端和目的端ggsci下執行EDIT PARAMS ./GLOBALS開啟GLOBALS檔案

加入如下的內容:

GGSCHEMA goldengate

 

在主端和目的端分別執行:進入/u02/ggs目錄:

SQL> @sequence.sql

 

在主端sqlplus中執行:

GRANT EXECUTE on goldengate.updateSequence TO goldengate;

 

在目的端sqlplus中執行:

SQL> GRANT EXECUTE on goldengate.replicateSequence TO goldengate;

 

 

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29108064/viewspace-2055098/,如需轉載,請註明出處,否則將追究法律責任。

相關文章