OGG双向DML复制怎么实现

本篇内容主要讲解“OGG双向DML复制怎么实现”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“OGG双向DML复制怎么实现”吧!

环境解释：hostname：slient,db_name:test作为源库，而hostname:one,db_name:onemo作为目标库，本次只需要配置一次反向的操作即可：即one为源端，  slient为目标端.配置步骤：1、源端：检查数据库是否在归档模式，建议在归档模式:SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     9Next log sequence to archive   11Current log sequence           11SQL>已归档；2.源库：添加附加日志来唯一标识一行记录，要在数据库级别打开最小开关。语法：alter database add supplemental log data;  SQL> select NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;NAME      FOR SUPPLEME--------- --- --------ONEMO     NO  YES3.源端测试用表测试数据用的是scott用户的下的表BONUS。要确保复制的表的日志信息是完整的，相关表必须是logging，一定要把nologing变成logging。SQL> conn scott/tiger;Connected.SQL> select * from BONUS;no rows selectedSQL> SQL>  desc BONUSName                                      Null?    Type----------------------------------------- -------- ---------------------------- ENAME                                              VARCHAR2(10)JOB                                                VARCHAR2(9)SAL                                                NUMBER COMM                                               NUMBERSQL> conn / as sysdbaConnected.SQL> --查看表BONUS的force_logging 属性语法： alter table schema.table_name logging;  SQL> select OWNER,TABLE_NAME,STATUS,LOGGING from dba_tables where owner=SCOTT and table_name=BONUS;OWNER                          TABLE_NAME                     STATUS   LOG------------------------------ ------------------------------ -------- ---SCOTT                          BONUS                          VALID    YES4.源端：以goldengate这个schema登陆数据库GGSCI (one) 1> dblogin userid ogg,password ogg;Successfully logged into database.GGSCI (one as ogg@onemo) 2> GGSCI (one as ogg@onemo) 7> add trandata scott.BONUS2017-10-27 04:57:07  WARNING OGG-06439  No unique key is defined for table BONUS. 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.BONUS.TRANDATA for scheduling columns has been added on table SCOTT.BONUS.TRANDATA for instantiation CSN has been added on table SCOTT.BONUS.GGSCI (one as ogg@onemo) 8>5.源端配置抓取进程GGSCI (one as ogg@onemo) 9> add extract ext_rev, tranlog, begin now,threads 1EXTRACT added.GGSCI (one as ogg@onemo) 10> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     STOPPED     EXT_REV     00:00:00      00:00:05    REPLICAT    RUNNING     REP_DEMO    00:00:00      00:00:066.源端：添加队列文件GGSCI (one as ogg@onemo) 12> add EXTTRAIL /u01/app/oracle/ogg/dirdat/rv, extract ext_rev,megabytes 100EXTTRAIL added.GGSCI (one as ogg@onemo) 13>7.源端编辑的抓取进程的参数extract；GGSCI (one as ogg@onemo) 30> edit param ext_revEXTRACT ext_revsetenv (ORACLE_SID=onemo)setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)  userid ogg,password ogg  exttrail /u01/app/oracle/ogg/dirdat/rvdynamicresolution  TABLE scott.bonus;  GGSCI (one as ogg@onemo) 31> 8.源库启动extact抓取进程：GGSCI (one as ogg@onemo) 28> start ext_revSending START request to MANAGER ...EXTRACT EXT_REV startingGGSCI (one as ogg@onemo) 29> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     EXT_REV     00:08:37      00:00:03    REPLICAT    RUNNING     REP_DEMO    00:00:00      00:00:01GGSCI (one as ogg@onemo) 30> 9.源库配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯GGSCI (one as ogg@onemo) 32> add extract dpe_rev, exttrailsource /u01/app/oracle/ogg/dirdat/rvEXTRACT added.--输出：目标主机怎么写，也是定义datapumo进程的输出。GGSCI (one as ogg@onemo) 34> add rmttrail /opt/ogg/ogg_home/dirdat/tv,EXTRACT dpe_rev,MEGABYTES 100RMTTRAIL added.GGSCI (one as ogg@onemo) 35> 10.源端配置datapump进程参数GGSCI (one as ogg@onemo) 39> edit param DPE_REVextract dpe_revsetenv (ORACLE_SID=onemore)setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )passthru--REPORT AT 01:59--reportrollover at 02:00rmthost  192.168.56.20,mgrport 7809, compressrmttrail /opt/ogg/ogg_home/dirdat/tvDynamicresolutiontable scott.bonus;~"dirprm/dpe_rev.prm" [New] 10L, 265C writtenGGSCI (one as ogg@onemo) 40> GGSCI (one as ogg@onemo) 40> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNINGEXTRACT     STOPPED     DPE_REV     00:00:00      00:05:17    EXTRACT     RUNNING     EXT_REV     00:00:00      00:00:02REPLICAT    RUNNING     REP_DEMO    00:00:00      00:00:07    --启动DPE_REVGGSCI (one as ogg@onemo) 41> start DPE_REVSending START request to MANAGER ...EXTRACT DPE_REV startingGGSCI (one as ogg@onemo) 42> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNINGEXTRACT     RUNNING     DPE_REV     00:00:00      00:05:34    EXTRACT     RUNNING     EXT_REV     00:00:00      00:00:07REPLICAT    RUNNING     REP_DEMO    00:00:00      00:00:04    GGSCI (one as ogg@onemo) 43>11.目标端为replicat进程创建checkpoint表[ogg@slient ogg_home]$ pwd/opt/ogg/ogg_home[ogg@slient ogg_home]$ ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBOLinux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38Operating system character set identified as UTF-8.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.GGSCI (slient) 1> dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (slient as ogg@test) 2> add checkpointtable ogg.rep_bouns_ckptSuccessfully created checkpoint table ogg.rep_bouns_ckpt.GGSCI (slient as ogg@test) 3> 12.目标端配置目标端replicate进程GGSCI (slient as ogg@test) 4> add replicat rep_rev,exttrail /opt/ogg/ogg_home/dirdat/tv,checkpointtable ogg.rep_bouns_ckptREPLICAT added.GGSCI (slient as ogg@test) 5> 13.编辑目标端replicate参数GGSCI (slient as ogg@test) 5>   edit param rep_revreplicat rep_revsetenv (oracle_sid=test)setenv (nls_lang ="american_america.zhs16gbk")userid ogg,password ogg--report at 01:59--reportrollover at 02:00reperror default,abenddiscardfile /opt/ogg/ogg_home/dirrpt/repb.dsc,append, megabytes 10assumetargetdefs--allownoopupdatesdynamicresolution--insertallrecordsmap scott.bonus,target scott.bonus;~~"dirprm/rep_rev.prm" [New] 13L, 356C writtenGGSCI (slient as ogg@test) 6> GGSCI (slient as ogg@test) 6> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DPEA        00:00:00      00:00:07EXTRACT     RUNNING     EXTA        00:00:00      00:00:10    REPLICAT    STOPPED     REP_REV     00:00:00      00:02:5714.目标端启动并查看replicate进程是否运行GGSCI (slient as ogg@test) 7> start REP_REVSending START request to MANAGER ...REPLICAT REP_REV startingGGSCI (slient as ogg@test) 8> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DPEA        00:00:00      00:00:02EXTRACT     RUNNING     EXTA        00:00:00      00:00:03REPLICAT    RUNNING     REP_REV     00:00:00      00:00:02    GGSCI (slient as ogg@test) 9> 15. 测试源端和目标端的数据--测试前先检查源库和目标库：源库：SQL> show userUSER is "SCOTT"SQL> SQL> select * from bonus;ENAME      JOB              SAL       COMM---------- --------- ---------- ----------wang       sales           1000         .1SQL> 目标库：SQL> show userUSER is "SCOTT"SQL> select * from BONUS;no rows selectedSQL>  开始测试：源库：SQL> insert into bonus values(li,manager,10000,0.2);1 row created.SQL> commmit;SQL>  select * from bonus;ENAME      JOB              SAL       COMM---------- --------- ---------- ----------li         manager        10000         .2wang       sales           1000         .1检查目标库：SQL>  select * from bonus;ENAME      JOB              SAL       COMM---------- --------- ---------- ----------li         manager        10000         .2再过一会查看：源库：SQL>  select * from bonus;ENAME      JOB              SAL       COMM---------- --------- ---------- ----------li         manager        10000         .2li         manager        10000         .2li         manager        10000         .2wang       sales           1000         .1目标库：SQL>  select * from bonus;ENAME      JOB              SAL       COMM---------- --------- ---------- ----------li         manager        10000         .2li         manager        10000         .2li         manager        10000         .2li         manager        10000         .2就这样，来回在两个库之间不停的copy过来copy去（未防止日志不停增长，关闭目标库、源库相关extract、replicate等进程）

到此，相信大家对“OGG双向DML复制怎么实现”有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！