OGG19.1 oracle12c到oracle12c集成模式配置实施

---

---

---- OGG19.1 配置集成捕获Extract进程、pump和replicat进程
---- Configuring the Primary Extract in Integrated Capture Mode

---

-- 20200716
-- Configuring the Primary Extract in Integrated Capture Mode
-- Basic parameters for Extract mining a non-mulitenant database

--- 本文源数据库和downstream数据库为同一个；源数据库和目标数据库的配置权限参看《OGG19.1 oracle12c到oracle12c经典模式配置实施 》
--- 源数据库 需 设置streams_pool_size: alter system set streams_pool_size = 1024M scope=spfile; --实时生效
----0.将primary extract in Integrated Capture Mode 注册进logminer server DB
------前提 在源库配置完成 用户 OGG 及其权限等
GGSCI > dblogin userid ogg@oggsdb password Password123
GGSCI (dbserver as ogg@oggsdb) 3> register extract exint database ---- 关键点1 ：否则报错：Logmining server does not exist on this Oracle database

---1. source db: Primary Extract in Integrated Capture Mode
添加一个extract in Integrated Capture Mode进程 ：
GGSCI > add extract exint,integrated tranlog,begin now ----, threads 2 ---- 关键点2 integrated tranlog：否则为 classic mode extract

GGSCI > edit params exint

添加以下内容

extract exint
setenv (ORACLE_SID = "OGGSDB")
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ogg@oggsdb, password Password123
--TRANLOGOPTIONS MININGUSERALIAS alias --downstream mining database streams_pool_size
TRANLOGOPTIONS INTEGRATEDPARAMS(parallelism 2)
TRANLOGOPTIONS GETCTASDML
DDL INCLUDE MAPPED
exttrail /oradata/ogg/dirdat/oggsdb/ei
discardfile /oradata/ogg/dirdat/exint.dsc, append, megabytes 100
getupdatebefores
nocompressdeletes
nocompressupdates
--LOGALLSUPCOLS
dboptions allowunusedcolumn
fetchoptions nousesnapshot
fetchoptions fetchpkupdatecols

table SCOTT.EMP;
table SCOTT.DEPT;

创建本地trail文件 ：

GGSCI > add exttrail /oradata/ogg/dirdat/oggsdb/ei, extract exint
EXTTRAIL added.

--ADD EXTTRAIL/RMTTRAIL /oradata/ogg/dirdat/oggsdb/ei, EXTRACT EXINT

--2. source db: Pump Extract in Integrated Capture Mode

添加一个pump进程（本质上也是extract进程），并指定远程trail文件：

GGSCI > add extract exppint,exttrailsource /oradata/ogg/dirdat/oggsdb/ei
EXTRACT added.

编辑pump参数文件：
GGSCI (dbserver) 16> edit params exppint

添加以下内容

EXTRACT exppint
passthru
rmthost 192.168.6.20, mgrport 7809 ---- target db主机ip，管理进程端口号 ##共用同一台主机的mgr进程 测试
rmttrail /oradata/ogg/dirdat/oggtdb/pi
table SCOTT.EMP;
table SCOTT.DEPT;

指定远程trail文件：
GGSCI > add rmttrail /oradata/ogg/dirdat/oggtdb/pi, extract exppint
RMTTRAIL added.

---

--3. target db：replicat 进程
--3.1 配置 target db ： ogg用户及权限
--3.2 配置 target db 端 MGR进程(共用)
--3.3 添加checkpoint表
GGSCI> edit params ./GLOBALS

添加以下内容

ggschema ogg
checkpointtable ogg.checkpoint

登陆target db添加checkpoint表

export ORACLE_SID=OGGTDB
./ggsci

GGSCI > dblogin userid ogg@OGGTDB, password Password123
Successfully logged into database.

GGSCI (dbserver as ogg@OGGTDB) > add checkpointtable ogg.checkpoint

--3.4 增加配置 replicat 进程
GGSCI (dbserver as ogg@oggtdb) 4> add replicat repint, INTEGRATED, exttrail /oradata/ogg/dirdat/oggtdb/pi ----关键点：INTEGRATED 关键字
----ADD REPLICAT repint, INTEGRATED, EXTTRAIL /oradata/ogg/dirdat/oggtdb/pi
----alter REPLICAT repint, INTEGRATED
----ALTER REPLICAT group, EXTSEQNO seqno, EXTRBA RBA

---- 多个replicat进程
----这两个新的参数文件与原有的参数文件的主要区别是将其中的复制表按负载均分为了两部分，分别放在单独的参数文件中。
----GGSCI> dblogin userid 用户名, password 密码
----GGSCI> ADD REPLICAT rgs_cx2,EXTTRAIL ./dirdat/cg checkpointtable gsgg_checkpoint
----GGSCI> ADD REPLICAT rgs_cx3,EXTTRAIL ./dirdat/cg checkpointtable gsgg_checkpoint
----GGSCI> info rgs_cx1,showch
----GGSCI> alter rgs_cx2, extseqno , extrba
----GGSCI> alter rgs_cx3, extseqno , extrba

编辑replicat参数文件：
GGSCI (dbserver as ogg@oggtdb) 6> edit params repint

添加以下内容

replicat repint
setenv (ORACLE_SID = "OGGTDB")
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ogg@oggtdb, password Password123

dboptions deferrefconst
gettruncates
report at 06:00
reportcount every 30 minutes, rate
reportrollover at 02:00
--reperror default, abend
REPERROR DEFAULT,DISCARD
DBOPTIONS NOSUPPRESSTRIGGERS
--handlecollisions
allownoopupdates
--assumetargetdefs
discardfile /oradata/ogg/dirdat/oggtdb/repint.dsc, append, megabytes 100
discardrollover at 02:00
--map scott.emp, target scott.emp;
map scott.dept, target scott.dept;

---

--3.5 启动 MGR 、Extract、Pump 和 REPLICAT 进程。
GGSCI > info all
GGSCI > start exint
GGSCI > start exppint
GGSCI > start repint

--3.6 测试和排错troubleshooting。

---

--Monitoring and Controlling Processing After the Instantiation
STATS REPLICAT repint

select * from V$GG_APPLY_RECEIVER;
select * from V$GOLDENGATE_TABLE_STATS

4. 源库和目标库的数据同步测试验证

最后，可如下测试验证：

源库执行，再源库和备份同时验证变更同步。

select * from scott.dept;

-- inert data
insert into scott.dept(deptno,dname,LOC) values(50,'HEADQUARTERS','WUHAN');
commit;

-- add table column and modify data
alter table scott.dept add(comment1 varchar2(30));
alter table scott.dept add(comment2 varchar2(30));
alter table scott.dept drop(comment2);

alter table scott.dept add(comment2 varchar2(30));
update scott.dept set comment2='YYY' where deptno=10;
commit;
alter table scott.dept drop(comment2);

update scott.dept set comment1='AAAAA' where deptno=50;
commit;
update scott.dept set comment1='AAA' where deptno=50;
commit;

-- update key value
update scott.dept set deptno=60 where deptno=50;
commit;

insert into scott.dept(deptno,dname,LOC) values(70,'HR','WUHAN-GUANGGU');
commit;

--- delete data
delete from scott.dept where deptno=70;
commit;