oracle还原至mysql_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目录下,

[root@db01 bin]# chown oracle.oinstall   sqluldr2.bin

[root@db01 bin]# chmod  775  sqluldr2.bin

[root@db01 bin]# su  -  oracle

[oracle@db01 ~]$ sqluldr2   help=yes

Valid Keywords:

user    = username/password@tnsname

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/yoon@CS_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即可

总结：

经过大量的测试后最终总结上面的导出命令语句,目前测试的表数据没有问题,至少对于我而言是这样.