GoldenGate学习6–Oracle到SQLServer数据同步

5月 15th, 2018

1.相关环境;
Oracle:11.2.0.1 on Linux x64
SQLServer:2005 on Windows 2008R2 x64
OGG:12.1.2.1.0

2.准备源端(Oralce端);
2.1开启数据库级别附加日志;
SELECT supplemental_log_data_min, force_logging FROM v$database;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;
SELECT supplemental_log_data_min, force_logging FROM v$database;
SQL> ALTER SYSTEM SWITCH LOGFILE;
2.2创建用户并授权;
CREATE USER ggadm IDENTIFIED BY ‘********’;
EXEC dbms_goldengate_auth.grant_admin_privilege(‘ggadm’); for Oracle 11.2.0.4 and later
EXEC dbms_goldengate_auth.grant_admin_privilege(‘ggadm’,grant_select_privileges=>true); for Oracle 11.2.0.3 or Earlier
2.3开启相关参数;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=true; for an Oracle 11.2.0.4 or greated database
2.4在ogg中配置安全认证;
./ggsci
ADD CREDENTIALSTORE
ALTER CREDENTIALSTORE ADD USER ggadm, PASSWORD ******** ALIAS alias ogg
2.5开启表级别附加日志(可选);
./ggsci
DBLOGIN USERIDALIAS ogg
ADD TRANDATA [container.]schema.table
INFO TRANDATA [container.]schema.table

3.配置表结构映射;
3.1在源端定义需要映射的表;
./ggsci
EDIT PARAM DEFGEN
DEFSFILE ./dirdef/source.def, PURGE
USERIDALIAS alias
TABLE schema.table1;
TABLE schema.table1;
3.2生成映射文件;
shell> defgen paramfile ./dirprm/defgen.prm
3.3拷贝文件到目标端相应的目录;

4.在源端和目标端分别开启管理进程;
EDIT PARAMS mgr

PORT 7809

START mgr
INFO mgr

5.在源端配置数据泵抽取(Data Pump Extract)进程;
ADD EXTRACT EXT1, TRANLOG, BEGIN NOW, THREADS 1
EDIT PARAMS ext1

EXTRACT ext1
USERIDALIAS ogg
RMTHOST dst_ip, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
RMTTRAIL ./dirdat/rt
# SEQUENCE schema.sequence_name;
TABLE schema.table;

ADD RMTTRAIL ./dirdat/rt, EXTRACT ext1, MEGABYTES 100;
INFO RMTTRAIL *

# START EXTRACT ext1
# INFO EXTRACT ext1, DETAIL

6.配置目标端(SQLServer端);
6.1配置ODBC数据源:控制面板->管理工具->数据源(ODBC)->系统DSN(添加),择驱动程序类型为[SQL Server Native Client],比如:名字为ogg;
6.2在目标端创建一样的表结构;
6.3添加检查点表;
./ggsci
EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ggschkpt

DBLOGIN SOURCEDB ogg, USERID uid, PASSWORD pwd
ADD CHECKPOINTTABLE
退出之后再进一次客户端;
6.4在目标端配置复制(Change Delivery)进程;
ADD REPLICAT rpl1, EXTTRAIL E:\ogg121\dirdat\rt
EDIT PARAMS rpl1

REPLICAT rpl1
TARGETDB ogg, USERID uid, PASSWORD pwd
HANDLECOLLISIONS
SOURCEDEFS E:\ogg121\dirdef\source.def
REPERROR DEFAULT, DISCARD
DISCARDFILE E:\ggate\dirrpt\rpl1.dsc APPEND
GETTRUNCATES
MAP schema.table, TARGET db.table;

# START REPLICAT rpl1
# INFO REPLICAT rpl1

7.查看对应的报告;
7.1查看抽取进程报告;
SEND EXTRACT EXT1, REPORT
VIEW REPORT EXT1
7.2查看复制进程报告;
SEND REPLICAT RPL1, REPORT
VIEW REPORT RPL1

TIPS:在Windows下安装ogg时需要注册服务;
C:\GG_DIR> INSTALL ADDSERVICE
./ggsci
CREATE SUBDIRS

标签:
目前还没有任何评论.