Transcript data_migration_tools..

Data Migration Tools
By d.c.b.a
http://www.anysql.net
http://www.mydul.net
Source & Target

Source



Target




Oracle
MySQL
Oracle
MySQL
Flat Text File
Download
Oracle Source

Oracle to Oracle


Oracle to MySQL


datacopy
ora2mysql
Oracle to Flat Text File

SQLULDR2
MySQL Source

MySQL to Oracle


MySQL to MySQL


mysql2ora
mysqlcopy
MySQL to Flat Text File

MYSQLULDR2
Basic Concept

Query on Source, DML on Target










Only Select Allowed on Source Database
Insert/Update/Delete/Script allowed on Target
Database,
Refer source columns by “:”+column name
Query1/Table1 for Source SELECT Statement
Query2/Table2 for Target DML Statement
Basic Data Types Supported. Object Data Types not
Supported.
Array Operation to Reduce Network Roundtrips.
Direct Load Supported for Oracle.
Parallel Supported for Both Oracle and MySQL.
Parameter File Supported.
Database Connection



User1 Option for Source Database
User2 Option for Target Database
Oracle Connection




User/Password@Host:Port:Database
User/Password@tnsname
“sys” for SYSDBA logon (Oracle Database)
MySQL Connection


User/Password@Host:Port:Database
Default Port is 3306
Return Code

Windows


Linux/Unix



%ERRORLEVEL%
$?
Zero value for success without any errors.
Non-zero value for error, different error
different return code.
From Oracle To Oracle
DATACOPY
DATACOPY


Manipulate Data Between Oracle Databases.
Operations






Direct Load Insert
Insert
Update
Delete
PL/SQL Block
The SYNC option
Direct Load Insert (DATACOPY)



Using TABLE2 option to specify the table
name on target database.
Specify the “direct=yes” option.
Example

datacopy user1=scott/tiger@prod1
user2=scott/tiger@prod2 query1=“select * from
emp” table2=emp direct=yes
Insert (DATACOPY)



Using QUERY2 Option to Specify the Insert
Statement on Target Database
Refer the source column value by “:” plus
column name
Example

datacopy user1=scott/tiger@prod1
user2=scott/tiger@prod2 query1=“select eno,
ename from emp” query2=“insert into emp(eno,
ename) values (:eno, :ename)”
Update (datacopy)



Using QUERY2 Option to Specify the Update
Statement on Target Database
Refer the source column value by “:” plus
column name
Example

datacopy user1=scott/tiger@prod1
user2=scott/tiger@prod2 query1=“select eno,
ename from emp” query2=“update emp set
ename = :ename where eno = :eno”
Delete (DATACOPY)



Using QUERY2 Option to Specify the Delete
Statement on Target Database
Refer the source column value by “:” plus
column name
Example

datacopy user1=scott/tiger@prod1
user2=scott/tiger@prod2 query1=“select eno
from emp” query2=“delete from emp where eno
= :eno”
PL/SQL Block (DATACOPY)



Using QUERY2 Option to Specify the PL/SQL
Script on Target Database
Refer the source column value by “:” plus
column name
Example

datacopy user1=scott/tiger@prod1
user2=scott/tiger@prod2 query1=“select eno
from emp” query2=“begin delete_emp(:eno);
end;”
SYNC Option (datacopy)


Auto Generate Target SQL or PL/SQL Script
without specify the “QUERY2” option.
Cooperation with “TABLE2” option.


SYNC=INSERT
Cooperation with “TABLE2” and “UNIQUE”
option.


SYNC=UPDATE/DELETE
SYNC=DELINS/INSUPD/UPDINS
SYNC Option (DELINS)

Command


datacopy … table1=emp table2=emp
unique=eno sync=delins
Real QUERY2 Value

Begin delete from emp where eno=:eno; insert
into emp (…) values (…); end;
SYNC Option (UPDINS)

Command


datacopy … table1=emp table2=emp
unique=eno sync=delins
Real QUERY2 Value

Begin update emp set … where eno=:eno; if
sql%rowcount == 0 then insert into emp (…)
values (…); end if; end;
SYNC Option (INSUPD)

Command


datacopy … table1=emp table2=emp
unique=eno sync=delins
Real QUERY2 Value

Begin insert into emp (…) values (…); if unique
error then update emp set … where eno=:eno;
end if; end;
Tuning (datacopy)





Multiple Block Read (read=…)
Sort Area Size (sort= …)
Hash Area Size (hash=…)
Direct Path Read (serial=yes)
Parallel Query (/*+ PARALLEL … */ Hint)
Multiple Thread Copy






Automatically Split Into Pieces by ROWID Range.
Need “SELECT_CATALOG_ROLE” to access
“DBA_EXTENTS” view.
Using “SPLIT” and “DEGREE” Option.
“SPLIT” for the base table for ROWID range
analyze.
“DEGREE” for the parallel degree.
Example

datacopy … split=emp degree=4 …
Why “SPLIT” Option

Source Query Can be Complex.




select … from emp, deptno where emp.deptno =
dept.deptno and emp.rowid >= :minrid and
emp.rowid < :maxrid
select deptno, count(*) from emp where rowid
>= :minrid and rowid < :maxrid group by deptno
Parallel Can Only Be One Dimemsion.
Reference The ROWID Range by “:MINRID”
and “:MAXRID”.
Character Set

Source Database


Target Database



NLS_LANG environment variable
“CHARSET” option for basic character set.
“NCHARSET” option for national character set.
From US7ASCII to ZHS16GBK


export NLS_LANG=.US7ASCII
datacopy … charset=ZHS16GBK …
Parameter File

Text File (test.par)







user1=scott/tiger@prod1
user2=scott/tiger@prod1
query1=select * from emp
table2=emp
read=128
serial=yes
Command

