文档课题：运用OGG实现oracle 19c(非多租户)到mysql 8的数据同步.

1、架构信息

流程解析：

 

Extract过程

Extract进程source解析操作，并写入到Trail文件中

Data Pump从Trail文件中读取操作数据，并通过网络发送

 

Replicat过程

Collector接收Data Pump发送过来的数据，并写入到Trail文件

Replicat读取Trail文件，并将数据写入到Target端

 

2、源端前期准备

2.1、建ogg系统目录

[root@leo-oracle-19c:~]# mkdir -p /ogg

[root@leo-oracle-19c:~]# chown -R oracle:oinstall /ogg

[root@leo-oracle-19c:~]# chmod -R 775 /ogg

 

2.2、修改环境变量

[oracle@leo-oracle-19c ~]$ vi .bash_profile

添加如下内容：

export GG_HOME=/ogg/oggsource

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

alias ggsci='cd $GG_HOME;ggsci'

 

[oracle@leo-oracle-19c ~]$ source .bash_profile

 

[root@leo-oracle-19c:~]# mkdir -p /ogg/oggsource

[root@leo-oracle-19c:~]# chown -R oracle:oinstall /ogg/oggsource

 

[root@leo-oracle-19c:~]# ls -ltr / | grep -w ogg

drwxrwxr-x    3 oracle oinstall         23 Aug  8 19:36 ogg

[root@leo-oracle-19c:~]# ls -ltr /ogg

total 0

drwxr-xr-x 2 oracle oinstall 6 Jul 24 16:14 oggsource

 

2.3、安装ogg

2.3.1、上传ogg安装包

[oracle@leo-oracle-19c ~]$ mkdir -p /u01/setup/oggpkg

sftp> lcd F:\installmedium\ogg

sftp> cd /u01/setup/oggpkg

sftp> put 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip

[oracle@leo-oracle-19c oggpkg]$ exit

logout

[root@leo-oracle-19c:~]# cd /u01/setup/oggpkg

[root@leo-oracle-19c:/u01/setup/oggpkg]# chown oracle:oinstall 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip

[root@leo-oracle-19c:~]# su - oracle

[oracle@leo-oracle-19c ~]$ cd /u01/setup/oggpkg/

[oracle@leo-oracle-19c oggpkg]$ unzip -q 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip

[oracle@leo-oracle-19c oggpkg]$ cd fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/

 

[oracle@leo-oracle-19c response]$ vi oggcore.rsp

--更新如下内容

INSTALL_OPTION=ora19c

SOFTWARE_LOCATION=/ogg/oggsource

 

2.3.2、静默安装ogg

[oracle@leo-oracle-19c Disk1]$ cd /u01/setup/oggpkg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1

[oracle@leo-oracle-19c Disk1]$ ./runInstaller -silent -nowait -responseFile /u01/setup/oggpkg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp

Starting Oracle Universal Installer...

 

Checking Temp space: must be greater than 120 MB.   Actual 47267 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 7167 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2024-07-24_04-18-49PM. Please wait ...[oracle@leo-oracle-19c Disk1]$ You can find the log of this install session at:

 /u01/app/oraInventory/logs/installActions2024-07-24_04-18-49PM.log

Successfully Setup Software.

The installation of Oracle GoldenGate Core was successful.

Please check '/u01/app/oraInventory/logs/silentInstall2024-07-24_04-18-49PM.log' for more details.

 

2.4、创建ogg子目录

[oracle@leo-oracle-19c oggsource]$ cd /ogg/oggsource

[oracle@leo-oracle-19c oggsource]$ ./ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO

Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23

Operating system character set identified as UTF-8.

 

Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.

 

 

 

GGSCI (leo-oracle-19c) 1> create subdirs

 

Creating subdirectories under current directory /ogg/oggsource

 

Parameter file                 /ogg/oggsource/dirprm: created.

Report file                    /ogg/oggsource/dirrpt: created.

Checkpoint file                /ogg/oggsource/dirchk: created.

Process status files           /ogg/oggsource/dirpcs: created.

SQL script files               /ogg/oggsource/dirsql: created.

Database definitions files     /ogg/oggsource/dirdef: created.

