Goldengate異構oracle->mysql搭建
Goldengate 異構oracle->mysql
環境：
源：
192.168.9.142 oracle ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip oracle11g
目標：
192.168.9.144 mysql ggs_121210_Linux_x64_MySQL_64bit.zip mysql5.6
下載地址：
同步的表：sender使用者下的test表
源庫已安裝ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip並已配置gg同構oracle->oracle(無影響)
===============================================================================
源端：
1 安裝ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip （略）
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
2 建立複製使用者ogg並授予許可權
SQL> create user ogg identified by 123456 default tablespace users temporary tablespace temp;
一堆許可權（測試環境之間：SQL>grant dba to ogg ）
SQL> grant connect,resource,unlimited tablespace to ogg;
SQL> grant create session,alter session to ogg;
SQL> grant execute on utl_file to ogg;
SQL> grant select any dictionary, select any table to ogg;
SQL> grant alter any table to ogg;
SQL> grant flashback any table to ogg;
SQL> grant select any transaction to ogg;
SQL> grant sysdba to ogg;
SQL> grant create table,insert any table,lock any table to ogg;
SQL> grant execute on dbms_flashback to ogg;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
3 建立同步的使用者和同步的表
SQL> create user sender identified by 123456;
SQL> create table test (id int);
SQL> alter table test add constraint pk_id primary key(id);
往表內插入資料：
SQL>
begin
for i in 1..5 loop
insert into test values(i);
end loop;
end;
/
SQL>select * from test；
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
4 開啟supplementary log
SQL> select force_logging, supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
YES YES
不是yes以下命令修改：
SQL> alter database force logging;
SQL> alter database add supplemental log data;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5 支援sequence
GGSCI (test1) 38> edit params ./globals
在統計模式下輸入並儲存：
ggschema ogg
~
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
6 支援ddl複製 （建議不配這個）
sql> @marker_setup.sql prompt: ogg
[oracle@test1 ~]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb>
[oracle@test1 goldengate]:testdb> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 5 14:06:58 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql prompt
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> alter system set recyclebin=off deferred scope=both; #必須，針對ddl複製
SQL> @ddl_setup.sql prompt: ogg
SQL> @role_setup.sql : ogg
SQL> grant GGS_GGSUSER_ROLE to ogg;
SQL> @ddl_enable.sql
10g需要安裝dbms_share_pool包：
sql> @?/rdbms/admin/dbmspool.sql sql> @ddl_pin ogg;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
7 配置MANAGER
GGSCI (test1) 3> edit param mgr
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
~
~
~
"dirprm/mgr.prm" [New] 8L, 222C written
GGSCI (test1) 4> start mgr
Manager started.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
8 新增extract
GGSCI (test1) 11> add extract ext2my,tranlog,begin now
EXTRACT added.
GGSCI (test1) 12> add exttrail ./dirdat/my,extract ext2my,megabytes 100
EXTTRAIL added.
GGSCI (test1) 13> edit params ext2my
新增以下內容：
EXTRACT ext2my
USERID ogg, PASSWORD 123456
TRANLOGOPTIONS EXCLUDEUSER ogg
RMTHOST 192.168.9.144, MGRPORT 7809
RMTTRAIL ./dirdat/my
TABLE sender.* ;
~
~
"dirprm/ext2my.prm" [New] 6L, 150C written
開啟抽取程式
GGSCI (test1) 33> start ext2my
Sending START request to MANAGER ...
EXTRACT EXT2MY starting
GGSCI (test1) 34> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT2MY 00:00:00 00:09:19
EXTRACT RUNNING E_TESTDB 00:00:00 00:00:07
EXTRACT RUNNING T_O01FDR 00:00:00 00:00:05
EXT2MY 為此次配置的oracle->mysql異構 抽取程式
E_TESTDB T_O01FDR 為之前配置oracle->oracle （可忽略）
程式狀態為running
如果程式狀態不是running，可以檢視報錯日誌
[oracle@test1 goldengate]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> tail -f ggserr.log
2014-11-05 13:28:24 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, eini.prm: PROCESS ABENDING.
2014-11-05 13:30:55 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext2my.
2014-11-05 13:36:36 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EINI starting.
2014-11-05 13:36:36 INFO OGG-00965 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EINI restarted automatically.
2014-11-05 13:36:36 INFO OGG-01017 Oracle GoldenGate Capture for Oracle, eini.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2014-11-05 13:36:36 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, eini.prm: EXTRACT EINI starting.
2014-11-05 13:36:36 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, eini.prm: Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2014-11-05 13:36:36 ERROR OGG-00049 Oracle GoldenGate Capture for Oracle, eini.prm: Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified.
2014-11-05 13:36:36 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, eini.prm: PROCESS ABENDING.
2014-11-05 14:02:41 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): edit params ./globals.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
9 源端建立defgen檔案並配置 （異構資料庫之間同步資料必須利用結構轉換檔案）
GGSCI (test1) 1> edit params defgen
新增以下內容：
defsfile ./dirdef/oratomy.def,purge
userid ogg, password 123456
table sender.*;
生成檔案：
[oracle@test1 ~]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> ./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, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2014-11-05 11:10:55
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: test1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 9257
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile ./dirdef/oratomy.def,purge
userid ogg, password ******
table sender.*;
Expanding wildcard sender.*:
Retrieving definition for SENDER.TEST
Definitions generated for 1 table in ./dirdef/oratomy.def
複製檔案到目標資料庫對應目錄：
[oracle@test1 goldengate]:testdb> cd dirdef/
[oracle@test1 dirdef]:testdb> ls
oratomy.def
[oracle@test1 dirdef]:testdb> scp oratomy.def root@192.168.9.144:/ggmysql/dirdef
root@192.168.9.144's password:
oratomy.def 100% 918 0.9KB/s 00:00
[oracle@test1 dirdef]:testdb>
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
10 源端：狀態
GGSCI (test1) 39> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT2MY 00:00:00 00:00:01
EXTRACT RUNNING E_TESTDB 00:00:00 00:00:01
EXTRACT RUNNING T_O01FDR 00:00:00 00:00:10
===============================================================================
目標端配置：
1 配置目標端環境變數
[root@dbadb tmp]# cd /root
[root@dbadb ~]# ll -a
total 220
dr-xr-x---. 22 root root 4096 Oct 30 23:37 .
dr-xr-xr-x. 28 root root 4096 Oct 31 03:18 ..
-rw-r--r--. 1 root root 147 Jul 10 23:28 \
[root@dbadb ~]# vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export GGHOME=/ggmysql
export PATH=$PATH:$GGHOME
~
~
~
".bash_profile" 14L, 225C written
[root@dbadb ~]#
[root@dbadb ~]# source .bash_profile
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
2 建立目錄/ggmysql
[root@dbadb ~]# mkdir /ggmysql
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
3 安裝ggs_121210_Linux_x64_MySQL_64bit.zip
[root@dbadb ~]# cd /tmp
[root@dbadb tmp]# ll
total 248416
-rw-r--r--. 1 root root 142619257 Nov 3 15:07 ggs_121210_Linux_x64_MySQL_64bit.zip
drwxr-x---. 2 oracle oinstall 4096 Oct 31 17:53 hsperfdata_oracle
drwx------. 2 root root 4096 Oct 30 23:37 keyring-FDY5u9
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 keyring-gxB03d
drwx------. 2 root root 4096 Oct 30 23:18 keyring-kIgrGO
drwx------. 2 root root 4096 Sep 28 22:21 keyring-tO56Uq
-rw-r--r--. 1 root root 21708 Oct 13 11:14 libaio-0.3.107-10.el6.x86_64.rpm
-rw-r--r--. 1 root root 23156366 Oct 16 15:31 MySQL-client-5.6.21-1.rhel5.x86_64.rpm
-rw-r--r--. 1 root root 88524802 Oct 16 15:43 MySQL-server-5.6.21-1.rhel5.x86_64.rpm
drwx------. 2 gdm gdm 4096 Oct 30 23:36 orbit-gdm
drwx------. 2 root root 4096 Oct 31 18:15 orbit-root
drwx------. 2 gdm gdm 4096 Oct 30 23:36 pulse-oLu0YfBAreEU
drwx------. 2 root root 4096 Oct 30 23:37 pulse-oZDqCnH2yFgj
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 pulse-Up2S1oVzRfXF
drwx------. 2 root root 4096 Oct 30 23:37 ssh-aNOMJk2023
[root@dbadb tmp]# cp ggs_121210_Linux_x64_MySQL_64bit.zip /ggmysql/
[root@dbadb tmp]# cd /ggmysql/
[root@dbadb ggmysql]# ll
total 139280
-rw-r-----. 1 root root 142619257 Nov 4 01:12 ggs_121210_Linux_x64_MySQL_64bit.zip
[root@dbadb ggmysql]# unzip ggs_121210_Linux_x64_MySQL_64bit.zip
Archive: ggs_121210_Linux_x64_MySQL_64bit.zip
inflating: ggs_Linux_x64_MySQL_64bit.tar
inflating: OGG-12.1.2.1.0-ReleaseNotes.pdf
inflating: OGG-12.1.2.1.0-README.txt
[root@dbadb ggmysql]#
[root@dbadb ggmysql]# pwd
/ggmysql
[root@dbadb ggmysql]# ls
ggs_121210_Linux_x64_MySQL_64bit.zip OGG-12.1.2.1.0-README.txt
ggs_Linux_x64_MySQL_64bit.tar OGG-12.1.2.1.0-ReleaseNotes.pdf
[root@dbadb ggmysql]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar
./
./libxerces-c.so.28
./libicuuc.so.48
./ggcmd
./pw_agent_util.sh
./libicudata.s
[root@dbadb ggmysql]# ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 20 2014 03:43:22
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (dbadb) 1> create subdirs
Creating subdirectories under current directory /ggmysql
Parameter files /ggmysql/dirprm: already exists
Report files /ggmysql/dirrpt: created
Checkpoint files /ggmysql/dirchk: created
Process status files /ggmysql/dirpcs: created
SQL script files /ggmysql/dirsql: created
Database definitions files /ggmysql/dirdef: created
Extract data files /ggmysql/dirdat: created
Temporary files /ggmysql/dirtmp: created
Credential store files /ggmysql/dircrd: created
Masterkey wallet files /ggmysql/dirwlt: created
Dump files /ggmysql/dirdmp: created
GGSCI (dbadb) 2>
安裝成功！
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
4 安裝mysql
[root@dbadb mysql]# cd /tmp
[root@dbadb tmp]# ll
total 248416
-rw-r--r--. 1 root root 142619257 Nov 3 15:07 ggs_121210_Linux_x64_MySQL_64bit.zip
drwxr-x---. 2 oracle oinstall 4096 Oct 31 17:53 hsperfdata_oracle
drwx------. 2 root root 4096 Oct 30 23:37 keyring-FDY5u9
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 keyring-gxB03d
drwx------. 2 root root 4096 Oct 30 23:18 keyring-kIgrGO
drwx------. 2 root root 4096 Sep 28 22:21 keyring-tO56Uq
-rw-r--r--. 1 root root 21708 Oct 13 11:14 libaio-0.3.107-10.el6.x86_64.rpm
-rw-r--r--. 1 root root 23156366 Oct 16 15:31 MySQL-client-5.6.21-1.rhel5.x86_64.rpm
-rw-r--r--. 1 root root 88524802 Oct 16 15:43 MySQL-server-5.6.21-1.rhel5.x86_64.rpm
drwx------. 2 gdm gdm 4096 Oct 30 23:36 orbit-gdm
drwx------. 2 root root 4096 Oct 31 18:15 orbit-root
drwx------. 2 gdm gdm 4096 Oct 30 23:36 pulse-oLu0YfBAreEU
drwx------. 2 root root 4096 Oct 30 23:37 pulse-oZDqCnH2yFgj
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 pulse-Up2S1oVzRfXF
drwx------. 2 root root 4096 Oct 30 23:37 ssh-aNOMJk2023
[root@dbadb tmp]# rpm -qa | grep mysql
[root@dbadb tmp]# yum remove mysql*
[root@dbadb tmp]# rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm
[root@dbadb tmp]# rpm -ivh MySQL-server-5.6.21-1.rhel5.x86_64.rpm
[root@dbadb tmp]# rpm -ivh MySQL-client-5.6.21-1.rhel5.x86_64.rpm
按照提示找2lmK2ShXJfpmA8rg初始密碼
[root@dbadb tmp]# mysqladmin -u root -p2lmK2ShXJfpmA8rg password 123456
Warning: Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
[root@dbadb tmp]#
[root@dbadb tmp]#
[root@dbadb tmp]# cd /var/lib/mysql/
[root@dbadb mysql]# ll
total 110612
-rw-rw----. 1 mysql mysql 12582912 Nov 4 01:29 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:29 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:29 ib_logfile1
drwx--x--x. 2 mysql mysql 4096 Nov 4 01:29 mysql
drwx------. 2 mysql mysql 4096 Nov 4 01:29 performance_schema
-rw-r--r--. 1 root root 113 Nov 4 01:29 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql 113 Nov 4 01:29 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql 4096 Nov 4 01:29 test
[root@dbadb mysql]# service mysql status;
ERROR! MySQL is not running
[root@dbadb mysql]# service mysql start
Starting MySQL... SUCCESS!
[root@dbadb mysql]# ll
total 110624
-rw-rw----. 1 mysql mysql 56 Nov 4 01:36 auto.cnf
-rw-r-----. 1 mysql root 2038 Nov 4 01:36 dbadb.err
-rw-rw----. 1 mysql mysql 6 Nov 4 01:36 dbadb.pid
-rw-rw----. 1 mysql mysql 12582912 Nov 4 01:36 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:29 ib_logfile1
drwx--x--x. 2 mysql mysql 4096 Nov 4 01:29 mysql
srwxrwxrwx. 1 mysql mysql 0 Nov 4 01:36 mysql.sock
drwx------. 2 mysql mysql 4096 Nov 4 01:29 performance_schema
-rw-r--r--. 1 root root 113 Nov 4 01:29 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql 113 Nov 4 01:29 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql 4096 Nov 4 01:29 test
[root@dbadb mysql]# mysqladmin -u root -p2lmK2ShXJfpmA8rg password 123456
Warning: Using a password on the command line interface can be insecure.
[root@dbadb mysql]#
[root@dbadb mysql]#
[root@dbadb mysql]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.11 sec)
mysql>
安裝成功！
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5 檢視源端複製過來的defgen檔案
[root@dbadb ~]# cd $GGHOME
[root@dbadb ggmysql]# pwd
/ggmysql
[root@dbadb ggmysql]# cd dirdef
[root@dbadb dirdef]# ls
oratomy.def
[root@dbadb dirdef]# cat oratomy.def
*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2014-11-05 11:10
*
* 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: windows-936
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 SENDER.TEST
Record length: 12
Syskey: 0
Columns: 1
ID 134 11 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 3
End of definition
[root@dbadb dirdef]#
檔案存在！！
6 配置mgr程式
[root@dbadb ggmysql]# ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 20 2014 03:43:22
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (dbadb) 1>
GGSCI (dbadb) 1> edit params mgr
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
~
~
~
"/ggmysql/dirprm/mgr.prm" 8L, 222C
GGSCI (dbadb) 2> edit param globals
checkpointtable sender.checkpoint
~
"/ggmysql/dirprm/globals.prm" 1L, 34C
7 建立checkpoint表
GGSCI (dbadb) 5> dblogin sourcedb sender userid root
Password:
Successfully logged into database.
GGSCI (dbadb DBLOGIN as root) 6> add checkpointtable sender.checkpoint
Successfully created checkpoint table sender.checkpoint.
GGSCI (dbadb DBLOGIN as root) 7> edit params ./GLOBALS #GLOBALS必須大寫，編輯GLOBALS需要推出ggsci再進入，輸入：
CHECKPOINTTABLE sender.checkpoint
~
~
~
"./GLOBALS" [New] 1L, 35C written
檢視CHECKPOINT TABLE
[root@dbadb ~]# mysql -u root -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database sender;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sender |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use sender;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_sender |
+------------------+
| checkpoint |
| checkpoint_lox |
+------------------+
2 rows in set (0.00 sec)
checkpoint建立成功！！
8 建立replicat恢復程式
GGSCI (dbadb DBLOGIN as root) 8> add replicat rep2my,exttrail /ggmysql/dirdat/my,checkpointtable sender.checkpoint
REPLICAT added.
GGSCI (dbadb DBLOGIN as root) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP2MY 00:00:00 00:00:08
GGSCI (dbadb DBLOGIN as root) 11> edit params rep2my
REPLICAT rep2my
sourcedefs /ggmysql/dirdef/oratomy.def
--APPLYNOOPUPDATES
--HANDLECOLLISIONS
SOURCEDB sender,userid root,password 123456
discardfile /ggmysql/dirrpt/rep2my_discard.log,megabytes 10
MAP sender.*, TARGET sender.*;
~
~
~
"dirprm/rep2my.prm" [New] 7L, 229C written
啟動程式：
GGSCI (dbadb DBLOGIN as root) 12> start REPLICAT REP2MY
Sending START request to MANAGER ...
REPLICAT REP2MY starting
GGSCI (dbadb DBLOGIN as root) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2MY 00:00:00 00:00:03
===============================================================================
初始化資料並做同步測試
（先把資料初始化好，就是兩邊資料一樣）
源端：
SQL> select * from test;
ID
----------
1
2
2 rows selected.
SQL>
目標端：
mysql> select * from TEST;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql>
確定初始資料一致，MYSQL 中，表名有大小寫區分，mysql建表TEST
環境：
源：
192.168.9.142 oracle ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip oracle11g
目標：
192.168.9.144 mysql ggs_121210_Linux_x64_MySQL_64bit.zip mysql5.6
下載地址：
同步的表：sender使用者下的test表
源庫已安裝ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip並已配置gg同構oracle->oracle(無影響)
===============================================================================
源端：
1 安裝ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip （略）
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
2 建立複製使用者ogg並授予許可權
SQL> create user ogg identified by 123456 default tablespace users temporary tablespace temp;
一堆許可權（測試環境之間：SQL>grant dba to ogg ）
SQL> grant connect,resource,unlimited tablespace to ogg;
SQL> grant create session,alter session to ogg;
SQL> grant execute on utl_file to ogg;
SQL> grant select any dictionary, select any table to ogg;
SQL> grant alter any table to ogg;
SQL> grant flashback any table to ogg;
SQL> grant select any transaction to ogg;
SQL> grant sysdba to ogg;
SQL> grant create table,insert any table,lock any table to ogg;
SQL> grant execute on dbms_flashback to ogg;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
3 建立同步的使用者和同步的表
SQL> create user sender identified by 123456;
SQL> create table test (id int);
SQL> alter table test add constraint pk_id primary key(id);
往表內插入資料：
SQL>
begin
for i in 1..5 loop
insert into test values(i);
end loop;
end;
/
SQL>select * from test；
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
4 開啟supplementary log
SQL> select force_logging, supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
YES YES
不是yes以下命令修改：
SQL> alter database force logging;
SQL> alter database add supplemental log data;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5 支援sequence
GGSCI (test1) 38> edit params ./globals
在統計模式下輸入並儲存：
ggschema ogg
~
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
6 支援ddl複製 （建議不配這個）
sql> @marker_setup.sql prompt: ogg
[oracle@test1 ~]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb>
[oracle@test1 goldengate]:testdb> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 5 14:06:58 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql prompt
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> alter system set recyclebin=off deferred scope=both; #必須，針對ddl複製
SQL> @ddl_setup.sql prompt: ogg
SQL> @role_setup.sql : ogg
SQL> grant GGS_GGSUSER_ROLE to ogg;
SQL> @ddl_enable.sql
10g需要安裝dbms_share_pool包：
sql> @?/rdbms/admin/dbmspool.sql sql> @ddl_pin ogg;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
7 配置MANAGER
GGSCI (test1) 3> edit param mgr
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
~
~
~
"dirprm/mgr.prm" [New] 8L, 222C written
GGSCI (test1) 4> start mgr
Manager started.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
8 新增extract
GGSCI (test1) 11> add extract ext2my,tranlog,begin now
EXTRACT added.
GGSCI (test1) 12> add exttrail ./dirdat/my,extract ext2my,megabytes 100
EXTTRAIL added.
GGSCI (test1) 13> edit params ext2my
新增以下內容：
EXTRACT ext2my
USERID ogg, PASSWORD 123456
TRANLOGOPTIONS EXCLUDEUSER ogg
RMTHOST 192.168.9.144, MGRPORT 7809
RMTTRAIL ./dirdat/my
TABLE sender.* ;
~
~
"dirprm/ext2my.prm" [New] 6L, 150C written
開啟抽取程式
GGSCI (test1) 33> start ext2my
Sending START request to MANAGER ...
EXTRACT EXT2MY starting
GGSCI (test1) 34> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT2MY 00:00:00 00:09:19
EXTRACT RUNNING E_TESTDB 00:00:00 00:00:07
EXTRACT RUNNING T_O01FDR 00:00:00 00:00:05
EXT2MY 為此次配置的oracle->mysql異構 抽取程式
E_TESTDB T_O01FDR 為之前配置oracle->oracle （可忽略）
程式狀態為running
如果程式狀態不是running，可以檢視報錯日誌
[oracle@test1 goldengate]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> tail -f ggserr.log
2014-11-05 13:28:24 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, eini.prm: PROCESS ABENDING.
2014-11-05 13:30:55 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext2my.
2014-11-05 13:36:36 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EINI starting.
2014-11-05 13:36:36 INFO OGG-00965 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EINI restarted automatically.
2014-11-05 13:36:36 INFO OGG-01017 Oracle GoldenGate Capture for Oracle, eini.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2014-11-05 13:36:36 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, eini.prm: EXTRACT EINI starting.
2014-11-05 13:36:36 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, eini.prm: Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2014-11-05 13:36:36 ERROR OGG-00049 Oracle GoldenGate Capture for Oracle, eini.prm: Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified.
2014-11-05 13:36:36 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, eini.prm: PROCESS ABENDING.
2014-11-05 14:02:41 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): edit params ./globals.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
9 源端建立defgen檔案並配置 （異構資料庫之間同步資料必須利用結構轉換檔案）
GGSCI (test1) 1> edit params defgen
新增以下內容：
defsfile ./dirdef/oratomy.def,purge
userid ogg, password 123456
table sender.*;
生成檔案：
[oracle@test1 ~]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> ./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, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2014-11-05 11:10:55
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: test1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 9257
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile ./dirdef/oratomy.def,purge
userid ogg, password ******
table sender.*;
Expanding wildcard sender.*:
Retrieving definition for SENDER.TEST
Definitions generated for 1 table in ./dirdef/oratomy.def
複製檔案到目標資料庫對應目錄：
[oracle@test1 goldengate]:testdb> cd dirdef/
[oracle@test1 dirdef]:testdb> ls
oratomy.def
[oracle@test1 dirdef]:testdb> scp oratomy.def root@192.168.9.144:/ggmysql/dirdef
root@192.168.9.144's password:
oratomy.def 100% 918 0.9KB/s 00:00
[oracle@test1 dirdef]:testdb>
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
10 源端：狀態
GGSCI (test1) 39> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT2MY 00:00:00 00:00:01
EXTRACT RUNNING E_TESTDB 00:00:00 00:00:01
EXTRACT RUNNING T_O01FDR 00:00:00 00:00:10
===============================================================================
目標端配置：
1 配置目標端環境變數
[root@dbadb tmp]# cd /root
[root@dbadb ~]# ll -a
total 220
dr-xr-x---. 22 root root 4096 Oct 30 23:37 .
dr-xr-xr-x. 28 root root 4096 Oct 31 03:18 ..
-rw-r--r--. 1 root root 147 Jul 10 23:28 \
[root@dbadb ~]# vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export GGHOME=/ggmysql
export PATH=$PATH:$GGHOME
~
~
~
".bash_profile" 14L, 225C written
[root@dbadb ~]#
[root@dbadb ~]# source .bash_profile
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
2 建立目錄/ggmysql
[root@dbadb ~]# mkdir /ggmysql
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
3 安裝ggs_121210_Linux_x64_MySQL_64bit.zip
[root@dbadb ~]# cd /tmp
[root@dbadb tmp]# ll
total 248416
-rw-r--r--. 1 root root 142619257 Nov 3 15:07 ggs_121210_Linux_x64_MySQL_64bit.zip
drwxr-x---. 2 oracle oinstall 4096 Oct 31 17:53 hsperfdata_oracle
drwx------. 2 root root 4096 Oct 30 23:37 keyring-FDY5u9
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 keyring-gxB03d
drwx------. 2 root root 4096 Oct 30 23:18 keyring-kIgrGO
drwx------. 2 root root 4096 Sep 28 22:21 keyring-tO56Uq
-rw-r--r--. 1 root root 21708 Oct 13 11:14 libaio-0.3.107-10.el6.x86_64.rpm
-rw-r--r--. 1 root root 23156366 Oct 16 15:31 MySQL-client-5.6.21-1.rhel5.x86_64.rpm
-rw-r--r--. 1 root root 88524802 Oct 16 15:43 MySQL-server-5.6.21-1.rhel5.x86_64.rpm
drwx------. 2 gdm gdm 4096 Oct 30 23:36 orbit-gdm
drwx------. 2 root root 4096 Oct 31 18:15 orbit-root
drwx------. 2 gdm gdm 4096 Oct 30 23:36 pulse-oLu0YfBAreEU
drwx------. 2 root root 4096 Oct 30 23:37 pulse-oZDqCnH2yFgj
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 pulse-Up2S1oVzRfXF
drwx------. 2 root root 4096 Oct 30 23:37 ssh-aNOMJk2023
[root@dbadb tmp]# cp ggs_121210_Linux_x64_MySQL_64bit.zip /ggmysql/
[root@dbadb tmp]# cd /ggmysql/
[root@dbadb ggmysql]# ll
total 139280
-rw-r-----. 1 root root 142619257 Nov 4 01:12 ggs_121210_Linux_x64_MySQL_64bit.zip
[root@dbadb ggmysql]# unzip ggs_121210_Linux_x64_MySQL_64bit.zip
Archive: ggs_121210_Linux_x64_MySQL_64bit.zip
inflating: ggs_Linux_x64_MySQL_64bit.tar
inflating: OGG-12.1.2.1.0-ReleaseNotes.pdf
inflating: OGG-12.1.2.1.0-README.txt
[root@dbadb ggmysql]#
[root@dbadb ggmysql]# pwd
/ggmysql
[root@dbadb ggmysql]# ls
ggs_121210_Linux_x64_MySQL_64bit.zip OGG-12.1.2.1.0-README.txt
ggs_Linux_x64_MySQL_64bit.tar OGG-12.1.2.1.0-ReleaseNotes.pdf
[root@dbadb ggmysql]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar
./
./libxerces-c.so.28
./libicuuc.so.48
./ggcmd
./pw_agent_util.sh
./libicudata.s
[root@dbadb ggmysql]# ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 20 2014 03:43:22
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (dbadb) 1> create subdirs
Creating subdirectories under current directory /ggmysql
Parameter files /ggmysql/dirprm: already exists
Report files /ggmysql/dirrpt: created
Checkpoint files /ggmysql/dirchk: created
Process status files /ggmysql/dirpcs: created
SQL script files /ggmysql/dirsql: created
Database definitions files /ggmysql/dirdef: created
Extract data files /ggmysql/dirdat: created
Temporary files /ggmysql/dirtmp: created
Credential store files /ggmysql/dircrd: created
Masterkey wallet files /ggmysql/dirwlt: created
Dump files /ggmysql/dirdmp: created
GGSCI (dbadb) 2>
安裝成功！
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
4 安裝mysql
[root@dbadb mysql]# cd /tmp
[root@dbadb tmp]# ll
total 248416
-rw-r--r--. 1 root root 142619257 Nov 3 15:07 ggs_121210_Linux_x64_MySQL_64bit.zip
drwxr-x---. 2 oracle oinstall 4096 Oct 31 17:53 hsperfdata_oracle
drwx------. 2 root root 4096 Oct 30 23:37 keyring-FDY5u9
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 keyring-gxB03d
drwx------. 2 root root 4096 Oct 30 23:18 keyring-kIgrGO
drwx------. 2 root root 4096 Sep 28 22:21 keyring-tO56Uq
-rw-r--r--. 1 root root 21708 Oct 13 11:14 libaio-0.3.107-10.el6.x86_64.rpm
-rw-r--r--. 1 root root 23156366 Oct 16 15:31 MySQL-client-5.6.21-1.rhel5.x86_64.rpm
-rw-r--r--. 1 root root 88524802 Oct 16 15:43 MySQL-server-5.6.21-1.rhel5.x86_64.rpm
drwx------. 2 gdm gdm 4096 Oct 30 23:36 orbit-gdm
drwx------. 2 root root 4096 Oct 31 18:15 orbit-root
drwx------. 2 gdm gdm 4096 Oct 30 23:36 pulse-oLu0YfBAreEU
drwx------. 2 root root 4096 Oct 30 23:37 pulse-oZDqCnH2yFgj
drwx------. 2 oracle oinstall 4096 Jul 10 06:56 pulse-Up2S1oVzRfXF
drwx------. 2 root root 4096 Oct 30 23:37 ssh-aNOMJk2023
[root@dbadb tmp]# rpm -qa | grep mysql
[root@dbadb tmp]# yum remove mysql*
[root@dbadb tmp]# rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm
[root@dbadb tmp]# rpm -ivh MySQL-server-5.6.21-1.rhel5.x86_64.rpm
[root@dbadb tmp]# rpm -ivh MySQL-client-5.6.21-1.rhel5.x86_64.rpm
按照提示找2lmK2ShXJfpmA8rg初始密碼
[root@dbadb tmp]# mysqladmin -u root -p2lmK2ShXJfpmA8rg password 123456
Warning: Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
[root@dbadb tmp]#
[root@dbadb tmp]#
[root@dbadb tmp]# cd /var/lib/mysql/
[root@dbadb mysql]# ll
total 110612
-rw-rw----. 1 mysql mysql 12582912 Nov 4 01:29 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:29 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:29 ib_logfile1
drwx--x--x. 2 mysql mysql 4096 Nov 4 01:29 mysql
drwx------. 2 mysql mysql 4096 Nov 4 01:29 performance_schema
-rw-r--r--. 1 root root 113 Nov 4 01:29 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql 113 Nov 4 01:29 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql 4096 Nov 4 01:29 test
[root@dbadb mysql]# service mysql status;
ERROR! MySQL is not running
[root@dbadb mysql]# service mysql start
Starting MySQL... SUCCESS!
[root@dbadb mysql]# ll
total 110624
-rw-rw----. 1 mysql mysql 56 Nov 4 01:36 auto.cnf
-rw-r-----. 1 mysql root 2038 Nov 4 01:36 dbadb.err
-rw-rw----. 1 mysql mysql 6 Nov 4 01:36 dbadb.pid
-rw-rw----. 1 mysql mysql 12582912 Nov 4 01:36 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov 4 01:29 ib_logfile1
drwx--x--x. 2 mysql mysql 4096 Nov 4 01:29 mysql
srwxrwxrwx. 1 mysql mysql 0 Nov 4 01:36 mysql.sock
drwx------. 2 mysql mysql 4096 Nov 4 01:29 performance_schema
-rw-r--r--. 1 root root 113 Nov 4 01:29 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql 113 Nov 4 01:29 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql 4096 Nov 4 01:29 test
[root@dbadb mysql]# mysqladmin -u root -p2lmK2ShXJfpmA8rg password 123456
Warning: Using a password on the command line interface can be insecure.
[root@dbadb mysql]#
[root@dbadb mysql]#
[root@dbadb mysql]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.11 sec)
mysql>
安裝成功！
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5 檢視源端複製過來的defgen檔案
[root@dbadb ~]# cd $GGHOME
[root@dbadb ggmysql]# pwd
/ggmysql
[root@dbadb ggmysql]# cd dirdef
[root@dbadb dirdef]# ls
oratomy.def
[root@dbadb dirdef]# cat oratomy.def
*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2014-11-05 11:10
*
* 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: windows-936
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 SENDER.TEST
Record length: 12
Syskey: 0
Columns: 1
ID 134 11 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 3
End of definition
[root@dbadb dirdef]#
檔案存在！！
6 配置mgr程式
[root@dbadb ggmysql]# ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 20 2014 03:43:22
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (dbadb) 1>
GGSCI (dbadb) 1> edit params mgr
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
~
~
~
"/ggmysql/dirprm/mgr.prm" 8L, 222C
GGSCI (dbadb) 2> edit param globals
checkpointtable sender.checkpoint
~
"/ggmysql/dirprm/globals.prm" 1L, 34C
7 建立checkpoint表
GGSCI (dbadb) 5> dblogin sourcedb sender userid root
Password:
Successfully logged into database.
GGSCI (dbadb DBLOGIN as root) 6> add checkpointtable sender.checkpoint
Successfully created checkpoint table sender.checkpoint.
GGSCI (dbadb DBLOGIN as root) 7> edit params ./GLOBALS #GLOBALS必須大寫，編輯GLOBALS需要推出ggsci再進入，輸入：
CHECKPOINTTABLE sender.checkpoint
~
~
~
"./GLOBALS" [New] 1L, 35C written
檢視CHECKPOINT TABLE
[root@dbadb ~]# mysql -u root -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database sender;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sender |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use sender;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_sender |
+------------------+
| checkpoint |
| checkpoint_lox |
+------------------+
2 rows in set (0.00 sec)
checkpoint建立成功！！
8 建立replicat恢復程式
GGSCI (dbadb DBLOGIN as root) 8> add replicat rep2my,exttrail /ggmysql/dirdat/my,checkpointtable sender.checkpoint
REPLICAT added.
GGSCI (dbadb DBLOGIN as root) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP2MY 00:00:00 00:00:08
GGSCI (dbadb DBLOGIN as root) 11> edit params rep2my
REPLICAT rep2my
sourcedefs /ggmysql/dirdef/oratomy.def
--APPLYNOOPUPDATES
--HANDLECOLLISIONS
SOURCEDB sender,userid root,password 123456
discardfile /ggmysql/dirrpt/rep2my_discard.log,megabytes 10
MAP sender.*, TARGET sender.*;
~
~
~
"dirprm/rep2my.prm" [New] 7L, 229C written
啟動程式：
GGSCI (dbadb DBLOGIN as root) 12> start REPLICAT REP2MY
Sending START request to MANAGER ...
REPLICAT REP2MY starting
GGSCI (dbadb DBLOGIN as root) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2MY 00:00:00 00:00:03
===============================================================================
初始化資料並做同步測試
（先把資料初始化好，就是兩邊資料一樣）
源端：
SQL> select * from test;
ID
----------
1
2
2 rows selected.
SQL>
目標端：
mysql> select * from TEST;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql>
確定初始資料一致，MYSQL 中，表名有大小寫區分，mysql建表TEST
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1321491/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Goldengate異構 mysql——>oraclGoMySql
- Oracle GoldenGate 異構平臺同步（Mysql到Oracle）OracleGoMySql
- GoldenGate表異構的解決方法Go
- GoldenGate在異構環境下的複製Go
- GoldenGate搭建Go
- ORACLE-體系結構Oracle
- Oracle GoldenGate環境搭建OracleGo
- 配置ogg異構oracle到mysqlOracleMySql
- mysql高可用架構MHA搭建MySql架構
- GoldenGate常見異常處理Go
- MySQL異構資料同步–tair為例MySqlAI
- Oracle->Mysql dblink 建立詳細過程OracleMySql
- mysql高可用架構MHA搭建（centos7+mysql5.7.28）MySql架構CentOS
- mysql goldengate同步 簡單配置MySqlGo
- MySQL 拼接Insert批量同步異構表資料MySql
- Goldengate_目錄結構Go
- EOS 區塊鏈資料實時異構到 MySQL區塊鏈MySql
- Linux下配置ORACLE-MYSQL 資料庫異構LinuxOracleMySql資料庫
- GoldenGate簡單複製環境的搭建Go
- GoldenGate異種資料庫之間的複製Go資料庫
- MYSQL搭建MySql
- 從 SQL Server 到 MySQL （一）：異構資料庫遷移ServerMySql資料庫
- 使用goldengate從mysql同步資料到oracleGoMySqlOracle
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- 配置GoldenGate捕獲Replicat端資料應用異常Go
- 基於bin-log&position搭建主從架構MySQL架構MySql
- 搭建異構 CPU 組成的邊緣計算 Kubernetes 叢集
- MySQL + Oracle GoldenGate + OGG Application AdpaterMySqlOracleGoAPP
- docker 搭建 MySQLDockerMySql
- 搭建oracle 11.2.0.4環境下的goldengate複製OracleGo
- Oracle-多表查詢Oracle
- Oracle-行列轉換Oracle
- ORACLE- Secure Backup DocumentationOracle
- MySQL異常處理MySql
- mysql 連線異常MySql
- Mysql主從架構搭建的時候遇到的問題MySql架構
- Golang 針對 MySQL 資料庫表結構的差異 SQL 工具GolangMySql資料庫
- wdcp環境innodb結構mysql資料庫表異常需要重建MySql資料庫