ogg一对多+级联+多对一同步测试

最新推荐文章于 2022-04-01 01:59:18 发布

lt89102476

最新推荐文章于 2022-04-01 01:59:18 发布

阅读量2.3k

点赞数

分类专栏： ogg oracle 文章标签： ogg同步

本文链接：https://blog.csdn.net/lt89102476/article/details/100011009

版权

oracle 同时被 2 个专栏收录

35 篇文章 0 订阅

订阅专栏

ogg

2 篇文章 0 订阅

订阅专栏

ogg配置一对多+级联复制-tina实验

为了测试windows上的ogg使用，这次特定选择了一台干净的windows主机
A：windows 7 64bit 本机
B：centos6.5 64bit 172.16.5.3
C：centos6.5 64bit 172.16.5.4

B和C 是linux的，已经配置过了
1.软件安装和用户配置
alter system set recyclebin=off scope=spfile;
shutdown immediate;
startup mount
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data(primary key,unique,foreign key) columns;
alter system set recyclebin=off scope=spfile;
alter database open;

root:
mkdir -p /u01/oracle/ogg
chown -R oracle:oinstall /u01/oracle/ogg

oracle:
vi .bash_profile
export OGG_HOME=/u01/oracle/ogg
export LD_LIBRARY_PATH= $LD_LIBRARY_PATH:$ ORACLE_HOME/lib:/usr/lib: $OGG_HOME export PATH=$ PATH: $OGG_HOME:$ HOME/bin:$ORACLE_HOME/bin
source .bash_profile

cd /u01/oracle/ogg
chown oracle:oinstall fbo_ggs_Linux_x64_ora11g_64bit.tar
su oracle
cd /u01/oracle/ogg
tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar

连接数据库，创建用户
create tablespace ogg datafile ‘’ size 100m;
create user oggtest identified by oggtest default tablespace ogg temporary tablespace temp;
grant dba to oggtest;

注意sid、文件位置有所不同
create tablespace ogg datafile ‘/u01/oradata/tinadb/ogg_01.dbf’ size 100m autoextend on maxsize 1g;
create tablespace ogg datafile ‘/u01/app/oracle/oradata/XTPLAT/ogg_01.dbf’ size 100m autoextend on maxsize 1g;

2.创建目录
./ggsci
create subdirs

测试表–一定要有主键
– Create table
create table TINA_01
(
owner VARCHAR2(30) not null,
object_name VARCHAR2(128) not null,
subobject_name VARCHAR2(30),
object_id NUMBER,
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE,
last_ddl_time DATE,
timestamp VARCHAR2(19),
status VARCHAR2(7),
temporary VARCHAR2(1),
generated VARCHAR2(1),
secondary VARCHAR2(1),
namespace NUMBER,
edition_name VARCHAR2(30)
) tablespace XTPLAT_DATA;
– Create/Recreate primary, unique and foreign key constraints
alter table openVone.TINA_01 add constraint PRI_TINA01 primary key (OWNER, OBJECT_NAME);

3.添加表级trandata
GGSCI (MYSQL_HA) 2> dblogin userid oggtest,password oggtest
Successfully logged into database.
GGSCI (MYSQL_HA) 2> add trandata openVone.tina_01
Logging of supplemental redo data enabled for table OPENVONE.TINA_01.

上面的操作三个节点都要做

----A是windows的，步骤稍微有些不同

1.windows安装ogg
ogg112101_ggs_Windows_x64_ora11g_64bit.zip
文件放到C，解压到ogg目录

2.安装oracle
环境变量：ORACLE_HOME D:\app\product\11.2.0\dbhome_1

3.cmd
C:\Users\liting>c:
C:\Users\liting>cd …/…/ogg
C:\ogg>
C:\ogg>ggsci —注意没有./执行了

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Windows x64 (optimized), Oracle 11g on Apr 23 2012 04:55:02
Copyright © 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (GUOJUN) 1>
GGSCI (GUOJUN) 1> dblogin userid openVone@xtplat_local,password openVone --先用一个用户测试下
Successfully logged into database.

