goldengate實施文件
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>
由於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
~
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- goldengate部署實施Go
- goldengate部署實施案例Go
- GoldenGate實施參考Go
- MySQL實施文件MySql
- GoldenGate實施故障彙總Go
- GoldenGate實施相關問題Go
- Goldengate容災系統實施方案Go
- GoldenGate “單連通” 實施日誌Go
- oracle goldengate專案實施結束OracleGo
- 【goldengate】官方文件筆記三 Oracle GoldenGate 實時報表Go筆記Oracle
- GoldenGate schema級複製 實施過程Go
- 在linux下實施goldengate11.2for oracle 11gR2安裝配置文件LinuxGoOracle
- 在RAC環境下安裝實施GoldenGateGo
- 今晚實施goldengate遇到的幾個問題Go
- 【轉】GoldenGate資料庫複製實施案例Go資料庫
- 完成oracle goldengate for db2 下面的實施OracleGoDB2
- 【goldengate】官方文件筆記四 Oracle GoldenGate實時資料分佈Go筆記Oracle
- 【goldengate】官方文件筆記五 Oracle GoldenGate實時資料倉儲Go筆記Oracle
- goldengate 生產環境下實施注意事項Go
- [原創]總結:實施goldengate director的步驟Go
- 【原創】警告:實施goldengate之前源庫務必要做的--此點在gg文件裡沒有提到Go
- 【goldengate】官方文件筆記二 Oracle GoldenGate 處理介面(GGSCI)Go筆記Oracle
- goldengate版本及官方文件下載地址Go
- GoldenGate 配置文件,裡面有引數說明Go
- GoldenGate Enterprise Manager Plug-In(12.1.0.3.0) 部署文件Go
- Oracle GoldenGate Best Practice - sample parameter files (文件 ID 1321696.1)OracleGo
- 從需求到實施整流程及相關文件模板(附一個案例)
- 基於LINUX的MySql二進位制本地升級實施文件LinuxMySql
- 實施顧問眼中的專案實施(轉)
- 基於LINUX7的21C單機 標準化實施文件Linux
- Oracle GoldenGate 11g官方文件Administrator’s GuideOracleGoGUIIDE
- 【goldengate】官方文件筆記一 配置Manager和網路連線Go筆記
- Oracle GoldenGate 簡單實驗OracleGo
- 實施專案--如何推進專案實施進度
- oracle goldengate實用文件兩個(所有命令幫助及ogg錯誤程式碼解釋)OracleGo
- 實施經驗:希門ERP實施流程及方法(轉)
- 專案實施方案
- 實施網路安全-A