Download
data migration tools n.
Skip this Video
Loading SlideShow in 5 Seconds..
Data Migration Tools PowerPoint Presentation
Download Presentation
Data Migration Tools

Data Migration Tools

200 Views Download Presentation
Download Presentation

Data Migration Tools

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Data Migration Tools By d.c.b.a http://www.anysql.net http://www.mydul.net

  2. Source & Target • Source • Oracle • MySQL • Target • Oracle • MySQL • Flat Text File • Download

  3. Oracle Source • Oracle to Oracle • datacopy • Oracle to MySQL • ora2mysql • Oracle to Flat Text File • SQLULDR2

  4. MySQL Source • MySQL to Oracle • mysql2ora • MySQL to MySQL • mysqlcopy • MySQL to Flat Text File • MYSQLULDR2

  5. 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.

  6. 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

  7. Return Code • Windows • %ERRORLEVEL% • Linux/Unix • $? • Zero value for success without any errors. • Non-zero value for error, different error different return code.

  8. From Oracle To Oracle datacopy

  9. DATACOPY • Manipulate Data Between Oracle Databases. • Operations • Direct Load Insert • Insert • Update • Delete • PL/SQL Block • The SYNC option

  10. 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

  11. 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)”

  12. 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 empset ename = :ename where eno = :eno”

  13. 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”

  14. 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;”

  15. 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

  16. 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;

  17. 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;

  18. 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;

  19. Tuning (datacopy) Multiple Block Read (read=…) Sort Area Size (sort= …) Hash Area Size (hash=…) Direct Path Read (serial=yes) Parallel Query (/*+ PARALLEL … */ Hint)

  20. 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 …

  21. 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”.

  22. Character Set • Source Database • NLS_LANG environment variable • Target Database • “CHARSET” option for basic character set. • “NCHARSET” option for national character set. • From US7ASCII to ZHS16GBK • export NLS_LANG=.US7ASCII • datacopy … charset=ZHS16GBK …

  23. 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 • datacopyparfile=test.par

  24. From Oracle To MySQL ORA2MYSQL

  25. Reference Multiple Thread Support Refer DATACOPY.

  26. 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

  27. Target SQL (ora2mysql) • ARRINS • Insert into emp (…) values (…), (…), (…) • INSUPD • Insert into emp (…) values (…) on duplicate key … • ARRUPD • Insert into emp (…) values (…) (…) (…) on duplicate key …

  28. Character Set MySQL Character Set is Controlled By “CHARSET” option. Oracle Character Set is Controlled By “NLS_LANG” Environment Variable.

  29. From Oracle To Flat File SQLULDR2

  30. Reference Multiple Thread Support Refer DATACOPY.

  31. Flat File Format • Fixed Width Format • How to Split Different Records? • Using the “FIELD” Option. • Using “0xXX” for Any Characters. • How to Split Different Fields? • Using the “RECORD” Option. • Using “0xXX” for Any Characters. • Example • sqluldr2 … field=0x07 record=0x06 …

  32. Output File Name • Dynamic File Name • %y=Year • %m=Month • %d=Day • %w=Week • %b=Batch Count • %p=Thread ID • %t=Timestamp (now() function)

  33. 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 …

  34. 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”.

  35. From MySQL to Oracle MYSQL2ORA

  36. Reference Target Operation Please Refer DATACOPY.

  37. Attention LONG/LOB values larger than 64KB Not Supported Now.

  38. 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=empsplitkey=empno degree=4 …

  39. 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”.

  40. The “SPLITKEY” Logic • Minimum Value • SELECT <splitcol> FROM <table> ORDER BY <splitcol> LIMIT 1 • Maximum Value • 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.

  41. 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

  42. From MySQL to MySQL MYSQLcopy

  43. Reference Multiple Thread Please Refer MYSQL2ORA.

  44. Attention Source & Target Database Connection Using the Same Character Set. Will Support Different Character Set Soon.

  45. 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

  46. Target SQL (mysqlcopy) • ARRINS • Insert into emp (…) values (…), (…), (…) • INSUPD • Insert into emp (…) values (…) on duplicate key … • ARRUPD • Insert into emp (…) values (…) (…) (…) on duplicate key …

  47. From MySQL to Text File MYSQLULDR2

  48. Reference Text File Format Please Refer SQLULDR2. Multiple Thread Please Refer MYSQL2ORA.

  49. 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

  50. Resrouce Download