GoldenGate -2> oracle 12c to mysql 数据同步(1)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--oracle
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
.1 开启归档模式
GoldenGate是基于oracle日志变化的捕获,所以为了完整的捕获到oracle数据库的变化,有必要将归档模式开启。
SQL> alter system set log_archive_dest_1='location=D:\app\arch' scope=both;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
注意,由于上面命令涉及停止数据库应用,应结合业务实际情况进行操作。
4.1.2 开启附加日志
在oracle中我们可以通过rowid来定位某条记录,但是目标端的数据库和源端数据库的数据库可能完全不一样,所以无法通过rowid来确定源端数据库的逻辑变化,这时附件日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后,对于源端的修改操作,oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
4.1.3 开启强制日志模式
SQL>alter database force logging;
SQL>SELECT FORCE_LOGGING FROM V$DATABASE;
alter system set enable_goldengate_replication=true;
4.1.4 goldengate创建用户并授权
源端oracle数据库创建账号:
create tablespace ggstab datafile 'D:\app\oradata\fdcp\ggstab01.dbf' size 1024M autoextend on ;
create user ggs identified by ggs default tablespace ggstab temporary tablespace temp;
grant dba to ggs;
C:\Users\Administrator>D:
D:\>cd D:/app/sgg
D:\app\sgg>./ggsci
'.' 不是内部或外部命令,也不是可运行的程序
或批处理文件。
D:\app\sgg>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Oracle 12c on Dec 18 2015 20:34:51
Operating system character set identified as GBK.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (WIN-5QL0BNKD7N8) 1> create subdirs
Creating subdirectories under current directory D:\app\sgg
Parameter files D:\app\sgg\dirprm: already exists
Report files D:\app\sgg\dirrpt: already exists
Checkpoint files D:\app\sgg\dirchk: already exists
Process status files D:\app\sgg\dirpcs: already exists
SQL script files D:\app\sgg\dirsql: already exists
Database definitions files D:\app\sgg\dirdef: already exists
Extract data files D:\app\sgg\dirdat: already exists
Temporary files D:\app\sgg\dirtmp: already exists
Credential store files D:\app\sgg\dircrd: already exists
Masterkey wallet files D:\app\sgg\dirwlt: already exists
Dump files D:\app\sgg\dirdmp: already exists
GGSCI (WIN-5QL0BNKD7N8) 2>edit params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.checkpoint
异构数据库之间同步数据必须利用结构转换文件
GGSCI (xxx) 1> edit params defgen
defsfile D:\app\sgg\dirdef\oratomy.def,purge
userid ggs, password ggs
table fdcp.*;
D:\app\sgg> defgen paramfile D:\app\sgg\dirprm\defgen.prm
edit params mgr
PORT 7809
DYNAMICPORTLIST 7840-7914
USERID ggs, PASSWORD ggs
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS D:\app\sgg\dirdat\*,usecheckpoints, minkeepdays 3
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 4
edit params extfdcp
EXTRACT extfdcp
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ggs, PASSWORD ggs
tranlogoptions dblogreader
GETTRUNCATES
--ddl include all
--DDLOPTIONS NOCROSSRENAME REPORT
--ddloptions addtrandata, report
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER ggs
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE D:\app\sgg\dirrpt\extfdcp.dsc,APPEND,MEGABYTES 1024
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 300s
EXTTRAIL D:\app\sgg\dirdat\fd
FETCHOPTIONS NOUSESNAPSHOT
--TRANLOGOPTIONS CONVERTUCS2CLOBS
TABLE FDCP.*;
edit params dpfdcp
EXTRACT dpfdcp
PASSTHRU
RMTHOST 172.26.115.254, MGRPORT 8809, compress, PARAMS -w 30
RMTTRAIL D:\app\tgg\dirdat\fd
TABLE FDCP.*;
add ext extfdcp,tranlog ,begin now
add exttrail D:\app\sgg\dirdat\fd ,ext extfdcp,megabytes 200
add ext dpfdcp,exttrailsource D:\app\sgg\dirdat\fd
add rmttrail D:\app\tgg\dirdat\fd,ext dpfdcp,megabytes 200
dblogin userid ggs,password ggs
add checkpointtable ggs.checkpoint
ADD EXTTRAIL D:\app\sgg\dirdat\fd, EXTRACT EXTFDCP
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--mysql
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>D:
D:\>cd D:\app\tgg
D:\app\tgg>ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), MySQL Enterprise on Dec 11 2015 15:48:43
Operating system character set identified as GBK.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (WIN-5QL0BNKD7N8) 1> create subdirs
Creating subdirectories under current directory D:\app\tgg
Parameter files D:\app\tgg\dirprm: created
Report files D:\app\tgg\dirrpt: created
Checkpoint files D:\app\tgg\dirchk: created
Process status files D:\app\tgg\dirpcs: created
SQL script files D:\app\tgg\dirsql: created
Database definitions files D:\app\tgg\dirdef: created
Extract data files D:\app\tgg\dirdat: created
Temporary files D:\app\tgg\dirtmp: created
Credential store files D:\app\tgg\dircrd: created
Masterkey wallet files D:\app\tgg\dirwlt: created
Dump files D:\app\tgg\dirdmp: created
GGSCI (WIN-5QL0BNKD7N8) 2>edit params mgr
PORT 8809
DYNAMICPORTLIST 8819-8859
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *,RETRIES 5 WAITMINUTES 3
PURGEOLDEXTRACTS D:\app\tgg\dirdat\*,usecheckpoints,minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 5
LAGCRITICALMINUTES 10
GGSCI (WIN-5QL0BNKD7N8) 3>edit params repfdcp
replicat repfdcp
DBOPTIONS HOST localhost,CONNECTIONPORT 3306
--targetdb fdcp_source, userid gzdzbl,password gzdzbl2015
targetdb fdcp_source, userid ggs,password ggs
assumetargetdefs
reperror default,discard
discardfile D:\app\tgg\dirrpt\repfdcp.dsc,append,megabytes 100
--map FDCP.*, target FDCP_SOURCE.*;
map FDCP.*, target `FDCP_SOURCE`.*;
edit params ./GLOBALS
GGSCHEMA mysql
CHECKPOINTTABLE mysql.checkpoint
--dblogin sourcedb fdcp_source@172.26.115.254:3306,userid ggs, password ggs
--dblogin sourcedb fdcp_source@172.0.0.1:3306,userid ggs, password ggs
dblogin sourcedb fdcp_source@localhost:3306,userid ggs, password ggs
add checkpointtable mysql.checkpoint
ADD replicat repfdcp EXTTRAIL D:\app\tgg\dirdat\fd,checkpointtable mysql.checkpoint
--ADD replicat repfdcp EXTTRAIL D:\app\tgg\dirdat\fd, nodbcheckpoint