利用AMP(Alibaba Migration Platfrom)神器轻松把Oracle数据库中的数据迁移到MySQL数据库

转载请注明出处： http://blog.csdn.net/guoyjoe/article/details/44926187

 上传AMP包并解压：tar -jxvf amp.2.1.0.tar

 启动AMP:nohup sh start.sh > /tmp/log.txt 1 & >2

1、我把AMP迁移工具部署到了192.168.1.8上，在192.168.1.8上也装了MySQL数据库，可以正常连接，如下：

 

2、配置AMP,如下

3、点保存并预检查，报错如下

目标库的大小写敏感设置 lower_table_case_name，看一下是什么？

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0    |
+------------------------+-------+

lower_case_table_names ---大小写敏感,lower_case_table_names=1 让MySQL不区分大小写！

+-----------------------


vi /u01/my3306/my.cnf  ---添加
lower_case_table_names=1
   
重启mysql
mysqld_safe --defaults-file=/u01/mysql3306/my.cnf --user=mysql &


mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+

把0改成1就搞定了

转换完成后还有几个报错，总结如下！

------------------不能用函数索引
 create index IDX_TUN on TB_USERBANK (TRIM(USER_LOGIN));   
 改
 create index IDX_TUN on TB_USERBANK (USER_LOGIN);


CREATE INDEX `IDX_CUSTNO` ON `DBPAY`.`TB_FINSERIAL` (`TRIM(CUSTNO)`); 
改
CREATE INDEX `IDX_CUSTNO` ON `DBPAY`.`TB_FINSERIAL` (`USTNO`); 


CREATE INDEX `IDX_FUNC_CHARGE_PAY_BILL` ON `DBCHARGE`.`CHARGE_PAY_BILL` (`SUBSTR(ORDER_ID,2,16)`); 
改
 CREATE INDEX `IDX_FUNC_CHARGE_PAY_BILL` ON `DBCHARGE`.`CHARGE_PAY_BILL` (`ORDER_ID`); 


-----------------表中的字段默认值不能用TO_CHAR(SYSDATE,'YYYYMMDD')
create table TB_ORDERINFO                                   
(
  OID_BILLNO  VARCHAR(32),
  ORDER_ID    VARCHAR(64) not null,
  OID_CHNL    VARCHAR(2) not null,
  TYPE_DC     VARCHAR(1) not null,
  OID_REGUSER VARCHAR(32) not null,
  DT_SYS      DATE,
  TRANS_DATE  CHAR(8) default TO_CHAR(SYSDATE,'YYYYMMDD') not null
);
改
create table TB_ORDERINFO                                  
(
  OID_BILLNO  VARCHAR(32),
  ORDER_ID    VARCHAR(64) not null,
  OID_CHNL    VARCHAR(2) not null,
  TYPE_DC     VARCHAR(1) not null,
  OID_REGUSER VARCHAR(32) not null,
  DT_SYS      DATE,
  TRANS_DATE  CHAR(8)  not null
);




-----------------Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs


CREATE TABLE IF NOT EXISTS `DBPAY`.`SYS_EXPORT_SCHEMA_01` (
    `PROCESS_ORDER` BIGINT, 
    `DUPLICATE` BIGINT, 
    `DUMP_FILEID` BIGINT, 
    `DUMP_POSITION` BIGINT, 
    `DUMP_LENGTH` BIGINT, 
    `DUMP_ALLOCATION` BIGINT, 
    `COMPLETED_ROWS` BIGINT, 
    `ERROR_COUNT` BIGINT, 
    `ELAPSED_TIME` BIGINT, 
    `OBJECT_TYPE_PATH` VARCHAR(200), 
    `OBJECT_PATH_SEQNO` BIGINT, 
    `OBJECT_TYPE` VARCHAR(30), 
    `IN_PROGRESS` CHAR(1), 
    `OBJECT_NAME` VARCHAR(500), 
    `OBJECT_LONG_NAME` VARCHAR(4000), 
    `OBJECT_SCHEMA` VARCHAR(30), 
    `ORIGINAL_OBJECT_SCHEMA` VARCHAR(30), 
    `PARTITION_NAME` VARCHAR(30), 
    `SUBPARTITION_NAME` VARCHAR(30), 
    `FLAGS` BIGINT, 
    `PROPERTY` BIGINT, 
    `COMPLETION_TIME` DATETIME, 
    `OBJECT_TABLESPACE` VARCHAR(30), 
    `SIZE_ESTIMATE` BIGINT, 
    `OBJECT_ROW` BIGINT, 
    `PROCESSING_STATE` CHAR(1), 
    `PROCESSING_STATUS` CHAR(1), 
    `BASE_PROCESS_ORDER` BIGINT, 
    `BASE_OBJECT_TYPE` VARCHAR(30), 
    `BASE_OBJECT_NAME` VARCHAR(30), 
    `BASE_OBJECT_SCHEMA` VARCHAR(30), 
    `ANCESTOR_PROCESS_ORDER` BIGINT, 
    `DOMAIN_PROCESS_ORDER` BIGINT, 
    `PARALLELIZATION` BIGINT, 
    `UNLOAD_METHOD` BIGINT, 
    `GRANULES` BIGINT, 
    `SCN` BIGINT, 
    `GRANTOR` VARCHAR(30), 
    `XML_CLOB` LONGTEXT, 
    `NAME` VARCHAR(30), 
    `VALUE_T` VARCHAR(4000), 
    `VALUE_N` BIGINT, 
    `IS_DEFAULT` BIGINT, 
    `FILE_TYPE` BIGINT, 
    `USER_DIRECTORY` VARCHAR(4000), 
    `USER_FILE_NAME` VARCHAR(4000), 
    `FILE_NAME` VARCHAR(4000), 
    `EXTEND_SIZE` BIGINT, 
    `FILE_MAX_SIZE` BIGINT, 
    `PROCESS_NAME` VARCHAR(30), 
    `LAST_UPDATE` DATETIME, 
    `WORK_ITEM` VARCHAR(30), 
    `OBJECT_NUMBER` BIGINT, 
    `COMPLETED_BYTES` BIGINT, 
    `TOTAL_BYTES` BIGINT, 
    `METADATA_IO` BIGINT, 
    `DATA_IO` BIGINT, 
    `CUMULATIVE_TIME` BIGINT, 
    `PACKET_NUMBER` BIGINT, 
    `OLD_VALUE` VARCHAR(4000), 
    `SEED` BIGINT, 
    `LAST_FILE` BIGINT, 
    `USER_NAME` VARCHAR(30), 
    `OPERATION` VARCHAR(30), 
    `JOB_MODE` VARCHAR(30), 
    `CONTROL_QUEUE` VARCHAR(30), 
    `STATUS_QUEUE` VARCHAR(30), 
    `REMOTE_LINK` VARCHAR(4000), 
    `VERSION` BIGINT, 
    `DB_VERSION` VARCHAR(30), 
    `TIMEZONE` VARCHAR(64), 
    `STATE` VARCHAR(30), 
    `PHASE` BIGINT, 
    `GUID` VARBINARY(16), 
    `START_TIME` DATETIME, 
    `BLOCK_SIZE` BIGINT, 
    `METADATA_BUFFER_SIZE` BIGINT, 
    `DATA_BUFFER_SIZE` BIGINT, 
    `DEGREE` BIGINT, 
    `PLATFORM` VARCHAR(101), 
    `ABORT_STEP` BIGINT, 
    `INSTANCE` VARCHAR(60)
) engine=INNODB charset=UTF8 COLLATE UTF8_bin comment = 'Data Pump Master Table EXPORT                         SCHEMA                        ';
改主要把VARCHAR(500)超过500全改成TEXT
CREATE TABLE IF NOT EXISTS `DBPAY`.`SYS_EXPORT_SCHEMA_01` (
    `PROCESS_ORDER` BIGINT, 
    `DUPLICATE` BIGINT, 
    `DUMP_FILEID` BIGINT, 
    `DUMP_POSITION` BIGINT, 
    `DUMP_LENGTH` BIGINT, 
    `DUMP_ALLOCATION` BIGINT, 
    `COMPLETED_ROWS` BIGINT, 
    `ERROR_COUNT` BIGINT, 
    `ELAPSED_TIME` BIGINT, 
    `OBJECT_TYPE_PATH` VARCHAR(200), 
    `OBJECT_PATH_SEQNO` BIGINT, 
    `OBJECT_TYPE` VARCHAR(30), 
    `IN_PROGRESS` CHAR(1), 
    `OBJECT_NAME` TEXT(500), 
    `OBJECT_LONG_NAME` TEXT(4000),
    `OBJECT_SCHEMA` VARCHAR(30), 
    `ORIGINAL_OBJECT_SCHEMA` VARCHAR(30), 
    `PARTITION_NAME` VARCHAR(30), 
    `SUBPARTITION_NAME` VARCHAR(30), 
    `FLAGS` BIGINT, 
    `PROPERTY` BIGINT, 
    `COMPLETION_TIME` DATETIME, 
    `OBJECT_TABLESPACE` VARCHAR(30), 
    `SIZE_ESTIMATE` BIGINT, 
    `OBJECT_ROW` BIGINT, 
    `PROCESSING_STATE` CHAR(1), 
    `PROCESSING_STATUS` CHAR(1), 
    `BASE_PROCESS_ORDER` BIGINT, 
    `BASE_OBJECT_TYPE` VARCHAR(30), 
    `BASE_OBJECT_NAME` VARCHAR(30), 
    `BASE_OBJECT_SCHEMA` VARCHAR(30), 
    `ANCESTOR_PROCESS_ORDER` BIGINT, 
    `DOMAIN_PROCESS_ORDER` BIGINT, 
    `PARALLELIZATION` BIGINT, 
    `UNLOAD_METHOD` BIGINT, 
    `GRANULES` BIGINT, 
    `SCN` BIGINT, 
    `GRANTOR` VARCHAR(30), 
    `XML_CLOB` LONGTEXT, 
    `NAME` VARCHAR(30), 
    `VALUE_T` VARCHAR(4000), 
    `VALUE_N` BIGINT, 
    `IS_DEFAULT` BIGINT, 
    `FILE_TYPE` BIGINT, 
    `USER_DIRECTORY` TEXT(4000),
    `USER_FILE_NAME` TEXT(4000),
    `FILE_NAME` TEXT(4000), 
    `EXTEND_SIZE` BIGINT, 
    `FILE_MAX_SIZE` BIGINT, 
    `PROCESS_NAME` VARCHAR(30), 
    `LAST_UPDATE` DATETIME, 
    `WORK_ITEM` VARCHAR(30), 
    `OBJECT_NUMBER` BIGINT, 
    `COMPLETED_BYTES` BIGINT, 
    `TOTAL_BYTES` BIGINT, 
    `METADATA_IO` BIGINT, 
    `DATA_IO` BIGINT, 
    `CUMULATIVE_TIME` BIGINT, 
    `PACKET_NUMBER` BIGINT, 
    `OLD_VALUE` TEXT(4000),
    `SEED` BIGINT, 
    `LAST_FILE` BIGINT, 
    `USER_NAME` VARCHAR(30), 
    `OPERATION` VARCHAR(30), 
    `JOB_MODE` VARCHAR(30), 
    `CONTROL_QUEUE` VARCHAR(30), 
    `STATUS_QUEUE` VARCHAR(30), 
    `REMOTE_LINK` TEXT(4000),
    `VERSION` BIGINT, 
    `DB_VERSION` VARCHAR(30), 
    `TIMEZONE` VARCHAR(64), 
    `STATE` VARCHAR(30), 
    `PHASE` BIGINT, 
    `GUID` VARBINARY(16), 
    `START_TIME` DATETIME, 
    `BLOCK_SIZE` BIGINT, 
    `METADATA_BUFFER_SIZE` BIGINT, 
    `DATA_BUFFER_SIZE` BIGINT, 
    `DEGREE` BIGINT, 
    `PLATFORM` VARCHAR(101), 
    `ABORT_STEP` BIGINT, 
    `INSTANCE` VARCHAR(60)
) engine=INNODB charset=UTF8 COLLATE UTF8_bin comment = 'Data Pump Master Table EXPORT                         SCHEMA                        ';




