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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
|
-- source 源端+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 查询实例日志状态
select log_mode,supplemental_log_data_min,force_logging from v$database;
-- 查询表空间级别日志模式
select tablespace_name,logging,force_logging from dba_tablespaces;
-- 查看对象级别日志模式
select table_name,logging from user_tables;
-- 启动归档模式(最好源库,目标库都打开)
archive log list;
select log_mode from v$database;
-- 先指定归档日志路径
alter system set log_archive_dest = '/dbdir/xxxdb/arch' scope=spfile sid='*';
archive log list;
-- 再启动归档
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
-- 打开最小附加日志:
alter database add supplemental log data;
-- 打开数据库实例强制日志模式:
alter database force logging;
--(源端即可)
show parameter enable_goldengate_replication;
alter system set enable_goldengate_replication=true scope=both;
-- 使日志更改生效
alter system switch logfile;
-- 回收站功能会影响同步,需要关闭回收站(oracle11g以后的版本可以不关闭)
alter session set recyclebin=off;
alter system set recyclebin=off deferred;
alter system set recyclebin=off scope=spfile;
show parameter recyclebin;
-- 1. 创建ogg用户表空间&分配权限,配置 +++++++++++
create tablespace ogg_tbs datafile '/dbdir/xxxdb/ogg/ogg_tbs_01.dbf' size 10m autoextend
on next 10m maxsize unlimited;
-- 创建ogg用户
create user ogg_admin identified by ogg123 default tablespace ogg_tbs temporary tablespace
temp quota unlimited on ogg_tbs;
-- 加权限
grant all privileges to ogg_admin;
grant create session to ogg_admin;
grant alter session,alter system to ogg_admin;
grant connect,resource,dba to ogg_admin;
grant select any dictionary,select any table,select any transaction to ogg_admin;
grant flashback any table to ogg_admin;
grant insert any table,update any table,drop any table,create table to ogg_admin;
grant select on dba_clusters to ogg_admin;
grant select on v_$database to ogg_admin;
grant execute on utl_file to ogg_admin;
grant unlimited tablespace to ogg_admin;
grant create table,create sequence to ogg_admin;
grant alter any table to ogg_admin;
grant execute on dbms_flashback to ogg_admin;
grant exempt access policy to ogg_admin;
grant dba to ogg_admin;
exec dbms_goldengate_auth.grant_admin_privilege('ogg_admin');
----非必须,但有利于数据捕获应用
alter system set undo_retention=86400;
-- 接下来要执行一些脚本设置
-- 进入OGG的安装文件夹,然后调用脚本:
sqlplus / as sysdba;
1. @marker_setup.sql;
2. @ddl_setup.sql;
3. @role_setup.sql;
4. grant ggs_ggsuser_role to ogg_admin;
5. @ddl_enable.sql;
6. @marker_status.sql;
7. @?/rdbms/admin/dbmspool
8. @ddl_pin.sql ogg
9. @sequence.sql
10.grant execute on ogg_admin.updatesequence to ogg_admin;
-- 2. 测试用户并登录 +++++++++++++++++++++++++++
GGSCI (sywu) 2> show all
GGSCI (sywu) 2> info mgr
GGSCI (source) 2> dblogin userid ogg_admin, password ogg123
Successfully logged into database
-- 添加 checkpoint
GGSCI (oracle120 as ogg_admin@xxxdb) 4> edit params globals
ggschema ogg_admin
checkpointtable ogg_admin.checkpoint
GGSCI (oracle120 as ogg_admin@xxxdb) 12> add checkpointtable ogg_admin.checkpoint
Successfully created checkpoint table ogg_admin.checkpoint.
-- 3. 管理进程配置 +++++++++++++++++++++++++++
GGSCI (source) 3> edit params mgr
--端口号
port 7809
--允许端口范围
dynamicportlist 7801-8200
--自启抽取进程
autostart er *
--重启抽取进程,每三分钟一个 重复五次
autorestart er *, retries 5, waitminutes 3
--删除超过三天的处理过的投递文件
purgeoldextracts ./dirdat/ *, usecheckpoints, minkeepdays 3
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45
-- ++++++++++++++++++++++++++++
port 7809
dynamicportlist 7801-8200
autostart extract *
autorestart extract *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/ *, usecheckpoints, minkeepdays 3
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45
GGSCI (source) 3> start mgr
-- 4. 抽取进程配置 +++++++++++++++++++++++++++
GGSCI (source) 9> edit params extra
extract extra
--dynamicresolution
setenv(oracle_sid="xxxdb")
setenv(oracle_home="/dbdir/app/oracle/product/12.2.0.1/dbhome_1")
setenv(nls_lang="AMERICAN_AMERICA.ZHS16GBK")
userid ogg_admin, password ogg123
report at 01:59
reportrollover at 02:00
cachemgr, cachesize 1gb
dboptions allowunusedcolumn
warnlongtrans 3h, checkinterval 5m
--trail 文件文件夹和标识(类似于sd*这样文件),命名仅仅能是2个字符。
exttrail ./dirdat/ex
discardfile ./dirrpt/discard.dsc, append, megabytes 1024
numfiles 6000
eofdelaycsecs 30
gettruncates
tranlogoptions dblogreader
tranlogoptions dblogreaderbufsize 1048576
--避免循环复制的出现
tranlogoptions excludeuser ogg_admin
br brinterval 2h , brdir br
getupdatebefores
--nocompressdeletes
-- 当你须要同步除Goldengate和Oracle数据库自带的用户外全部用户的DDL操作时,
-- 可在源端主抽取进程中增加以下的内容
ddl include all
ddloptions addtrandata
--tableexclude hr.xxx;
table hr.*;
sequence hr.*;
-- 检查确认 extra 配置文件内容无误。
/opt/app/ogg/checkprm /opt/app/ogg/dirprm/extra.prm -C extract -m Classic -V
GGSCI (source) 5> view params extra
-- 增加一个抽取进程 extra,名字可以根据需要修改(注意:最长8位字符)
GGSCI (source) 6> add extract extra, tranlog, begin now
GGSCI (source) 6> add extract extra, sourceistable
-- 将EX_EMP抽取的源数据变更数据trail写到该文件夹
GGSCI (source) 7> add exttrail ./dirdat/ex, extract extra megabytes 1024
-- 查看添加情况
GGSCI>info exttrail
GGSCI>info extract extra
-- 管理进程的常用命令
GGSCI > start extract e1 --启动e1进程
GGSCI > stop extract e1 --关闭e1进程
GGSCI > kill extract e1 --强制停止e1进程
GGSCI > info e1 --查看进程参数信息
GGSCI > stats e1 --查看进程运行信息
GGSCI > view report e1 ---查看运行报告
--其中extract可以不用
-- 5. 投递进程配置 +++++++++++++++++++++++++++++++++++
GGSCI (source) 8> edit params pump
extract pump
setenv(oracle_sid="xxxdb")
setenv(oracle_home="/dbdir/app/oracle/product/12.2.0.1/dbhome_1")
setenv(nls_lang="AMERICAN_AMERICA.ZHS16GBK")
--setenv(nls_lang="american_america.zhs16gbk")
userid ogg_admin, password ogg123
report at 01:59
reportrollover at 02:00
cachemgr cachesize 1gb
flushcsecs 30
numfiles 6000
eofdelaycsecs 30
rmthost 10.10.200.23, compress, mgrport 7809, tcpbufsize 100000, tcpflushbytes 300000
rmttrail ./dirdat/pp
--passthru
gettruncates
--dynamicresolution
getupdatebefores
--nocompressdeletes
table hr.*;
sequence hr.*;
-- 检查确认配置文件内容无误
/opt/app/ogg/checkprm /opt/app/ogg/dirprm/pump.prm -C extract -m Classic -V
GGSCI (source) 9> view params pump
GGSCI (source) 10> add extract pump, exttrailsource ./dirdat/ex, begin now
GGSCI (source) 11> add rmttrail ./dirdat/pp, extract pump megabytes 1024
-- 这一步很重要,不要忘记。++++++++++++++++++++++++++++++++++++++++++++++
-- 为需要抽取数据的schema增加trandata
GGSCI (sywu) 2>add trandata username.table_name
GGSCI (sywu) 2>add trandata *.*
GGSCI (sywu) 2>add trandata hr.*
GGSCI (sywu) 2>info trandata hr.*
GGSCI (sywu) 2>delete trandata hr.*
-- 复制过程太慢:
-- 看到这里,我明白问题出在哪了。同步表没有主键,在设置了trandata后,update、delete操作
-- 使用所有列绑定为一个列作为唯一标识来同步变化的。后来手工添加了主键,但是trandata还是按照之前
-- 的方法来做,并没有采用主键。解决方法很简单,删除原有trandata,重新add trandata使主键生效。
select sid,serial#,paddr from v$session where username='ogg_admin';
-- 6. 复制进程配置 (主主同步才涉及这个)+++++++++++++++++++++++++++++++++++
GGSCI (target) 4> edit params rep
replicat rep
--dynamicresolution
setenv(oracle_sid="xxxdb")
setenv(oracle_home="/dbdir/app/oracle/product/12.2.0.1/dbhome_1")
setenv(nls_lang="AMERICAN_AMERICA.ZHS16GBK")
--setenv(nls_lang="american_america.zhs16gbk")
userid ogg_admin, password ogg123
report at 01:59
reportrollover at 02:00
cachemgr, cachesize 1gb
--reperror (default,discard,abend)
discardfile ./dirrpt/rep.dsc, append, megabytes 1024
discardrollover at 06:00
gettruncates
numfiles 5000
eofdelaycsecs 30
assumetargetdefs
allownoopupdates
getupdatebefores
map hr.*, target hr.*;
-- 检查确认配置文件内容无误
/opt/app/ogg/checkprm /opt/app/ogg/dirprm/rep.prm -C replicat -m Classic -V
GGSCI (target) 8> view params rep
GGSCI (target) 9> add replicat rep, exttrail ./dirdat/pp, checkpointtable ogg_admin.checkpoint
|