Data migration tools
Download
1 / 51

Data Migration Tools - PowerPoint PPT Presentation


  • 168 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Data Migration Tools' - ananda


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Data migration tools

Data Migration Tools

By d.c.b.a

http://www.anysql.net

http://www.mydul.net


Source target
Source & Target

  • Source

    • Oracle

    • MySQL

  • Target

    • Oracle

    • MySQL

    • Flat Text File

  • Download


Oracle source
Oracle Source

  • Oracle to Oracle

    • datacopy

  • Oracle to MySQL

    • ora2mysql

  • Oracle to Flat Text File

    • SQLULDR2


Mysql source
MySQL Source

  • MySQL to Oracle

    • mysql2ora

  • MySQL to MySQL

    • mysqlcopy

  • MySQL to Flat Text File

    • MYSQLULDR2


Basic concept
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
Database Connection


Return code
Return Code

  • Windows

    • %ERRORLEVEL%

  • Linux/Unix

    • $?

  • Zero value for success without any errors.

  • Non-zero value for error, different error different return code.



Datacopy1
DATACOPY

  • Manipulate Data Between Oracle Databases.

  • Operations

    • Direct Load Insert

    • Insert

    • Update

    • Delete

    • PL/SQL Block

  • The SYNC option


Direct load insert datacopy
Direct Load Insert (DATACOPY)

  • Using TABLE2 option to specify the table name on target database.

  • Specify the “direct=yes” option.

  • Example


Insert datacopy
Insert (DATACOPY)

  • Using QUERY2 Option to Specify the Insert Statement on Target Database

  • Refer the source column value by “:” plus column name

  • Example


Update datacopy
Update (datacopy)

  • Using QUERY2 Option to Specify the Update Statement on Target Database

  • Refer the source column value by “:” plus column name

  • Example


Delete datacopy
Delete (DATACOPY)

  • Using QUERY2 Option to Specify the Delete Statement on Target Database

  • Refer the source column value by “:” plus column name

  • Example


Pl sql block datacopy
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


Sync option datacopy
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
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
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
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
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
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
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
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 …


Parameter file
Parameter File



Reference
Reference

Multiple Thread Support Refer DATACOPY.


Sync option ora2mysql
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
Target SQL (ora2mysql)

  • ARRINS

    • Insert into emp (…) values (…), (…), (…)

  • INSUPD

    • Insert into emp (…) values (…) on duplicate key …

  • ARRUPD

    • Insert into emp (…) values (…) (…) (…) on duplicate key …


Character set1
Character Set

MySQL Character Set is Controlled By “CHARSET” option.

Oracle Character Set is Controlled By “NLS_LANG” Environment Variable.



Reference1
Reference

Multiple Thread Support Refer DATACOPY.


Flat file format
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 …


Output file name
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
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 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”.



Reference2
Reference

Target Operation Please Refer DATACOPY.


Attention
Attention

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


Multiple thread copy1
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 …


Why split option1
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
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.


  • Sync option datacopy1
    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



    Reference3
    Reference

    Multiple Thread Please Refer MYSQL2ORA.


    Attention1
    Attention

    Source & Target Database Connection Using the Same Character Set.

    Will Support Different Character Set Soon.


    Sync option mysqlcopy
    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
    Target SQL (mysqlcopy)

    • ARRINS

      • Insert into emp (…) values (…), (…), (…)

    • INSUPD

      • Insert into emp (…) values (…) on duplicate key …

    • ARRUPD

      • Insert into emp (…) values (…) (…) (…) on duplicate key …



    Reference4
    Reference

    Text File Format Please Refer SQLULDR2.

    Multiple Thread Please Refer MYSQL2ORA.


    Load into target directly
    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


    Download

    Resrouce

    Download


    Links
    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


    ad