共计 6731 个字符，预计需要花费 17 分钟才能阅读完成。
本篇内容主要讲解“OGG 双向 DML 复制怎么实现”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“OGG 双向 DML 复制怎么实现”吧!
环境解释：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 复制怎么实现”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
