自律自由

yaogang

0%

oracle 11g 同库搭建 ogg-进阶2

发表于 更新于 分类于

oracle 11g 同库搭建 ogg-进阶2

本文为进阶2，比上一篇进阶1多了源库实施生成数据，主要做记录，其中配置若有误请见谅！！！

此次为了方便就同机搭建，所以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
2
3
4
5
6
7
8
--基本步骤
1.搭建goldengate，开启mgr、extract、pump，不开启replicate
2.查询当前scn（当前事务的最小start_time>extract时间）
3.按scn导出源库schema数据（exp/expdp）
4.目标库导入数据
5.检查目标trail文件中scn对应的序号和rba
6.修改并开启rba进程
7.检查、测试同步

1. 新建ogg用户

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

# 上传oracle goldengate安装文件解压
su - ogg
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
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
export LC_ALL=en_US.UTF-8

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
--11.2.0.4 以上需要开启  11g一下需要执行: alter system set recyclebin=off scope=both;
sqlplus / as sysdba
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;  --这里为了简单直接给dba，不然给下面权限

--grant resource, connect to ogg;
--grant execute on utl_file to ogg;
--grant select any dictionary to ogg;
--grant select any table to ogg;
--grant alter any table to ogg;
--grant flashback any table to ogg;
--grant execute on dbms_flashback to ogg;
--grant ggs_ggsuser_role to ogg;

--创建测试数据用户
drop user yg1 cascade;
drop user yg2 cascade;
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;
conn yg1/oracle
create table t1 as select * from dba_tables;
create table t2 as select * from dba_tables;
create table t3 (id int,t_date date);

--实时动态插入数据，模拟生产环境
declare
  job_num number;
begin
  dbms_job.submit(
    job => job_num,
    what => 'insert into t3 values(dbms_random.value*10, sysdate);', 
    next_date => sysdate, 
    interval =>'sysdate+1/24/60/6'
  ); 
  commit;
end;
/

--查询测试数据
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select job from user_jobs;
select * from t3;

--开启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;
alter system set UNDO_RETENTION = 10800;
alter system set OPEN_CURSORS = 10000;
--注意一定要在/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;

--如果出现下面这个报错执行： 先执行 @ddl_disable , 再执行 grant create table,create sequence to ogg; 最后再执行 role_setup.sql 和ddl_enable.sql ,后面如果这个ddl触发器还是报错，那还得重新marker授权

--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
cd /home/ogg/sogg/
./ggsci
create subdirs

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

5. 源端添加checkpointtable

1
2
3
4
5
6
7
8
9
10
11
cd /home/ogg/sogg/
./ggsci
EDIT PARAMS ./GLOBALS
-----------------------------------------
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ggschkpt1
-----------------------------------------
exit
./ggsci
DBLOGIN USERID ogg, PASSWORD oracle
ADD CHECKPOINTTABLE

6. 目标添加checkpointtable

1
2
3
4
5
6
7
8
9
10
11
cd /home/ogg/togg/
./ggsci
EDIT PARAMS ./GLOBALS
-----------------------------------------
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ggschkpt2
-----------------------------------------
exit
./ggsci
DBLOGIN USERID ogg, PASSWORD oracle
ADD CHECKPOINTTABLE

7. 源配置mgr

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EDIT PARAMS MGR
-----------------------------------------
PORT 7809 
DYNAMICPORTLIST 7800-8000
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,WAITMINUTES 2,RETRIES 5, RESETMINUTES 5

USERID ogg, PASSWORD oracle

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
-----------------------------------------

8. 目标配置mgr

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EDIT PARAMS MGR
-----------------------------------------
PORT 8809 
DYNAMICPORTLIST 8800-9000
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, WAITMINUTES 2, RESETMINUTES 5, RETRIES 5

USERID ogg, PASSWORD oracle

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

9. 源端配置 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
42
43
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    # 此处threads推荐等于redolog的threads

info extract *

# 为捕获进程添加本地trail文件
ADD EXTTRAIL /home/ogg/sogg/dirdat/aa, EXTRACT EORA_1, MEGABYTES 100

# 启动 extract 进程
start extract eora_1

# 查看 extract 进程
info eora_1

10. 配置 Pump 进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
EDIT PARAMS PORA_1        
# 需要指定目标端的mgr端口和目标端trail文件
-----------------------------------------
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

11. 配置目标端的 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
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.*;
-----------------------------------------

# 指定目标端需要apply的trail文件
add replicat RORA_1, exttrail /home/ogg/togg/dirdat/pa

12. 源库scn数据手动导入到目标库

1
2
3
4
5
6
7
8
9
10
11
12
--expdp help=y
--impdp help=y
sqlplus system/oracle
create or replace directory expdp_dir as '/home/oracle';
select current_scn from v$database;
--1732604
exit
expdp system/oracle schemas=yg1 directory=expdp_dir dumpfile=yg-test.dmp nologfile=y FLASHBACK_SCN=1732604 REUSE_DUMPFILES=y exclude=job
sqlplus sytem/oracle
create or replace directory impdp_dir as '/home/oracle';
exit
impdp system/oracle schemas=yg1 directory=impdp_dir dumpfile=yg-test.dmp nologfile=y remap_schema=yg1:yg2

13. 启动REPLICAT RORA_1 进程

1
2
3
start replicat RORA_1,aftercsn 1732604

info all

14. 测试数据同步

1
2
3
4
5
6
sqlplus yg1/oracle
select * from t3;

sqlplus yg2/oracle
select * from tab;
select * from t3;

至此，ogg-进阶2就搭建完成了，是不是复杂了很多感觉，别灰心，从前面简单的开始，慢慢来,哈哈img

1
2
其中个别参数我也还没理解，我们老师上课mgr是没有加入userid参数的，但是我的purge历史数据报错，网上说是需要指定登录数据库清理历史数据，果然加上就没报错，我也不确定是不是一定需要。
总之出问题多看 ./ggserr.log 就行了，从简单的开始，一步一步来就行了。