Oracle data migration to MySQL

ORACLE db:11.2.0.3.0

MYSQL db:5.5.14

As a result of the project requirements, there are several ways to migrate data from Oracle production to the MySQL database as initial data:

1. ORACLE OGG

2. Manually write select "INSERT into" script

3, tools, this time I was using the tool (SQLULDR2), tools can go to www.anysql.net to download


How to use:
Upload the Sqluldr2.bin tool to Oracle's Bin directory,
[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 (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 is executed before data unload.
Postsql = SQL or scripts to is executed after data unload.
LOB = Extract LOB values to a 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 the ' 0x ' to specify hex character code,
\r=0x0d \n=0x0a |=0x7c, =0x2c, \t=0x09,: =0x3a, #=0x23, "=0x22" =0x27

Each time the specific meaning and meaning of the argument is not described, see for yourself

Note the following 3 points:
1, the Oracle database to migrate data to MySQL, do not pass the delimiter method, test the delimiter, in the import into the MySQL process will have a warning, some data is truncated, to avoid detours

2. Remember that the character set of the Oracle production environment is GBK, while the character set of the MySQL production environment is UTF-8

3, export all Oracle data into the INSERT INTO statement, so that during the insertion process to avoid errors, can be restored through the MySQL restore command, such as the error process will be terminated, the source inserted data, there is a warning in the middle, unable to verify

After testing the Oracle export to insert into text, after testing n multiple times during the restore to MySQL process, the final Export command is as follows:
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: Username/password @ service name

Query: Querying statements

CharSet: Character Set UTF-8, the character set for exporting GBK character set data in Oracle to text format is UTF-8

Text: Export file type, must be mysqlins, become the file data automatically generated insert MySQL statement "' symbol, for example: ' Zhang San '

Format: MySQL

File: Export Data File Save path

%b: The letter B must be lowercase, can generate multiple data text files, for example: Ceshi_1,ceshi_2 ...; Avoid generating a super-large data file directly

Size: Must be exported in megabytes per file, 30000MB, each data file exported 30G

Table: The generated data file directly contains the name of the tables, otherwise it will be generated insert into "", no table name

Safe:yes This must add, in the test a large number of tables, found that some Oracle table export process will be reported ORA-24345 error, said to be a tool bug, add parameter Safe=yes can


Summarize:
After a lot of testing and finally summed up the above Export command statement, the current test table data is not a problem, at least for me.

Oracle data migration to MySQL