Extract data files             /ogg/oggsource/dirdat: created.

Temporary files                /ogg/oggsource/dirtmp: created.

Credential store files         /ogg/oggsource/dircrd: created.

Master encryption key wallet files /ogg/oggsource/dirwlt: created.

Dump files                     /ogg/oggsource/dirdmp: created.

 

重要目录说明：

dirchk：存放检查点(checkpoint)文件

dirdat：存放trail文件

dirdef：存放通过defgen工具生成的源或目标端数据定义文件

dirpcs：存放进程状态文件

dirprm：存放配置参数文件

dirrpt：存放进程报告文件

dirsql：存放sql脚本文件

dirtmp：当事务所需要的内存超过已分配的内存时，默认存储在此目录

 

2.5、数据库配置

2.5.1、开启归档、附加日志等功能

--oracle数据库配置

a、开启数据库归档

b、开启数据库级别附加日志

c、开启强制日志

d、设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE

e、创建OGG用户，包括源端用户、目标端用户以及OGG抽取用户

 

sys@TMIS> col name for a10

sys@TMIS> col force_logging for a30

sys@TMIS> select name,supplemental_log_data_min,force_logging,log_mode from v$database;

 

NAME       SUPPLEME FORCE_LOGGING                  LOG_MODE

---------- -------- ------------------------------ ------------

TMIS       NO       NO                             NOARCHIVELOG

 

--开启最小补充日志.

sys@TMIS> alter database add supplemental log data;

 

Database altered.

 

--在数据库级别开启所有列的补充日志.

sys@TMIS> alter database add supplemental log data (all) columns;

 

Database altered.

 

--开启强制日志

sys@TMIS> alter database force logging;

 

Database altered.

 

--开启ogg replication参数

sys@TMIS> alter system set enable_goldengate_replication=true;

 

System altered.

 

sys@TMIS> host mkdir -p /u01/app/oracle/archivelog

sys@TMIS> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';

 

System altered.

 

sys@TMIS> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TMIS> startup mount;

ORACLE instance started.

 

Total System Global Area 2466249664 bytes

Fixed Size                  8899520 bytes

Variable Size             536870912 bytes

Database Buffers         1912602624 bytes

Redo Buffers                7876608 bytes

Database mounted.

sys@TMIS> alter database archivelog;

 

Database altered.

 

sys@TMIS> alter database open;

 

Database altered.

 

sys@TMIS> select name,supplemental_log_data_min,force_logging,log_mode from v$database;

 

NAME       SUPPLEME FORCE_LOGGING                  LOG_MODE

---------- -------- ------------------------------ ------------

TMIS       YES      YES                            ARCHIVELOG

 

2.5.2、建数据库ogg用户

sys@TMIS> create user ogg identified by ogg123;

 

User created.

 

sys@TMIS> grant dba to ogg;

 

Grant succeeded.

 

sys@TMIS> grant select any dictionary to ogg;

 

Grant succeeded.

 

sys@TMIS> grant execute on sys.dbms_lock to ogg;

 

Grant succeeded.

 

sys@TMIS> grant select any transaction to ogg;

 

Grant succeeded.

 

sys@TMIS> grant select any table to ogg;

 

Grant succeeded.

 

sys@TMIS> grant flashback any table to ogg;

 

Grant succeeded.

 

sys@TMIS> grant alter any table to ogg;

 

Grant succeeded.

 

sys@TMIS> exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE);

 

PL/SQL procedure successfully completed.

 

2.5.3、建业务用户

sys@TMIS> create user alina identified by alina123;

 

User created.

 

sys@TMIS> grant dba to alina;

 

Grant succeeded.

 

sys@TMIS> grant select any dictionary to alina;

 

Grant succeeded.

 

sys@TMIS> grant execute on sys.dbms_lock to alina;

 

Grant succeeded.

 

2.6、ogg配置

GGSCI (leo-oracle-19c) 1> edit params mgr

添加如下内容：

PORT 7809

 

GGSCI (leo-oracle-19c) 3> add credentialstore

 

Credential store created.

 

GGSCI (leo-oracle-19c) 4> alter credentialstore add user ogg@192.168.133.101/tmis, password ogg123 alias ora19c

 

