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

    数据迁移是数据库 dba日常工作之一，偶尔会有跨平台异构数据库的数据迁移，这种数据迁移有一定难度，要处理2种数据库间的
特殊数据类型，要处理生产场景中的异常数据，当然跨平台异构数据迁移的工具也因数据库不同有很多种。这里记录一种oracle数据
迁移到mysql的简单快捷方法。
    源端环境：
    操作系统：Red Hat Enterprise Linux Server release 5.4 (Tikanga)
    数据库版本：oracle 10.2.0.1
    数据库字符集：AMERICAN_AMERICA.ZHS16GBK
    迁移数据：test.cemp
    数据迁移要点：注意中文乱码
    数据预览：

    目标端环境：
    操作系统版本：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数据库服务器端创建如下文件目录结构

     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执行导出工具时报错：
[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适合小数据量的数据迁移。