自律自由

yaogang

0%

oracle 11g 同库搭建ogg测试(GoldenGate)

发表于 更新于 分类于

oracle 11g 同库搭建ogg测试(GoldenGate)

关于dg和ogg的优缺点就不说了，自己了解。第一次照着老师的做还是有一定的难度，参数比较多不理解，期间还涉及scn，搞很久搞不定，后面照着网上简单博客来，循序渐进练习，本文为很简单的入门数据同步，主要做记录，其中配置若有误请见谅！！！

此次为了方便就同机搭建，所以mgr端口不同，如果不同机搭建，mgr端口可以一致

搭建环境

name version
操作系统 CentOS Linux release 7.6.1810 (Core)
数据库版本 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
ogg版本 Oracle GoldenGate Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bi

1. 新建ogg用户

1
2
3
4
5
6
7
8
9
10
11
12
groupadd ogg
useradd -g ogg -G oinstall,dba ogg
echo "oracle"| passwd --stdin oracle

--上传oracle goldengate安装文件解压
rz
mkdir sogg togg
mkdir sogg/temp
unzip V34339-01.zip
tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar -C sogg/
tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar -C togg/
--rm -f V34339-01.zip OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf  Oracle*

2. 配置ogg环境变量

1
2
3
4
5
6
7
8
9
# 注意，此次同机就一个用户，如果是双机的话2个系统都要创建用户和配置环境变量
export TMP=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=SRM
export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/sbin:/u01/app/oracle/product/11.2.0/dbhome_1/OPatch:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib
export CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/JRE:/u01/app/oracle/product/11.2.0/dbhome_1/jlib:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA.UTF8

3. source和target都准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
--11.2.0.4 以上需要开启  11g一下需要执行: alter system set recyclebin=off scope=both;
set lin 200 pages 30000
alter system set enable_goldengate_replication = true;

create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/SRM/ogg_tbs01.dbf' size 100M autoextend on;

create user ogg identified by oracle default tablespace ogg_tbs;
grant dba to ogg;

--创建测试数据用户
set lin 200 pages 30000
create tablespace test_tbs datafile '/u01/app/oracle/oradata/SRM/test_tbs01.dbf' size 10M autoextend on;
create user yg1 identified by oracle default tablespace test_tbs;
create user yg2 identified by oracle default tablespace test_tbs;
grant dba to yg1,yg2;

--开启archivelog、附加log、强制log
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
--注意一定要在/home/ogg/sogg目录下执行，不然会hang住
@/home/ogg/sogg/marker_setup.sql
--ogg
@/home/ogg/sogg/ddl_setup.sql
--ogg
@/home/ogg/sogg/role_setup.sql
--ogg

--GRANT GGS_GGSUSER_ROLE TO ogg;

--如果出现下面这个报错执行： drop TRIGGER SYS.GGS_DDL_TRIGGER_BEFORE ;
--有时候删除也不行，需要先ddl_disable再授权，具体报错谷歌一下就行

--ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

@/home/ogg/sogg/ddl_enable.sql

4. 创建 GoldenGate 的工作目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 注意 ogg 的参数配置文件中不能加 # 作为注释行
cd /home/ogg/sogg/
./ggsci
create subdirs

cd /home/ogg/togg/
./ggsci
create subdirs

# 源端添加checkpointtable
cd /home/ogg/sogg/
./ggsci

EDIT PARAMS ./GLOBALS
-----------------------------------------
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ggschkpt1
-----------------------------------------
exit

./ggsci
DBLOGIN USERID ogg, PASSWORD oracle
ADD CHECKPOINTTABLE

# 目标源端添加checkpointtable
cd /home/ogg/togg/
./ggsci
EDIT PARAMS ./GLOBALS
-----------------------------------------
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ggschkpt2
-----------------------------------------
exit
./ggsci
DBLOGIN USERID ogg, PASSWORD oracle
ADD CHECKPOINTTABLE

5. 源配置mgr

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EDIT PARAMS MGR
-----------------------------------------
PORT 7809 
DYNAMICPORTLIST 7800-8000
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,WAITMINUTES 2,RETRIES 5, RESETMINUTES 5
PURGEOLDEXTRACTS /home/oracle/sogg/dirdat,USECHECKPOINTS,MINKEEPDAYS 1
PURGEDDLHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7,FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7                                        

LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
-----------------------------------------

6. 目标配置mgr

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
EDIT PARAMS MGR
-----------------------------------------
PORT 8809 
DYNAMICPORTLIST 8800-9000
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, WAITMINUTES 2, RESETMINUTES 5, RETRIES 5
PURGEOLDEXTRACTS /home/ogg/togg/dirdat, USECHECKPOINTS, MINKEEPDAYS 3
PURGEDDLHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7,FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7

LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
-----------------------------------------
start mgr
info mgr

7. 源端配置 Extract 进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
EDIT PARAMS EORA_1
-----------------------------------------
EXTRACT EORA_1
SETENV (ORACLE_SID="SRM")
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
USERID ogg, PASSWORD oracle

GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES,RATE
NUMFILES 5000
DISCARDROLLOVER AT 3:00
WARNLONGTRANS 2h,CHECKINTERVAL 3m
DYNAMICRESOLUTION
DBOPTIONS ALLOWUNUSEDCOLUMN
IGNOREAPPLOPS
IGNOREREPLICATES
FETCHOPTIONS NOUSESNAPSHOT
FETCHOPTIONS FETCHPKUPDATECOLS
DISCARDFILE /home/ogg/sogg/dirrpt/eora_a.dsc,APPEND,MEGABYTES 1000
EXTTRAIL /home/ogg/sogg/dirdat/aa,MEGABYTES 100

TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS EXCLUDEUSER ogg
CACHEMGR CACHEDIRECTORY /home/ogg/sogg/temp

DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT 
TABLE yg1.*;
-----------------------------------------

ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW, threads 1

info extract *

ADD EXTTRAIL /home/ogg/sogg/dirdat/aa, EXTRACT EORA_1, MEGABYTES 100

# 启动 extract 进程
start extract eora_1

# 查看 extract 进程
info eora_1

8. 配置 Pump 进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
EDIT PARAMS PORA_1
-----------------------------------------
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
PASSTHRU
NUMFILES 5000
RMTHOST yg-ocm, MGRPORT 8809,COMPRESS
RMTTRAIL /home/ogg/togg/dirdat/pa
DYNAMICRESOLUTION

TABLE yg1.*;
-----------------------------------------
# 为pump进程指定 本地trail 和 远程trail文件
ADD EXTRACT PORA_1, EXTTRAILSOURCE /home/ogg/sogg/dirdat/aa
ADD RMTTRAIL /home/ogg/togg/dirdat/pa, EXTRACT PORA_1, MEGABYTES 100

START EXTRACT PORA_1
info all

9. 配置目标端的 Replicat 进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
EDIT PARAMS RORA_1
-----------------------------------------
REPLICAT RORA_1 
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
USERID ogg, PASSWORD oracle 
SQLEXEC "alter session set constraints=deferred"
DBOPTIONS DEFERREFCONST
GETTRUNCATES
REPORT AT 06:00
REPORTCOUNT EVERY 1 MINUTES,RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT,ABEND
NUMFILES 5000
DISCARDFILE ./dirrpt/rep_a.dsc,APPEND,MEGABYTES 1024m
DISCARDROLLOVER AT 02:00
GROUPTRANSOPS 1000
HANDLECOLLISIONS
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DDLERROR DEFAULT IGNORE
DDL INCLUDE MAPPED
DDLOPTIONS REPORT

MAP yg1.*, TARGET yg2.*;
-----------------------------------------

add replicat RORA_1, exttrail /home/ogg/togg/dirdat/pa 

start REPLICAT RORA_1

info all

9. 测试数据同步

1
2
3
4
5
sqlplus yg1/oracle
create table t as select * from dual;

sqlplus yg2/oracle
select * from t;

至此，最简单的ogg就搭建完成了，其中没有复杂优化的参数，没有导入导出数据，仅仅只是做到了2个用户的数据同步，哈哈!

参考博文: https://www.cnblogs.com/lottu/p/9542681.html