Credential store altered.

 

GGSCI (leo-oracle-19c) 5> info credentialstore

 

Reading from credential store:

 

Default domain: OracleGoldenGate

 

  Alias: ora19c

  Userid: ogg@192.168.133.101/tmis

 

GGSCI (leo-oracle-19c) 6> dblogin useridalias ora19c

Successfully logged into database.

 

GGSCI (leo-oracle-19c as ogg@tmis) 7> add schematrandata alina

 

2024-07-24 18:00:47  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "alina".

 

2024-07-24 18:00:47  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "alina".

 

2024-07-24 18:00:47  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema "alina".

 

GGSCI (leo-oracle-19c as ogg@tmis) 8> info schematrandata alina

 

2024-07-24 18:01:03  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "ALINA".

 

2024-07-24 18:01:03  INFO    OGG-01980  Schema level supplemental logging is enabled on schema "ALINA" for all scheduling columns.

 

GGSCI (leo-oracle-19c as ogg@tmis) 9> list tables alina.*

 

Found 0 tables matching list criteria.

 

3、目标端前期准备

3.1、安装mysql

说明：此处安装mysql 8.0.27，具体安装步骤不再赘述.

 

注意：目标端MySQL数据库安装时注意参数lower_case_table_names在初始化时指定为1，使数据库名、表名大小写不敏感.

 

3.2、关闭防火墙

[root@leo-mysql8-ogg ~]# systemctl stop firewalld

[root@leo-mysql8-ogg ~]# systemctl disable firewalld

[root@leo-mysql8-ogg ~]# systemctl status firewalld

 

说明：防火墙开启对后面ogg进程会造成影响.

 

3.3、建系统ogg用户

[root@leo-mysql8-ogg ~]# groupadd ogg

[root@leo-mysql8-ogg ~]# useradd -g ogg ogg

[root@leo-mysql8-ogg ~]# id ogg

uid=1002(ogg) gid=1002(ogg) groups=1002(ogg)

[root@leo-mysql8-ogg ~]# echo ogg | passwd --stdin ogg

[root@leo-mysql8-ogg ~]# mkdir -p /ogg/oggtarget

[root@leo-mysql8-ogg ~]# chown ogg:ogg /ogg/oggtarget

 

3.4、安装ogg

3.4.1、上传安装包

sftp> lcd F:\installmedium\ogg

sftp> cd /home/ogg

sftp> put 213000_ggs_Linux_x64_MySQL_64bit.zip

 

3.4.2、解压安装包

[root@leo-mysql8-ogg ~]# cd /home/ogg

[root@leo-mysql8-ogg ogg]# chown ogg:ogg 213000_ggs_Linux_x64_MySQL_64bit.zip

[root@leo-mysql-ogg ~]# su - ogg

[ogg@leo-mysql8-ogg ~]$ unzip -q 213000_ggs_Linux_x64_MySQL_64bit.zip

[ogg@leo-mysql8-ogg ~]$ ls -tlr

total 438728

-rw-rw-r--. 1 ogg ogg 360540160 Jul 29  2021 ggs_Linux_x64_MySQL_64bit.tar

-rw-r--r--. 1 ogg ogg    306395 Aug 11  2021 oracle-goldengate-release-notes_21.3.pdf

-rw-r--r--. 1 ogg ogg      2409 Aug 11  2021 OGG-21.3.0.0-README.txt

-rw-r--r--. 1 ogg ogg  88401830 Jul 15 22:23 213000_ggs_Linux_x64_MySQL_64bit.zip

 

[ogg@leo-mysql8-ogg ~]$ tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C /ogg/oggtarget/

 

3.4.3、编译环境变量

[ogg@leo-mysql8-ogg ~]$ vi .bash_profile

--添加如下内容

export GG_HOME=/ogg/oggtarget

export PATH=$PATH:$HOME/bin:$GG_HOME

alias ggsci='cd $GG_HOME;ggsci'

 

[ogg@leo-mysql8-ogg ~]$ source .bash_profile

 

3.4.4、建ogg工作子目录

[ogg@leo-mysql8-ogg ~]$ cd $GG_HOME

