异构数据库数据迁移 oracle to mysql之oracle sqlloader和mysql load data

时间:2022-08-09 16:12:53
    数据迁移是数据库 dba日常工作之一,偶尔会有跨平台异构数据库的数据迁移,这种数据迁移有一定难度,要处理2种数据库间的
特殊数据类型,要处理生产场景中的异常数据,当然跨平台异构数据迁移的工具也因数据库不同有很多种。这里记录一种oracle数据
迁移到mysql的简单快捷方法。
    源端环境:
    操作系统:Red Hat Enterprise Linux Server release 5.4 (Tikanga)
    数据库版本:oracle 10.2.0.1
    数据库字符集:AMERICAN_AMERICA.ZHS16GBK
    迁移数据:test.cemp
    数据迁移要点:注意中文乱码
    数据预览:
异构数据库数据迁移 oracle to mysql之oracle sqlloader和mysql load data
    目标端环境:
    操作系统版本:CentOS Linux release 7.4.1708 (Core)
    数据库版本:mysql 5.6.29
    数据库字符集:utf8
    目标端库名:test
    目标端表:cemp
    cemp建表语句:
create table cemp(
empno int,
ename varchar(10),
job varchar(9),
mgr int,
hiredate date,
sal decimal,
comm decimal,
deptno int
);
    数据迁移使用到的工具
    源端oracle数据导出工具:sqluldr2_linux64_10204.bin
    目标端mysql导入工具:load data

    数据迁移过程
    1、源端oracle,在oracle数据库服务器端创建如下文件目录结构
异构数据库数据迁移 oracle to mysql之oracle sqlloader和mysql load data
     sqluldr2_test_cemp_20180530.sh 文件内容,其中注意通过charset指定目标端mysql字符集,其中sql指定引用的外部sql脚本
[oracle@orcl10201 script]$ pwd
/oracle/20180530/script
[oracle@orcl10201 script]$ cat sqluldr2_test_cemp_20180530.sh 
/oracle/20180530/sqluldr2_linux64_10204.bin  user=test/test sql=/oracle/20180530/sql/sqluldr2_test_cemp_20180530.sql table=cemp filed='|' charset=utf8 FILE=/oracle/20180530/data/sqluldr2_test_cemp_20180530.txt log=/oracle/20180530/log/sqluldr2_test_cemp_20180530.log 
[oracle@orcl10201 script]$ 
     sqluldr2_test_cemp_20180530.sql 脚本内容
[oracle@orcl10201 sql]$ pwd
/oracle/20180530/sql
[oracle@orcl10201 sql]$ cat sqluldr2_test_cemp_20180530.sql 
select * from cemp;
[oracle@orcl10201 sql]$
    oracle数据库导出工具 sqluldr2_linux64_10204.bin 可在网上下载。
    导出命令执行

[oracle@orcl10201 script]$ pwd
/oracle/20180530/script
[oracle@orcl10201 script]$ nohup ./sqluldr2_test_cemp_20180530.sh &
[1] 12748
[oracle@orcl10201 script]$ nohup: appending output to `nohup.out'
    查看导出日志:
[oracle@orcl10201 log]$ pwd
/oracle/20180530/log
[oracle@orcl10201 log]$ cat sqluldr2_test_cemp_20180530.log 
           0 rows exported at 2018-05-25 23:34:37, size 0 MB.
          14 rows exported at 2018-05-25 23:34:37, size 0 MB.
         output file /oracle/20180530/data/sqluldr2_test_cemp_20180530.txt closed at 14 rows, size 0 MB.
[oracle@orcl10201 log]$
    查看导出数据:
[oracle@orcl10201 data]$ pwd
/oracle/20180530/data
[oracle@orcl10201 data]$ cat sqluldr2_test_cemp_20180530.txt 
7369,SMITH,职员,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,销售,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,销售,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,管理员,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,销售,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,管理员,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,管理员,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,销售,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,职员,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,职员,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,职员,7782,1982-01-23 00:00:00,1300,,10
[oracle@orcl10201 data]$ 
    2、目标端mysql操作,将 /oracle/20180530/data/sqluldr2_test_cemp_20180530.txt上传到centos7mysql的/tmp目录下
[root@centos7mysql tmp]# pwd
/tmp
[root@centos7mysql tmp]# ls -l sqluldr2_test_cemp_20180530.txt 
-rwxrwxr-x 1 mysql mysql 747 May 31 09:13 sqluldr2_test_cemp_20180530.txt
[root@centos7mysql tmp]#
    root登陆mysql数据库
[root@centos7mysql tmp]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
    在test数据库中创建cemp表
mysql> use test;
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> 
mysql> create table cemp(
    -> empno int,
    -> ename varchar(10),
    -> job varchar(9),
    -> mgr int,
    -> hiredate date,
    -> sal decimal,
    -> comm decimal,
    -> deptno int
    -> );
Query OK, 0 rows affected (0.66 sec)
mysql> show create table cemp\G
*************************** 1. row ***************************
       Table: cemp
Create Table: CREATE TABLE `cemp` (
  `empno` int(11) DEFAULT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,0) DEFAULT NULL,
  `comm` decimal(10,0) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
    mysql加载/tmp/ sqluldr2_test_cemp_20180530.txt到test.cemp表中
mysql> LOAD DATA INFILE '/tmp/sqluldr2_test_cemp_20180530.txt' INTO TABLE cemp FIELDS TERMINATED BY  ',' ; 
Query OK, 14 rows affected, 11 warnings (0.01 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 11
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
    查看数据迁移结果
异构数据库数据迁移 oracle to mysql之oracle sqlloader和mysql load data
      数据迁移过程中的异常处理
      在oracle执行导出工具时报错:
[oracle@orcl10201 script]$ ./sqluldr2_test_cemp_20180530.sh 
/oracle/20180530/sqluldr2_linux64_10204.bin: error while loading shared libraries: libclntsh.so.1ot 
open shared object file: No such file or directory
[oracle@orcl10201 script]$
[oracle@orcl10201 20180530]$ csscan
csscan: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: ile or directory
[oracle@orcl10201 20180530]$
    异常处理:
[root@orcl10201 ~]# find / -name libclntsh.so.10.1
/oracle/app/oracle/product/10.2.0.1/db/lib32/libclntsh.so.10.1
/oracle/app/oracle/product/10.2.0.1/db/lib/libclntsh.so.10.1
[root@orcl10201 ~]# vi /etc/ld.so.conf
[root@orcl10201 ~]# ldconfig
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmemso.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmuc.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libordim10.soO is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmasf.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmastk.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmevq.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefw.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmadbg.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmuk.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmadbg.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmuk.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefud.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefsql.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefw.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmadm.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefut.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmalk.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmalk.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefvr.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefsql.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefud.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmarl.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmuc.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefos.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmemso.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefvr.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmevq.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmasf.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefos.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmeoci.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmastk.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmadm.so.0 is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmefut.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmarl.so is empty, not checked.
ldconfig: File /oracle/app/oracle/product/10.2.0.1/db/lib/libnmeoci.so.0 is empty, not checked.
ldconfig: /oracle/app/oracle/product/10.2.0.1/db/lib/libexpat.so.0 is not a symbolic link
[root@orcl10201 ~]# 
    异常处理结果检查:
[oracle@orcl10201 20180530]$ csscan
Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Fri May 25 19:53:08 2018
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Username: 
Password: 
ORA-01017: invalid username/password; logon denied
Scanner terminated unsuccessfully.
[oracle@orcl10201 20180530]$ 
    总结:oracle sqlloader和mysql load data适合小数据量的数据迁移。