合使用kettle全量迁移Oracle到MySQL

发布日期 2022年7月5日 · 已更新 2022年9月1日

0 865 6

👉 本文共约1659个字，系统预计阅读时间或需7分钟。

环境准备
Oracle和MySQL环境准备
kettle环境准备
Oracle端数据初始化
kettle配置全量同步
数据校验
kettle优化
命令行运行
总结

环境准备

Oracle和MySQL环境准备

-- 创建专用网络
docker network create --subnet=172.72.7.0/24 ora-network

-- oracle 压测工具
docker pull lhrbest/lhrdbbench:1.0 

docker rm -f lhrdbbench
docker run -d --name lhrdbbench -h lhrdbbench \
  --net=ora-network --ip 172.72.7.26 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/lhrdbbench:1.0 \
  /usr/sbin/init

-- Oracle 12c
docker rm -f lhrora1221
docker run -itd --name lhrora1221 -h lhrora1221 \
  --net=ora-network --ip 172.72.7.34 \
  -p 1526:1521 -p 3396:3389 \
  --privileged=true \
  lhrbest/oracle_12cr2_ee_lhr_12.2.0.1:2.0 init

-- mysql
docker rm -f mysql8027
docker run -d --name mysql8027 -h mysql8027 -p 3418:3306 \
  --net=ora-network --ip 172.72.7.35 \
  -v /etc/mysql/mysql8027/conf:/etc/mysql/conf.d \
  -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
  mysql:8.0.27

cat >  /etc/mysql/mysql8027/conf/my.cnf << "EOF"
[mysqld]
default-time-zone = '+8:00'
log_timestamps = SYSTEM
skip-name-resolve
log-bin
server_id=80273418
character_set_server=utf8mb4
default_authentication_plugin=mysql_native_password
EOF

mysql -uroot -plhr -h 172.72.7.35
create database lhrdb;

-- 业务用户
CREATE USER lhr identified by lhr;
alter user lhr identified by lhr;
GRANT DBA to lhr ;
grant SELECT ANY DICTIONARY to lhr;
GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr;

-- 启动监听
vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
lsnrctl start
lsnrctl status

-- 创建专用网络

docker network create --subnet=172.72.7.0/24 ora-network

-- oracle 压测工具

docker pull lhrbest/lhrdbbench:1.0

docker rm -f lhrdbbench

docker run -d --name lhrdbbench -h lhrdbbench \

--net=ora-network --ip 172.72.7.26 \

-v /sys/fs/cgroup:/sys/fs/cgroup \

--privileged=true lhrbest/lhrdbbench:1.0 \

/usr/sbin/init

-- Oracle 12c

docker rm -f lhrora1221

docker run -itd --name lhrora1221 -h lhrora1221 \

--net=ora-network --ip 172.72.7.34 \

-p 1526:1521 -p 3396:3389 \

--privileged=true \

lhrbest/oracle_12cr2_ee_lhr_12.2.0.1:2.0 init

-- mysql

docker rm -f mysql8027

docker run -d --name mysql8027 -h mysql8027 -p 3418:3306 \

--net=ora-network --ip 172.72.7.35 \

-v /etc/mysql/mysql8027/conf:/etc/mysql/conf.d \

-e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \

mysql:8.0.27

cat > /etc/mysql/mysql8027/conf/my.cnf << "EOF"

[mysqld]

default-time-zone = '+8:00'

log_timestamps = SYSTEM

skip-name-resolve

log-bin

server_id=80273418

character_set_server=utf8mb4

default_authentication_plugin=mysql_native_password

EOF

mysql -uroot -plhr -h 172.72.7.35

create database lhrdb;

-- 业务用户

CREATE USER lhr identified by lhr;

alter user lhr identified by lhr;

GRANT DBA to lhr ;

grant SELECT ANY DICTIONARY to lhr;

GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr;

-- 启动监听

vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

lsnrctl start

lsnrctl status

kettle环境准备

docker rm -f lhrkettle
docker run -itd --name lhrkettle -h lhrkettle \
  --net=ora-network --ip 172.72.7.88 \
  -p 7390:3389 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/kettle:1.0 \
  /usr/sbin/init

docker rm -f lhrkettle

docker run -itd --name lhrkettle -h lhrkettle \

