环境说明： 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
