goldengate单向复制文档

1：实验环境

2：实验步骤

--下面的2.1-2.2步骤，都需要在源端和目标端分别执行。

2.1：准备工作 

2.1.1 建表空间

create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg01.dbf' size 200m; 

2.1.2 创建用户并授权

--建立用于复制的数据库账号并授权：

create user ogg identified by oracle default tablespace ogg;

grant connect,resource,dba to ogg;  

为了方便，这里直接赋予dba权限，如果对数据库安全要求高，可以去查询ogg文档，赋予复制需要的最小权限！(source和target端做相同的操作) 

2.1.3 配环境变量

--在源端和目标端配置环境变量（这里以源端为例）：

[oracle@source_pc ~]$ vi .bash_profile 

添加一行：

export GG_HOME=/home/oracle/goldengate

[oracle@source_pc ~]$ source .bash_profile

2.1.4 启动监听

 

[oracle@source_pc admin]$ lsnrctl start

2.1.5 配TNS

2.1.5.1 源端

[oracle@source_pc ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/[oracle@source_pc admin]$ vi tnsnames.ora 添加：gg_target =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.226)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )

 

--测试看是否配置成功(tnsping gg_target)

--显示OK字样，表示配置成功

2.1.5.2 目标端

[oracle@target_pc admin]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/[oracle@target_pc admin]$ vi tnsnames.ora 添加：gg_source=  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.225)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )

 

2.1.6 检查是否处于归档模式

 

如果是distabled，则需要关库，启动到mount状态，alter database archivelog;

2.1.7 开启数据库附加日志

SQL> select supplemental_log_data_min  2  from v$database; SUPPLEME--------NO SQL> alter database add supplemental log data; Database altered. SQL> select supplemental_log_data_min  2  from v$database; SUPPLEME--------YES SQL>

 

2.1.8 开启force logging

SQL> select force_logging  2  from v$database; FOR---NO SQL> alter database force logging; Database altered. SQL> select force_logging  2  from v$database; FOR---YES

 

2.1.9 准备测试数据

源端scott用户下有一个t表，有7条数据:

 

目标端scott用户下新建一个t表，但是不插入数据（只复制表定义，不填充数据 ）：

2.2：安装goldengate

--目标端及源端都需安装

2.2.1下载安装包

我的机器是32位的，所以用安装包：

2.2.2 将安装包上传到Linux下

--可以用samba服务，winscp软件或者xmanager进行传输

2.2.3 解压

