Use the AMP (Alibaba Migration Platfrom) artifact to easily migrate data in the Oracle database to the MySQL database, migrationplatfrom
Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/44926187
Upload the AMP package and decompress it: tar-jxvf amp.2.1.0.tar
Start AMP: nohup sh start. sh>/tmp/log.txt 1 &> 2
1. I deployed the AMP migration tool on 192.168.1.8 and installed the MySQL database on 192.168.1.8, which can be normally connected as follows:
2. Configure the AMP as follows:
3. Click Save and pre-check. The following error is returned:
Set lower_table_case_name for the Case sensitivity of the target database. What is it?
Mysql> show variables like '% case % ';
+ ------------------------ + ------- +
| Variable_name | Value |
+ ------------------------ + ------- +
| Lower_case_file_system | OFF |
| Lower_case_table_names | 0 |
+ ------------------------ + ------- +
Lower_case_table_names --- case sensitive. lower_case_table_names = 1 makes MySQL Case Insensitive!
+ -----------------------
Vi/u01/my3306/my. cnf --- add
Lower_case_table_names = 1
Restart 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 |
+ ------------------------ + ------- +
Change 0 to 1.
There are several errors after the conversion is complete, which are summarized as follows!
------------------ Function indexes cannot be used.
Create index IDX_TUN on TB_USERBANK (TRIM (USER_LOGIN ));
Change
Create index IDX_TUN on TB_USERBANK (USER_LOGIN );
Create index 'idx _ custno' ON 'dbpa'. 'tb _ FINSERIAL '('trim (CUSTNO )');
Change
Create index 'idx _ custno' ON 'dbpa'. 'tb _ FINSERIAL '('ustno ');
Create index 'idx _ FUNC_CHARGE_PAY_BILL 'ON 'dbcharge'. 'charge _ PAY_BILL' ('substr (ORDER_ID, 2, 16 )');
Change
Create index 'idx _ FUNC_CHARGE_PAY_BILL 'ON 'dbcharge'. 'charge _ PAY_BILL' ('order _ id ');
----------------- The default value of the field in the table cannot be 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
);
Change
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, It is 65535. this includes des storage overhead, check the manual. you have to change some columns to TEXT or BLOBs
Create table if not exists 'dbpa'. '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 _ SS ss' CHAR (1 ),
'Object _ name' VARCHAR (500 ),
'Object _ LONG_NAME 'VARCHAR (4000 ),
'Object _ scheme' VARCHAR (30 ),
'Original _ OBJECT_SCHEMA 'VARCHAR (30 ),
'Partition _ name' VARCHAR (30 ),
'Subpartition _ name' VARCHAR (30 ),
'Flags' BIGINT,
'Properties' 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,
'Parallconfiguration' BIGINT,
'Unload _ method' BIGINT,
'Granrules' 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,
'Gree' BIGINT,
'Platform' VARCHAR (101 ),
'Abort _ step' BIGINT,
'Instance' VARCHAR (60)
) Engine = INNODB charset = UTF8 COLLATE UTF8_bin comment = 'data Pump Master Table EXPORT scheme ';
Change VARCHAR (500) over 500 to TEXT
Create table if not exists 'dbpa'. '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 _ SS ss' CHAR (1 ),
'Object _ name' TEXT (500 ),
'Object _ LONG_NAME 'TEXT (4000 ),
'Object _ scheme' VARCHAR (30 ),
'Original _ OBJECT_SCHEMA 'VARCHAR (30 ),
'Partition _ name' VARCHAR (30 ),
'Subpartition _ name' VARCHAR (30 ),
'Flags' BIGINT,
'Properties' 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,
'Parallconfiguration' BIGINT,
'Unload _ method' BIGINT,
'Granrules' 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,
'Gree' BIGINT,
'Platform' VARCHAR (101 ),
'Abort _ step' BIGINT,
'Instance' VARCHAR (60)
) Engine = INNODB charset = UTF8 COLLATE UTF8_bin comment = 'data Pump Master Table EXPORT scheme ';
[Specified key was too long; max key length is 767 bytes]: indicates that if the length of this field exceeds 255, the index creation will receive a warning:
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)
)
Convert UNIQUE_ID VARCHAR2 (256) not null. If it is less than 256, no error is returned.
Create index 'name _ LIST_INDEX1 'ON 'dbrisk'. 'name _ List' ('unique _ id ');
