[마이그] oracle to mysql migration by manual scripts / oracle to mysql 이관하기

dbconvert는 12C multitenant인 환경에서 비정상 작동을 해버린다.... pdb... 빌어먹을...

그리하여 제일 싫어하는 수동 이관방법을 찾아보고 시험을 해보려 한다

tpcc스키마의 

order_line 테이블 기준으로 설명을 한다

[ora10@oracle admin]$ exp system/******** file=table.dmp log=log.log tables=TPCC.ORDER_LINE rows=n; Export: Release 10.2.0.5.0 - Production on Sun Jun 4 08:42:10 2017 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export specified tables via Conventional Path ... Current user changed to TPCC . . exporting table                     ORDER_LINE Export terminated successfully without warnings. [ora10@oracle admin]$

SQL> desc tpcc.order_line;  Name   Null?    Type  ----------------------------------------- -------- ----------------------------  OL_W_ID   NOT NULL NUMBER  OL_D_ID   NOT NULL NUMBER  OL_O_ID   NOT NULL NUMBER  OL_NUMBER   NOT NULL NUMBER  OL_I_ID    NUMBER  OL_DELIVERY_D    DATE  OL_AMOUNT    NUMBER  OL_SUPPLY_W_ID    NUMBER  OL_QUANTITY    NUMBER  OL_DIST_INFO    CHAR(24) SQL> select count(*) from tpcc.order_line;            COUNT(*) ----------     986094

테이블정보(오라클)은 확인했고

toad나 sqlplus 혹은 sqlgate, orage 등의 여러가지 방법을 통해 데이터를 뽑는다

하지만! 여기서 주의해야 할점은

특정 컬럼들은 긴 String을 저장할것이고 그렇다면 컬럼에 심심치 않게 쉼표 , 를 쓸것이다 ' " 등등 모두 포함이다

field termination이 , 쉼표로 정의되어있어 긴 문장이 있고 쉼표가 포함된 String이 있는 컬럼으로 인해

컬럼순서가 안맞고 데이터또한 안맞는 현상이 일어난다..

(ERROR 1262 (01000): Row 3 was truncated; it contained more data than there were input columns) 같은

그래서 pipe를사용한다.. 다른어떤것을 써도 상관없다 쉼표만 쓰지말자.. 절대..

데이터를 뽑는다면? date 형식 때문에

toad에서 뽑으면

1|1|13|10|92574|2015/04/18 오전 12:17:29|21.9|1|5|I0aQ3NGd5x6pSUrr0fCFycKr

1|1|13|11|93366|2015/04/18 오전 12:17:29|13.22|1|5|SqMvlJAdC0TWJjOw8NUCOjZJ

1|1|13|12|51087|2015/04/18 오전 12:17:29|60.04|1|5|M0XAPjCFJgIFx4tTjUf8Qvui

sqlplus를 통해서 뽑으면

1|1|13|10|92574|18-APR-15|21.9|1|5|I0aQ3NGd5x6pSUrr0fCFycKr

1|1|13|11|93366|18-APR-15|13.22|1|5|SqMvlJAdC0TWJjOw8NUCOjZJ

1|1|13|12|51087|18-APR-15|60.04|1|5|M0XAPjCFJgIFx4tTjUf8Qvui

1|1|13|13|78470|18-APR-15|26.54|1|5|SRb5yhnjN4rcF9WtyCpfEOzD

으로 다르게 나온다..

물론 위 두개의 값으로 작업을 한다면 심심치 않은 문제가 발생할것이다

그러면? 포멧을 통일 해줘야지?

toad에서 view에 들어가서 toad option 에서 data grids > data에서 date, time format을 조정한다

1|1|13|10|92574|2015-04-18 00:17:29|21.9|1|5|I0aQ3NGd5x6pSUrr0fCFycKr

1|1|13|11|93366|2015-04-18 00:17:29|13.22|1|5|SqMvlJAdC0TWJjOw8NUCOjZJ

1|1|13|12|51087|2015-04-18 00:17:29|60.04|1|5|M0XAPjCFJgIFx4tTjUf8Qvui

1|1|13|13|78470|2015-04-18 00:17:29|26.54|1|5|SRb5yhnjN4rcF9WtyCpfEOzD

보기좋은 포멧으로 변경 되었다면 mysql쪽으로 가서 load data를 사용하도록 하자...

mysql> use tpcc Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_tpcc | +----------------+ | CUSTOMER       | | DISTRICT       | | HISTORY        | | ITEM           | | NEW_ORDER      | | ORDERS         | | ORDER_LINE     | | STOCK          | | T              | | WAREHOUSE      | +----------------+ 10 rows in set (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `ORDER_LINE2` (     ->   `OL_W_ID` decimal(22,0) NOT NULL,     ->   `OL_D_ID` decimal(22,0) NOT NULL,     ->   `OL_O_ID` decimal(22,0) NOT NULL,     ->   `OL_NUMBER` decimal(22,0) NOT NULL,     ->   `OL_I_ID` decimal(22,0) DEFAULT NULL,     ->   `OL_DELIVERY_D` datetime DEFAULT NULL,     ->   `OL_AMOUNT` decimal(22,0) DEFAULT NULL,     ->   `OL_SUPPLY_W_ID` decimal(22,0) DEFAULT NULL,     ->   `OL_QUANTITY` decimal(22,0) DEFAULT NULL,     ->   `OL_DIST_INFO` varchar(24) COLLATE utf8_bin DEFAULT NULL,     ->   PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; Query OK, 0 rows affected (0.86 sec) mysql> show tables; +----------------+ | Tables_in_tpcc | +----------------+ | CUSTOMER       | | DISTRICT       | | HISTORY        | | ITEM           | | NEW_ORDER      | | ORDERS         | | ORDER_LINE     | | ORDER_LINE2    | | STOCK          | | T              | | WAREHOUSE      | +----------------+ 11 rows in set (0.00 sec) mysql>

data를 load 하자

mysql> LOAD DATA INFILE '/ora10/tabledump/ORDER_LINE2.txt'     -> INTO TABLE ORDER_LINE2     ->   FIELDS TERMINATED BY '|'     ->   LINES TERMINATED BY '\r\n'     ->   IGNORE 1 LINES     -> (OL_W_ID, OL_D_ID,OL_O_ID,OL_NUMBER,OL_I_ID,@OL_DELIVERY_D,OL_AMOUNT,OL_SUPPLY_W_ID,OL_QUANTITY,OL_DIST_INFO)     -> SET OL_DELIVERY_D = STR_TO_DATE(@OL_DELIVERY_D, '%Y-%m-%d %H:%i:%s'); Query OK, 986094 rows affected, 65535 warnings (2 min 38.31 sec) Records: 986094  Deleted: 0  Skipped: 0  Warnings: 887171  mysql> select count(*) from ORDER_LINE2; +----------+ | count(*) | +----------+ |   986094 | +----------+ 1 row in set (0.29 sec)

*위에서 데이터포멧을 맞춰준것은 SET절에서 DATE형식에 맞추기 위함이다 '%Y-%m-%d %H:%i:%s' 요부분은 아래의 정의를 보고 잘 조정하도록 한다

http://www.w3resource.com/mysql/date-and-time-functions/mysql-date_format-function.php

정상적으로 모든 데이터가 들어갔고 툴을 사용하면 2시간 걸리던 데이터가 2분38초만에 모두 들어갔다

oracle to mysql은 위와 같이 테이블단위로 이관도 가능하겠다

번거롭겠지만..