Oracle database dmp to mysql format

1, Background

The customer of the first project is oracle database, which needs to import a table data to the mysql database of the self research system. Party A has given the dmp file in oracle format!
Treatment idea:
① . install Oracle database.
② Import the dmp file in Oracle format provided by Party A into our self built oracle.
③ Install a MySQL database or use an existing MySQL database.
④ Use Navicat tool and the tool data transfer function to transfer Oracle data to mysql!

2, Install the Oracle database.

Ignore this step and go through a bunch of installation tutorials.
It is roughly installed using Centos7 desktop server.
Reference connection: https://www.cnblogs.com/nmlwh/p/11923553.html#top

3, Import dmp backup file from Oracle database.

Backup restore reference: https://www.cnblogs.com/xiaohuizhenyoucai/p/11100750.html

① Import with imp command

[oracle@localhost mnt]$  imp system/*******@abc file=/mnt/sxgddzhdaochu20220302.dmp ignore=y full=y

system: oracle user
*****: represents the password
@abc: import data into abc database
File: the absolute path of the imported file.

report errors:

[oracle@localhost mnt]$ imp system/aaaAAA3451@abc file=/mnt/sxgddzhdaochu20220302.dmp ignore=y full=y

Import: Release 12.1.0.2.0 - Production on Tue Mar 8 21:14:59 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SXGDDZH, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SXGDDZH's objects into SYSTEM
. importing SXGDDZH's objects into SYSTEM
. . importing table                     "WO_ORDER"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SYSTEM"."WO_ORDER"."CALLIN_CODE" (actual: 33, maximum: 30)

Cause analysis:
Roughly, the character set of the dmp file does not match the character set of the built abc database, so an error is reported!

terms of settlement:

SQL>connect username/password as SYSDBA;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK ;
ALTER DATABASE CHARACTER SET ZHS16GBK;
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
 Don't panic if you report an error at this time. Prompt that the new character set must be a superset. At this time INTRENAL_USE Directive does not check character set supersets. Enter the following command:

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

Import again after modification:

[oracle@localhost mnt]$ imp system/aaaAAA3451@abc file=/mnt/sxgddzhdaochu20220302.dmp ignore=y full=y

Import: Release 12.1.0.2.0 - Production on Tue Mar 8 21:45:49 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SXGDDZH, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SXGDDZH's objects into SYSTEM
. importing SXGDDZH's objects into SYSTEM
. . importing table                     "WO_ORDER"    1540160 rows imported
Import terminated successfully without warnings.

Check through Navicat management tool that there are already WO_ORDER table!

3, Build Mysql database through Docker.

① . pull mysql image

[oracle@localhost mnt]$  docker pull mysql:5.7.20

② , run mysql container

[root@localhost ~]# docker run -d -p 3306:3306 --name mysql  -v /opt/mysql/data:/var/lib/mysql  -v /opt/mysql/logs:/var/log/mysql  -v /opt/mysql/conf:/etc/mysql/mysql.conf.d  -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.20

Note: remember to close selinux, otherwise an error may be reported.

③ . enter the mysql container and authorize the mysql root user to connect remotely

[root@localhost ~]#  docker exec -it mysql sh
# mysql -uroot -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

At this point, use Navicat tool to connect to mysql successfully.

4, Use the data transfer function of Navicat tool to transfer the specified tables in Oracle database to mysql database.

The syntax of the two databases is different, and the table field types are also different. If the Principality Navicat tool exports and imports, there will be many errors!

① . data transmission

Select the source and destination of the transfer

Select the table to synchronize

start

report errors:

[ERR] 1> 1170 - BLOB/TEXT column 'BLANK3' used in key specification without a key length

The reasons for error reporting can be referred to: https://blog.csdn.net/helloxiaozhe/article/details/83018347
Basically, there are problems in building indexes and constraints!
terms of settlement:
Cancel the index and foreign key constraints!

The data will be transferred from Oracle to MySQL after the constraint is removed! Well, this is the end of the article!
The rest is the synchronization of mysql export table data to project mysql!