使用Docker转换Oracle数据库.DMP文件格式 Linux

前言 .DMP为Oracle的数据库导出格式，针对用exp指令导出的.DMP格式文件，可以使用Docker for Linux安装Oracle进行导入并转换为其他格式。使用Docker相较于其他方法比较不依赖于环境，并且便于清理。经过无数次的踩坑，现在把可以完整导入包含中文字符的.DMP文件教程详述如下。
过程拉取镜像：

docker pull wnameless/oracle-xe-11g-r2

创建容器：

docker run -d --name=oracle_vm -p1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g-r2

用Navicat Premium 12尝试连接：

连接参数	值
IP地址	docker宿主机的IP地址
端口	1521
SID	xe
服务名	xe
用户名	system
密码	oracle

连接报错：
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
因为默认禁止了远程连接
确认能连接上之后就继续操作，把.DMP复制到容器里

docker cp demo.DMP oracle_vm:/root

进入容器

docker exec -it oracle_vm bash

执行导入操作

imp system/oracle file=/root/demo.DMP full=y ignore=y

执行结果为

Import: Release 11.2.0.2.0 - Production on Fri Jan 10 08:05:06 2020Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionExport file created by EXPORT:V10.02.01 via conventional pathWarning: the objects were exported by NEUSOFT, not by youimport done in US7ASCII character set and AL16UTF16 NCHAR character set# 这里AL16UTF16是当前的NLS_LANG环境变量的值 import server uses ZHS16GBK character set (possible charset conversion) export client uses ZHS16GBK character set (possible charset conversion)# 这里ZHS16GBK显示的是.DMP文件的编码 . importing NEUSOFT's objects into SYSTEM . importing NEUSOFT's objects into SYSTEM IMP-00017: following statement failed with ORACLE error 957: "CREATE TABLE "XXXX_TABLE" ("????" VARCHAR2(100), "????" VARCHAR2(100), " ""????" VARCHAR2(50), "????" VARCHAR2(3) NOT NULL ENABLE, "????" VARCHAR2(3)" " NOT NULL ENABLE, "????" VARCHAR2(20) NOT NULL ENABLE, "????" VARCHAR2(200)" ", "????" DATE NOT NULL ENABLE, "????" VARCHAR2(20), "??" VARCHAR2(20), "???" "??" VARCHAR2(18), "????" CHAR(1), "?????" VARCHAR2(18) NOT NULL ENABLE, "??" "?" VARCHAR2(18) NOT NULL ENABLE, "?????" NUMBER(12, 2), "????" NUMBER(12, 2" "), "????" NUMBER(12, 2), "????" NUMBER(12, 2), "????" NUMBER(12, 2), "????"" " NUMBER(12, 2), "?????" NUMBER(12, 2), "?????" NUMBER(12, 2), "????" NUMBER" "(12, 2), "??" VARCHAR2(3), "????" NUMBER(12, 2), "?????" NUMBER(12, 2), "??" "???" NUMBER(12, 2), "????" NUMBER(12, 2), "????" CHAR(1), "?????" NUMBER(12" ", 2), "?????" NUMBER, "????" NUMBER(12, 2), "????" NUMBER(12, 2), "????" NU" "MBER(12, 2), "?????" NUMBER, "?????" NUMBER, "?????" NUMBER, "?????" NUMBER" ", "?????" NUMBER, "?????" CHAR(1), "?????" NUMBER, "?????" NUMBER, "?????" " "NUMBER, "????" NUMBER(12, 2), "????" NUMBER(12, 2), "????" VARCHAR2(3), "??" "??" DATE, "?????" VARCHAR2(20), "????" DATE, "????" VARCHAR2(3), "?????" VA" "RCHAR2(20), "?????" VARCHAR2(20), "?????" VARCHAR2(50), "????" VARCHAR2(14)" " NOT NULL ENABLE, "????" VARCHAR2(100), "????" VARCHAR2(3), "????" VARCHAR2" "(4), "????" NUMBER(4, 0), "?????" VARCHAR2(20), "???" VARCHAR2(20), "????" " "DATE, "????" VARCHAR2(30), "????" VARCHAR2(10), "????" CHAR(1), "????" CHAR" "(1), "????" VARCHAR2(3), "??" CHAR(1), "????" VARCHAR2(3) NOT NULL ENABLE, " ""??" CHAR(1), "??" VARCHAR2(3), "??" VARCHAR2(3), "???" VARCHAR2(6))PCTFR" "EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 7340032 FREELISTS " "1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPR" "ESS" IMP-00003: ORACLE error 957 encountered ORA-00957: duplicate column name Import terminated successfully with warnings.

为了解决这个问题，要改变NLS_LANG环境变量和.DMP文件匹配，就可以导入:

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

但是经过格式转换会出现列长度溢出的情况:

value too large for column "SYSTEM"."XXXX_TABLE"."??" (actual: 24, maximum: 20)

【使用Docker转换Oracle数据库.DMP文件格式】还需要再改数据库的编码为正确的编码才可以正确导入
在容器终端输入sqlplus /nolog，并登录为sysdba：

sqlplus /nolog connect sys/oracle as SYSDBA; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; SHUTDOWN IMMEDIATE; STARTUP;

再次导入提示：

Import: Release 11.2.0.2.0 - Production on Fri Jan 10 08:14:47 2020Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionExport file created by EXPORT:V10.02.01 via conventional pathWarning: the objects were exported by NEUSOFT, not by youimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing NEUSOFT's objects into SYSTEM . importing NEUSOFT's objects into SYSTEM . . importing table"XXXX_TABLE" IMP-00058: ORACLE error 904 encountered ORA-00904: "??ǚ": invalid identifier IMP-00017: following statement failed with ORACLE error 20000: "DECLARESREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '31303139'; SREC.MAX" "VAL := 'F7C6B9C7CDD1CEBB'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS" " := DBMS_STATS.NUMARRAY(255400001549791000000000000000000000,12865279602060" "30000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC" " := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"XXXX_TABLE"','"??2??"', NU" "LL ,NULL,NULL,1324,.000755287009063444,2024,srec,9,6); END; " IMP-00003: ORACLE error 20000 encountered ORA-20000: Unable to set values for column ??2??: does not exist or insufficient privileges ORA-06512: at "SYS.DBMS_STATS", line 8837 ORA-06512: at "SYS.DBMS_STATS", line 9103 ORA-06512: at line 1 Import terminated successfully with warnings.

这是说明帐号权限不对，要用sysdba权限才可以导入:

imp "'sys/oracle as sysdba'" rows=y indexes=n buffer=2800000 file=/root/demo.DMP ignore=y feedback=1000 full=y

成功导入后尝试在Navicat里查看，发现报错:

ORA-28547: connection to server failed, probable Oracle Net admin error

问题在于Navicat自带的ori.dll是lite版，不支持GBK编码的连接，因此导致错误，从这里下载并在navicat里指定ori.dll为下载的版本即可。http://www.itmop.com/downinfo/332917.html
成功连接上后，在sys的表空间里可以找到导入的表，用Navicat随便导出。
参考