Oracle資料移轉至MySQL

標籤：

ORACLE DB: 11.2.0.3.0

MYSQL DB: 5.5.14

因項目需求,需要將ORACLE生產中資料移轉至MYSQL資料庫中作為初始資料,方法有如下幾種：

1、ORACLE OGG

2、通過手動編寫select “insert into”指令碼

3、工具,本次我就是使用了工具(sqluldr2),工具可以到www.anysql.net去下載


使用方法：
將sqluldr2.bin工具上傳到oracle的bin目錄下,
[[email protected] bin]# chown oracle.oinstall   sqluldr2.bin
 
[[email protected] bin]# chmod  775  sqluldr2.bin
 
[[email protected] bin]# su  -  oracle 

[[email protected] ~]$ sqluldr2   help=yes
Valid Keywords:
   user    = username/[email protected]
   sql     = SQL file name
   query   = select statement
   field   = separator string between fields
   record  = separator string between records
   rows    = print progress for every given rows (default, 1000000)
   file    = output file name(default: uldrdata.txt)
   log     = log file name, prefix with + to append mode
   fast    = auto tuning the session level parameters(YES)
   text    = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
   charset = character set name of the target database.
   ncharset= national character set name of the target database.
   parfile = read command option from parameter file
   read    = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
   sort    = set SORT_AREA_SIZE at session level (UNIT:MB)
   hash    = set HASH_AREA_SIZE at session level (UNIT:MB)
   array   = array fetch size
   head    = print row header(Yes|No)
   batch   = save to new file for every rows batch (Yes/No)
   size    = maximum output file piece size (UNIB:MB)
   serial  = set _serial_direct_read to TRUE at session level
   trace   = set event 10046 to given level at session level
   table   = table name in the sqlldr control file
   control = sqlldr control file and path.
   mode    = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE
   buffer  = sqlldr READSIZE and BINDSIZE, default 16 (MB)
   long    = maximum long field size
   width   = customized max column width (w1:w2:...)
   quote   = optional quote string
   data    = disable real data unload (NO, OFF)
   alter   = alter session SQLs to be execute before unload
   safe    = use large buffer to avoid ORA-24345 error (Yes|No)
   crypt   = encrypted user information only (Yes|No)
   sedf/t  = enable character translation function
   null    = replace null with given value
   escape  = escape character for special characters
   escf/t  = escape from/to characters list
   format  = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.
   exec    = the command to execute the SQLs.
   prehead = column name prefix for head line.
   rowpre  = row prefix string for each line.
   rowsuf  = row sufix string for each line.
   colsep  = separator string between column name and value.
   presql  = SQL or scripts to be executed before data unload.
   postsql = SQL or scripts to be executed after data unload.
   lob     = extract lob values to single file (FILE).
   lobdir  = subdirectory count to store lob files .
   split   = table name for automatically parallelization.
   degree  = parallelize data copy degree (2-128).
   hint    = MySQL SQL hint for the Insert, for example IGNORE.
   unique  = Unique Column List for the MySQL target table.
   update  = Enable MySQL ON DUPLICATE SQL statement(YES/NO).
   crack   = The crack key for the content protection.
   uncrack = The uncrack key for the content protection.

  for field and record, you can use ‘0x‘ to specify hex character code,
  \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 ‘=0x27

每次參數所代表的具體含義和意義不描述,自己看

注意以下3點：
1、將ORACLE資料庫中資料移轉到MYSQL,不要通過分隔字元的方法,測試過分隔字元,在匯入到MYSQL過程中會有警告,一些資料被截斷,避免大家走彎路

2、切記ORACLE生產環境的字元集是GBK,而MYSQL生產環境的字元集是UTF-8

3、將ORACLE資料全部匯出成insert into 語句,這樣在插入的過程避免出錯,可以通過MYSQL還原命令直接還原,如出錯進程會終止,source插入資料,中間出現警告,無法去驗證

在測試ORACLE匯出成insert into文本後,在還原到MYSQL過程中,測試N多次後,最終匯出命令如下：
sqluldr2.bin   user=yoon/[email protected]_177 query="select * from YOON.CESHI" charset=UTF8 text=MYSQLINS format=SQL  file=/u01/backup/oracle_mysql/CESHI_%b.txt  size=30000MB table=CESHI safe=YES

user : 使用者名稱/密碼@服務名

query：查詢語句

charset：字元集UTF-8 , 將oracle中gbk字元集資料匯出為文字格式設定後的字元集為UTF-8

text ：匯出檔案類型,必須為MYSQLINS, 成為的檔案資料中自動產生插入mysql語句的``符號,例如：`張三`

format：格式為MYSQL

file：匯出資料檔案儲存路徑

%b：字母b必須為小寫,可產生多個資料文字檔 ,例如：CESHI_1，CESHI_2 ......;  避免直接產生一個超級大的資料檔案

size：必須以MB為單位,匯出的每個檔案的大小,30000MB,匯出的每個資料檔案30G

table：產生的資料檔案中直接包含表名,否則會成產生insert into " ",沒有表名

safe：YES   這個一定要加,在測試大量的表中,發現有的oracle表匯出過程中會報ORA-24345錯誤,說是工具的BUG,加參數safe=YES即可


總結：
經過大量的測試後最終總結上面的匯出命令語句,目前測試的表資料沒有問題,至少對於我而言是這樣.

Oracle資料移轉至MySQL