4.开始部署
D:\app\product\11.2.0\dbhome_1\BIN>sqlplus sys/tina@xtplat_local as sysdba (本地cmd中进行配置db归档)
D:\app\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=xtplat
D:\app\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba —下面这样才是对的，否则@xtplat_local 是走网络监听进去的，一旦监听关闭，就连不上

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 2月 21 15:58:37 2019
Copyright © 1982, 2013, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
alter system set recyclebin=off scope=spfile;
shutdown immediate;
startup mount
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data(primary key,unique,foreign key) columns;
alter database open;

连接数据库，创建用户
create tablespace ogg datafile ‘’ size 100m;
create user oggtest identified by oggtest default tablespace ogg temporary tablespace temp;
grant dba to oggtest;

注意sid、文件位置有所不同
create tablespace ogg datafile ‘/app/product/11.2.0/dbhome_1/oradata/ogg_01.dbf’ size 100m autoextend on maxsize 1g;

2.创建目录
./ggsci
create subdirs

测试表–一定要有主键
– Create table
create table openVone.TINA_01
(
owner VARCHAR2(30) not null,
object_name VARCHAR2(128) not null,
subobject_name VARCHAR2(30),
object_id NUMBER,
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE,
last_ddl_time DATE,
timestamp VARCHAR2(19),
status VARCHAR2(7),
temporary VARCHAR2(1),
generated VARCHAR2(1),
secondary VARCHAR2(1),
namespace NUMBER,
edition_name VARCHAR2(30)
) tablespace XTPLAT;
– Create/Recreate primary, unique and foreign key constraints
alter table openVone.TINA_01 add constraint PRI_TINA01 primary key (OWNER, OBJECT_NAME);

开始配置节点间的同步：
设计是A 同步到B、C

其实跟一对一不同的就是，A有两个传输dump进程

A：源端1
edit params ./GLOBALS
GGSCHEMA oggtest

1）mgr进程
edit params mgr
port 7809
dynamicportlist 7810-7815
autorestart extract *,retries 10,waitminutes 1,resetminutes 5
autorestart replicat ,retries 10,waitminutes 1,resetminutes 5
purgeoldextracts ./dirdat/,usecheckpoints,minkeepdays 3

start mgr

2）extract进程
add extract extA ,tranlog,begin now
add exttrail ./dirdat/nd,extract extA,megabytes 100

edit params extA 内容如下：
extract extA
dynamicresolution
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
–ddl include all
userid oggtest,password oggtest
tranlogoptions excludeuser oggtest
exttrail ./dirdat/ab
table openVone.TINA_01;

3）data pump进程
edit params dpb 内容如下：
extract dpb
dynamicresolution
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
passthru
rmthost 172.16.5.3,mgrport 7809
rmttrail ./dirdat/ne
table openVone.TINA_01;

重点命令：
ADD EXTRACT dpb,EXTTRAILSOURCE ./dirdat/nd
add rmttrail ./dirdat/ne ,extract dpb

注意保持一致，exttrail就是nd，rmtrail就是ne

传向C的dump文件
edit params dpc 内容如下：
extract dpc
dynamicresolution
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
passthru
rmthost 172.16.5.4,mgrport 7809
rmttrail ./dirdat/ne
table openVone.TINA_01;

ADD EXTRACT dpc,EXTTRAILSOURCE ./dirdat/nd
add rmttrail ./dirdat/ne ,extract dpc

注意保持一致，exttrail就是nd，rmtrail就是ne

配置好以后就是：
GGSCI (GUOJUN) 18> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED DPB 00:00:00 00:01:45
EXTRACT STOPPED DPC 00:00:00 00:00:13
EXTRACT STOPPED EXTA 00:00:00 00:06:56

B:目标端 172.16.5.4
edit params ./GLOBALS
GGSCHEMA oggtest
checkpointtable oggtest.checkpoint

dblogin userid oggtest,password oggtest
GGSCI (test4) 5> add checkpointtable oggtest.checkpoint
Successfully created checkpoint table oggtest.checkpoint.

