Oracle的高可用方案有很多,常见的有DataGuard和GoldenGate,都是实现数据库的主从复制,但是又有差别,这里主要介绍GoldenGate的配置。

本文记录我配置 Oracle GoldenGate 的过程;折腾了好久,无比艰辛。这里主要介绍主从同步,主主的话可能风险更大,对数据库运维不精通的谨慎使用。

  • 操作系统:Suse Linux 12 SP3
  • Oracle版本:Oracle 12c R2
  • GoldenGate版本:Oracle Golden Gate 19c

主要参考文章:

配置过程,涉及到的SQL语句如下,首先配置数据源端:

  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

接下来配置目标数据库的sql语句:

  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
-- target 目标端+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 创建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 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');

-- 接下来要执行一些脚本设置
-- 进入OGG的安装文件夹,然后调用脚本:
sqlplus / as sysdba;
1. @sequence.sql;
2. grant execute on ogg_admin.replicatesequence to ogg_admin;

-- 添加checkpoint +++++++++++++++++++++++++++++++++
GGSCI (source) 2> dblogin userid ogg_admin, password ogg123
GGSCI (source) 4> edit params globals
ggschema ogg_admin
checkpointtable ogg_admin.checkpoint

GGSCI (source) 4> add checkpointtable ogg_admin.checkpoint


-- 管理进程配置 +++++++++++++++++++++++++++++++++++
GGSCI (target) 5> edit params mgr
port 7809
dynamicportlist 7801-8200
autostart replicat *
autorestart replicat *, retries 5, waitminutes 3
--purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 7
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45

-- 复制进程配置 +++++++++++++++++++++++++++++++++++
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
--控制OGG给多少张table和map进行初始化内存分配,默认1000
numfiles 5000
eofdelaycsecs 30
assumetargetdefs
allownoopupdates
getupdatebefores
ddl include mapped
ddlerror default ignore retryop
--mapexclude schema.*_tmp
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
GGSCI (target) 7> start mgr


-- 初始化目标数据库(从源备份数据库,在目标还原,保持初始数据一致) ++++++++++++++++++++++++++++++
-- expdp s/s directory=dump_dir1 dumpfile=sas1.dmp logfile=aa.log
-- schemas=scott 
-- tables=emp,dept 
-- query='where deptno=20'    		带条件
-- tablespaces=temp,example  		按表空间
-- full=y       					整个库
-- content=
									-- all			所有
									-- metadata_only只导表结构
									-- data_only 	只导出表数据

-- impdp
-- remap_schema=scott:system    	更改owner
-- tablespaces=example   			导入表空间
-- table_exists_action   			-- skip 		是如果已存在表,则跳过并处理下一个对象;
									-- append		是为表增加数据;
									-- truncate		是截断表,然后为其增加新数据;
									-- replace		是删除已存在表,重新建表并追加数据

-- 查询SCN号
select current_scn from v$database;
-- 16543601519172
select dbms_flashback.get_system_change_number current_scn from dual;

-- 源端导出数据(可以导出截止到某个版本号之前的数据,之后的不导出)
expdp system/pwd@orcl1 cluster=no parallel=6 filesize=10G 
	dumpfile=orcl1_db_20190923_%U.dmp logfile=orcl1_db_20190923.log 
	SCHEMAS=USERNAME compression=ALL  flashback_scn=16543601519172
	
-- 目标端导入数据
impdp system/pwd@target directory=data_pump cluster=no parallel=6 
	dumpfile=orcl1_db_20190923_%u.dmp logfile=imp_orcl1_db_20190923.log 
	schemas=username data_options=skip_constraint_errors

-- 数据量小的话不用拆分文件并行备份
select current_scn from v$database;
-- 2491756
expdp system/passwd@xxxdb directory=dumps_dir dumpfile=oracle22.dmp logfile=oracle22.log 
	schemas=hr,fdapi compression=all flashback_scn = 2491756;

--impdp system/passwd@xxxdb directory=dumps_dir dumpfile=hr_all.dmp schemas=hr 
--data_options=skip_constraint_errors;
impdp system/passwd@xxxdb directory=dumps_dir dumpfile=20200403_022201.fdapi.dmp 
	table_exists_action=replace

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 启动目标端进程
GGSCI (target) 10> start rep aftercsn 2491756

-- 查看复制进程状态
GGSCI (target) 10> info rep

大致就是这样的配置过程,其中有个别地方可能不太准确,可以同步参考网上其它内容。

比起MySQL,Oracle的主从方案都显得有些复杂,现在用Oracle的企业也越来越少了吧,我这也是硬着头皮上,自己摸索了一遍又一遍,最后也不敢轻易在生产系统应用;这是金融行业的特点,稳定压倒一切。一般的互联网公司大概没有这个问题吧,已经都被马云他们去IOE了。

(完)