天下數(shù)據(jù)---做天下最好的IDC服務(wù)商
利用AMP(Alibaba Migration Platfrom)神器輕松把Oracle數(shù)據(jù)庫(kù)中的數(shù)據(jù)遷移到MySQL數(shù)據(jù)庫(kù) 啟動(dòng)AMP:nohup sh start.sh > /tmp/log.txt 1 & >2 1、我把AMP遷移工具部署到了192.168.1.8上，在192.168.1.8上也裝了MySQL數(shù)據(jù)庫(kù)，可以正常連接，如下：
 2、配置AMP,如下
 3、點(diǎn)保存并預(yù)檢查，報(bào)錯(cuò)如下
 目標(biāo)庫(kù)的大小寫敏感設(shè)置 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不區(qū)分大小寫! +----------------------- 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就搞定了
 轉(zhuǎn)換完成后還有幾個(gè)報(bào)錯(cuò)，總結(jié)如下! ------------------不能用函數(shù)索引 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`); -----------------表中的字段默認(rèn)值不能用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)超過(guò)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 '; 錯(cuò)誤失敗原因[Specified key was too long; max key length is 767 bytes]：意思這個(gè)字段如果超過(guò)255長(zhǎng)度，建索引會(huì)有警告： 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) ) 轉(zhuǎn) UNIQUE_ID VARCHAR2(256) not null, 搞成小于256就不會(huì)報(bào)錯(cuò)了。 CREATE INDEX `NAME_LIST_INDEX1` ON `DBRISK`.`NAME_LIST` (`UNIQUE_ID`); |