1）mgr进程
edit param mgr
port 7809
dynamicportlist 7810-7815
autorestart extract *,retries 10,waitminutes 1,resetminutes 5
autorestart replicat ,retries 10,waitminutes 1,resetminutes 5
purgeoldextracts ./dirdat/,usecheckpoints,minkeepdays 3

2）replicate进程
add replicat repa exttrail ./dirdat/ne ,checkpointtable oggtest.checkpoint; —这个可行，昨天是文件名写错了
add replicat repa exttrail ./dirdat/ne,begin now,nodbcheckpoint; —这个没有使用checkpoint，也可以行，直接读取redo

edit param repa 内容如下：
replicat repa
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
reperror default,discard --如果复制数据出错则忽略
–reperror (default,ignore)
–ddl include all
–ddlerror default ignore retryop maxretries 3 retrydelay 5
userid oggtest,password oggtest
handlecollisions --当目标端已有数据时，略过重复数据错误
assumetargetdefs ----两台数据库数据结构一致则使用此参数
discardfile ./dirrpt/repa_ne.dsc,purge --错误信息写入XXXX文件，追加模式，最大100m
map openVone.TINA_01 ,target openVone.TINA_01;

C:目标端 172.16.5.3
edit params ./GLOBALS
GGSCHEMA oggtest
checkpointtable oggtest.checkpoint

dblogin userid oggtest,password oggtest
GGSCI (test4) 5> add checkpointtable oggtest.checkpoint
Successfully created checkpoint table oggtest.checkpoint.

2）replicate进程
add replicat repac exttrail ./dirdat/ne ,checkpointtable oggtest.checkpoint; —这个可行，昨天是文件名写错了
add replicat repac exttrail ./dirdat/ne,begin now,nodbcheckpoint; —这个没有使用checkpoint，也可以行，直接读取redo

edit param repac 内容如下：
replicat repac
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
reperror default,discard
userid oggtest,password oggtest
handlecollisions
assumetargetdefs
—ddl include mapped
—ddloptions report
discardfile ./dirrpt/repa_ne.dsc,purge
map openVone.TINA_01 ,target openVone.TINA_01;

启动：
GGSCI (GUOJUN) 26> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED DPB 00:00:00 00:11:50 —这个是5.3
EXTRACT RUNNING DPC 00:00:00 00:00:02 —这个是5.4
EXTRACT RUNNING EXTA 00:00:00 00:00:05

DPB失败因为警告swap不足，但还是可以正常启动
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 3.13G
CACHESIZEMAX (strict force to disk): 2.75G

2019-02-21 16:41:10 WARNING OGG-01842 CACHESIZE PER DYNAMIC DETERMINATION (2G)
LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 3.13G
Check swap space. Recommended swap/extract: 128G (64bit system).

GGSCI (GUOJUN) 48> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPB 00:00:00 00:00:07
EXTRACT RUNNING DPC 00:00:00 00:00:01
EXTRACT RUNNING EXTA 00:00:00 00:00:02

验证172.16.5.4 --成功同步
bobo test1
bobo test2
OPENVON jieguochenggong

验证172.16.5.3 --成功同步

其他命令：
修改replicat进程重新开始同步
alter replicat repac ,begin now

—如果要同步ddl，需要先执行三个脚本才行。

级联复制的思想，应该就是两个一对一，这里不测试。

ogg配置多对一复制-tina实验

适用于聚合数据，要排除掉truncate，数据的隔离依赖于业务分离。

首先我们要知道，所谓的多对一，其实跟一对一差不多。
源A 源B配置相同，只是文件名不同，都指向C的同一个表
目标C 配置2个replicate，应用到同一个表。

设计我这次的配置文件参数
环境：
A：172.16.3.34 dbsid：tinadb 自己的测试机
B：172.16.5.4 dbsid：XTPLAT daihai的虚拟机
C：172.16.5.3 dbsid：XTPLAT 本机虚拟机

1.软件安装和用户配置
alter system set recyclebin=off scope=spfile;
shutdown immediate;
startup mount
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data(primary key,unique,foreign key) columns;
alter system set recyclebin=off scope=spfile;
alter database open;

root:
mkdir -p /u01/oracle/ogg
chown -R oracle:oinstall /u01/oracle/ogg