--net=ora-network --ip 172.72.7.88 \

-p 7390:3389 \

-v /sys/fs/cgroup:/sys/fs/cgroup \

--privileged=true lhrbest/kettle:1.0 \

/usr/sbin/init

Oracle端数据初始化


-- 源端数据初始化
/usr/local/swingbench/bin/oewizard  -s -create -c /usr/local/swingbench/wizardconfigs/oewizard.xml -create \
-version 2.0  -cs //172.72.7.34/lhrsdb  -dba "sys as sysdba" -dbap lhr -dt thin \
-ts users -u lhr -p lhr -allindexes  -scale 0.0001  -tc 16 -v -cl

col TABLE_NAME format a30
SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' ;
select object_type,count(*) from dba_objects where owner='LHR' group by object_type;
select object_type,status,count(*) from dba_objects where owner='LHR' group by object_type,status;
select sum(bytes)/1024/1024 from dba_segments where owner='LHR';

-- 检查键是否正确：https://www.dbaup.com/ogg-01296-biaoyouzhujianhuoweiyijiandanshirengranshiyongquanbulielaijiexixing.html
-- 否则OGG启动后，会报错：OGG-01296、OGG-06439、OGG-01169  Encountered an update where all key columns for target table LHR.ORDER_ITEMS are not present.
select owner, constraint_name, constraint_type, status, validated 
from dba_constraints 
where owner='LHR' 
and VALIDATED='NOT VALIDATED';

select 'alter table lhr.'||TABLE_NAME||' enable validate constraint '||CONSTRAINT_NAME||';' 
from dba_constraints 
where owner='LHR'
and VALIDATED='NOT VALIDATED';

-- 删除外键
SELECT 'ALTER TABLE  LHR.'|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';' 
FROM DBA_constraints d where  owner='LHR' and d.CONSTRAINT_TYPE='R';

sqlplus lhr/lhr@172.72.7.34:1521/lhrsdb

@/oggoracle/demo_ora_create.sql
@/oggoracle/demo_ora_insert.sql

SQL> select * from tcustmer;

CUST NAME                           CITY                 ST
---- ------------------------------ -------------------- --
WILL BG SOFTWARE CO.                SEATTLE              WA
JANE ROCKY FLYER INC.               DENVER               CO

-- 创建2个clob和blob类型的表
sqlplus lhr/lhr@172.72.7.34:1521/lhrsdb  @/oggoracle/demo_ora_lob_create.sql
exec testing_lobs;
select * from lhr.TSRSLOB;

drop table IMAGE_LOB;
CREATE TABLE IMAGE_LOB (
   T_ID VARCHAR2 (5) NOT NULL,
   T_IMAGE BLOB,
   T_CLOB  CLOB 
   );

-- 插入blob文件  
CREATE OR REPLACE DIRECTORY D1 AS '/home/oracle/';
grant all on DIRECTORY  D1 TO PUBLIC;
CREATE OR REPLACE NONEDITIONABLE PROCEDURE IMG_INSERT(TID      VARCHAR2,
                                                      FILENAME VARCHAR2,
                                                      name VARCHAR2) AS
    F_LOB BFILE;
    B_LOB BLOB;
BEGIN
    INSERT INTO IMAGE_LOB
        (T_ID, T_IMAGE,T_CLOB)
    VALUES
        (TID, EMPTY_BLOB(),name) RETURN T_IMAGE INTO B_LOB;
    F_LOB := BFILENAME('D1', FILENAME);
    DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);
    DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB, DBMS_LOB.GETLENGTH(F_LOB));
    DBMS_LOB.FILECLOSE(F_LOB);
    COMMIT;
END;
/

BEGIN
    IMG_INSERT('1','1.jpg','dbaup.com');
    IMG_INSERT('2','2.jpg','www.dbaup.com');
 END;
/

select * from IMAGE_LOB;

-----  oracle所有表
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ADDRESSES                      TABLE
CARD_DETAILS                   TABLE
CUSTOMERS                      TABLE
IMAGE_LOB                      TABLE
INVENTORIES                    TABLE
LOGON                          TABLE
ORDERENTRY_METADATA            TABLE
ORDERS                         TABLE
ORDER_ITEMS                    TABLE
PRODUCTS                       VIEW
PRODUCT_DESCRIPTIONS           TABLE
PRODUCT_INFORMATION            TABLE
PRODUCT_PRICES                 VIEW
TCUSTMER                       TABLE
TCUSTORD                       TABLE
TSRSLOB                        TABLE
TTRGVAR                        TABLE
WAREHOUSES                     TABLE

