原理图:
试验环境 | 源端 | 目标端 |
操作系统 | Windows 7 64位 | Windows 10 64位 |
IP地址 | 192.168.1.6 | 192.168.1.8 |
数据库 | Oracle 11.2.0.4.0 64bit | Oracle 11.2.0.4.0 64bit |
数据库账号 | ggs | ggs |
GoldenGate | https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html | |
数据库表 | demo | demo |
dirdat目录 | E:\app\Administrator\product\11.2.0\ogg\dirdat | D:\JL\A\ogg\dirdat |
查看数据库是否是归档模式:
select log_mode from v$database;
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
在源端中打开force logging
select force_logging from v$database;
alter database force logging;
select force_logging from v$database;
在源端中打开supplemental log
select supplemental_log_data_min from v$database;
alter database add supplemental log data;
select supplemental_log_data_min from v$database;
在源端切换日志,使更改生效
alter system switch logfile;
select supplemental_log_data_min from v$database;
关闭源端回收站
show parameter recyclebin
alter system set recyclebin=off;
# ----------- 如果不成功,执行以下命令 -------------
alter system set recyclebin=off deferred;
创建ogg管理用户
create user ggs identified by ggs default tablespace users temporary tablespace temp;
grant connect ,resource,unlimited tablespace to ggs;
grant execute on utl_file 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;
在源端和目标端创建测试表:demo
conn scott/tiger
drop table demo purge ;
create table demo(id number primary key,ename varchar2(10));
在目标端创建ggs用户:
create user ggs identified by ggs default tablespace users temporary tablespace temp;
grant dba to ggs;
ogg源端配置:
(1)添加 manager 服务
编辑文件,并在文件中申明manager的名字。
edit params ./GLOBALS
MGRSERVNAME GGMGR
添加服务
install addservice
创建相关目录
GGSCI (Win10) 1> create subdirs
(2)添加 表级的trandata
GGSCI (Win10) 1> dblogin userid ggs,password ggs
GGSCI (Win10) 1> add trandata scott.demo
(3)配置mgr管理进程
GGSCI (Win10) 1> edit params mgr
添加以下内容:
port 7809
dynamicportlist 7810-7815
autorestart extract *,waitminutes 2,retries 5
(4)配置extract抽取进程,输入edit params eora将会弹出文本编辑器并写入
GGSCI (Win10) 1> edit params eora
以下是添加内容:
extract eora
userid ggs,password ggs
exttrail E:\app\Administrator\product\11.2.0\ogg\dirdat\et
table scott.demo;
(6)添加extract进程并且创建本地trail文件
GGSCI (Win10) 1> add extract eora,tranlog,begin now
GGSCI (Win10) 1> add exttrail E:\app\Administrator\product\11.2.0\ogg\dirdat\et,extract eora
GGSCI (Win10) 1> start eora
(7)配置pump进程,输入edit params pump将会弹出文本编辑器并写入
GGSCI (Win10) 1> edit params pump
以下添加内容:
extract pump
Userid ggs,password ggs
rmthost 192.168.1.8, mgrport 7809
rmttrail D:\JL\A\ogg\dirdat
Table scott.demo;
>>> rmthost :目标端ip及mgr进程端口
>>> rmttrail:目标端dirdata路径
(8)添加pump进程,并将传输进程添加到队列:
GGSCI (Win10) 1> add extract pump,exttrailsource E:\app\Administrator\product\11.2.0\ogg\dirdat\et
-- 注意`rmttrail`的地址为`源端`服务器的目录
GGSCI (Win10) 1> add rmttrail D:\JL\A\ogg\dirdat\et,extract pump
-- 注意`rmttrail`的地址为`目标端`服务器的目录
(9)启动源端所需服务
start mgr
start eora
start pump
ogg目标端配置:
(1)登录数据库
dblogin userid ggs,password ggs:
(2)添加checkpoint表
add checkpointtable ggs.checkpoint
(3)配置mgr管理进程,输入edit params mgr将会弹出文本编辑器并写入:
GGSCI (Win10) 1> edit params mgr
添加以下内容:
port 7809
dynamicportlist 7810-7811
autostart er *
autorestart extract ,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts E:\app\win10\ogg\install\dirdat\et,usecheckpoints,minkeepdays 3
(4)配置的复制进程,输入edit params repl将会弹出文本编辑器并写入:
GGSCI (Win10) 1> edit params repl
添加以下内容:
replicat repl
userid ggs,password ggs
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/repl.dsc,append,megabytes 50
MAP scott.demo, target scott.demo;
(5)添加复制进程并启动
GGSCI (Win10) 1> add replicat repl,exttrail E:\app\Administrator\product\11.2.0\ogg\dirdat,checkpointtable ggs.checkpoint
GGSCI (Win10) 1> start repl
(6)启动目标端进程
start mgr
start repl
插入数据验证:
SQL> INSERT INTO "SCOTT"."DEMO"("ID", "ENAME") VALUES ('1', '测试1');
已创建 1 行。
SQL> INSERT INTO "SCOTT"."DEMO"("ID", "ENAME") VALUES ('2', '测试2');
已创建 1 行。
SQL> commit;