Demo - geneseeq/data-migration GitHub Wiki

准备

oracle用户准备

创建ogg用户

create user ogg identified by welcome1 default tablespace users temporary tablespace temp;
grant connect, dba to ogg;
commit;
创建测试用户

create tablespace ggtest; -- optional step
create user ggtest identified by welcome1 default tablespace ggtest temporary tablespace temp;
grant connect, resource to ggtest;
commit;

创建表空间如果报错ORA-02199: 缺失 DATAFILE/TEMPFILE 子句，解决办法

show parameter db_create_file_d
alter system set db_create_file_dest='E:\app\test\product\12.1.0\dbhome_1\oradata\FJFZHB';

oracle测试数据准备

测试表创建

create table TEST ( id varchar(10) not null,username varchar(20) default '' not null , contents varchar(150)default '' not null , primary key (id) ) ;
测试数据

insert into TEST values('10011','test','jkjjk');
update TEST set contents='update' where id='10011';
delete from ggtest.gys where id='10021';

oracle archivelog

启动归档模式

sqlplus "/as sysdba" --系统用户连接oracle
shutdown immediate; --关闭数据库
startup mount; --启动数据库到mount状态
alter database archivelog; --启用归档模式
alter database open; alter database add supplemental log data; alter database force logging; --ogg需要
archive log list; --查看修改的归档模式
SELECT supplemental_log_data_min, force_logging FROM v$database;

example

测试需要两台机器，源端windows（192.168.0.57）安装oracle和ogg for oracle，目标端ubuntu14.04（192.168.0.61）安装mongodb和ogg for bigdata

源端操作

源端地址：F:\app\Administrator\product\12.1.2\oggcore

启动mgr进程

  F:\app\Administrator\product\12.1.2\oggcore_1>ggsci.exe
  
  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 11g on Dec 11 2015 17:32:51
  Operating system character set identified as GBK.
  
  Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
  
  GGSCI (WIN-0IRTKRANCQ1) 1> encrypt password welcome1 BLOWFISH ENCRYPTKEY DEFAULT
  
  Using Blowfish encryption with DEFAULT key.
  Encrypted password:  AACAAAAAAAAAAAIARIXFKCQBMFIGFARA
  Algorithm used:  BLOWFISH

  GGSCI (WIN-0IRTKRANCQ1) 2> edit params mgr
  内容为：
  PORT 7829
  userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
  purgeoldextracts F:\app\Administrator\product\12.1.2\oggcore_1\dirdat\*, usecheckpoints

  GGSCI (WIN-0IRTKRANCQ1) 3> start mgr
  Manager started.
  
  GGSCI (WIN-0IRTKRANCQ1) 4> info mgr
  Manager is running (IP port WIN-0IRTKRANCQ1.7829, Process ID 20536).
  
  GGSCI (WIN-0IRTKRANCQ1) 5>

投递初始化表结构和数据

  (1)在F:\app\Administrator\product\12.1.2\oggcore_1\dirprm目录下编辑ini_ext.prm文件，内容为：

  SOURCEISTABLE
  userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
  --RMTHOSTOPTIONS
  RMTHOST 192.168.0.61, MGRPORT 7000 #需要目标端mgr启动，否则投递失败
  RMTFILE /home/geneseeq/ogg/dirdat/initld, MEGABYTES 2, PURGE
  --DDL include objname ggtest.*
  TABLE ggtest.*;

  (2)执行投递动作，此动作执行之前，需要启动目标端mgr
  extract.exe paramfile dirprm/ini_ext.prm reportfile dirrpt/ini_ext.rpt

  (3)执行成功的话，目标端机器会有/home/geneseeq/ogg/dirdat/initld文件

新增extract进程

  为测试的表添加表级附加日志
  dblogin userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA,BLOWFISH,ENCRYPTKEY DEFAULT
  add trandata ggtest.*
  [oracle@sandbox oggora]$ ggsci
   
  Oracle GoldenGate Command Interpreter for Oracle
  Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
  Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
  Operating system character set identified as UTF-8.
   
  Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. 
   
  GGSCI (sandbox.localdomain) 1> edit params ggext
  extract ggext
  userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
  --RMTHOSTOPTIONS
  RMTHOST <target-ip>, MGRPORT <target-mgrport>
  RMTFILE /home/ogg/dirdat/or, MEGABYTES 2, PURGE
  DDL include objname ggtest.*
  -- SOURCECATALOG orcl
  TABLE ggtest.*;
   
  GGSCI (sandbox.localdomain) 2> dblogin userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
  Successfully logged into database.
   
  GGSCI (sandbox.localdomain as ogg@orcl) 3> register extract GGEXT database
   
  2016-02-16 15:37:21  INFO    OGG-02003  Extract GGEXT successfully registered with database at SCN 17151616.
   
  GGSCI (sandbox.localdomain as ogg@orcl) 4> add extract ggext, INTEGRATED TRANLOG, BEGIN NOW
  EXTRACT (Integrated) added.
  
  GGSCI (sandbox.localdomain as ogg@orcl) 4>start ggext

目标端操作

目标端路径：/home/geneseeq/ogg

启动mgr进程

cd $OGGHOME &&　./ggsci

	>create subdirs
	>edit params mgr
	PORT 7000
	>start manager

