Data migration tools
This presentation is the property of its rightful owner.
Sponsored Links
1 / 51

Data Migration Tools PowerPoint PPT Presentation


  • 107 Views
  • Uploaded on
  • Presentation posted in: General

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.

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

  • User1 Option for Source Database

  • User2 Option for Target Database

  • Oracle Connection

    • [email protected]:Port:Database

    • [email protected]

    • “sys” for SYSDBA logon (Oracle Database)

  • MySQL Connection

    • [email protected]:Port:Database

    • Default Port is 3306


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.


Datacopy

From Oracle To Oracle

datacopy


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

    • datacopy [email protected] [email protected] query1=“select * from emp” table2=emp direct=yes


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

    • datacopy [email protected] [email protected] query1=“select eno, ename from emp” query2=“insert into emp(eno, ename) values (:eno, :ename)”


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

    • datacopy [email protected] [email protected] query1=“select eno, ename from emp” query2=“update empset ename = :ename where eno = :eno”


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

    • datacopy [email protected] [email protected] query1=“select eno from emp” query2=“delete from emp where eno = :eno”


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

    • datacopy [email protected] [email protected] query1=“select eno from emp” query2=“begin delete_emp(:eno); end;”


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

  • Text File (test.par)

    • [email protected]

    • [email protected]

    • query1=select * from emp

    • table2=emp

    • read=128

    • serial=yes

  • Command

    • datacopyparfile=test.par


Ora2mysql

From Oracle To MySQL

ORA2MYSQL


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.


Sqluldr2

From Oracle To Flat File

SQLULDR2


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


Mysql2ora

From MySQL to Oracle

MYSQL2ORA


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


    Mysqlcopy

    From MySQL to MySQL

    MYSQLcopy


    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 …


    Mysqluldr2

    From MySQL to Text File

    MYSQLULDR2


    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


  • Login