1 / 51

Data Migration Tools

Data Migration Tools. By d.c.b.a http://www.anysql.net http://www.mydul.net. Source & Target. Source Oracle MySQL Target Oracle MySQL Flat Text File Download. Oracle Source. Oracle to Oracle d atacopy Oracle to MySQL ora2mysql Oracle to Flat Text File SQLULDR2. MySQL Source.

aquila-mccarthy
Download Presentation

Data Migration Tools

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related