配置mongodb

  cat /home/geneseeq/ogg/dirprm/mongo.props
  内容为：
  gg.handlerlist=mongodb		
  gg.handler.mongodb.type=oracle.goldengate.delivery.handler.mongodb.MongoDBHandler
  gg.handler.mongodb.clientURI=mongodb://localhost:27017/ #使用的mongodb数据库
  #gg.handler.mongodb.clientURI=mongodb://ogg:ogg@localhost:27017/?authSource=admin&authMechanism=SCRAM-SH		
  gg.handler.mongodb.mode=tx
  goldengate.userexit.timestamp=utc
  goldengate.userexit.writers=javawriter
  javawriter.stats.display=TRUE
  javawriter.stats.full=TRUE
  gg.log=log4j
  gg.log.level=DEBUG
  gg.report.time=30sec
  ##CHANGE THE PATH BELOW
  gg.classpath=/home/ogg/mongo/lib/mongo-java-driver-3.2.2.jar:/home/ogg/mongo/bin/ogg-mongodb-adapter-1.0.jar:
  javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar:

解析初始化表结构和数据

  (1)配置irmong.prm

  cat /home/geneseeq/ogg/dirprm/irmongo.prm
  -- Initial load
  SPECIALRUN
  END RUNTIME
  EXTFILE dirdat/initld
  TARGETDB LIBFILE libggjava.so SET property=dirprm/mongo.props
  REPORTCOUNT EVERY 1 MINUTES, RATE
  GROUPTRANSOPS 10000
  MAP ggtest.*, TARGET ggtest.*;

  (2)执行命令，解析数据
  ./replicat paramfile dirprm/irmongo.prm reportfile dirrpt/irmongo.rpt

  (3)导入成功，可以进入mongodb查看导入数据
  root@iZuf64p0ug077ml3ockroiZ:/home/ogg# mongo
  MongoDB shell version: 3.2.10
  connecting to: test
  Server has startup warnings:
  2016-11-15T09:20:43.469+0000 I CONTROL  [initandlisten]
  2016-11-15T09:20:43.469+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
  2016-11-15T09:20:43.469+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
  2016-11-15T09:20:43.469+0000 I CONTROL  [initandlisten]
  2016-11-15T09:20:43.469+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
  2016-11-15T09:20:43.469+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
  2016-11-15T09:20:43.470+0000 I CONTROL  [initandlisten]
  > show dbs
  GGTEST    0.000GB
  QASOURCE  0.000GB
  admin     0.000GB
  local     0.000GB
  petshop   0.000GB
  petshot   0.000GB
  test      0.000GB
  > use GGTEST
  switched to db GGTEST
  > show collections
  GYS
  TEST
  > use.GYS.find()
  2016-12-02T13:36:42.581+0800 E QUERY    [thread1] ReferenceError: use is not defined :
  @(shell):1:1
  > db.GYS.find()
  { "_id" : ObjectId("583fa72102510b49751e0ebe"), "USERNAME" : "test05", "ID" : "10015", "CONTENTS" : "jkjjk05" }
  { "_id" : ObjectId("583fa72102510b49751e0ebf"), "USERNAME" : "test06", "ID" : "10016", "CONTENTS" : "jkjjk06" }
  { "_id" : ObjectId("583fa72102510b49751e0ec0"), "USERNAME" : "test07", "ID" : "10017", "CONTENTS" : "jkjjk07" }

启动replicat进程

          配置rmongo.prm，其内容为:
          
          `REPLICAT rmongo`
          `-- Trail file for this example is located in "AdapterExamples/trail" directory`
          `-- Command to add REPLICAT`
          `-- add replicat rmongo, exttrail AdapterExamples/trail/tr`
          `TARGETDB LIBFILE libggjava.so SET property=dirprm/mongo.props`
          `REPORTCOUNT EVERY 1 MINUTES, RATE`
          `GROUPTRANSOPS 10000`
          `#SOURCECATALOG ORCL` #这个一定要注释掉，否则会报OGG-02649错误
          `MAP GENESEEQ.*, TARGET GENESEEQ.*;`

  replicat进程是来解析extract进程投递过来的文件
  GGSCI (sandbox.localdomain) 7> add replicat rmongo, exttrail dirdat/or

  REPLICAT added.
  GGSCI (sandbox.localdomain) 8> start replicat rmongo

  Sending START request to MANAGER ...
  REPLICAT RMONGO starting
   
  GGSCI (sandbox.localdomain) 9> info rmongo
   
  REPLICAT   RMONGO    Last Started 2016-09-01 14:45   Status RUNNING
  Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
  Process ID           8994
  Log Read Checkpoint  File dirdat/or000000000
                       First Record  RBA 0

测试

执行INSERT,DELETE.UPDATE,CREATE TABLE等动作测试数据是否实时迁移成功

说明

停用oracle archivelog

sqlplus "/as sysdba" --系统用户连接oracle
shutdown immediate; --关闭数据库
startup mount; --启动数据库到mount状态
alter database noarchivelog; --启用归档模式
archive log list; --查看修改的归档模式
SELECT supplemental_log_data_min, force_logging FROM v$database;
清理归档日志

sqlplus "/as sysdba" --系统用户连接oracle
show parameter db_recovery; --如果TYEP为string，VALUE为空，只需手工删除归档日志即可，否则需要使用RMAN命令删除
切换rman命令，使用sys/passwd登录
手动删除E:\app\Administrator\fast_recovery_area\orcl\ARCHIVELOG下文件
输入crosscheck archivelog all;检验一次 delete expired archivelog all
删除oracle用户

sqlplus "/as sysdba
drop user test cascade;
删除oracle表和表空间

sqlplus "/as sysdba
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;