[oracle@source_pc goldengate]$ <span style="BACKGROUND-COLOR: #ffcc33">unzip ogg112101_fbo_ggs_Linux_x86_ora10g_32bit.zip</span>Archive:  ogg112101_fbo_ggs_Linux_x86_ora10g_32bit.zip  inflating: fbo_ggs_Linux_x86_ora10g_32bit.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@source_pc goldengate]$ <span style="BACKGROUND-COLOR: #ffcc33">tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar </span>UserExitExamples/UserExitExamples/ExitDemo_passthru/UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIXUserExitExamples/ExitDemo_passthru/exitdemo_passthru.cUserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUXUserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSSUserExitExamples/ExitDemo_passthru/exitdemopassthru.vcprojUserExitExamples/ExitDemo_passthru/readme.txtUserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARISUserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUXUserExitExamples/ExitDemo_lobs/UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcprojUserExitExamples/ExitDemo_lobs/Makefile_lob.HPUXUserExitExamples/ExitDemo_lobs/exitdemo_lob.cUserExitExamples/ExitDemo_lobs/Makefile_lob.AIXUserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARISUserExitExamples/ExitDemo_lobs/Makefile_lob.LINUXUserExitExamples/ExitDemo_lobs/readme.txtUserExitExamples/ExitDemo/UserExitExamples/ExitDemo/exitdemo.vcprojUserExitExamples/ExitDemo/Makefile_exit_demo.SOLARISUserExitExamples/ExitDemo/exitdemo_utf16.cUserExitExamples/ExitDemo/readme.txtUserExitExamples/ExitDemo/exitdemo.cUserExitExamples/ExitDemo/Makefile_exit_demo.HPUXUserExitExamples/ExitDemo/Makefile_exit_demo.AIXUserExitExamples/ExitDemo/Makefile_exit_demo.LINUXUserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSSUserExitExamples/ExitDemo_pk_befores/UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.cUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARISUserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcprojUserExitExamples/ExitDemo_pk_befores/readme.txtUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIXUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUXUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUXUserExitExamples/ExitDemo_more_recs/UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUXUserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcprojUserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIXUserExitExamples/ExitDemo_more_recs/readme.txtUserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.cUserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUXUserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARISbcpfmt.tplbcrypt.txtcfg/cfg/ProfileConfig.xmlcfg/jps-config-jse.xmlcfg/password.propertiescfg/MPMetadataSchema.xsdcfg/mpmetadata.xmlcfg/Config.propertieschkpt_ora_create.sqlcobgenconvchkpccntl.tplddl_cleartrace.sqlddl_ddl2file.sqlddl_disable.sqlddl_enable.sqlddl_filter.sqlddl_nopurgeRecyclebin.sqlddl_ora10.sqlddl_ora10upCommon.sqlddl_ora11.sqlddl_ora9.sqlddl_pin.sqlddl_purgeRecyclebin.sqlddl_remove.sqlddl_session.sqlddl_session1.sqlddl_setup.sqlddl_status.sqlddl_staymetadata_off.sqlddl_staymetadata_on.sqlddl_trace_off.sqlddl_trace_on.sqlddl_tracelevel.sqlddlcobdefgendemo_more_ora_create.sqldemo_more_ora_insert.sqldemo_ora_create.sqldemo_ora_insert.sqldemo_ora_lob_create.sqldemo_ora_misc.sqldemo_ora_pk_befores_create.sqldemo_ora_pk_befores_insert.sqldemo_ora_pk_befores_updates.sqldirjar/dirjar/xmlparserv2.jardirjar/spring-security-cas-client-3.0.1.RELEASE.jardirjar/spring-security-taglibs-3.0.1.RELEASE.jardirjar/org.springframework.transaction-3.0.0.RELEASE.jardirjar/identityutils.jardirjar/xpp3_min-1.1.4c.jardirjar/jps-api.jardirjar/jacc-spi.jardirjar/org.springframework.jdbc-3.0.0.RELEASE.jardirjar/org.springframework.aspects-3.0.0.RELEASE.jardirjar/identitystore.jardirjar/jps-mbeans.jardirjar/fmw_audit.jardirjar/commons-codec-1.3.jardirjar/jmxremote_optional-1.0-b02.jardirjar/spring-security-core-3.0.1.RELEASE.jardirjar/log4j-1.2.15.jardirjar/commons-logging-1.0.4.jardirjar/org.springframework.context-3.0.0.RELEASE.jardirjar/org.springframework.asm-3.0.0.RELEASE.jardirjar/org.springframework.expression-3.0.0.RELEASE.jardirjar/org.springframework.instrument-3.0.0.RELEASE.jardirjar/jps-wls.jardirjar/jps-upgrade.jardirjar/jdmkrt-1.0-b02.jardirjar/jps-ee.jardirjar/jps-common.jardirjar/org.springframework.beans-3.0.0.RELEASE.jardirjar/jps-manifest.jardirjar/oraclepki.jardirjar/spring-security-acl-3.0.1.RELEASE.jardirjar/jagent.jardirjar/osdt_xmlsec.jardirjar/jps-patching.jardirjar/org.springframework.context.support-3.0.0.RELEASE.jardirjar/jps-unsupported-api.jardirjar/monitor-common.jardirjar/osdt_cert.jardirjar/spring-security-web-3.0.1.RELEASE.jardirjar/spring-security-config-3.0.1.RELEASE.jardirjar/org.springframework.aop-3.0.0.RELEASE.jardirjar/xstream-1.3.jardirjar/jps-internal.jardirjar/osdt_core.jardirjar/org.springframework.core-3.0.0.RELEASE.jardirjar/slf4j-api-1.4.3.jardirjar/ldapjclnt11.jardirjar/org.springframework.test-3.0.0.RELEASE.jardirjar/org.springframework.orm-3.0.0.RELEASE.jardirjar/org.springframework.web-3.0.0.RELEASE.jardirjar/slf4j-log4j12-1.4.3.jardirjar/jsr250-api-1.0.jardirprm/dirprm/jagent.prmemsclntextractfreeBSD.txtggMessage.datggcmdggscihelp.txtjagent.shkeygenlibantlr3c.solibdb-5.2.solibgglog.solibggrepo.solibicudata.so.38libicui18n.so.38libicuuc.so.38libxerces-c.so.28libxml2.txtlogdumpmarker_remove.sqlmarker_setup.sqlmarker_status.sqlmgrnotices.txtoggerrparams.sqlprvtclkm.plbpw_agent_util.shremove_seq.sqlreplicatretracereverserole_setup.sqlsequence.sqlserversqlldr.tpltcperrsucharset.hulg.sqlusrdecs.hzlib.txt

 

2.2.4 create subdirs

[oracle@source_pc goldengate]$[oracle@source_pc goldengate]$ ./ggsci Oracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.   GGSCI (source_pc) 1> create subdirs Creating subdirectories under current directory /home/oracle/goldengate Parameter files                /home/oracle/goldengate/dirprm: already existsReport files                   /home/oracle/goldengate/dirrpt: createdCheckpoint files               /home/oracle/goldengate/dirchk: createdProcess status files           /home/oracle/goldengate/dirpcs: createdSQL script files               /home/oracle/goldengate/dirsql: createdDatabase definitions files     /home/oracle/goldengate/dirdef: createdExtract data files             /home/oracle/goldengate/dirdat: createdTemporary files                /home/oracle/goldengate/dirtmp: createdStdout files                   /home/oracle/goldengate/dirout: created

 

2.3 goldengate配置

2.3.1 将源端t表现有数据全部同步到目标端t表里

--可以通过exp,imp实现完全同步，也可以用extract,replicate进程同步。这里使用的是后者。

2.3.1.1 启动mgr进程