18 rows selected.

SELECT COUNT(*) FROM LHR.ADDRESSES                      UNION ALL
SELECT COUNT(*) FROM LHR.CARD_DETAILS                   UNION ALL
SELECT COUNT(*) FROM LHR.CUSTOMERS                      UNION ALL
SELECT COUNT(*) FROM LHR.IMAGE_LOB                      UNION ALL
SELECT COUNT(*) FROM LHR.INVENTORIES                    UNION ALL
SELECT COUNT(*) FROM LHR.LOGON                          UNION ALL
SELECT COUNT(*) FROM LHR.ORDERENTRY_METADATA            UNION ALL
SELECT COUNT(*) FROM LHR.ORDERS                         UNION ALL
SELECT COUNT(*) FROM LHR.ORDER_ITEMS                    UNION ALL
SELECT COUNT(*) FROM LHR.PRODUCT_DESCRIPTIONS           UNION ALL
SELECT COUNT(*) FROM LHR.PRODUCT_INFORMATION            UNION ALL
SELECT COUNT(*) FROM LHR.TCUSTMER                       UNION ALL
SELECT COUNT(*) FROM LHR.TCUSTORD                       UNION ALL
SELECT COUNT(*) FROM LHR.TSRSLOB                        UNION ALL
SELECT COUNT(*) FROM LHR.TTRGVAR                        UNION ALL
SELECT COUNT(*) FROM LHR.WAREHOUSES
;

  COUNT(*)
----------
       281
       281
       231
         4
    900724
       575
         4
       424
      1642
      1000
      1000
         2
         2
         1
         0
      1000

16 rows selected.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

-- 源端数据初始化

/usr/local/swingbench/bin/oewizard -s -create -c /usr/local/swingbench/wizardconfigs/oewizard.xml -create \

-version 2.0 -cs //172.72.7.34/lhrsdb -dba "sys as sysdba" -dbap lhr -dt thin \

-ts users -u lhr -p lhr -allindexes -scale 0.0001 -tc 16 -v -cl

col TABLE_NAME format a30

SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' ;

select object_type,count(*) from dba_objects where owner='LHR' group by object_type;

select object_type,status,count(*) from dba_objects where owner='LHR' group by object_type,status;

select sum(bytes)/1024/1024 from dba_segments where owner='LHR';

-- 检查键是否正确：https://www.dbaup.com/ogg-01296-biaoyouzhujianhuoweiyijiandanshirengranshiyongquanbulielaijiexixing.html

-- 否则OGG启动后，会报错：OGG-01296、OGG-06439、OGG-01169 Encountered an update where all key columns for target table LHR.ORDER_ITEMS are not present.

select owner, constraint_name, constraint_type, status, validated

from dba_constraints

where owner='LHR'

and VALIDATED='NOT VALIDATED';

select 'alter table lhr.'||TABLE_NAME||' enable validate constraint '||CONSTRAINT_NAME||';'

from dba_constraints

where owner='LHR'

and VALIDATED='NOT VALIDATED';

-- 删除外键

SELECT 'ALTER TABLE LHR.'|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';'

FROM DBA_constraints d where owner='LHR' and d.CONSTRAINT_TYPE='R';

sqlplus lhr/lhr@172.72.7.34:1521/lhrsdb

@/oggoracle/demo_ora_create.sql

@/oggoracle/demo_ora_insert.sql

SQL> select * from tcustmer;

CUST NAME CITY ST

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

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

-- 创建2个clob和blob类型的表

sqlplus lhr/lhr@172.72.7.34:1521/lhrsdb @/oggoracle/demo_ora_lob_create.sql

exec testing_lobs;

select * from lhr.TSRSLOB;

drop table IMAGE_LOB;

CREATE TABLE IMAGE_LOB (

T_ID VARCHAR2 (5) NOT NULL,

T_IMAGE BLOB,

T_CLOB CLOB

);

-- 插入blob文件

CREATE OR REPLACE DIRECTORY D1 AS '/home/oracle/';

