源端是oracle数据库。目标端是mysql数据库。源端的配置就是前一篇文章中的配置。基础的配置都一样的。没啥好说。具体的往下看
源端
创建wu.mytable表，这是测试表
添加抽取进程，不使用数据泵
GGSCI (primary) 43> edit param ext_my
extract ext_my
userid ogg,password oracle
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME = "/u01/app/oracle10g")
setenv (ORACLE_SID = ocp)
exttrail ./dirdat/my
rmthost 192.168.1.12,mgrport 7810
rmttrail /app/ogg/dirdat/my
table wu.mytable;
GGSCI (primary) 44> add extract ext_my,tranlog, begin now;;
EXTRACT added.
GGSCI (primary) 45> dblogin userid ogg
Password:
Successfully logged into database.
GGSCI (primary) 47> add trandata wu.mytable
Logging of supplemental redo data enabled for table WU.MYTABLE.
GGSCI (primary) 49> add exttrail ./dirdat/my,extract ext_my,megabytes 100
EXTTRAIL added.
GGSCI (primary) 51> add rmttrail /app/ogg/dirdat/my,extract ext_my,megabytes 100
RMTTRAIL added.
设置定义文件
GGSCI (primary) 53> edit params defgen
defsfile ./dirdef/mytable.def
userid ogg,password oracle
table wu.mytable;
然后退出到shell下
[oracle@primary ogg]$ more ./dirprm/defgen.prm
defsfile ./dirdef/mytable.def
userid ogg,password oracle
table wu.mytable;
[oracle@primary ogg]$ ./defgen paramfile ./dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 05:03:13
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-08-31 22:15:50
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Dec 17 11:42:39 EST 2008, Release 2.6.18-128.el5
Node: primary
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 4664
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile ./dirdef/mytable.def
userid ogg,password ******
table wu.mytable;
Retrieving definition for WU.MYTABLE
Definitions generated for 1 table in ./dirdef/mytable.def
[oracle@primary ogg]$ more ./dirdef/mytable.def
*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2013-08-31 22:15
*
* Field descriptions for each column entry:
*
* 1 Name
* 2 Data Type
* 3 External Length
* 4 Fetch Offset
* 5 Scale
* 6 Level
* 7 Null
* 8 Bump if Odd
* 9 Internal Length
* 10 Binary Length
* 11 Table Length
* 12 Most Significant DT
* 13 Least Significant DT
* 14 High Precision
* 15 Low Precision
* 16 Elementary Item
* 17 Occurs
* 18 Key Column
* 19 Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table WU.MYTABLE
Record length: 70
Syskey: 0
Columns: 2
ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2
NAME 0 10 56 0 0 1 0 10 10 0 0 0 0 0 1 0 0 0
End of definition
把这个文件拷到mysql端ogg的相同目录。比如我的为/app/ogg/dirdef/mytable.def。oracle是源段，有一个ogg。mysql是目的端，有一个ogg。比如我的是从源段/u01/app/ogg/dirdef/mytable.def拷到目的端/app/ogg//dirdef/mytable.def。
GGSCI (primary) 3> start ext_my
Sending START request to MANAGER ...
EXTRACT EXT_MY starting
GGSCI (primary) 4> view param ext_my
extract ext_my
userid ogg,password oracle
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME = "/u01/app/oracle10g")
setenv (ORACLE_SID = ocp)
exttrail ./dirdat/my
rmthost 192.168.1.12,mgrport 7810
rmttrail /app/ogg/dirdat/my
table wu.mytable;
源段配置完毕
目标端mysql配置。
mysql安装见我另外一篇文章。http://blog.csdn.net/shliesce/article/details/10428911
然后创建oggtest数据库。从oracle复制过来的wu.mytable表就是放在oggtest.mytable中。
ogg for mysql这个大家就自己下载吧。我是从别人那里拷来的。官网地址需要邮箱认证。不嫌麻烦的朋友认证下就可以了。
ogg的安装和之前一样。unzip，tar,create suddirs。这里一笔带过。因为这些是最基本的安装。大家应该都知道了。不会的话看我另一篇博文http://blog.csdn.net/shliesce/article/details/10429251
编辑 mgr进程GGSCI (standby) 2> edit param mgr
PORT 7810
DYNAMICPORTLIST 7800-7900
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (standby) 3> start mgr
Manager started.
GGSCI (standby) 6> edit param ./GLOBALS
checkpointtable oggtest.checkpoint
GGSCI (standby) 9> dblogin sourcedb oggtest,userid root
Password:
2013-08-31 21:56:34 WARNING OGG-00769 MySQL Login failed: . SQL error (2002). Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).
ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE , USER root, PORT 3306.
因为我安装mysql的时候socket文件默认是/app/mysql/mysql.socket。这里他默认是找/tmp/mysql.sock这个默认位置的。解决方法是ln -s /app/mysql/mysql.socket /tmp/mysql.sock
GGSCI (standby) 10> dblogin sourcedb oggtest,userid root
Password:
Successfully logged into database.
GGSCI (standby) 11> add checkpointtable oggtest.checkpoint
Successfully created checkpoint table oggtest.checkpoint.
GGSCI (standby) 24>ADD replicat repl_my EXTTRAIL /app/ogg/dirdat/my ,checkpointtable oggtest.checkpoint
GGSCI (standby) 25> edit param repl_my
REPLICAT repl_my
sourcedb oggtest,userid root,password root
sourcedefs ./dirdef/mytable.def
ASSUMETARGETDEFS
discardfile /app/ogg/discard.sql,append, megabytes 50
map wu.mytable, target oggtest.mytable;
GGSCI (standby) 26> start repl_my
Sending START request to MANAGER ...
REPLICAT REPL_MY starting
GGSCI (standby) 27> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPL_MY 00:00:00 00:00:08
配置完成。下面测试
oracle端wu用户下
SQL> insert into mytable values(1,'a');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into mytable values(2,'b');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytable;
ID NAME
---------- ----------
1 a
2 b
目的端mysql下oggtest数据库下查看
mysql> select * from oggtest.mytable;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
成功。OK~~
730
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
