windows环境一台服务器搭建下搭建Oracle GoldenGate单向复制配置
一.ogg Windows环境安装
https://blog.csdn.net/lebron3v/article/details/80226013
进入ogg安装目录
D:\app\Administrator\product\ogg>ggsci
创建需要的目录
D:\app\Administrator\product\ogg>create subdirs
二.oracle windows 环境下创建多个实例
https://blog.csdn.net/lxpaopao/article/details/113712576
三.日志设置(source 和 target)
GoldenGate通过抓取源端数据库重做日志进行分析,将获取的数据应用到目标端,实现数据同步。因此,源数据库需要必须处于归档模式,并启用附加日志和强制日志。
source源端数据库日志设置
C:\Users\lx>sqlplus /@orclbak as sysdba --存在多个实例,连接指定实例
SQL>select log_mode,supplemental_log_data_min,force_logging from v$database;
--如果都是NO 则需要开启
--归档日志
SQL>archive log list; --查看归档日志是否开启
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
--强制日志
SQL>alter database force logging;
--附加日志
SQL>alter database add supplemental log data;
target数据库日志设置参考source
四.源库和目标库创建 ogg 用户
create user ggs identified by ggs default tablespace users temporary tablespace temp;
grant dba to ggs; --建立goldengate管理用户
五、配置相关进程
1.在Source 和Target上配置Manager
GGSCI (DESKTOP-CRD8FOL) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (DESKTOP-CRD8FOL) 2> edit params mgr
PORT 7809
GGSCI (DESKTOP-CRD8FOL) 3> start manager
Manager started.
2.配置SourceDB 的复制队列
先连接到数据库,测试连接:
GGSCI (DESKTOP-CRD8FOL) 10> dblogin userid ggs@orcl, password ggs
Successfully logged into database.
--增加一个抽取:
GGSCI (DESKTOP-CRD8FOL as ggs@orcl) 11> add extract ext1,tranlog, begin now
EXTRACT added.
--exttrail建立的trail文件是本地的,是给extract进程使用,将捕获的日志数据写入到trail文件中。
GGSCI (DESKTOP-CRD8FOL as ggs@orcl) 12> add exttrail D:\app\Administrator\product\ogg\dirdat\lt, extract ext1
EXTTRAIL added
--编辑抽取进程ext1参数:
GGSCI (DESKTOP-CRD8FOL as ggs@orcl) 13> edit params ext1
extract ext1
userid ggs@orcl,password ggs
rmthost 127.0.0.1,mgrport 7809
rmttrail D:\app\Administrator\product\ogg\dirdat\lt
dynamicresolution
table ggs.*;
GGSCI (DESKTOP-CRD8FOL as ggs@orcl) 14> start ext1
GGSCI (DESKTOP-CRD8FOL as ggs@orcl) 14> info all
--正常情况如下
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
3.配置TargetDB 同步队列
3.1在Target 端添加checkpoint表:
GGSCI (DESKTOP-CRD8FOL) 6> edit params ./GLOBAL
GGSCHEMA ggs
CHECKPOINTTABLE ggs.checkpoint
--添加如上2条记录。
GGSCI (DESKTOP-CRD8FOL) 12> dblogin userid ggs@orclbak, password ggs
Successfully logged into database.
--说明,这个用户是在Source 库启用DDL 创建的,我在Target 库也创建了这个用户。
GGSCI (DESKTOP-CRD8FOL as ggs@orclbak) 13> add checkpointtable ggs.checkpoint
Successfully created checkpoint tableGGATE.CHECKPOINT.
3.2 创建同步队列
GGSCI (DESKTOP-CRD8FOL as ggs@orclbak) 14> add replicat rep1,exttrail D:\app\Administrator\product\ogg\dirdat\lt, checkpointtable ggs.checkpoint
REPLICAT added.
GGSCI (DESKTOP-CRD8FOL as ggs@orclbak) 15> edit params rep1
replicat rep1
userid ggs@orclbak,password ggs
assumetargetdefs
discardfile D:\app\Administrator\product\ogg\dirdat\rep1_discard.txt,append
MAP ggs.*,TARGET ggs.*;
3.3开启同步队列
GGSCI (DESKTOP-CRD8FOL) 14> start ext1
GGSCI (DESKTOP-CRD8FOL) 14> start rep1
GGSCI (DESKTOP-CRD8FOL) 14> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:07
REPLICAT RUNNING REP1 00:00:00 00:00:06
所有的进程状态都是RUNNING,正常。
六.测试
在Source 和Target数据库的ggs用户下,创建一个EMP表
CREATE TABLE "GGS"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
)
--在Source数据库下执行
insert into GGS.EMP select * from scott.EMP where empno='7900';
--在Target数据库下查看GGS.EMP表多出了一条记录
select * from GGS.EMP;
抽取信息
接受信息