grant all on DIRECTORY D1 TO PUBLIC;

CREATE OR REPLACE NONEDITIONABLE PROCEDURE IMG_INSERT(TID VARCHAR2,

FILENAME VARCHAR2,

name VARCHAR2) AS

F_LOB BFILE;

B_LOB BLOB;

BEGIN

INSERT INTO IMAGE_LOB

(T_ID, T_IMAGE,T_CLOB)

VALUES

(TID, EMPTY_BLOB(),name) RETURN T_IMAGE INTO B_LOB;

F_LOB := BFILENAME('D1', FILENAME);

DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);

DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB, DBMS_LOB.GETLENGTH(F_LOB));

DBMS_LOB.FILECLOSE(F_LOB);

COMMIT;

END;

BEGIN

IMG_INSERT('1','1.jpg','dbaup.com');

IMG_INSERT('2','2.jpg','www.dbaup.com');

END;

select * from IMAGE_LOB;

----- oracle所有表

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

ADDRESSES TABLE

CARD_DETAILS TABLE

CUSTOMERS TABLE

IMAGE_LOB TABLE

INVENTORIES TABLE

LOGON TABLE

ORDERENTRY_METADATA TABLE

ORDERS TABLE

ORDER_ITEMS TABLE

PRODUCTS VIEW

PRODUCT_DESCRIPTIONS TABLE

PRODUCT_INFORMATION TABLE

PRODUCT_PRICES VIEW

TCUSTMER TABLE

TCUSTORD TABLE

TSRSLOB TABLE

TTRGVAR TABLE

WAREHOUSES TABLE

18 rows selected.

SELECT COUNT(*) FROM LHR.ADDRESSES UNION ALL

SELECT COUNT(*) FROM LHR.CARD_DETAILS UNION ALL

SELECT COUNT(*) FROM LHR.CUSTOMERS UNION ALL

SELECT COUNT(*) FROM LHR.IMAGE_LOB UNION ALL

SELECT COUNT(*) FROM LHR.INVENTORIES UNION ALL

SELECT COUNT(*) FROM LHR.LOGON UNION ALL

SELECT COUNT(*) FROM LHR.ORDERENTRY_METADATA UNION ALL

SELECT COUNT(*) FROM LHR.ORDERS UNION ALL

SELECT COUNT(*) FROM LHR.ORDER_ITEMS UNION ALL

SELECT COUNT(*) FROM LHR.PRODUCT_DESCRIPTIONS UNION ALL

SELECT COUNT(*) FROM LHR.PRODUCT_INFORMATION UNION ALL

SELECT COUNT(*) FROM LHR.TCUSTMER UNION ALL

SELECT COUNT(*) FROM LHR.TCUSTORD UNION ALL

SELECT COUNT(*) FROM LHR.TSRSLOB UNION ALL

SELECT COUNT(*) FROM LHR.TTRGVAR UNION ALL

SELECT COUNT(*) FROM LHR.WAREHOUSES

;

COUNT(*)

----------

281

231

900724

575

424

1642

1000

16 rows selected.

最终，在Oracle端共包括16张表，2个视图，其中2个表TSRSLOB和IMAGE_LOB包括了blob和clob字段。

kettle配置全量同步

连接到kettle远程桌面：mstsc

用户名和密码：root/lhr

启动kettle图形界面：

sh  /usr/local/data-integration/spoon.sh &

1	sh /usr/local/data-integration/spoon.sh &

新建Oracle和MySQL的连接，如下：

完成后，会自动生成如下的执行树：

点击run，开始执行即可：

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(6)

标签：数据迁移异构迁移 kettle

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合使用kettle全量迁移Oracle到MySQL

环境准备

Oracle和MySQL环境准备

kettle环境准备

Oracle端数据初始化

kettle配置全量同步

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 10 月
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

合 使用kettle全量迁移Oracle到MySQL

环境准备

Oracle和MySQL环境准备

kettle环境准备

Oracle端数据初始化

kettle配置全量同步

相关文章

您可能还喜欢...

Different Upgrade Methods For Upgrading Your Database (Doc ID 419550.1)

使用OGG传统模式将Oracle同步到kafka(全量+增量)

数据库数据迁移完成后完整性的校验

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合使用kettle全量迁移Oracle到MySQL

发表回复取消回复