Oracle Golden Gate 搭建
参考:《Oracle Golden Gate 知识点总结》
1、安装数据库
oracle 12c
源库 prod 192.168.10.20
目标库 standby 192.168.10.30
2、下载OGG,解压,安装12版本至/u01/ogg目录
3、配置环境变量
vim .bash_profile
export PATH=$ORACLE_HOME/bin:$PATH:$PATH/bin:$ORACLE_BASE/ogg
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib:$ORACLE_BASE/ogg
export GGATE=/u01/ogg
source .bash_profile
4、建立目录
cd /u01/ogg
./ggsci
->create subdirs
->exit
5、设置归档模式、强制日志、附加日志
select log_mode,supplemental_log_data_min,force_logging from v$database;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter database force logging;
alter database add supplemental log data
select log_mode,supplemental_log_data_min,force_logging from v$database;
6、创建表空间、用户、授权
create user ogg identified by ogg default tablespace ogg;
alter user ogg quota unlimited on ogg;
grant dba to ogg;
create tablespace sender datafile '/u01/oradata/prod/sender.dbf' size 100M autoextend off;
create user sender identified by sender default tablespace sender;
alter user sender quota unlimited on sender;
grant connect,resource to sender;
create user ogg identified by ogg default tablespace ogg;
alter user ogg quota unlimited on ogg;
grant dba to ogg;
create tablespace receiver datafile '/u01/oradata/standby/receiver.dbf' size 100M autoextend off;
create user receiver identified by receiver default tablespace receiver;
alter user receiver quota unlimited on receiver;
grant connect,resource to receiver;
7、oracle 12c 版本和部分 11g 版本需要额外设置
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
8、建立测试表
conn sender/sender
create table t1(id number(10),name char(10));
insert into t1 values (1,'wei');
commit;
conn receiver/receiver
create table t1(id number(10),name char(10));
一、抽取、传送写成一个ext1进程的配置
prod源端操作:配置mgr、ext1
./ggsci
dblogin userid ogg,password ogg
quit
./ggsci
show
edit params ./GLOBALS
ggschema ogg
edit params mgr
PORT 7809
start mgr
info all
add extract ext1,tranlog,begin now
add rmttrail /u01/ogg/dirdat/sd,extract ext1
edit params ext1
extract ext1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv(ORACLE_SID=prod)
userid ogg,password ogg
gettruncates
rmthost 192.168.10.30,mgrport 7809
rmttrail /u01/ogg/dirdat/sd
table sender.t1;
start extract ext1
standby目标端操作:配置管理进程mgr、复制进程pump1
./ggsci
dblogin userid ogg,password ogg
quit
./ggsci
edit params ./GLOBALS
ggschema ogg
checkpointtable ogg.checkpoint
edit params mgr
PORT 7809
start mgr
add checkpointtable ogg.checkpoint
add replicat rep1,exttrail /u01/ogg/dirdat/sd,checkpointtable ogg.checkpoint
edit params rep1
replicat rep1
--handlecollisions
assumetargetdefs
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv(ORACLE_SID=standby)
userid ogg,password ogg
map sender.t1, target receiver.t1;
start replicat rep1
1,$s/^I//g //对tab内容清空命令
二、
prod源端操作:配置管理进程mgr、抽取进程ext1、传输进程pump1、
./ggsci
dblogin userid ogg,password ogg
quit
./ggsci
show
edit params ./GLOBALS
ggschema ogg
edit params mgr
PORT 7809
dynamicportlist 7800-8000
autorestart extract *,retries 5,waitminutes 2,resetminutes 5
start mgr
info all
add extract ext1,tranlog,begin now
add exttrail /u01/ogg/dirdat/et,extract ext1
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
table sender.*;
start extract ext1
info all
edit params pump1
extract pump1
dynamicresolution
passthru
rmthost 192.168.10.30,mgrport 7809,compress
rmttrail /u01/ogg/dirdat/pt
table sender.*;
add extract pump1,exttrailsource /u01/ogg/dirdat/et
add rmttrail /u01/ogg/dirdat/pt,extract pump1
start pump1
info all
standby目标端操作:配置管理进程mgr、复制进程pump1
./ggsci
dblogin userid ogg,password ogg
show
edit params ./GLOBALS
ggschema ogg
checkpointtable ogg.checkpoint
edit params mgr
PORT 7809
dynamicportlist 7800-8000
autostart er *
autorestart extract *,waitminutes 2,resetminutes 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts /u01/ogg/dirdat/pt*, usecheckpoints, minkeepdays 3
start mgr
info all
add checkpointtable ogg.checkpoint
add replicat rep1,exttrail /u01/ogg/dirdat/pt,checkpointtable ogg.checkpoint
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
userid ogg,password ogg
map sender.*, target receiver.*;
start replicat rep1
1,$s/^I//g //对tab内容清空命令
三、单向ddl的添加(功能:对建表同步)
stop mgr
info all
cd /u01/ogg
sql>
!pwd
@marker_setup.sql
@ddl_setup
@role_setup
@ddl_enable.sql
@ddl_pin ogg
stop ext1
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table sender.*;
start mgr
start ext1
standby 上
stop mgr
stop rep1
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
map sender.*, target receiver.*;
start mgr
start rep1
测试,建表,插入数据,查看
四、配置active-passive
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
map receiver.*, target sender.*;
add checkpointtable ogg.checkpoint
add replicat rep1,exttrail /u01/ogg/dirdat/pt,checkpointtable ogg.checkpoint
standby 上,
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table receiver.*;
add extract ext1,tranlog,begin now
add exttrail /u01/ogg/dirdat/et,extract ext1
edit params pump1
extract pump1
dynamicresolution
passthru
rmthost 192.168.10.20,mgrport 7809,compress
rmttrail /u01/ogg/dirdat/pt
table receiver.*;
add extract pump1,exttrailsource /u01/ogg/dirdat/et
add rmttrail /u01/ogg/dirdat/pt,extract pump1
info all
//prod
lag extract ext1
stop extract ext1
@ddl_disable.sql
lag extract pump1
stop extract pump1
//standby
lag replicat rep1
stop rep1
@marker_setup.sql
@ddl_setup
@role_setup
@ddl_enable.sql
@ddl_pin ogg
start ext1
start pump1
五、配置双向active-active
停止进程
//prod上
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table sender.*;
gettruncates
tranlogoptions excludeuser ogg
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
gettruncates
map receiver.*, target sender.*;
@ddl_enable.sql
//standby上
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
map sender.*, target receiver.*;
ignoretruncates
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/er
ddl include all
ddloptions addtrandata, report
table receiver.*;
tranlogoptions excludeuser ogg
ignoretruncates
重启进程
10、根据日志,查看错误
如遇到错误,请查看日志,
参考:《Oracle Golden Gate 知识点总结》
1、安装数据库
oracle 12c
源库 prod 192.168.10.20
目标库 standby 192.168.10.30
2、下载OGG,解压,安装12版本至/u01/ogg目录
3、配置环境变量
vim .bash_profile
export PATH=$ORACLE_HOME/bin:$PATH:$PATH/bin:$ORACLE_BASE/ogg
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib:$ORACLE_BASE/ogg
export GGATE=/u01/ogg
source .bash_profile
4、建立目录
cd /u01/ogg
./ggsci
->create subdirs
->exit
5、设置归档模式、强制日志、附加日志
select log_mode,supplemental_log_data_min,force_logging from v$database;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter database force logging;
alter database add supplemental log data
select log_mode,supplemental_log_data_min,force_logging from v$database;
6、创建表空间、用户、授权
在prod上
create user ogg identified by ogg default tablespace ogg;
alter user ogg quota unlimited on ogg;
grant dba to ogg;
create tablespace sender datafile '/u01/oradata/prod/sender.dbf' size 100M autoextend off;
create user sender identified by sender default tablespace sender;
alter user sender quota unlimited on sender;
grant connect,resource to sender;
在standby上
create user ogg identified by ogg default tablespace ogg;
alter user ogg quota unlimited on ogg;
grant dba to ogg;
create tablespace receiver datafile '/u01/oradata/standby/receiver.dbf' size 100M autoextend off;
create user receiver identified by receiver default tablespace receiver;
alter user receiver quota unlimited on receiver;
grant connect,resource to receiver;
7、oracle 12c 版本和部分 11g 版本需要额外设置
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
8、建立测试表
conn sender/sender
create table t1(id number(10),name char(10));
insert into t1 values (1,'wei');
commit;
conn receiver/receiver
create table t1(id number(10),name char(10));
一、抽取、传送写成一个ext1进程的配置
prod源端操作:配置mgr、ext1
./ggsci
dblogin userid ogg,password ogg
quit
./ggsci
show
edit params ./GLOBALS
ggschema ogg
edit params mgr
PORT 7809
start mgr
info all
add extract ext1,tranlog,begin now
add rmttrail /u01/ogg/dirdat/sd,extract ext1
edit params ext1
extract ext1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv(ORACLE_SID=prod)
userid ogg,password ogg
gettruncates
rmthost 192.168.10.30,mgrport 7809
rmttrail /u01/ogg/dirdat/sd
table sender.t1;
start extract ext1
standby目标端操作:配置管理进程mgr、复制进程pump1
./ggsci
dblogin userid ogg,password ogg
quit
./ggsci
edit params ./GLOBALS
ggschema ogg
checkpointtable ogg.checkpoint
edit params mgr
PORT 7809
start mgr
add checkpointtable ogg.checkpoint
add replicat rep1,exttrail /u01/ogg/dirdat/sd,checkpointtable ogg.checkpoint
edit params rep1
replicat rep1
--handlecollisions
assumetargetdefs
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv(ORACLE_SID=standby)
userid ogg,password ogg
map sender.t1, target receiver.t1;
start replicat rep1
1,$s/^I//g //对tab内容清空命令
二、
prod源端操作:配置管理进程mgr、抽取进程ext1、传输进程pump1、
./ggsci
dblogin userid ogg,password ogg
quit
./ggsci
show
edit params ./GLOBALS
ggschema ogg
edit params mgr
PORT 7809
dynamicportlist 7800-8000
autorestart extract *,retries 5,waitminutes 2,resetminutes 5
start mgr
info all
add extract ext1,tranlog,begin now
add exttrail /u01/ogg/dirdat/et,extract ext1
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
table sender.*;
start extract ext1
info all
edit params pump1
extract pump1
dynamicresolution
passthru
rmthost 192.168.10.30,mgrport 7809,compress
rmttrail /u01/ogg/dirdat/pt
table sender.*;
add extract pump1,exttrailsource /u01/ogg/dirdat/et
add rmttrail /u01/ogg/dirdat/pt,extract pump1
start pump1
info all
standby目标端操作:配置管理进程mgr、复制进程pump1
./ggsci
dblogin userid ogg,password ogg
show
edit params ./GLOBALS
ggschema ogg
checkpointtable ogg.checkpoint
edit params mgr
PORT 7809
dynamicportlist 7800-8000
autostart er *
autorestart extract *,waitminutes 2,resetminutes 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts /u01/ogg/dirdat/pt*, usecheckpoints, minkeepdays 3
start mgr
info all
add checkpointtable ogg.checkpoint
add replicat rep1,exttrail /u01/ogg/dirdat/pt,checkpointtable ogg.checkpoint
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
userid ogg,password ogg
map sender.*, target receiver.*;
start replicat rep1
1,$s/^I//g //对tab内容清空命令
三、单向ddl的添加(功能:对建表同步)
stop mgr
info all
cd /u01/ogg
sql>
!pwd
@marker_setup.sql
@ddl_setup
@role_setup
@ddl_enable.sql
@ddl_pin ogg
stop ext1
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table sender.*;
start mgr
start ext1
standby 上
stop mgr
stop rep1
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
map sender.*, target receiver.*;
start mgr
start rep1
测试,建表,插入数据,查看
四、配置active-passive
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
map receiver.*, target sender.*;
add checkpointtable ogg.checkpoint
add replicat rep1,exttrail /u01/ogg/dirdat/pt,checkpointtable ogg.checkpoint
standby 上,
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table receiver.*;
add extract ext1,tranlog,begin now
add exttrail /u01/ogg/dirdat/et,extract ext1
edit params pump1
extract pump1
dynamicresolution
passthru
rmthost 192.168.10.20,mgrport 7809,compress
rmttrail /u01/ogg/dirdat/pt
table receiver.*;
add extract pump1,exttrailsource /u01/ogg/dirdat/et
add rmttrail /u01/ogg/dirdat/pt,extract pump1
info all
//prod
lag extract ext1
stop extract ext1
@ddl_disable.sql
lag extract pump1
stop extract pump1
//standby
lag replicat rep1
stop rep1
@marker_setup.sql
@ddl_setup
@role_setup
@ddl_enable.sql
@ddl_pin ogg
start ext1
start pump1
五、配置双向active-active
停止进程
//prod上
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table sender.*;
gettruncates
tranlogoptions excludeuser ogg
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
gettruncates
map receiver.*, target sender.*;
@ddl_enable.sql
//standby上
edit params rep1
replicat rep1
reperror default,discard
assumetargetdefs
discardfile /u01/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
userid ogg,password ogg
map sender.*, target receiver.*;
ignoretruncates
edit params ext1
extract ext1
dynamicresolution
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/ogg/dirdat/er
ddl include all
ddloptions addtrandata, report
table receiver.*;
tranlogoptions excludeuser ogg
ignoretruncates
重启进程
10、根据日志,查看错误
如遇到错误,请查看日志,
cd */ogg