Oracle Golden Gate 搭建

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、创建表空间、用户、授权

在prod上

create tablespace ogg datafile '/u01/oradata/prod/ogg.dbf' size 100M autoextend off;
    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 tablespace ogg datafile '/u01/oradata/standby/ogg.dbf' size 100M autoextend off;
    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

tail -100f ,/ggserr.log