189 8069 5689
本篇内容主要讲解“OGG双向DML复制怎么实现”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“OGG双向DML复制怎么实现”吧!
创新互联建站主营玉林网站建设的网络公司,主营网站建设方案,app软件定制开发,玉林h5小程序开发搭建,玉林网站营销推广欢迎玉林等地区企业咨询
环境解释：hostname：slient,db_name:test作为源库，而hostname:one,db_name:onemo作为目标库，
本次只需要配置一次反向的操作即可：即one为源端， slient为目标端.
配置步骤：
1、源端：检查数据库是否在归档模式，建议在归档模式:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SQL>
已归档；
2.源库：添加附加日志来唯一标识一行记录，要在数据库级别打开最小开关。
语法：alter database add supplemental log data;
SQL> select NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME FOR SUPPLEME
--------- --- --------
ONEMO NO YES
3.源端测试用表
测试数据用的是scott用户的下的表BONUS。要确保复制的表的日志信息是完整的，相关表必须是logging，一定要把nologing变成logging。
SQL> conn scott/tiger;
Connected.
SQL> select * from BONUS;
no rows selected
SQL>
SQL> desc BONUS
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
SQL> conn / as sysdba
Connected.
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 YES
4.源端：以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.BONUS
2017-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 1
EXTRACT added.
GGSCI (one as ogg@onemo) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_REV 00:00:00 00:00:05
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:06
6.源端：添加队列文件
GGSCI (one as ogg@onemo) 12> add EXTTRAIL /u01/app/oracle/ogg/dirdat/rv, extract ext_rev,megabytes 100
EXTTRAIL added.
GGSCI (one as ogg@onemo) 13>
7.源端编辑的抓取进程的参数extract；
GGSCI (one as ogg@onemo) 30> edit param ext_rev
EXTRACT ext_rev
setenv (ORACLE_SID=onemo)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/app/oracle/ogg/dirdat/rv
dynamicresolution
TABLE scott.bonus;
GGSCI (one as ogg@onemo) 31>
8.源库启动extact抓取进程：
GGSCI (one as ogg@onemo) 28> start ext_rev
Sending START request to MANAGER ...
EXTRACT EXT_REV starting
GGSCI (one as ogg@onemo) 29> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_REV 00:08:37 00:00:03
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:01
GGSCI (one as ogg@onemo) 30>
9.源库配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯
GGSCI (one as ogg@onemo) 32> add extract dpe_rev, exttrailsource /u01/app/oracle/ogg/dirdat/rv
EXTRACT added.
--输出：目标主机怎么写，也是定义datapumo进程的输出。
GGSCI (one as ogg@onemo) 34> add rmttrail /opt/ogg/ogg_home/dirdat/tv,EXTRACT dpe_rev,MEGABYTES 100
RMTTRAIL added.
GGSCI (one as ogg@onemo) 35>
10.源端配置datapump进程参数
GGSCI (one as ogg@onemo) 39> edit param DPE_REV
extract dpe_rev
setenv (ORACLE_SID=onemore)
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
passthru
--REPORT AT 01:59
--reportrollover at 02:00
rmthost 192.168.56.20,mgrport 7809, compress
rmttrail /opt/ogg/ogg_home/dirdat/tv
Dynamicresolution
table scott.bonus;
~
"dirprm/dpe_rev.prm" [New] 10L, 265C written
GGSCI (one as ogg@onemo) 40>
GGSCI (one as ogg@onemo) 40> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPE_REV 00:00:00 00:05:17
EXTRACT RUNNING EXT_REV 00:00:00 00:00:02
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:07
--启动DPE_REV
GGSCI (one as ogg@onemo) 41> start DPE_REV
Sending START request to MANAGER ...
EXTRACT DPE_REV starting
GGSCI (one as ogg@onemo) 42> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPE_REV 00:00:00 00:05:34
EXTRACT RUNNING EXT_REV 00:00:00 00:00:07
REPLICAT 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]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating 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 ogg
Successfully logged into database.
GGSCI (slient as ogg@test) 2> add checkpointtable ogg.rep_bouns_ckpt
Successfully 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_ckpt
REPLICAT added.
GGSCI (slient as ogg@test) 5>
13.编辑目标端replicate参数
GGSCI (slient as ogg@test) 5> edit param rep_rev
replicat rep_rev
setenv (oracle_sid=test)
setenv (nls_lang ="american_america.zhs16gbk")
userid ogg,password ogg
--report at 01:59
--reportrollover at 02:00
reperror default,abend
discardfile /opt/ogg/ogg_home/dirrpt/repb.dsc,append, megabytes 10
assumetargetdefs
--allownoopupdates
dynamicresolution
--insertallrecords
map scott.bonus,target scott.bonus;
~
~
"dirprm/rep_rev.prm" [New] 13L, 356C written
GGSCI (slient as ogg@test) 6>
GGSCI (slient as ogg@test) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPEA 00:00:00 00:00:07
EXTRACT RUNNING EXTA 00:00:00 00:00:10
REPLICAT STOPPED REP_REV 00:00:00 00:02:57
14.目标端启动并查看replicate进程是否运行
GGSCI (slient as ogg@test) 7> start REP_REV
Sending START request to MANAGER ...
REPLICAT REP_REV starting
GGSCI (slient as ogg@test) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPEA 00:00:00 00:00:02
EXTRACT RUNNING EXTA 00:00:00 00:00:03
REPLICAT RUNNING REP_REV 00:00:00 00:00:02
GGSCI (slient as ogg@test) 9>
15. 测试源端和目标端的数据
--测试前先检查源库和目标库：
源库：
SQL> show user
USER is "SCOTT"
SQL>
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
wang sales 1000 .1
SQL>
目标库：
SQL> show user
USER is "SCOTT"
SQL> select * from BONUS;
no rows selected
SQL>
开始测试：
源库：
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 .2
wang 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 .2
li manager 10000 .2
li manager 10000 .2
wang sales 1000 .1
目标库：
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
就这样，来回在两个库之间不停的copy过来copy去（未防止日志不停增长，关闭目标库、源库相关extract、replicate等进程）
到此，相信大家对“OGG双向DML复制怎么实现”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！