[ogg@leo-mysql8-ogg oggtarget]$ ggsci

 

Oracle GoldenGate Command Interpreter for MySQL

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047

Oracle Linux 7, x64, 64bit (optimized), MySQL  on Jul 28 2021 18:17:46

Operating system character set identified as UTF-8.

 

Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.

 

 

 

GGSCI (leo-mysql8-ogg) 1> create subdirs

 

Creating subdirectories under current directory /ogg/oggtarget

 

Parameter file                 /ogg/oggtarget/dirprm: created.

Report file                    /ogg/oggtarget/dirrpt: created.

Checkpoint file                /ogg/oggtarget/dirchk: created.

Process status files           /ogg/oggtarget/dirpcs: created.

SQL script files               /ogg/oggtarget/dirsql: created.

Database definitions files     /ogg/oggtarget/dirdef: created.

Extract data files             /ogg/oggtarget/dirdat: created.

Temporary files                /ogg/oggtarget/dirtmp: created.

Credential store files         /ogg/oggtarget/dircrd: created.

Master encryption key wallet files /ogg/oggtarget/dirwlt: created.

Dump files                     /ogg/oggtarget/dirdmp: created.

 

3.4.5、mgr进程配置

GGSCI (leo-mysql8-ogg) 2> edit params mgr

--添加如下内容

port 8809

ACCESSRULE, PROG *,IPADDR *, ALLOW

GGSCI (leo-mysql8-ogg) 4> start mgr

Manager started.

 

 

GGSCI (leo-mysql8-ogg) 5> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

 

4、源端建测试数据

sys@TMIS> conn alina/alina123

Connected.

alina@TMIS> create table fruits