--在源端和目标端皆需要启动

源端：GGSCI (source_pc) 14> edit params mgr在该参数文件输入： PORT 7809GGSCI (source_pc) 4> view params mgr PORT 7809  GGSCI (source_pc) 5> start mgr Manager started.  GGSCI (source_pc) 6> info mgr Manager is running (IP port source_pc.7809).

 

2.3.1.2 source端添加extract进程

 

GGSCI (source_pc) 7> add extract einig1,sourceistableEXTRACT added.--sourceistable代表直接从表中读取数据GGSCI (source_pc) 4> edit params einig1 --einig1代表extract initial load group 1缩写  在该文件里添加以下内容： extract einig1setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)userid ogg,password oraclermthost 192.168.8.226,mgrport 7809rmttask replicat,group rinig1table scott.t;

2.3.1.3 target端添加replicat进程

GGSCI (target_pc) 1> add replicat rinig1,specialrunREPLICAT added. GGSCI (target_pc) 2> edit params rinig1--rinig1代表replicat initial load group 1缩写  添加如下内容：replicat rinig1    setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)       assumetargetdefs   userid ogg,password oraclediscardfile ./dirrpt/rinig1.dsc,purge   map scott.t,target scott.t;--rinig1的名字必须同source端定义的group名字相同  

 

2.3.1.4 source端启动extract进程

GGSCI (source_gg) 19> start extract einig1

 

2.3.1.5 target端验证

 

现在，虽然源端的数据能够全部同步到目标端了。但是还没有实现实时同步，比如，我现在在源端里插入一条新的数据，目标库是查不到的。

2.3.2 实时同步复制

2.3.2.1:修改参数文件

GGSCI (source_pc) 3> edit params eora_t1在该参数文件中添加：   extract eora_t1  setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)  userid ogg,password oracle  exttrail ./dirdat/aa  table scott.t; 

2.3.2.2：开启scott用户下表的附加日志 

GGSCI (source_pc) 1> dblogin userid ogg,password oracleSuccessfully logged into database. GGSCI (source_pc) 4> add trandata scott.tLogging of supplemental redo data enabled for table SCOTT.T. 

 

2.3.2.3：添加extract进程，添加trail文件

GGSCI (source_pc) 1> add extract eora_t1,tranlog,begin now EXTRACT added.  --extract进程名不能超过8个字符GGSCI (source_pc) 2> add exttrail ./dirdat/aa,extract eora_t1,megabytes 100 //添加trail文件  EXTTRAIL added.  --exttrail文件名前缀不能超过2个字符GGSCI (source_pc) 3> start extract eora_t1  Sending START request to MANAGER ...  EXTRACT EORA_T1 starting  

 

--这时，在/home/oracle/goldengate/dirdat下可以看到生成的文件aa000000。

 

2.3.2.4：添加pump进程

GGSCI (source_pc) 5> edit params pora_t1  添加如下内容： extract pora_t1  setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)  passthru  rmthost 192.168.8.226,mgrport 7809  rmttrail ./dirdat/pa  table scott.t;   GGSCI (source_pc) 9> add extract pora_t1,exttrailsource ./dirdat/aa  //这里aa文件名同前面extract进程参数文件中定义的trail文件名一致  EXTRACT added.  GGSCI (source_pc) 10> add rmttrail ./dirdat/pa,extract pora_t1,megabytes 100 //添加传输到target数据库的trail问文件名，应该同参数文件中描述的一致  RMTTRAIL added.   GGSCI (source_pc) 11> start extract pora_t1  Sending START request to MANAGER ...  EXTRACT PORA_T1 starting  

 

 

2.3.2.5：在target端添加检查表，配置replicat进程

GGSCI (target_pc) 1> edit params ./GLOBALS  GGSCI (target_pc) 2> view params ./GLOBALS  checkpointtable ogg.ggschkpt   GGSCI (target_pc) 3> exit //这里需要退出ggsci终端  [oracle@ target_pc ~]$ sqlplus ogg/oracle  SQL> select tname from tab;  no rows selected   [oracle@ target_pc ogg]$ ggsci   GGSCI target_ (pc) 1> dblogin userid ogg,password oracle  Successfully logged into database.  GGSCI (target_pc) 2> add checkpointtable  No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...  Successfully created checkpoint table ogg.ggschkpt.   SQL> select tname from tab;   TNAME  ------------------------------  GGSCHKPT  GGSCHKPT_LOX   GGSCI (target_pc) 3> edit params rora_t1  GGSCI (target_pc) 4> view params rora_t1  replicat rora_t1  setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)  userid ogg,password oracle handlecollisions  assumetargetdefs  discardfile ./dirrpt/rora_t1.dsc,purge  map scott.t ,target scott.t;   GGSCI (target_pc) 5> add replicat rora_t1,exttrail ./dirdat/pa  REPLICAT added.   GGSCI (target_pc) 6> start replicat rora_t1   Sending START request to MANAGER ...  REPLICAT RORA_T1 starting  

 

2.3.2.6： 测试

在源端：

 

目标端：

 

   

 --假如目标端有源端没有的数据，也不妨碍源端将新数据同步过来。