源端:
OS: RedHat Linux 6.3 64-bit
DB: Oracle Database 11gR2(11.2.0.1)
IP: 192.168.1.226
同步表名: scott.tcustmer,scott.tcustord
目标端:
OS: RedHat Linux 6.3 64-bit
DB: Oracle Database 11gR2(11.2.0.1)
IP: 192.168.1.228
同步表: scott.gg_test
複制架構圖
一、安裝GoldenGate1,源端:将資料庫設為Archive Mode,安裝之前需要打開Oracle的supplemental log。目标端不需要此操作
[email protected]> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 338
Next log sequence to archive 340
Current log sequence 340
[email protected]> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL> ALTER DATABASE ADD supplemental log DATA;
DATABASE altered.
SQL> ALTER system switch logfile;
System altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database
SUPPLEME
--------
YES
注:SUPPLEMENTAL_LOG_DATA_MIN值為implicit亦可
2,源端和目标端安裝goldengate
mkdir /app/ogg
3,檢視源端ogg版本
[[email protected] ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
4,建立源端和目标端ogg目錄
A,建立源端ogg目錄
GGSCI (zjtdbrac1) 1> create subdirs
Creating subdirectories under current directory /app/ogg
Parameter files /app/ogg/dirprm: already exists
Report files /app/ogg/dirrpt: created
Checkpoint files /app/ogg/dirchk: created
Process status files /app/ogg/dirpcs: created
SQL script. files /app/ogg/dirsql: created
Database definitions files /app/ogg/dirdef: created
Extract data files /app/ogg/dirdat: created
Temporary files /app/ogg/dirtmp: created
Stdout files /app/ogg/dirout: created
B,建立目标端ogg目錄
GGSCI (zjtdbrac2) 1> create subdirs
Creating subdirectories under current directory /app/ogg
Parameter files /app/ogg/dirprm: already exists
Report files /app/ogg/dirrpt: created
Checkpoint files /app/ogg/dirchk: created
Process status files /app/ogg/dirpcs: created
SQL script. files /app/ogg/dirsql: created
Database definitions files /app/ogg/dirdef: created
Extract data files /app/ogg/dirdat: created
Temporary files /app/ogg/dirtmp: created
Stdout files /app/ogg/dirout: created
二、配置goldengate 1 ,建立goldengate管理使用者A,源端:建立goldengate管理使用者,并賦予适當權限
[email protected]> create user ggs identified by ggs;
User created.
[email protected]> grant dba to ggs;
Grant succeeded.
[email protected]> grant connect to ggs;
Grant succeeded.
[email protected]> grant alter any table to ggs;
Grant succeeded.
[email protected]> grant alter session to ggs;
Grant succeeded.
[email protected]> grant create session to ggs;
Grant succeeded.
[email protected]> grant flashback any table to ggs;
Grant succeeded.
[email protected]> grant select any dictionary to ggs;
Grant succeeded.
[email protected]> grant resource to ggs;
Grant succeeded.
[email protected]> grant drop any table to ggs;
Grant succeeded.
[email protected]> grant delete any table to ggs;
Grant succeeded.
[email protected]> grant execute on utl_file to ggs;
Grant succeeded.
[email protected]> grant execute on dbms_flashback to ggs;
Grant succeeded.
[email protected]> grant unlimited tablespace to ggs;
Grant succeeded.
B,目标端:建立goldengate管理使用者,并賦予适當權限
[email protected]> create user ggs identified by ggs;
User created.
[email protected]> grant dba to ggs;
Grant succeeded.
[email protected]> grant connect to ggs;
Grant succeeded.
[email protected]> grant alter any table to ggs;
Grant succeeded.
[email protected]> grant alter session to ggs;
Grant succeeded.
[email protected]> grant create session to ggs;
Grant succeeded.
[email protected]> grant update any table to ggs;
Grant succeeded.
[email protected]> grant select any dictionary to ggs;
Grant succeeded.
[email protected]> grant select any table to ggs;
Grant succeeded.
[email protected]> grant resource to ggs;
Grant succeeded.
[email protected]> grant drop any table to ggs;
Grant succeeded.
[email protected]> grant delete any table to ggs;
Grant succeeded.
[email protected]> grant execute on utl_file to ggs;
Grant succeeded.
[email protected]> grant insert any table to ggs;
Grant succeeded.
[email protected]> grant unlimited tablespace to ggs;
Grant succeeded.
2 ,配置mgrA,源端:建立編輯manager參數檔案,PORT為7500,儲存退出。
[[email protected] ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (zjtdbrac1) 1> edit params mgr
port 7500
dynamicportlist 7500-7505
autorestart extract *,waitminutes 2 ,retries 5
"dirprm/mgr.prm" [New] 5L, 85C written
GGSCI (zjtdbrac1) 2> view params mgr
port 7500
dynamicportlist 7500-7505
autorestart extract *,waitminutes 2 ,retries 5
GGSCI (zjtdbrac1) 3> start mgr
Manager started.
其 中port參數标示mgr程序通信的端口,是配置mgr程序必須的。dynamicportlist(可選),表示manager程序可以為源端和目标端 的動态通信時采用的動态端口。autorestart(可選),表示如果extract程序失敗,則每隔2分鐘嘗試重新開機一次,最多重新開機5次。預設為2分鐘 重新開機一次。
檢視manager程序
[[email protected] ogg]$ ps -ef|grep mgr|grep -v grep
root 11 2 0 11:50 ? 00:00:00 [async/mgr]
postfix 1923 1887 0 11:52 ? 00:00:01 qmgr -l -t fifo -u
oracle 2688 1 1 13:54 ? 00:00:01 ./mgr PARAMFILE /app/ogg/dirprm/mgr.prm REPORTFILE /app/ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
B,目标端:建立編輯manager參數檔案,PORT為7500,儲存退出。
[[email protected] ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (zjtdbrac2) 1> edit params mgr
port 7500
dynamicportlist 7500-7505
autorestart extract *,waitminutes 2 ,retries 5
"dirprm/mgr.prm" [New] 4L, 84C written
GGSCI (zjtdbrac2) 2> view params mgr
port 7500
dynamicportlist 7500-7505
autorestart extract *,waitminutes 2 ,retries 5
GGSCI (zjtdbrac2) 3> start mgr
Manager started.
檢視manager程序
[[email protected] ogg]$ ps -ef|grep mgr|grep -v grep
root 11 2 0 11:51 ? 00:00:00 [async/mgr]
postfix 1726 1702 0 11:54 ? 00:00:00 qmgr -l -t fifo -u
oracle 3166 1 1 13:58 ? 00:00:01 ./mgr PARAMFILE /app/ogg/dirprm/mgr.prm REPORTFILE /app/ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
3 ,配置表級補充日志A,源端:
[[email protected] ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 11 14:01:10 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[email protected]> create table gg_test(id int primary key);
Table created.
GGSCI (zjtdbrac1) 1> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (zjtdbrac1) 5> add trandata scott.gg_test
Logging of supplemental redo data enabled for table SCOTT.GG_TEST.
注:goldengate中在表級配置補充日志必須先開啟資料庫級補充日志才能生效。
B,目标端:
[email protected]> create table gg_test(id int primary key);
Table created.
4 ,配置extract程序A,源端:
GGSCI (zjtdbrac1) 1> edit params eora
extract eora
dynamicresolution
userid ggs,password ggs
setenv(ORACLE_SID=orcl)
exttrail ./dirdat/et
table scott.gg_test;
"dirprm/eora.prm" [New] 6L, 121C written
GGSCI (zjtdbrac1) 2> view params eora
extract eora
dynamicresolution
userid ggs,password ggs
setenv(ORACLE_SID=orcl)
exttrail ./dirdat/et
table scott.gg_test;
dynamicresolution, 預設值,表示在分析日志時遇到中繼資料即立刻操作,如建立表等。而不是一次性操作所有的中繼資料,nodynamicresolution。setenv,設 置ORACLE_SID環境變量。exttrail,抽取的臨時檔案存放位置。table,需要操作的表。
GGSCI (zjtdbrac1) 3> add extract eora,tranlog,begin now
EXTRACT added.
TRANLOG 表示使用事務日志的方式
BEGIN NOW 表示從現在開始,你也可以指定一個具體的開始時間
GGSCI (zjtdbrac1) 4> add exttrail ./dirdat/et,extract eora
EXTTRAIL added.
配置extract程序的本地trail檔案路徑
GGSCI (zjtdbrac1) 5> start eora
Sending START request to MANAGER ...
EXTRACT EORA starting
啟動extract程序
5 ,配置pump程序A,源端:
GGSCI (zjtdbrac1) 6> edit params pump_so
extract pump_so
passthru
dynamicresolution
userid ggs,password ggs
rmthost 192.168.1.228, mgrport 7500
setenv(ORACLE_SID=orcl)
rmttrail ./dirdat/rt
table scott.gg_test;
"dirprm/pump_so.prm" [New] 9L, 168C written
GGSCI (zjtdbrac1) 7> view params pump_so
extract pump_so
passthru
dynamicresolution
userid ggs,password ggs
rmthost 192.168.1.228, mgrport 7500
setenv(ORACLE_SID=orcl)
rmttrail ./dirdat/rt
table scott.gg_test;
rmthost 配置遠端主機ip或者主機名。rmttrail,配置遠端主機存放的trail檔案。passthru,data-pump在抽取時使用pass- through模式,不會檢查表定義,速度更快。但是由于沒有檢查表定義,也就無法使用映射和轉換函數。使用passthru時可以将資料抽取到沒有安裝 資料庫的中轉伺服器上。passthru可以和nopassthru配合使用,這樣就可以針對某些表使用pass-through模式,某些表不使用 pass-through模式。具體如下:
passthru
table scott.gg_passthru;
nopassthru
table scott.gg_nopassthru;
GGSCI (zjtdbrac1) 8> add extract pump_so,exttrailsource ./dirdat/et
EXTRACT added.
配置pump_so程序的本地trail檔案路徑,exttrailsource直接指定trail檔案的路徑
GGSCI (zjtdbrac1) 9> add rmttrail ./dirdat/rt ,extract pump_so
RMTTRAIL added.
配置pump_so程序的遠端trail檔案路徑
GGSCI (zjtdbrac1) 10> start pump_so
Sending START request to MANAGER ...
EXTRACT PUMP_SO starting
啟動data-pump程序
GGSCI (zjtdbrac1) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:00:05
EXTRACT RUNNING PUMP_SO 00:00:00 00:00:03
6 ,配置檢查點表A,目标端:
GGSCI (zjtdbrac2) 1> edit params ./GLOBALS
checkpointtable scott.checkczm
"./GLOBALS" [New] 1L, 31C written
GGSCI (zjtdbrac2) 2> view params ./GLOBALS
checkpointtable scott.checkczm
建立檢查點表
GGSCI (zjtdbrac2) 3> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (zjtdbrac2) 5> add checkpointtable scott.checkczm
Successfully created checkpoint table scott.checkczm.
7 ,配置複制程序A,目标端
GGSCI (zjtdbrac2) 5> edit params pora
replicat pora
userid ggs, password ggs
assumetargetdefs
discardfile ./dirrpt/pora.dsc, purge
map scott.gg_test, target scott.gg_test;
"dirprm/pora.prm" [New] 5L, 134C written
GGSCI (zjtdbrac2) 6> view params pora
replicat pora
userid ggs, password ggs
assumetargetdefs
discardfile ./dirrpt/pora.dsc, purge
map scott.gg_test, target scott.gg_test;
assumetargetdefs, 不進行表結構檢查,直接複制資料。discardfile,将沒有成功複制的資料資訊記錄到檔案中,後面的purge選項,表示每次有記錄新資訊時删除之 前的資訊。map,對表進行映射,這裡隻是指定要操作的表scott.gg_test。
GGSCI (zjtdbrac2) 2> add replicat pora,exttrail ./dirdat/rt
REPLICAT added.
配置複制程序
GGSCI (zjtdbrac2) 3> start pora
Sending START request to MANAGER ...
REPLICAT PORA starting
啟動複制程序
三、驗證資料同步A,源端:
SQL> conn scott/tiger;
Connected.
SQL> insert into gg_test values(1);
1 row created.
SQL> commit;
Commit complete.
B,目标端:
SQL> select * from gg_test;
ID
----------
1
資料成功同步
參考至:
《GoldenGate Windows and UNIX Reference Guide 11g Release 2 Patch Set 1》
http://www.code365.org/?p=1594
補充: 1 ,停止複制①停止Capture(在源端)
②停止Data Pump(在源端)
③停止Replicat(在目标端)
GGSCI (zjtdbrac1) 4> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (zjtdbrac1) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT RUNNING EORA 00:00:00 00:00:10
EXTRACT RUNNING PUMP_SO 00:00:00 00:00:09
GGSCI (zjtdbrac1) 6> stop eora
Sending STOP request to EXTRACT EORA ...
Request processed.
GGSCI (zjtdbrac1) 7> stop pump_so
Sending STOP request to EXTRACT PUMP_SO ...
Request processed.
GGSCI (zjtdbrac2) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING PORA 00:00:00 00:00:00
GGSCI (zjtdbrac2) 12> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (zjtdbrac2) 13> stop pora
Sending STOP request to REPLICAT PORA ...
Request processed.
2 ,配置的查詢• 查詢配置的主要指令
• 查詢配置與狀态:INFO指令
• 查詢處理情況:VIEW REPORT指令
GGSCI> INFO 名> [detail]
GGSCI> VIEW REPORT 名>
![](/image//image/default/fj.png)
1.jpg
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14359/viewspace-772408/,如需轉載,請注明出處,否則将追究法律責任。
轉載于:http://blog.itpub.net/14359/viewspace-772408/