cd /u01/oracle/ogg
chown oracle:oinstall fbo_ggs_Linux_x64_ora11g_64bit.tar
su oracle
cd /u01/oracle/ogg
tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar

连接数据库，创建用户
create tablespace ogg datafile ‘’ size 100m;
create user oggtest identified by oggtest default tablespace ogg temporary tablespace temp;
grant dba to oggtest;

2.创建目录
./ggsci
create subdirs

上面的操作三个节点都要做

4.复制配置
A：源端1 （3.34 tinadb 主目录也不同）
edit params ./GLOBALS
GGSCHEMA oggtest

start mgr

2）extract进程
add extract ext1 ,tranlog,begin now
add exttrail ./dirdat/ab,extract ext1,megabytes 100

edit params ext1 内容如下：
extract ext1
dynamicresolution
setenv (ORACLE_SID=tinadb)
setenv (ORACLE_HOME=/u01/oracle)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
–ddl include all
userid oggtest,password oggtest
tranlogoptions excludeuser oggtest
exttrail ./dirdat/ab
table openVone.TINA_01;

3）data pump进程
edit params dp1 内容如下：
extract dp1
dynamicresolution
setenv (ORACLE_SID=tinadb)
setenv (ORACLE_HOME=/u01/oracle)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
passthru
rmthost 172.16.5.3,mgrport 7809
rmttrail ./dirdat/ac
table openVone.TINA_01;

重点命令：
ADD EXTRACT DP1,EXTTRAILSOURCE ./dirdat/ab
add rmttrail ./dirdat/ac ,extract dp1

注意保持一致，exttrail就是ab，rmtrail就是ac

B：源端2
edit params ./GLOBALS
GGSCHEMA oggtest

start mgr

2）extract进程
add extract ext2,tranlog,begin now
add exttrail ./dirdat/bk,extract ext2,megabytes 100

edit params ext2 内容如下：
extract ext2
dynamicresolution
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid oggtest,password oggtest
tranlogoptions excludeuser oggtest
exttrail ./dirdat/bk
table openVone.TINA_01;

3）data pump进程
edit params dp2 内容如下：
extract dp2
dynamicresolution
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
passthru – --直通模式或普通模式直通模式用在两边表名、列名一致，可以直接映射的情况，不需要额外配置；普通模式可以配置
rmthost 172.16.5.3,mgrport 7809
rmttrail ./dirdat/bm
table openVone.TINA_01;

ADD EXTRACT DP2,EXTTRAILSOURCE ./dirdat/bk
add rmttrail ./dirdat/bm ,extract dp2

C：目标端
edit params GLOBALS
GGSCHEMA oggtest
checkpointtable oggtest.checkpoint

dblogin userid oggtest,password oggtest
GGSCI (test4) 5> add checkpointtable oggtest.checkpoint
Successfully created checkpoint table oggtest.checkpoint.

2）replicate进程1
add replicat rep1 exttrail ./dirdat/ac ,checkpointtable oggtest.checkpoint;
add replicat rep3 exttrail ./dirdat/ac,begin now,nodbcheckpoint; —下面这个生效

edit param rep1 内容如下：
replicat rep3
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
reperror default,discard --如果复制数据出错则忽略
–reperror (default,ignore)
–ddl include all
–ddlerror default ignore retryop maxretries 3 retrydelay 5
userid oggtest,password oggtest
handlecollisions --当目标端已有数据时，略过重复数据错误
assumetargetdefs ----两台数据库数据结构一致则使用此参数
discardfile ./dirrpt/rep3_ac.dsc,purge --错误信息写入XXXX文件，追加模式，最大100m
map openVone.TINA_01 ,target openVone.TINA_01;

3）replicate进程2
add replicat rep4 exttrail ./dirdat/bm ,begin now,nodbcheckpoint;

edit param rep4 内容如下：
replicat rep4
setenv (ORACLE_SID=XTPLAT)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
reperror default,discard
userid oggtest,password oggtest
handlecollisions
assumetargetdefs
discardfile ./dirrpt/rep4_bm.dsc,purge
map openVone.TINA_01 ,target openVone.TINA_01;