错误失败原因[Specified key was too long; max key length is 767 bytes]：意思这个字段如果超过255长度，建索引会有警告：
CREATE  INDEX `NAME_LIST_INDEX1` ON `DBRISK`.`NAME_LIST` (`UNIQUE_ID`);


create table NAME_LIST
(
  ID          NUMBER(18) not null,
  UNIQUE_ID   VARCHAR2(256) not null,
  NAME        VARCHAR2(255),
  STATUS      NUMBER(2) default 0,
  EXPIRES     TIMESTAMP(6),
  COMMENTS    VARCHAR2(255),
  SOURCE      VARCHAR2(255),
  TAG         VARCHAR2(255) not null,
  TYPE        VARCHAR2(20),
  CREATE_TIME TIMESTAMP(6),
  UPDATE_TIME TIMESTAMP(6),
  TAG_2       VARCHAR2(256),
  RISK_TYPE   VARCHAR2(20),
  RISK_ROLE   VARCHAR2(20),
  OPR_NAME    VARCHAR2(255),
  RISK_SOURCE VARCHAR2(20)
)


转  UNIQUE_ID   VARCHAR2(256) not null,  搞成小于256就不会报错了。
CREATE  INDEX `NAME_LIST_INDEX1` ON `DBRISK`.`NAME_LIST` (`UNIQUE_ID`);