OGG同步Oracle到Oracle

安装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


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

源端进程配置

MGR管理进程配置

1
2
3
4


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

抽取进程配置

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


GGSCI>add extract extest01,tranlog,threads 1,begin now
GGSCI>add exttrail /u01/ogg/dirdat/EX,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
warnlongtrans 4h,checkinterval 10m
EXTTRAIL /u01/ogg/dirdat/EX
GETTRUNCATES
TABLE TEST.*;

投递进程配置

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


GGSCI >add extract putest01,EXTTRAILSOURCE /u01/ogg/dirdat/EX
GGSCI >add rmttrail /u01/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/ogg/dirdat/RE
USERID ogg,PASSWORD ogg
TABLE TEST.*; 

目标端进程配置

MGR管理进程配置

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/ogg/dirdat/*,usecheckpoints, minkeepdays 7

创建checkpoint表

1
2


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

复制进程配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


GGSCI >add replicat retest01,exttrail /u01/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/ogg/dirrpt/retest01.dsc,append,megabytes 100
MAP TEST.*,TARGET TEST.*;

同步初始化

源端同步表添加附加日志

1
2


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

启动源端所有进程

1
2


GGSCI>start extract extest01
GGSCI>start extract putest01

查看源端SCN

1
2


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

源端导出同步表

1

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

目标端导入数据表

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

启动目标端复制进程

1

GGSCI>start retest01,aftercsn 100000

OGG用户密码加密

获取key值

1

$ <OGG_HOME>./keygen 128 1

创建密钥

1
2


$ vi ENCKEYS  
kasaur_key 0x00268557C534302D9702CB1B16958365

生成加密

1
2
3
4
5


GGSCI (source) 1> encrypt password ogg encryptkey kasaur_key
Encrypted password:  AADAAAAAAAAAAADAYGGHJDTDVFRICBLJGJQFDEEDRCOEDBEAWFSDCGYENGBGSEVEDAEHTFHFLDAGJHKJ
Algorithm used:  AES128
GGSCI (source) 4> dblogin userid ogg,password AADAAAAAAAAAAADAYGGHJDTDVFRICBLJGJQFDEEDRCOEDBEAWFSDCGYENGBGSEVEDAEHTFHFLDAGJHKJ ,encryptkey kasaur_key
Successfully logged into database.