注意，这里只配置复制数据，不配置复制ddl以及truncate

上述步骤已经更正为正确步骤

启动A：3.34
start *
GGSCI (MYSQL_HA) 35> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 02:08:35
EXTRACT RUNNING EXT1 00:00:00 00:00:05

启动：5.4
GGSCI (test4) 40> start ext2
Sending START request to MANAGER …
ERROR: opening port for MGR MGR (No route to host).

原来是/etc/hosts的ip写错了
GGSCI (test4) 68> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP2 00:00:00 00:01:19
EXTRACT RUNNING EXT2 00:03:13 00:00:01

启动5.3
GGSCI (test4) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:07
REPLICAT RUNNING REP2 00:00:00 00:00:04

验证同步：
在A 3.34上插入一条新数据 TINA TEST02
在B 5.4上插入一条新数据 OPENVONE TEST01

查看C：没有同步
最可怕的是进程都是running的。。。

怀疑是ca这里出了问题
rmttrail ./dirdat/ac
table openVone.tina_01;

add rmttrail /u01/oracle/ogg/dirdat/ca ,extract dp1 --开始我以为传到c名字要改，原来不用
add rmttrail /u01/oracle/ogg/dirdat/bn,extract dp2

其实还是跟dp进程同名的文件
add rmttrail /u01/oracle/ogg/dirdat/ac ,extract dp1 --开始我以为传到c名字要改，原来不用
add rmttrail /u01/oracle/ogg/dirdat/bm,extract dp2

删掉replicat进程重建
GGSCI (test4) 63> delete replicat rep1
Deleted REPLICAT REP1.
GGSCI (test4) 65> delete replicat rep2
Deleted REPLICAT REP2.

数据还是不同步

查看细节：
info rep1 ,detail
删除之前错误的文件
delete rmttrail /u01/oracle/ogg/dirdat/ca ,extract dp1;
delete rmttrail /u01/oracle/ogg/dirdat/bn ,extract dp2;

删除抽取进程
GGSCI (test4) 11> delete extract ext2
2019-02-20 08:07:02 WARNING OGG-01753 Cannot unregister EXTRACT EXT2 from database because no database login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT command with LOGRETENTION. Issue DBLOGIN first.
Deleted EXTRACT EXT2.

GGSCI (test4) 12> delete extract dp2
2019-02-20 08:07:08 WARNING OGG-01753 Cannot unregister EXTRACT DP2 from database because no database login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT command with LOGRETENTION. Issue DBLOGIN first.
Deleted EXTRACT DP2.

*************************=
重新验证：insert
在A 3.34上插入一条新数据 TINA TEST009
在B 5.4上插入一条新数据 OPENVONE jjjj

查看C：正常同步

正常同步的信息：

** Run Time Messages **

Opened trail file /u01/oracle/ogg/dirdat/bm000000 at 2019-02-20 08:12:11

MAP resolved (entry openVone.TINA_01):
map “OPENVONE”.“TINA_01” ,target openVone.TINA_01;
Using following columns in default map by name:
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
Using the following key columns for target table OPENVONE.TINA_01: OWNER, OBJECT_NAME.

验证：update
A:3.34 更新一个字段内容为update_test 正常同步

验证：delete
A：删除 TINA TEST009这一条正常同步

此时C：剩下2条数据
SYS ICOL$
OPENVONE jjjj

验证：truncate
A：直接执行truncate：truncate table TINA_01
C：没有同步truncate，保留了两条数据，非常好！！

A又插入两条新数据SSS GUOJIA 正常同步
验证：update
B:5.4 更新一个字段内容为update_tina 正常同步

验证：delete
B: 删除一条独有数据 OPENVONE TEST5 OGG不受影响
B：再删除一条公有数据 OPENVONE jjjj 正常同步

此时C：剩下3条数据
SYS ICOL$
TINA SSS
TINA GUOJIA

验证：truncate
B：直接执行truncate：truncate table TINA_01
C：没有同步truncate，保留了3条数据

然后B又插入，C继续同步，因此truncate是被跳过的。达到预期的目的