datacopy parfile=test.par
From Oracle To MySQL
ORA2MYSQL
Reference

Multiple Thread Support Refer DATACOPY.
SYNC Option (ora2mysql)


Auto Generate Target SQL without specify
the “QUERY2” option.
Cooperation with “TABLE2” option.


SYNC=INSERT/ARRINS
Cooperation with “TABLE2” and “UNIQUE”
option.

SYNC=UPDATE/DELETE/INSUPD/ARRUPD
Target SQL (ora2mysql)

ARRINS


INSUPD


Insert into emp (…) values (…), (…), (…)
Insert into emp (…) values (…) on duplicate
key …
ARRUPD

Insert into emp (…) values (…) (…) (…) on
duplicate key …
Character Set


MySQL Character Set is Controlled By
“CHARSET” option.
Oracle Character Set is Controlled By
“NLS_LANG” Environment Variable.
From Oracle To Flat File
SQLULDR2
Reference

Multiple Thread Support Refer DATACOPY.
Flat File Format


Fixed Width Format
How to Split Different Records?



How to Split Different Fields?



Using the “FIELD” Option.
Using “0xXX” for Any Characters.
Using the “RECORD” Option.
Using “0xXX” for Any Characters.
Example

sqluldr2 … field=0x07 record=0x06 …
Output File Name

Dynamic File Name







%y=Year
%m=Month
%d=Day
%w=Week
%b=Batch Count
%p=Thread ID
%t=Timestamp (now() function)
Multiple Output Files

Split Output File by “SIZE” Options.



Unit Megabytes
sqluldr2 … size=500 file=data_%b.txt
Split Output File by “BATCH” Options.


Store each rows in a single file
sqlldr2 … rows=5000000 batch=yes
file=data_%b.txt …
SQL*Loader Control File




SQL * Loader need a parameter file (describing
the format of the flat file) to load flat text file
into Oracle database.
“TABLE” for the table name of target database.
“MODE” for the SQL * Loader option, default is
“INSERT”, other options are “APPEND”,
“REPLACE” and “TRUNCATE”.
“CONTROL” for the SQL*Loader control file
name, default is “<table name>_sqlldr.ctl”.
From MySQL to Oracle
MYSQL2ORA
Reference

Target Operation Please Refer DATACOPY.
Attention

LONG/LOB values larger than 64KB Not
Supported Now.
Multiple Thread Copy






Automatically Split Into Pieces by Given Column.
Using “SPLIT”, “SPLITKEY” and “DEGREE”
Option.
“SPLIT” for the base table.
“SPLITKEY” for the key prefix column of the
base table.
“DEGREE” for the parallel degree.
Example

mysql2ora … split=emp splitkey=empno degree=4 …
Why “SPLIT” Option

Source Query Can be Complex.




select … from emp, deptno where emp.deptno =
dept.deptno and emp.empno >= :minrid and
emp.empno < :maxrid
select deptno, count(*) from emp where empno
>= :minrid and empno < :maxrid group by
deptno
Parallel Can Only Be One Dimemsion.
Reference The ROWID Range by “:MINRID”
and “:MAXRID”.
The “SPLITKEY” Logic

Minimum Value


Maximum Value




SELECT <splitcol> FROM <table> ORDER BY
<splitcol> LIMIT 1
SELECT <splitcol> FROM <table> ORDER BY <splitcol> DESC
LIMIT 1
Split The Range Into Degree Pieces.
Only Number/Date/Char Columns Supported.
Data May Not Be Evenly Distributed.
SYNC Option (datacopy)


Auto Generate Target SQL or PL/SQL Script
without specify the “QUERY2” option.
Cooperation with “TABLE2” option.


SYNC=INSERT
Cooperation with “TABLE2” and “UNIQUE”
option.


SYNC=UPDATE/DELETE
SYNC=DELINS/INSUPD/UPDINS
From MySQL to MySQL
MYSQLCOPY
Reference

Multiple Thread Please Refer MYSQL2ORA.
Attention


Source & Target Database Connection Using
the Same Character Set.
Will Support Different Character Set Soon.
SYNC Option (mysqlcopy)


Auto Generate Target SQL without specify
the “QUERY2” option.
Cooperation with “TABLE2” option.


SYNC=INSERT/ARRINS
Cooperation with “TABLE2” and “UNIQUE”
option.

SYNC=UPDATE/DELETE/INSUPD/ARRUPD
Target SQL (mysqlcopy)

ARRINS


INSUPD


Insert into emp (…) values (…), (…), (…)
Insert into emp (…) values (…) on duplicate
key …
ARRUPD

Insert into emp (…) values (…) (…) (…) on
duplicate key …
From MySQL to Text File
MYSQLULDR2
Reference


Text File Format Please Refer SQLULDR2.
Multiple Thread Please Refer MYSQL2ORA.
Load Into Target Directly


Support In Memory “Load Local Infile”
Interface by “LOAD” and “TABLE” option with
File Format.
Example

mysqluldr2 user=/@::test load=/@::test
table=emp2
Resrouce
DOWNLOAD
Links










http://www.mydul.net/software/datacopy.zip
http://www.mydul.net/software/ora2mysql.zip
http://www.mydul.net/software/ora2mysql_linux64.zip
http://www.mydul.net/software/sqluldr.zip
http://www.mydul.net/software/mysql2ora.zip
http://www.mydul.net/software/mysql2ora_linux64.zip
http://www.mydul.net/software/mysqlcopy.zip
http://www.mydul.net/software/mysqlcopy_linux64.zip
http://www.mydul.net/software/mysqluldr2.zip
http://www.mydul.net/software/mysqluldr2_linux64.zip