異構資料庫資料遷移 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適合小資料量的資料遷移。