(

f_id varchar2(10) not null,

s_id number(38) not null,

f_name varchar2(255) not null,

f_price number(8,2) not null,

primary key(f_id)

);

 

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values('a1',101,'apple',5.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('b1',101,'blackberry',10.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('bs1',102,'orange',11.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('bs2',105,'melon',8.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('t1',102,'banana',10.3);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('t2',102,'grape',5.3);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('o2',103,'coconut',9.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('c0',101,'cherry',3.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('a2',103,'apricot',2.2);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('l2',104,'lemon',6.4);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('b2',104,'berry',7.6);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('m1',106,'mango',15.7);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('m2',105,'xbabay',2.6);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('t4',107,'xbababa',3.6);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('m3',105,'xxtt',11.6);

alina@TMIS> insert into fruits (f_id,s_id,f_name,f_price) values ('b5',107,'xxxx',3.6);

alina@TMIS> commit;

alina@TMIS> col F_NAME for a30

alina@TMIS> select * from fruits;

 

F_ID             S_ID F_NAME                            F_PRICE

---------- ---------- ------------------------------ ----------

a1                101 apple                                 5.2

b1                101 blackberry                           10.2

bs1               102 orange                               11.2

bs2               105 melon                                 8.2

t1                102 banana                               10.3

t2                102 grape                                 5.3

o2                103 coconut                               9.2

c0                101 cherry                                3.2

a2                103 apricot                               2.2

l2                104 lemon                                 6.4

b2                104 berry                                 7.6

m1                106 mango                                15.7

m2                105 xbabay                                2.6

t4                107 xbababa                               3.6

m3                105 xxtt                                 11.6

b5                107 xxxx                                  3.6

 

16 rows selected.

 

alina@TMIS> create table books

(

bk_id number(38) not null primary key,

bk_title varchar2(50) not null,

copyright date not null

   );

 

alina@TMIS> insert into books values (11078,'Learning MYSQL','2010-01-01');

alina@TMIS> insert into books values (11033,'Study Html','2011-01-01');

alina@TMIS> insert into books values (11035,'How to use php','2003-01-01');

alina@TMIS> insert into books values (11072,'Teach yourself javascript','2005-01-01');

alina@TMIS> insert into books values (11028,'Learning C++','2005-01-01');

alina@TMIS> insert into books values (11069,'MYSQL professional','2009-01-01');

alina@TMIS> insert into books values (11026,'Guide to MySQL 5.7','2008-01-01');

alina@TMIS> insert into books values (11041,'Inside VC++','2011-01-01');

alina@TMIS> commit;

alina@TMIS> select * from books;

 

     BK_ID BK_TITLE                       COPYRIGHT

---------- ------------------------------ -------------------

     11078 Learning MYSQL                 2010-01-01 00:00:00

     11033 Study Html                     2011-01-01 00:00:00

     11035 How to use php                 2003-01-01 00:00:00

     11072 Teach yourself javascript      2005-01-01 00:00:00

     11028 Learning C++                   2005-01-01 00:00:00

     11069 MYSQL professional             2009-01-01 00:00:00

     11026 Guide to MySQL 5.7             2008-01-01 00:00:00

     11041 Inside VC++                    2011-01-01 00:00:00

 

8 rows selected.

 

5、目标端建表结构

--使用navicat在oracle目标端生成MySQL类型的建表语句(具体步骤参见笔者此前博客).

[mysql@leo-mysql8-ogg ~]$ cat ddl.sql

SET NAMES utf8;

SET FOREIGN_KEY_CHECKS = 0;

 

-- ----------------------------

-- Table structure for BOOKS

-- ----------------------------

DROP TABLE IF EXISTS `BOOKS`;

CREATE TABLE `BOOKS`  (

  `BK_ID` decimal(38, 0) NOT NULL,

  `BK_TITLE` varchar(50) NOT NULL,

  `COPYRIGHT` datetime NOT NULL,

  PRIMARY KEY (`BK_ID`)

);

 

-- ----------------------------

-- Table structure for FRUITS

-- ----------------------------

DROP TABLE IF EXISTS `FRUITS`;

CREATE TABLE `FRUITS`  (

  `F_ID` varchar(10) NOT NULL,

  `S_ID` decimal(65, 30) NOT NULL,

  `F_NAME` varchar(255) NOT NULL,

  `F_PRICE` decimal(8, 2) NOT NULL,

  PRIMARY KEY (`F_ID`)

);

 

SET FOREIGN_KEY_CHECKS = 1;

 

mysql> create database tmisdb;

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| tmisdb             |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> use tmisdb

Database changed

mysql> show tables;

Empty set (0.00 sec)

 

[mysql@leo-mysql8-ogg ~]$ mysql -uroot -p -D tmisdb -f < ddl.sql                 

Enter password:

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| tmisdb             |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> use tmisdb;

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_tmisdb |

+------------------+

| books            |

| fruits           |

+------------------+

2 rows in set (0.00 sec)

 

mysql> desc BOOKS;

+-----------+---------------+------+-----+---------+-------+

| Field     | Type          | Null | Key | Default | Extra |

+-----------+---------------+------+-----+---------+-------+

| BK_ID     | decimal(38,0) | NO   | PRI | NULL    |       |

| BK_TITLE  | varchar(50)   | NO   |     | NULL    |       |

| COPYRIGHT | datetime      | NO   |     | NULL    |       |

+-----------+---------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

 

注意：若参数lower_case_table_names在数据库建库初始化时未指定为1，此处数据库名、表名会大小写敏感.

 

6、全量同步数据

注意：oracle全量数据同步到mysql时源端需停业务，不能产生新数据.

6.1、oracle源端操作

GGSCI (leo-oracle-19c) 1> edit params ext0

添加如下内容：

EXTRACT ext0

USERIDALIAS ora19c

rmthost 192.168.133.37,mgrport 8809

rmttask replicat,group rep0

TABLE ALINA.BOOKS;

TABLE ALINA.FRUITS;

 

说明：192.168.133.37为目标端IP，8809为目标端mgr中的端口.

 

GGSCI (leo-oracle-19c) 2> add extract ext0, sourceistable

Extract added.

 

GGSCI (leo-oracle-19c) 3> start mgr

Manager started.

 

GGSCI (leo-oracle-19c) 4> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

 

6.2、mysql目标端操作

mysql> create user 'ogg'@'%' identified by 'ogg';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant all on *.* to 'ogg'@'%';

Query OK, 0 rows affected (0.00 sec)

 

GGSCI (leo-mysql8-ogg) 2> dblogin sourcedb tmisdb@192.168.133.37:3306,userid ogg,password ogg

Successfully logged into database.

 

GGSCI (leo-mysql8-ogg) 6> edit params rep0

--添加如下内容.

replicat rep0

targetdb TMISDB@192.168.133.37:3306 userid ogg password ogg

MAP ALINA.BOOKS, TARGET TMISDB.BOOKS;

MAP ALINA.FRUITS, TARGET TMISDB.FRUITS;

 

GGSCI (leo-mysql8-ogg) 7> add replicat rep0, specialrun

Replicat added.

 

6.3、源端启动全量数据同步

说明：直接启动源端ext0，目标端rep0不用启动，mgr会自动启动它，等同步结束后，其会自动关闭.

 

GGSCI (leo-oracle-19c) 5> start ext0

 

Sending START request to Manager ...

Extract group EXT0 starting.

GGSCI (leo-oracle-19c) 9> info ext0,detail

 

Extract    EXT0      Last Started 2024-08-07 15:56   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Table ALINA.FRUITS

                     2024-08-07 15:56:06  Record 16

Task                 SOURCEISTABLE

 

......

 

6.4、目标端数据确认

mysql> select * from BOOKS;

+-------+---------------------------+---------------------+

| BK_ID | BK_TITLE                  | COPYRIGHT           |

+-------+---------------------------+---------------------+

| 11026 | Guide to MySQL 5.7        | 2008-01-01 00:00:00 |

| 11028 | Learning C++              | 2005-01-01 00:00:00 |

| 11033 | Study Html                | 2011-01-01 00:00:00 |

| 11035 | How to use php            | 2003-01-01 00:00:00 |

| 11041 | Inside VC++               | 2011-01-01 00:00:00 |

| 11069 | MYSQL professional        | 2009-01-01 00:00:00 |

| 11072 | Teach yourself javascript | 2005-01-01 00:00:00 |

| 11078 | Learning MYSQL            | 2010-01-01 00:00:00 |

+-------+---------------------------+---------------------+

8 rows in set (0.00 sec)

 

mysql> select * from FRUITS;

+------+------------------------------------+------------+---------+

| F_ID | S_ID                               | F_NAME     | F_PRICE |

+------+------------------------------------+------------+---------+

| a1   | 101.000000000000000000000000000000 | apple      |    5.20 |

| a2   | 103.000000000000000000000000000000 | apricot    |    2.20 |

| b1   | 101.000000000000000000000000000000 | blackberry |   10.20 |

| b2   | 104.000000000000000000000000000000 | berry      |    7.60 |

| b5   | 107.000000000000000000000000000000 | xxxx       |    3.60 |

| bs1  | 102.000000000000000000000000000000 | orange     |   11.20 |

| bs2  | 105.000000000000000000000000000000 | melon      |    8.20 |

| c0   | 101.000000000000000000000000000000 | cherry     |    3.20 |

| l2   | 104.000000000000000000000000000000 | lemon      |    6.40 |

| m1   | 106.000000000000000000000000000000 | mango      |   15.70 |

| m2   | 105.000000000000000000000000000000 | xbabay     |    2.60 |

| m3   | 105.000000000000000000000000000000 | xxtt       |   11.60 |

| o2   | 103.000000000000000000000000000000 | coconut    |    9.20 |

| t1   | 102.000000000000000000000000000000 | banana     |   10.30 |

| t2   | 102.000000000000000000000000000000 | grape      |    5.30 |

| t4   | 107.000000000000000000000000000000 | xbababa    |    3.60 |

+------+------------------------------------+------------+---------+

16 rows in set (0.00 sec)

 

6.5、删除初始化进程

说明：全量数据同步完成后，删除ext0和rep0进程.

GGSCI (leo-oracle-19c) 11> delete extract ext0

Extract group EXT0 deleted.

 

GGSCI (leo-mysql8-ogg DBLOGIN as ogg) 8> delete replicat rep0

 

Database error 1102: Incorrect database name ''

 

2024-08-08 20:35:36  WARNING OGG-14064  Could not delete heartbeat table entries for  group name REP0: ().

Replicat group REP0 deleted.

 

7、增量数据实时同步

7.1、源端抽取进程配置

--添加extract进程

GGSCI (leo-oracle-19c) 12> add extract exta integrated tranlog begin now

Integrated Extract added.

 

--extract进程绑定trail文件

GGSCI (leo-oracle-19c) 13> add exttrail ./dirdat/ta extract exta

EXTTRAIL added.

 

GGSCI (leo-oracle-19c) 14> dblogin useridalias ora19c

Successfully logged into database.

 

--注册extract进程

GGSCI (leo-oracle-19c as ogg@tmis) 15> register extract exta database

 

2024-08-07 19:17:52  INFO    OGG-02003  Extract group EXTA successfully registered with database at SCN 4885496.

 

 

GGSCI (leo-oracle-19c as ogg@tmis) 16> edit params exta

--添加如下内容.

EXTRACT exta

USERIDALIAS ora19c

--rmthost 192.168.133.37,mgrport 8809

--rmttask replicat,group rep0

TRANLOGOPTIONS FETCHPARTIALLOB

EXTTRAIL ./dirdat/ta

TABLE ALINA.BOOKS;

TABLE ALINA.FRUITS;

 

 

GGSCI (leo-oracle-19c as ogg@tmis) 18> start exta

 

Sending START request to Manager ...

Extract group EXTA starting.

 

GGSCI (leo-oracle-19c as ogg@tmis) 26> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EXTA        00:00:00      00:00:02   

 

7.2、源端投递进程配置

GGSCI (leo-oracle-19c as ogg@orclcdb) 11> edit param pump

--添加如下内容.

extract pump

userid ogg@192.168.133.101/tmis, password ogg123

rmthost 192.168.133.37,mgrport 8809

rmttrail /ogg/oggtarget/dirdat/ta

TABLE ALINA.BOOKS;

TABLE ALINA.FRUITS;

 

说明：192.168.133.37为目标端IP，8809为目标端mgr进程中的端口.

 

--将本地trail文件与投递进程pump绑定

GGSCI (leo-oracle-19c as ogg@orclcdb) 21> add extract pump, exttrailsource /ogg/oggsource/dirdat/ta

Extract added.

 

--将目标端trail绑定pump进程

GGSCI (leo-oracle-19c as ogg@orclcdb) 22> add rmttrail /ogg/oggtarget/dirdat/ta, extract pump, megabytes 100

RMTTRAIL added.

 

GGSCI (leo-oracle-19c) 10> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EXTA        00:00:00      00:00:00   

EXTRACT     STOPPED     PUMP        00:00:00      00:00:10   

 

 

GGSCI (leo-oracle-19c) 11> start pump

 

Sending START request to Manager ...

Extract group PUMP starting.

 

 

GGSCI (leo-oracle-19c) 12> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EXTA        00:00:00      00:00:06   

EXTRACT     RUNNING     PUMP        00:00:00      00:01:08    

 

7.3、目标端复制进程配置

7.3.1、添加checkpointtable

GGSCI (leo-mysql8-ogg) 8> dblogin sourcedb tmisdb@192.168.133.37:3306,userid ogg,password ogg

Successfully logged into database.

 

GGSCI (leo-mysql8-ogg) 9> add checkpointtable tmisdb.checkpoint

 

Successfully created checkpoint table ogg.checkpoint.

 

说明：tmisdb为新建的数据库名.

 

7.3.2、配置复制进程

GGSCI (leo-mysql8-ogg) 11> edit params repl

添加如下配置：

replicat repl

targetdb TMISDB@192.168.133.37:3306 userid ogg password ogg

MAP ALINA.BOOKS, TARGET TMISDB.BOOKS;

MAP ALINA.FRUITS, TARGET TMISDB.FRUITS;

 

GGSCI (leo-mysql8-ogg) 1> sh pwd

 

/ogg/oggtarget

 

GGSCI (leo-mysql8-ogg) 2> add rep repl, exttrail ./dirdat/ta, checkpointtable tmisdb.checkpoint

 

Replicat added.

 

说明：此处也可以配置nodbcheckpoint，建议配置checkpointtable.

 

GGSCI (leo-mysql8-ogg) 4> start repl

 

Sending START request to Manager ...

Replicat group REPL starting.

 

 

GGSCI (leo-mysql8-ogg) 5> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REPL        00:00:00      00:00:00   

 

8、同步测试

8.1、源端目标端数据确认

--源端oracle查询数据

alina@TMIS> select * from fruits where s_id=105;

 

F_ID             S_ID F_NAME                  F_PRICE

---------- ---------- -------------------- ----------

bs2               105 melon                       8.2

m2                105 xbabay                      2.6

m3                105 xxtt                       11.6

 

--目标端mysql查询数据

mysql> use TMISDB

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_tmisdb |

+------------------+

| books            |

| checkpoint       |

| checkpoint_lox   |

| fruits           |

+------------------+

4 rows in set (0.00 sec)

 

mysql> select * from FRUITS where s_id=105;

+------+------------------------------------+--------+---------+

| F_ID | S_ID                               | F_NAME | F_PRICE |

+------+------------------------------------+--------+---------+

| bs2  | 105.000000000000000000000000000000 | melon  |    8.20 |

| m2   | 105.000000000000000000000000000000 | xbabay |    2.60 |

| m3   | 105.000000000000000000000000000000 | xxtt   |   11.60 |

+------+------------------------------------+--------+---------+

3 rows in set (0.00 sec)

 

8.2、oracle源端删除数据

alina@TMIS> delete from fruits where s_id=105;

 

no rows selected

 

alina@TMIS> commit;

 

Commit complete.

 

alina@TMIS> select * from fruits where s_id=105;

 

no rows selected

 

8.3、源端ogg进程数据采集情况

注意：执行完delete提交后，stats进程才能显示相关抽取投递数据情况.

GGSCI (leo-oracle-19c) 17> stats exta

 

Sending STATS request to Extract group EXTA ...

 

Start of statistics at 2024-08-08 12:41:23.

 

Output to ./dirdat/ta:

 

Extracting from ALINA.FRUITS to ALINA.FRUITS:

 

*** Total statistics since 2024-08-08 12:41:19 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Daily statistics since 2024-08-08 12:41:19 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Hourly statistics since 2024-08-08 12:41:19 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Latest statistics since 2024-08-08 12:41:19 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

End of statistics.

 

GGSCI (leo-oracle-19c) 18> stats pump

 

Sending STATS request to Extract group PUMP ...

 

Start of statistics at 2024-08-08 12:41:32.

 

Output to /ogg/oggtarget/dirdat/ta:

 

Extracting from ALINA.FRUITS to ALINA.FRUITS:

 

*** Total statistics since 2024-08-08 12:41:20 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Daily statistics since 2024-08-08 12:41:20 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Hourly statistics since 2024-08-08 12:41:20 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Latest statistics since 2024-08-08 12:41:20 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

End of statistics.

 

 

GGSCI (leo-oracle-19c) 19> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EXTA        00:00:00      00:00:00   

EXTRACT     RUNNING     PUMP        00:00:00      00:00:04   

 

8.4、目标端mysql查询确认

mysql> select * from FRUITS where s_id=105;

Empty set (0.00 sec)

 

GGSCI (leo-mysql8-ogg) 9> stats repl

 

Sending STATS request to Replicat group REPL ...

 

Start of statistics at 2024-08-08 12:44:24.

 

Replicating from ALINA.FRUITS to TMISDB.FRUITS:

 

*** Total statistics since 2024-08-08 12:41:24 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Daily statistics since 2024-08-08 12:41:24 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Hourly statistics since 2024-08-08 12:41:24 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

*** Latest statistics since 2024-08-08 12:41:24 ***

    Total inserts                              0.00

    Total updates                              0.00

    Total deletes                              3.00

    Total upserts                              0.00

    Total discards                             0.00

    Total operations                           3.00

 

End of statistics.

 

GGSCI (leo-mysql8-ogg) 10> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REPL        00:00:00      00:00:00   

 

说明：如上所示，源端oracle与目标端mysql数据保持一致.

 

参考网址：

https://www.jb51.net/database/306027up8.htm

https://cloud.tencent.com/developer/article/1868868

https://zhuanlan.zhihu.com/p/360452315