运用OGG实现oracle 19c(非多租户)到mysql 8的数据同步

最新推荐文章于 2024-09-24 11:36:02 发布

子小安

最新推荐文章于 2024-09-24 11:36:02 发布

阅读量32

点赞数

文章标签： oracle mysql 数据库

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

运用OGG实现oracle 19c(非多租户)到mysql 8的数据同步_mysql

流程解析：

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