一次Oracle导入dmp文件的日志记录

Export: Release 11.2.0.4.0 - Production on Sat Aug 3 11:10:29 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "DAMS"."SYS_EXPORT_TABLE_01":  DAMS/********@10.*.*.*:1526/sjzy directory=DATA_PUMP_DIR DUMPFILE=DAMS_20190803.DMP LOGFILE=DAMS_20190803.LOG TABLES=DA_APP_SYS_INFO,DA_DB_META_DATA,BBBB,AAAA,DA_COLUMN_META_DATA,DA_DATA_ASSET_CARD,DA_DATA_ASSET_CATALOG,DA_ASSET_CARD2CAT_RELATIONSHIP 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.511 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DAMS"."AAAA"                2.652 GB 13070393 rows
. . exported "DAMS"."BBBB"                 206.5 MB  556507 rows
. . exported "DAMS"."CCCC"                 107.7 MB  559744 rows
. . exported "DAMS"."DDDD"              25.05 MB  105779 rows
. . exported "DAMS"."EEEE"     50.52 MB  513861 rows
. . exported "DAMS"."GGGG"                    113.5 KB     127 rows
. . exported "DAMS"."HHHH"                    109.0 KB     127 rows
. . exported "DAMS"."IIII"                38.10 KB     183 rows
Master table "DAMS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DAMS.SYS_EXPORT_TABLE_01 is:
  /oracle/database/product/11.2.0/db_1/rdbms/log/DAMS_20190803.DMP
Job "DAMS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 3 11:12:54 2019 elapsed 0 00:02:20

--IMP尝试，报IMP-00038错误

Microsoft Windows [版本 10.0.18362.476]
(c) 2019 Microsoft Corporation。保留所有权利。
C:\Users\baoqi>imp metadata/XXXX  file=c:\DAMS_20190803.dmp full=y ignore=y
Import: Release 11.2.0.1.0 - Production on 星期三 11月 27 11:55:07 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
IMP-00038: 无法转换为环境字符集句柄
IMP-00000: 未成功终止导入

--阅读导出日志，决定尝试用IMPDB

--创建tt资源目录
C:\Users\baoqi>sqlplus sys/XXXX as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 27 12:00:47 2019
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory tt as 'c:\';
目录已创建。
SQL> ^Z
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

--用IMPDB尝试

C:\Users\baoqi>impdp metadata/XXXX dumpfile=DAMS_20190803.dmp directory=tt
Import: Release 11.2.0.1.0 - Production on 星期三 11月 27 12:01:12 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: 操作无效
ORA-39070: 无法打开日志文件。
ORA-39087: 目录名 TT 无效

--报ORA-39002、ORA-39070: 无法打开日志文件、ORA-39087: 目录名 TT 无效: 操作无效不管三七二十一，给metadata赋上dba权限

C:\Users\baoqi>impdp metadata/XXXX dumpfile=DAMS_20190803.dmp directory=tt
Import: Release 11.2.0.1.0 - Production on 星期三 11月 27 12:50:01 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: 参数值无效
ORA-39000: 转储文件说明错误
ORA-31640: 无法打开要读取的转储文件 "c:\DAMS_20190803.dmp"
ORA-27041: 无法打开文件
OSD-04002: ？???？？?
O/S-Error: (OS 2) ？？？????？???？？???

--还是报错，初步判断是目录权限的问题，修改相关目录

--创建tt资源目录

C:\Users\baoqi>sqlplus sys/XXXX as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 27 12:00:47 2019
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory tt as 'c:\Oracle';
目录已创建。
SQL> ^Z
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

--再次执行导入，这次错误变了

C:\Users\baoqi>impdp metadata/XXXX dumpfile=DAMS_20190803.dmp directory=tt
Import: Release 11.2.0.1.0 - Production on 星期三 11月 27 12:50:16 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "METADATA"."SYS_IMPORT_FULL_01"
启动 "METADATA"."SYS_IMPORT_FULL_01":  metadata/******** dumpfile=DAMS_20190803.dmp directory=tt
处理对象类型 TABLE_EXPORT/TABLE/TABLE
ORA-39083: 对象类型 TABLE:"DAMS"."AAAA" 创建失败, 出现错误:
ORA-01918: 用户 'DAMS' 不存在
失败的 sql 为:
CREATE TABLE "DAMS"."AAAA" ("ID" VARCHAR2(50 CHAR) NOT NULL ENABLE, "TABLE_NAME" VARCHAR2(200 CHAR), "TABLE_NAME_CHS" VARCHAR2(2000 CHAR), "SCHEMA_NAME" VARCHAR2(200 CHAR), "DATABASE_ID" VARCHAR2(50 CHAR), "APP_SYS_ID" VARCHAR2(50 CHAR), "TABLE_STATUS" VARCHAR2(200 CHAR), "TABLE_DESC" VARCHAR2(2000 CHAR), "CREATE_DATE" DATE, "CREATOR" VARCHAR2(100 CHAR),
ORA-39083: 对象类型 TABLE:"DAMS"."DA_DATA_ASSET_CARD" 创建失败, 出现错误:
ORA-01918: 用户 'DAMS' 不存在
失败的 sql 为:
CREATE TABLE "DAMS"."BBBB" ("ID" VARCHAR2(60 CHAR) NOT NULL ENABLE, "CREATE_DATE" TIMESTAMP (6), "UPDATE_DATE" TIMESTAMP (6), "APP_SCOPE" VARCHAR2(255 CHAR), "DB_ID" VARCHAR2(255 CHAR), "SCHEMA_NAME" VARCHAR2(255 CHAR), "SYSTEM_ID" VARCHAR2(255 CHAR), "SCHEMA_DESC" VARCHAR2(255 CHAR), "ORDER_ID" VARCHAR2(255 CHAR), "VERSION" NUMBER(10,0), "CENTER_SCHEM
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
处理对象类型 TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "METADATA"."SYS_IMPORT_FULL_01" 已经完成, 但是有 8 个错误 (于 12:50:19 完成)

--变更 再次导入，少了DAMS用户对象
--在数据库中创建DAMS用户

C:\Users\baoqi>impdp metadata/XXXX dumpfile=DAMS_20190803.dmp directory=tt remap_schema=onlinecol:onlinecolzs remap_tablespace=ts_onlinecol_prod:zaixianzs_data
Import: Release 11.2.0.1.0 - Production on 星期三 11月 27 12:53:01 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39146: 方案 "ONLINECOL" 不存在
已成功加载/卸载了主表 "METADATA"."SYS_IMPORT_FULL_01"
启动 "METADATA"."SYS_IMPORT_FULL_01":  metadata/******** dumpfile=DAMS_20190803.dmp directory=tt remap_schema=onlinecol:onlinecolzs remap_tablespace=ts_onlinecol_prod:zaixianzs_data
处理对象类型 TABLE_EXPORT/TABLE/TABLE
ORA-39083: 对象类型 TABLE:"DAMS"."DA_DATA_ASSET_CARD" 创建失败, 出现错误:
ORA-00959: 表空间 'DAMS_DATA' 不存在
失败的 sql 为:
CREATE TABLE "DAMS"."DA_DATA_ASSET_CARD" ("ID" VARCHAR2(50 CHAR) NOT NULL ENABLE, "CARD_NAME" VARCHAR2(1000 CHAR), "TABLE_ID" VARCHAR2(50 CHAR), "BIZ_DESCRIPTION" VARCHAR2(2000 CHAR), "HOT_INDEX" NUMBER DEFAULT (0), "CREATE_DATE" TIMESTAMP (6), "UPDATE_DATE" TIMESTAMP (6), "CARD_STATUS" VARCHAR2(255 CHAR), "DATA_LOAD_STATUS" VARCHAR2(255 CHAR), "MANAGE_DEPT" V
ORA-39083: 对象类型 TABLE:"DAMS"."DA_ASSET_CARD2CAT_RELATIONSHIP" 创建失败, 出现错误:
ORA-00959: 表空间 'DAMS_DATA' 不存在
....
失败的 sql 为:
CREATE TABLE "DAMS"."BBBB" ("ID" VARCHAR2(60 CHAR) NOT NULL ENABLE, "CREATE_DATE" TIMESTAMP (6), "UPDATE_DATE" TIMESTAMP (6), "APP_SCOPE" VARCHAR2(255 CHAR), "DB_ID" VARCHAR2(255 CHAR), "SCHEMA_NAME" VARCHAR2(255 CHAR), "SYSTEM_ID" VARCHAR2(255 CHAR), "SCHEMA_DESC" VARCHAR2(255 CHAR), "ORDER_ID" VARCHAR2(255 CHAR), "VERSION" NUMBER(10,0), "CENT
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
处理对象类型 TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "METADATA"."SYS_IMPORT_FULL_01" 已经完成, 但是有 8 个错误 (于 12:53:02 完成)

--根据提示ORA-00959: 表空间 'DAMS_DATA' 不存在
--在数据库中创建DAMS_DATA表空间，置为DAMS的缺省表空间

C:\Users\baoqi>impdp metadata/XXXX dumpfile=DAMS_20190803.dmp directory=tt remap_schema=onlinecol:onlinecolzs remap_tablespace=ts_onlinecol_prod:zaixianzs_data;
Import: Release 11.2.0.1.0 - Production on 星期三 11月 27 13:00:10 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39146: 方案 "ONLINECOL" 不存在
已成功加载/卸载了主表 "METADATA"."SYS_IMPORT_FULL_01"
启动 "METADATA"."SYS_IMPORT_FULL_01":  metadata/******** dumpfile=DAMS_20190803.dmp directory=tt remap_schema=onlinecol:onlinecolzs remap_tablespace=ts_onlinecol_prod:zaixianzs_data;
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "DAMS"."BBBB"                2.652 GB 13070393 行
. . 导入了 "DAMS"."AAAA"                 206.5 MB  556507 行
. . 导入了 "DAMS"."CCCC"                 107.7 MB  559744 行
. . 导入了 "DAMS"."DDDD"              25.05 MB  105779 行
. . 导入了 "DAMS"."EEEE"     50.52 MB  513861 行
. . 导入了 "DAMS"."FFFF"                    113.5 KB     127 行
. . 导入了 "DAMS"."HHHH"                    109.0 KB     127 行
. . 导入了 "DAMS"."IIII"                38.10 KB     183 行
处理对象类型 TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: 对象类型 OBJECT_GRANT 创建失败, 出现错误:
ORA-01917: 用户或角色 'META_RESULT' 不存在
失败的 sql 为:
GRANT SELECT ON "DAMS"."BBBB" TO "META_RESULT"
ORA-39083: 对象类型 OBJECT_GRANT 创建失败, 出现错误:
ORA-01917: 用户或角色 'DAMS_DP' 不存在
...
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) := 'ID';  DBMS_METADATA.GET_STAT_INDNAME('DAMS','AAAA',c,1,i_o,i_n);   INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "METADATA"."SYS_IMPORT_FULL_01" 已经完成, 但是有 69 个错误 (于 17:00:44 完成)

虽然不完美，但终归解决了