OGG双向同步

安装OGG软件

选择数据库版本 ogg_install01

指定安装路径以及数据库路径 ogg_install02

数据库配置

开启强制日志、附加日志、并确定处于归档模式

1
2
3


SQL> ALTER DATABASE FORCE LOGGING;  
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
SQL> alter system set enable_goldengate_replication=true;

创建表空间和用户

1
2
3
4
5
6
7


create tablespace ogg datafile '/u01/app/oracle/oradata ogg.dbf' size 5G autoextend off;
create user ogg identified by ogg default tablespace ogg;
grant connect,resource,create session,alter session,select any dictionary to ogg;
grant select any table,flashback any table,alter any table to ogg;
grant insert any table,update any table,delete any table,select any transaction to ogg;
grant execute on DBMS_CAPTURE_ADM to ogg;
grant execute on dbms_streams_adm to ogg;

A同步B

MGR管理进程配置(A)

1
2
3
4


GGSCI>edit params mgr
port 7809
DYNAMICPORTLIST 7840-7850
PURGEOLDEXTRACTS /u01/ogg/dirdat/*,usecheckpoints,minkeepdays 7

抽取进程配置(A)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


GGSCI>add extract extest01,tranlog,threads 1,begin now
GGSCI>add exttrail /u01/app/ogg/dirdat/TO,extract extest01,megabytes 200
GGSCI>edit param extest01
extract extest01
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="source")
USERID ogg,PASSWORD ogg
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADER LOGRETENTION ENABLED
TRANLOGOPTIONS EXCLUDEUSER OGG    ---排除OGG用户提交的事务
warnlongtrans 4h,checkinterval 10m
EXTTRAIL /u01/app/ogg/dirdat/TO
GETTRUNCATES
TABLE TEST.TAB1;

投递进程配置(A)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


GGSCI >add extract putest01,EXTTRAILSOURCE /u01/app/ogg/dirdat/TO
GGSCI >add rmttrail /u01/app/ogg/dirdat/RE,ext putest01,megabytes 200
GGSCI >edit params putest01
extract putest01
passthru
USERID ogg,PASSWORD ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="source")
rmthost 192.168.88.129,mgrport 7809
rmttrail /u01/app/ogg/dirdat/RE
USERID ogg,PASSWORD ogg
TABLE TEST.tab1; 

MGR管理进程配置(B)

1
2
3
4
5
6


GGSCI >edit params mgr
port 7809
DYNAMICPORTLIST 7840-7850
autorestart er *, retries 5, waitminutes 3
ACCESSRULE, PROG SERVER, ALLOW
purgeoldextracts /u01/app/ogg/dirdat/*,usecheckpoints, minkeepdays 7

创建checkpoint表(B)

1
2


GGSCI>dblogin userid ogg,password ogg
GGSCI>add checkpointtable ogg.ckpttest01

复制进程配置(B)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


GGSCI >add replicat retest01,exttrail /u01/app/ogg/dirdat/RE,checkpointtable ogg.ckpttest01
GGSCI >edit params retest01
replicat retest01
batchsql
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="target")
ASSUMETARGETDEFS
USERID ogg,PASSWORD ogg
discardfile /u01/app/ogg/dirrpt/retest01.dsc,append,megabytes 100
MAP TEST.tab1,TARGET TEST.tab1;

A同步B初始化

同步表添加附加日志(A)

1
2


GGSCI>dblogin userid ogg,password ogg
GGSCI>add trandata TEST.tab1

启动抽取进程和投递进程(A)

1
2


GGSCI>start extract extest01
GGSCI>start extract putest01

查看SCN(A)

1
2


sql> col scn for 9999999999999999
sql> select dbms_flashback.get_system_change_number scn from dual;

利用数据泵导出同步表(A)

1

$ expdp \"sys/luhengxing as sysdba\" DIRECTORY=DMP_DIR DUMPFILE=OGG.DMP LOGFILE=OGG.LOG CLUSTER=N FLASHBACK_SCN=100000 TABLES=TEST.TAB1

导出同步表数据(B)

1

impdp \"sys/luhengxing as sysdba\" DIRECTORY=DMP_DIR DUMPFILE=OGG.DMP LOGFILE=OGG.LOG CLUSTER=N remap_tablespace=TEST:TEST remap_schema=TEST:TEST

启动复制进程(B)

1

GGSCI>start retest01,aftercsn 10000

B同步A

抽取进程配置(B)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


GGSCI>add extract extest02,tranlog,threads 1,begin now
GGSCI>add exttrail /u01/app/ogg/dirdat/TO,extract extest02,megabytes 200
GGSCI>edit param extest02
extract extest02
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="target")
USERID ogg,PASSWORD ogg
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADER LOGRETENTION ENABLED
TRANLOGOPTIONS EXCLUDEUSER OGG    ---排除OGG用户提交的事务
warnlongtrans 4h,checkinterval 10m
EXTTRAIL /u01/app/ogg/dirdat/TO
GETTRUNCATES
TABLE TEST.TAB1;

投递进程配置(B)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


GGSCI >add extract putest02,EXTTRAILSOURCE /u01/app/ogg/dirdat/TO
GGSCI >add rmttrail /u01/app/ogg/dirdat/RE,ext putest01,megabytes 200
GGSCI >edit params putest02
extract putest02
passthru
USERID ogg,PASSWORD ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="target")
rmthost 192.168.88.128,mgrport 7809
rmttrail /u01/app/ogg/dirdat/RE
USERID ogg,PASSWORD ogg
TABLE TEST.tab1;

创建checkpoint表(A)

1
2


GGSCI>dblogin userid ogg,password ogg
GGSCI>add checkpointtable ogg.ckpttest02

复制进程配置(A)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


GGSCI >add replicat retest02,exttrail /u01/app/ogg/dirdat/RE,checkpointtable ogg.ckpttest02
GGSCI >edit params retest02
replicat retest02
batchsql
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="source")
ASSUMETARGETDEFS
USERID ogg,PASSWORD ogg
discardfile /u01/app/ogg/dirrpt/retest02.dsc,append,megabytes 100
MAP TEST.tab1,TARGET TEST.tab1;

启动抽取和投递进程(B)

1
2


GGSCI>start extest02
GGSCI>start putest02

启动复制进程(A)

1

GGSCI>start retest02