OGG双向DML复制怎么实现

蜗牛互联网技术资讯2021-12-222810

本篇内容主要讲解“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 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等进程）