一、环境准备
1、环境信息
192.168.184.171 oracle11g 源端
192.168.184.172 oracle11g-bak 目标端
版本 | ip | hostname | |
---|---|---|---|
源端 | Oracle GoldenGate 11.2.1.0.3 for Oracle on Linux x86-64 | 192.168.184.171 | oracle11g |
目标端 | Oracle GoldenGate 11.2.1.0.3 for Oracle on Linux x86-64 | 192.168.184.172 | oracle11g-bak |
2、安装oracle11g数据库
源端和目标端都执行安装
可参考:
使用脚本安装Oracle11g、Oracle12c、Oracle19c
3、开启归档模式
源端和目标端都执行,可参考:
startup;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
4、开启强制日志和辅助日志
源端和目标端都执行
(1)查询是否开启
select force_logging,supplemental_log_data_min from v$database;
显示NO的时候表示没有开启
(2)开启强制日志
alter database force logging;
(3)开启辅助日志
alter database add supplemental log data;
(4)开启主键附加日志
alter database add supplemental log data (primary key) columns;
(5)开启全列附加日志
alter database add supplemental log data (all) columns;
(6)再次检查
当显示为YES的时候表示开启成功。
select force_logging,supplemental_log_data_min from v$database;
5、下载ogg软件包
因为我们是同步oracle数据到oracle数据
(1)官网下载
https://www.oracle.com/cn/middleware/technologies/goldengate-downloads.html
(2)网盘下载
链接: https://pan.baidu.com/s/1ABiUlleqbMzyV0lFVlYHWg?pwd=mz8v
提取码: mz8v
二、安装goldengate软件
1、安装ogg软件
(1)创建目录
su - root
mkdir -p /goldengate
chown -R oracle:oinstall /goldengate
(2)上传软件
上传V34339-01.zip到/root/目录下
(3)解压V34339-01.zip
unzip V34339-01.zip
(4)解压fbo_ggs_Linux_x64_ora11g_64bit.tar
mv fbo_ggs_Linux_x64_ora11g_64bit.tar /goldengate/
cd /goldengate/
tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
cd /goldengate/
chown -R oracle:oinstall /goldengate
ll
2、设置环境变量
su - oracle
vim .bash_profile
添加如下环境变量,上面那一块是oracle的环境变量
export OGG_SRC_HOME=/goldengate
source .bash_profile
3、创建数据库goldengate用户
(1)创建ggs用户
su - oracle
sqlplus / as sysdba
create user ggs identified by ggs;
(2)源端数据库ggs用户权限
grant CONNECT, RESOURCE to ggs ;
grant CREATE SESSION, ALTER SESSION to ggs ;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ggs ;
grant ALTER ANY TABLE to ggs ;
grant FLASHBACK ANY TABLE to ggs ;
grant EXECUTE ON DBMS_FLASHBACK to ggs ;
grant SELECT ON DBA_CLUSTERS to ggs ;
grant SELECT ANY TRANSACTION to ggs ;
(3)目标端数据库ggs权限
grant CONNECT, RESOURCE to ggs ;
grant CREATE SESSION, ALTER SESSION to ggs ;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ggs ;
grant CREATE TABLE to ggs ;
grant INSERT ANY TABLE to ggs ;
grant UPDATE ANY TABLE to ggs ;
grant DELETE ANY TABLE to ggs ;
4、创建测试数据
(1)源端
使用sys用户登录,创建表空间和临时表空间
create tablespace test_1 datafile '/home/oracle/oradata/orcl/test_1.dbf' size 100m autoextend on next 100m maxsize 400m extent management local;
CREATE TEMPORARY TABLESPACE test_temp_1
TEMPFILE '/home/oracle/oradata/orcl/test_temp_1.dbf'
SIZE 20M
AUTOEXTEND ON
NEXT 5M MAXSIZE 100M;
修改ggs的表空间和默认表空间
ALTER USER ggs DEFAULT TABLESPACE test_1 TEMPORARY TABLESPACE test_temp_1;
使用ggs登录oracle数据库客户端工具插入数据
CREATE TABLE student_info (
student_id NUMBER(10) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
gender CHAR(1) CHECK (gender IN ('M', 'F')),
birth_date DATE,
email VARCHAR2(100) UNIQUE,
create_time TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- 单条记录插入
INSERT INTO student_info VALUES
(1, '张三', 'M', TO_DATE('2000-01-15', 'YYYY-MM-DD'), 'zhangsan@edu.cn', DEFAULT);
-- 多条记录插入
INSERT ALL
INTO student_info VALUES (2, '李四', 'M', DATE '2001-03-22', 'lisi@example.com', SYSTIMESTAMP)
INTO student_info VALUES (3, '王芳', 'F', TO_DATE('1999-12-05', 'YYYY-MM-DD'), 'wangfang@mail.com', DEFAULT)
SELECT * FROM DUAL;
commit;
SELECT * FROM student_info;
(2)目标端
使用sys用户登录,创建表空间和临时表空间
create tablespace test_1 datafile '/home/oracle/oradata/orcl/test_1.dbf' size 100m autoextend on next 100m maxsize 400m extent management local;
CREATE TEMPORARY TABLESPACE test_temp_1
TEMPFILE '/home/oracle/oradata/orcl/test_temp_1.dbf'
SIZE 20M
AUTOEXTEND ON
NEXT 5M MAXSIZE 100M;
修改ggs的表空间和默认表空间
ALTER USER ggs DEFAULT TABLESPACE test_1 TEMPORARY TABLESPACE test_temp_1;
使用ggs登录oracle数据库客户端工具,创建表结构
CREATE TABLE student_info (
student_id NUMBER(10) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
gender CHAR(1) CHECK (gender IN ('M', 'F')),
birth_date DATE,
email VARCHAR2(100) UNIQUE,
create_time TIMESTAMP DEFAULT SYSTIMESTAMP
);
三、OGG链路配置
1、源端配置
(1)创建目录
su - oracle
cd /goldengate/
./ggsci
create subdirs
(2)配置并启动mgr
edit params mgr
添加如下内容
port 7809
dynamicportlist 7810-8000
autorestart er *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 10
LAGCRITICALMINUTES 15
start manager
查看进程
info all
(3)配置抽取进程
edit params ehx2al
添加如下内容:
extract ehx2al
SETENV (ORACLE_SID=orcl) #此处为源端数据库实例名
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ggs ,password ggs
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
EXTTRAIL ./dirdat/ed
NUMFILES 3000
EOFDELAYCSECS 30
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
--BR BRINTERVAL 2H , BRDIR BR
GETUPDATEBEFORES
GETTRUNCATES
--NOCOMPRESSDELETES / COMPRESSDELETES
--WARNLONGTRANS 3H, CHECKINTERVAL 3M
table ggs.student_info; #源端需要同步的表,这里是ggs.student_info
源端添加配置进程
add extract ehx2al,tranlog,threads 1,begin now
add exttrail ./dirdat/ed,extract ehx2al,megabytes 200
(4)配置投递进程
edit params phx2al
添加如下内容:
extract phx2al
SETENV (ORACLE_SID=orcl) #此处为源端数据库实例名
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD ggs
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
FLUSHCSECS 30
NUMFILES 3000
EOFDELAYCSECS 30
RMTHOST 192.168.184.172,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000 #192.168.184.172是指的目标服务器的IP地址
RMTTRAIL ./dirdat/pd
GETTRUNCATES
PASSTHRU (pass through)
DYNAMICRESOLUTION
GETUPDATEBEFORES
table ggs.student_info;
添加配置进程
Add extract phx2al exttrailsource ./dirdat/ed
Add rmttrail ./dirdat/pd extract phx2al megabytes 200
2、目标端配置
(1)创建目录
su - oracle
cd /goldengate/
./ggsci
create subdirs
(2)配置并启动mgr
edit params mgr
添加如下内容
port 7809
dynamicportlist 7810-8000
autorestart er *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 10
LAGCRITICALMINUTES 15
start manager
查看进程
info all
(3)配置复制进程
目标端复制(应用)进程配置。
edit params rhx2al
添加如下内容:
replicat rhx2al
SETENV (ORACLE_SID=orcl)
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD ggs
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND /DISCARD
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
GETTRUNCATES
NUMFILES 3000
EOFDELAYCSECS 30
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
GETUPDATEBEFORES
GROUPTRANSOPS 1
MAXTRANSOPS 1
HANDLECOLLISIONS
MAP ggs.student_info, TARGET ggs.student_info;
添加复制进程
ADD REPLICAT rhx2al,EXTTRAIL ./dirdat/pd,checkpointtable ggs.checkpoint
(4)建立检查表
连接目标数据库 用户为ggs 密码为ggs
dblogin userid ggs password ggs
添加检查表
add checkpointtable ggs.checkpoint
3、启动进程
(1)启动源端mgr进程
之前已经启动
info all
(2)启动目标端mgr进程
之前已经启动
info all
(3)启动源端抽取进程ehx2al
start ehx2al
info all
(4)启动源端投递进程phx2al
start phx2al
info all
(5)启动目标端复制进程rhx2al
start rhx2al
info all
4、查看日志
如果遇到启动不了进程,可以在这里进行查看日志。
(1)源端
su - oracle
cd /goldengate
tail -100f ggserr.log
(2)目标端
su - oracle
cd /goldengate
tail -100f ggserr.log
四、查看数据是否同步
1、查看数据是否同步
之前在创建测试数据的时候,在目标端只是创建了表结构,现在使用ggs用户进行登录,查看student_info表是否有数据
select * from student_info;
2、源端创建插入增量数据
(1)在源端插入数据
INSERT INTO student_info VALUES
(4,'新增测试', 'M', TO_DATE('2000-01-15', 'YYYY-MM-DD'), 'xinzeng@edu.cn', DEFAULT);
(2)检查目标端是否同步
select * from student_info;