環境說明： linux為Linux 2.6.32-573.el6.x86_64 Oracle為 11g EnterPRise Edition Release 11.2.0.1.0 - 64bit Production ogg為 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit 10.100.25.14 (原庫ip) 10.100.25.16(目標庫ip)說明： goldengate為ogg管理用戶 oggdemo為生產用戶源庫、目標庫都操作： 1. 上傳ogg安裝包與安裝ogg軟件。[oracle@11g ~]$ mkdir ogg[oracle@11g ~]$ cd ogg[oracle@11g ogg]$ lsogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip[oracle@11g ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zipinflating: fbo_ggs_Linux_x64_ora11g_64bit.tar inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf inflating: Oracle GoldenGate 11.2.1.0.1 README.txt inflating: Oracle GoldenGate 11.2.1.0.1 README.doc [oracle@11g ogg]$ tar vxf fbo_ggs_Linux_x64_ora11g_64bit.tar UserExitExamples/UserExitExamples/ExitDemo_more_recs/UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUXUserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS。。。。省略大量輸出。。。。。ucharset.hulg.sqlusrdecs.hzlib.txt2. 數據庫為ogg傳遞數據做環境調整。 （歸檔，數據庫級最小附加日志）[oracle@11g ogg]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 9 03:03:20 2014SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 21Next log sequence to archive 23Current log sequence 23SQL> alter database add supplemental log data;Database altered.SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;System altered.3. 創建專用的ogg表空間與ogg用戶，ogg用戶授權 （以后卸載ogg直接刪除表空間即可）SQL> create tablespace goldgate datafile '/home/oracle/app/oradata/orcl/ogg01.dbf' size 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;Tablespace created.SQL> CREATE USER goldengate IDENTIFIED BY goldengate DEFAULT TABLESPACE goldgate;User created.SQL> GRANT CONNECT TO goldengate;Grant succeeded.SQL> GRANT CREATE session TO goldengate;Grant succeeded.SQL> GRANT ALTER SESSION TO goldengate;Grant succeeded.SQL> GRANT RESOURCE TO goldengate;Grant succeeded.SQL> GRANT SELECT ANY DICTIONARY TO goldengate;Grant succeeded.SQL> GRANT SELECT ANY TABLE TO goldengate;Grant succeeded.SQL> GRANT FlashBACK ANY TABLE TO goldengate;Grant succeeded.SQL> GRANT ALTER ANY TABLE TO goldengate;Grant succeeded. 說明：在goldengate目錄下，執行 ./ggsci 進入命令行界面，左側提示出現GGSCI (myhost) 1>，表示進入成功如果ggsci進入出錯，設置一下環境變量：export LD_LIBRARY_PATH=$ORACLE_HOME/lib [oracle@11g ~]$ cd ogg --驗證是否可以進入ggsci命令界面[oracle@11g ogg]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (11g) 1> -- 成功進入界面SQL> GRANT INSERT ANY TABLE TO goldengate;Grant succeeded.SQL> GRANT UPDATE ANY TABLE TO goldengate;Grant succeeded.SQL> GRANT DELETE ANY TABLE TO goldengate;Grant succeeded. ———————————————————————————————————————————————————————————————4. 創建ogg子目錄源庫與目標庫都執行如下： GGSCI (11g) 3> create subdirsCreating subdirectories under current directory /home/oracle/oggParameter files /home/oracle/ogg/dirprm: already existsReport files /home/oracle/ogg/dirrpt: createdCheckpoint files /home/oracle/ogg/dirchk: createdProcess status files /home/oracle/ogg/dirpcs: createdSQL script files /home/oracle/ogg/dirsql: createdDatabase definitions files /home/oracle/ogg/dirdef: createdExtract data files /home/oracle/ogg/dirdat: createdTemporary files /home/oracle/ogg/dirtmp: createdStdout files /home/oracle/ogg/dirout: created參數說明：該命令會在OGG安裝目錄下建立若干子目錄，其中幾個主要目錄如下所示：dirchk：用于存放各個進程的檢查點dirdat：用于存放數據隊列文件dirprm：用于存放各進程參數文件dirrpt：用于存放各進程報告dirpcs：存放各個正在運行的進程信息 5. 創建mgr進程 GGSCI (11g) 4> edit param mgr （--在mgr文件中加入 port 7809）GGSCI (11g) 8> view param mgrport 7809GGSCI (11g) 5> start mgrManager started.GGSCI (11g) 6> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING————————————————————————————————————————————————————源庫操作：6. 創建oggdemo測試用戶SQL> create user oggdemo identified by oggdemo;User created.SQL> grant dba to oggdemo;Grant succeeded.SQL> conn oggdemo;Enter passWord: Connected.SQL> create table oggdemo(id int);Table created.7. GGSCI命令行中登錄數據庫，為所有要復制的數據表添加trandata GGSCI (11g) 9> dblogin userid oggdemo , password oggdemoSuccessfully logged into database.GGSCI (11g) 10> add trandata oggdemo.oggdemo2014-11-09 04:12:37 WARNING OGG-00869 No unique key is defined for table 'OGGDEMO'. 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 OGGDEMO.OGGDEMO.————————————————————————————————————————————————————————————————目標端操作：SQL> create user oggdemo identified by oggdemo;User created.SQL> grant dba to oggdemo;Grant succeeded.SQL> conn oggdemo;Enter password: Connected.SQL> create table oggdemo(id int);Table created.————————————————————————————————————————————————————————源庫操作：8. 創建ext進程，tail文件，data_pump進程GGSCI (11g) 11> add ext exta, tranlog, begin nowEXTRACT added.GGSCI (11g) 12> add exttrail /home/oracle/ogg/dirdat/la, ext exta, MEGABYTES 20 -- 創建源trail文件EXTTRAIL added.GGSCI (11g) 13> add extract dpea, EXTTRAILSOURCE /home/oracle/ogg/dirdat/la -- 創建data_pump進程EXTRACT added.GGSCI (11g) 14> add rmttrail /home/oracle/ogg/dirdat/ra, ext dpea, MEGABYTES 20 --創建目標端trail文件RMTTRAIL added.GGSCI (11g) 15> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT STOPPED DPEA 00:00:00 00:02:48 EXTRACT STOPPED EXTA 00:00:00 00:09:36GGSCI (11g) 17> edit param exta --exta文件添加內容顯示如下GGSCI (11g) 35> view param extaEXTRACT extasetenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )setenv (ORACLE_SID = orcl)USERID goldengate, PASSWORD goldengateEXTTRAIL /home/oracle/ogg/dirdat/ladynamicresolutiontable oggdemo.oggdemo;TRANLOGOPTIONS EXCLUDEUSER goldengateGGSCI (11g) 36> edit param dpea --dpea文件添加內容顯示如下GGSCI (11g) 39> view param dpeaextract dpeasetenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )passthrurmthost 10.100.25.16,mgrport 7809, compressrmttrail /home/oracle/ogg/dirdat/radynamicresolutiontable oggdemo.oggdemo; 參數說明：passthru表示本進程是一個傳輸進程data pump，無需跟數據庫交互，只需要搬運數據即可；因為data pump要傳輸數據到目標，所以需要配置rmthost和rmttrail指定目標主機和隊列信息———————————————————————————————————————————————————————————— 目標數據庫操作：9. 創建目標數據庫的replicate進程GGSCI (11g) 6> add rep repa, exttrail /home/oracle/ogg/dirdat/ra, nodbcheckpointREPLICAT added.參數說明： repa為進程名，一般為rep開頭表示是replicat進程，后面可以加1-2位字符標識，一般與ext進程對應；exttrail表示要抽取的數據隊列，注意是目標端的隊列位置；nodbcheckpoint表示不使用數據庫檢查點。 GGSCI (11g) 17> edit param repa -- repa文件添加內容顯示如下GGSCI (11g) 20> view param repareplicat repasetenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)setenv (ORACLE_SID = orcl)userid goldengate, password goldengatereperror default,abenddiscardfile /home/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10assumetargetdefsdynamicresolutionmap oggdemo.oggdemo, target oggdemo.oggdemo;ok，至此單向復制已經配置完畢。 ——————————————————————————————————————下面配置反方復制：(原目標庫)源庫操作：10. 創建ext進程，tail文件，data_pump進程GGSCI (11g) 11> add ext extb, tranlog, begin nowEXTRACT added.GGSCI (11g) 12> add exttrail /home/oracle/ogg/dirdat/lb, ext extb, MEGABYTES 20 -- 創建源trail文件EXTTRAIL added.GGSCI (11g) 13> add extract dpeb, EXTTRAILSOURCE /home/oracle/ogg/dirdat/lb -- 創建data_pump進程EXTRACT added.GGSCI (11g) 14> add rmttrail /home/oracle/ogg/dirdat/rb, ext dpeb, MEGABYTES 20 --創建目標端trail文件RMTTRAIL added.GGSCI (11g) 17> edit param extb --extb文件添加內容顯示如下GGSCI (11g) 35> view param extbEXTRACT extbsetenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )setenv (ORACLE_SID = orcl)USERID goldengate, PASSWORD goldengateEXTTRAIL /home/oracle/ogg/dirdat/lbdynamicresolutiontable oggdemo.oggdemo;TRANLOGOPTIONS EXCLUDEUSER goldengateGGSCI (11g) 36> edit param dpeb --dpeb文件添加內容顯示如下GGSCI (11g) 39> view param dpebextract dpebsetenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )passthrurmthost 10.100.25.14,mgrport 7809, compressrmttrail /home/oracle/ogg/dirdat/rbdynamicresolutiontable oggdemo.oggdemo; 參數說明：passthru表示本進程是一個傳輸進程data pump，無需跟數據庫交互，只需要搬運數據即可；因為data pump要傳輸數據到目標，所以需要配置rmthost和rmttrail指定目標主機和隊列信息———————————————————————————————————————————————————————————— （原原庫）目標數據庫操作：11. 創建目標數據庫的replicate進程GGSCI (11g) 6> add rep repb, exttrail /home/oracle/ogg/dirdat/rb, nodbcheckpointREPLICAT added.參數說明： repa為進程名，一般為rep開頭表示是replicat進程，后面可以加1-2位字符標識，一般與ext進程對應；exttrail表示要抽取的數據隊列，注意是目標端的隊列位置；nodbcheckpoint表示不使用數據庫檢查點。 GGSCI (11g) 17> edit param repb -- repb文件添加內容顯示如下GGSCI (11g) 20> view param repbreplicat repbsetenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)setenv (ORACLE_SID = orcl)userid goldengate, password goldengatereperror default,abenddiscardfile /home/oracle/ogg/dirrpt/repb.dsc,append, megabytes 10assumetargetdefsdynamicresolutionmap oggdemo.oggdemo, target oggdemo.oggdemo;ok，至此雙向復制已經配置完成——————————————————————————————12. 測試源庫：GGSCI (11g) 12> start er *Sending START request to MANAGER ...EXTRACT DPEA startingSending START request to MANAGER ...EXTRACT EXTA startingSending START request to MANAGER ...REPLICAT REPB startingGGSCI (11g) 10> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DPEA 00:00:00 00:00:10 EXTRACT RUNNING EXTA 00:00:00 00:00:02 REPLICAT RUNNING REPB 00:00:00 00:00:04 目標庫：GGSCI (11g) 12> start er *Sending START request to MANAGER ...EXTRACT DPEA startingSending START request to MANAGER ...EXTRACT EXTA startingSending START request to MANAGER ...REPLICAT REPB startingGGSCI (11g) 10> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DPEA 00:00:00 00:00:10 EXTRACT RUNNING EXTA 00:00:00 00:00:02 REPLICAT RUNNING REPB 00:00:00 00:00:04 源庫：SQL> select * from oggdemo;no rows selected目標庫：SQL> select * from oggdemo;no rows selected源庫：SQL> insert into oggdemo values(1);1 row created.ok，雙向復制測試成功。 說明這里作者只配置了DML操作，關于DDL操作還需要進一步配置。 轉載請標明出處。SQL> commit;Commit complete.SQL> select * from oggdemo;ID----------1目標庫：SQL> select * from oggdemo;ID----------1SQL> insert into oggdemo values(2);1 row created.SQL> commit;Commit complete.SQL> select * from oggdemo;ID----------12源庫：SQL> select * from oggdemo;ID----------21
