oracle不同版本导入

oracle数据库不同系统版本导入

1问题

时间：20180620

服务器xx.xx.x.x出现问题

服务器系统 windows—server2008  

数据库版本Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 – Prod

需要恢复的表    系统      XX_XXXX_20180616.DMP

2 恢复到linux系统

恢复到系统xx.xx.x.x

系统

[root@localhost backup]# cat /etc/issue CentOS release 6.5 (Final) oracle数据库版本 SQL>  select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE      11.2.0.4.0        Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

 

 

主要步骤

[root@localhost ~]# su - oracle [oracle@localhost ~]$ sqlplus / as sysdba SQL> show user; USER is "SYS" SQL> set lin 10000 SQL> select * from dba_directories;---查看系统备份文件是否存在 SQL> CREATE USER zat IDENTIFIED BY 123456;---创建备份文件账户   User created.   SQL> GRANT connect,dba to zat;----授予权限   Grant succeeded. SQL>select * from dba_directories;   OWNER                         DIRECTORY_NAME                DIRECTORY_PATH ------------------------------ - SYS                        DMP                       /home/backup   SQL> create directory dmp as '/home/backup';--不存在就创建 ‘’里面是文件夹路径，备份文件所在文件夹 SQL> grant read,write on directory dmp to zat; --授予文件夹权限   Grant succeeded.   SQL> select username, default_tablespace from dba_users ;---查看所属账户所用表空间   SQL> alter tablespace USERS rename to DY;--导入表空间不一致倒入不了   Tablespace altered.   SQL> alter tablespace DY rename to USERS;导入后表空间更改回来   Tablespace altered.   SQL> select username, default_tablespace from dba_users ;

 

linux脚本查看并修改：此文件即相关数据备份dmp文件的权限及所有用户组对应oracle和oinstall 要有执行权限

[oracle@localhost backup]$ cat imp.sh #/home/ backup #!/bin/bash ORACLE_HOME=/home/app/oracle/product/db#oracle软件目录 ORACLE_SID=orcl #服务名 export ORACLE_HOME PATH ORACLE_SID DD=`date +%Y%m%d` export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 impdp zat/123456  directory=dmp dumpfile=XX_XXXX_20180616.DMP  logfile=zats_$DD.log 切换oracle用户到目录下面去执行imp.sh文件

 

报错处理

[root@localhost backup]# head -100 xxxx_20180621.log ;;; Import: Release 11.2.0.4.0 - Production on Thu Jun 21 20:47:50 2018   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, OLAP, Data Mining and Real Application Testing options Master table "XXXX"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "XXXX"."SYS_IMPORT_FULL_01":  xxxx/******** directory=dmp dumpfile=XX_XXXX_20180616.DMP logfile=xxxx_20180621.log Processing object type SCHEMA_EXPORT/USER ORA-39083: Object type USER failed to create with error: ORA-00959: tablespace 'DY' does not exist Failing sql is:  CREATE USER "XX_XXXX" IDENTIFIED BY VALUES '0E1EB8CB9918542F' DEFAULT TABLESPACE "DY" TEMPORARY TABLESPACE "TEMP" Processing object type SCHEMA_EXPORT/SYSTEM_GRANT ORA-39083: Object type SYSTEM_GRANT failed to create with error: ORA-01917: user or role 'XX_XXXX' does not exist

 

创建文件空间

日志

[root@localhost backup]# head zats_20180621.log ;;; Import: Release 11.2.0.4.0 - Production on Thu Jun 21 21:02:52 2018   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, OLAP, Data Mining and Real Application Testing options Master table "ZAT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "ZAT"."SYS_IMPORT_FULL_01":  zat/******** directory=dmp dumpfile=XX_XXXX_20180616.DMP logfile=zats_20180621.log Processing object type SCHEMA_EXPORT/USER

 

[root@localhost backup]# cat zats_20180621.log ;;; Import: Release 11.2.0.4.0 - Production on Thu Jun 21 21:02:52 2018   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, OLAP, Data Mining and Real Application Testing options Master table "ZAT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "ZAT"."SYS_IMPORT_FULL_01":  zat/******** directory=dmp dumpfile=XX_XXXX_20180616.DMP logfile=zats_20180621.log Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/DB_LINK Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "XX_XXXX"."CORP_TOTAL"                      13.88 MB  186154 rows . . imported "XX_XXXX"."SYS_LOG"                         1.830 MB    2001 rows . . imported "XX_XXXX"."PUB_EDUCATE"                     2.153 MB     216 rows . . imported "XX_XXXX"."SYS_USER"                        318.6 KB    2588 rows . . imported "XX_XXXX"."SYS_OFFICE"                      315.3 KB    2737 rows . . imported "XX_XXXX"."FLOW_ROLE_USERS"                 125.7 KB      15 rows . . imported "XX_XXXX"."SYS_AREA"                        199.1 KB    1194 rows . . imported "XX_XXXX"."SYS_MENU"                        140.0 KB     300 rows . . imported "XX_XXXX"."EFF_SUPERVISION"                 13.79 KB       4 rows . . imported "XX_XXXX"."FLOW_INSTENCE"                   11.96 KB       3 rows . . imported "XX_XXXX"."FLOW_MODEL"                      8.796 KB       2 rows . . imported "XX_XXXX"."FLOW_NODE_MODEL"                 12.89 KB      32 rows . . imported "XX_XXXX"."FLOW_OPRT_MODULE"                8.929 KB       3 rows . . imported "XX_XXXX"."FLOW_OPRT_TYPE"                  9.070 KB       1 rows . . imported "XX_XXXX"."FLOW_ROLE"                       7.914 KB      15 rows . . imported "XX_XXXX"."OA_OFFICE"                       10.42 KB      84 rows . . imported "XX_XXXX"."PUB_IMAGE_MANAGEMENT"            9.765 KB      21 rows . . imported "XX_XXXX"."SYS_ATTACH"                      17.17 KB      33 rows . . imported "XX_XXXX"."SYS_DICT"                        41.33 KB     383 rows . . imported "XX_XXXX"."SYS_ROLE"                        8.312 KB       6 rows . . imported "XX_XXXX"."SYS_ROLE_MENU"                   11.61 KB     175 rows . . imported "XX_XXXX"."SYS_USER_OFOUTSIDE"              36.89 KB     592 rows . . imported "XX_XXXX"."SYS_USER_ROLE"                   34.42 KB    2580 rows . . imported "XX_XXXX"."SYS_WARNING"                     9.726 KB       1 rows . . imported "XX_XXXX"."TROUBLE_CLUES"                   49.46 KB       3 rows . . imported "XX_XXXX"."ALARM_RECORDS"                       0 KB       0 rows . . imported "XX_XXXX"."EXAMPLE_TEST"                        0 KB       0 rows . . imported "XX_XXXX"."FLOWACCREDIT"                        0 KB       0 rows . . imported "XX_XXXX"."FLOWACCREDITDETAILS"                 0 KB       0 rows . . imported "XX_XXXX"."FLOW_BASIC_EXAMPLE"                  0 KB       0 rows . . imported "XX_XXXX"."FLOW_NODE_INSTENCE"                  0 KB       0 rows . . imported "XX_XXXX"."FLOW_NODE_INSTENCE_NEW"              0 KB       0 rows . . imported "XX_XXXX"."FLOW_NODE_INSTENCE_PIC"              0 KB       0 rows . . imported "XX_XXXX"."FLOW_OPRT_BILL"                      0 KB       0 rows . . imported "XX_XXXX"."FLOW_ROLE_USERS_MANAGE"              0 KB       0 rows . . imported "XX_XXXX"."FOUR_BUILD_DEMAND_REPORT"            0 KB       0 rows . . imported "XX_XXXX"."FOUR_BUILD_DETAIL_REPORT"            0 KB       0 rows . . imported "XX_XXXX"."FOUR_BUILD_EQUIPMENT"                0 KB       0 rows . . imported "XX_XXXX"."FOUR_BUILD_TYPE"                     0 KB       0 rows . . imported "XX_XXXX"."GROUP_HONOR"                         0 KB       0 rows . . imported "XX_XXXX"."INS_BUILDING"                        0 KB       0 rows . . imported "XX_XXXX"."LABOUR_ANNUAL_REPORT"                0 KB       0 rows . . imported "XX_XXXX"."LABOUR_BUILD_ACT"                    0 KB       0 rows . . imported "XX_XXXX"."LABOUR_FEE_COLLECT"                  0 KB       0 rows . . imported "XX_XXXX"."LABOUR_FEE_LIST"                     0 KB       0 rows . . imported "XX_XXXX"."LABOUR_QUESTIONNAIRE"                0 KB       0 rows . . imported "XX_XXXX"."LABOUR_UNION_FUNDS"                  0 KB       0 rows . . imported "XX_XXXX"."MEMBER_METTING_REGISTER"             0 KB       0 rows . . imported "XX_XXXX"."MESSAGE_SMS_INTF"                    0 KB       0 rows . . imported "XX_XXXX"."MESSAGE_SMS_RECEIVER"                0 KB       0 rows . . imported "XX_XXXX"."MESSAGE_SMS_RECORDS"                 0 KB       0 rows . . imported "XX_XXXX"."MESSAGE_SMS_SAMPLE"                  0 KB       0 rows . . imported "XX_XXXX"."MESSAGE_SMS_TARIFF"                  0 KB       0 rows . . imported "XX_XXXX"."NOTICE_RECEIVER"                     0 KB       0 rows . . imported "XX_XXXX"."NOTICE_RECORDS"                      0 KB       0 rows . . imported "XX_XXXX"."NOTICE_SAMPLE"                       0 KB       0 rows . . imported "XX_XXXX"."ORG_LABOUR"                          0 KB       0 rows . . imported "XX_XXXX"."ORG_PARTY"                           0 KB       0 rows . . imported "XX_XXXX"."PARTY_BUILD_ACT"                     0 KB       0 rows . . imported "XX_XXXX"."PARTY_CENTR_GROUP"                   0 KB       0 rows . . imported "XX_XXXX"."PARTY_FEE"                           0 KB       0 rows . . imported "XX_XXXX"."PARTY_FEE_COLLECT"                   0 KB       0 rows . . imported "XX_XXXX"."PARTY_FEE_LIST"                      0 KB       0 rows . . imported "XX_XXXX"."PSN_HONOR"                           0 KB       0 rows . . imported "XX_XXXX"."PSN_HR_INFO"                         0 KB       0 rows . . imported "XX_XXXX"."PSN_INFO_SIGN"                       0 KB       0 rows . . imported "XX_XXXX"."PSN_PARTY_INFO"                      0 KB       0 rows . . imported "XX_XXXX"."PSN_WARN"                            0 KB       0 rows . . imported "XX_XXXX"."PSN_WORK"                            0 KB       0 rows . . imported "XX_XXXX"."SYS_ROLE_OFFICE"                     0 KB       0 rows . . imported "XX_XXXX"."ZDH_COLSE_METTING_EXECUTE"           0 KB       0 rows . . imported "XX_XXXX"."ZDH_GROUP_SURVEY"                    0 KB       0 rows . . imported "XX_XXXX"."ZDH_PROPOSAL_MANAGER"                0 KB       0 rows . . imported "XX_XXXX"."ZDH_STAFF_REGISTER"                  0 KB       0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job"ZAT"."SYS_IMPORT_FULL_01" successfully completed at ThuJun 21 21:03:12 2018 elapsed 0 00:00:19

成功恢复到linux

3 windows恢复

本地windows7 系统

数据库版本 oracle11.2

 

 

Windows|  bat脚本 @echo off rem impdp 'xxxx/"123456"'  directory=dmp REMAP_SCHEMA=XX_XXXX:xxxx dumpfile=XX_XXXX_20180616.DMP  logfile=xxxx_20180621.log  REMAP_TABLESPACE=XX_XXXX:USERS @forfiles /p e:\backup /s /m * -d -15 /c "cmd /c del /f @file"   创建有户名和密码 CREATE USER xxxx IDENTIFIED BY 123456; 分配权限 GRANT connect,dba to xxxx;   create or replace directory dmp as 'C:\新建文件夹'; grant read,write on directory dmp  to xxxx;   select username,default_tablespace,temporary_tablespace from dba_users where username='xxxx’ select username, default_tablespace from dba_users where username='DY';   alter tablespace USERS rename to DY;

 

创建步骤和linux下一样

 

 

 

 

 

查看select * from CORP_TOTAL;

 

报错

;;;

Import: Release 11.2.0.1.0 - Production on 星期四 6月 21 15:04:11 2018   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 已成功加载/卸载了主表 "XXXX"."SYS_IMPORT_FULL_01" 启动 "XXXX"."SYS_IMPORT_FULL_01":  "xxxx/********" directory=dmp REMAP_SCHEMA=XX_XXXX:xxxx dumpfile=XX_XXXX_20180616.DMP logfile=xxxx_20180621.log REMAP_TABLESPACE=XX_XXXX:USERS 处理对象类型 SCHEMA_EXPORT/USER ORA-31684: 对象类型 USER:"XXXX" 已存在 处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT 处理对象类型 SCHEMA_EXPORT/ROLE_GRANT 处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE 处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 处理对象类型 SCHEMA_EXPORT/DB_LINK ORA-31684: 对象类型 DB_LINK:"XXXX"."LINK.XXXX" 已存在 ORA-31684: 对象类型 DB_LINK:"XXXX"."LINK.HRTEST1" 已存在 处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOWACCREDITDETAILS_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOWACCREDIT_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOWROLE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_BASIC_EXAMPLE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_DEAL_OPTIONAL_IDEA_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_INSTENCE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_MODEL_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_NODE_INSTENCE_NEW_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_NODE_INSTENCE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_NODE_MODEL_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_OPRT_BILL_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_OPRT_MODEL_SQL_CONF_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_OPRT_MODEL_SQL_FIELD_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_OPRT_MODULE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_OPRT_TYPE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_ROLEUSER_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_ROLE_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_TODO_DEAL_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_TRAN_EXP_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."FLOW_TRAN_THREE_EXP_SEQ" 已存在 ORA-31684: 对象类型 SEQUENCE:"XXXX"."SYS_COMPANY_SEQ" 已存在 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE ORA-39151: 表 "XXXX"."EFF_SUPERVISION" 已存在。由于跳过了 table_exists_action, 将跳过所有相关元数据和数据。 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA . . 导入了 "XXXX"."CORP_TOTAL"                    13.88 MB  186154 行 . . 导入了 "XXXX"."SYS_LOG"                       1.830 MB    2001 行 . . 导入了 "XXXX"."PUB_EDUCATE"                   2.153 MB     216 行 . . 导入了 "XXXX"."SYS_USER"                      318.6 KB    2588 行 . . 导入了 "XXXX"."SYS_OFFICE"                    315.3 KB    2737 行 . . 导入了 "XXXX"."FLOW_ROLE_USERS"               125.7 KB      15 行 . . 导入了 "XXXX"."SYS_AREA"                      199.1 KB    1194 行 . . 导入了 "XXXX"."SYS_MENU"                      140.0 KB     300 行 . . 导入了 "XXXX"."FLOW_INSTENCE"                 11.96 KB       3 行 . . 导入了 "XXXX"."FLOW_MODEL"                    8.796 KB       2 行 . . 导入了 "XXXX"."FLOW_NODE_MODEL"               12.89 KB      32 行 . . 导入了 "XXXX"."FLOW_OPRT_MODULE"              8.929 KB       3 行 . . 导入了 "XXXX"."FLOW_OPRT_TYPE"                9.070 KB       1 行 . . 导入了 "XXXX"."FLOW_ROLE"                     7.914 KB      15 行 . . 导入了 "XXXX"."OA_OFFICE"                     10.42 KB      84 行 . . 导入了 "XXXX"."PUB_IMAGE_MANAGEMENT"          9.765 KB      21 行 . . 导入了 "XXXX"."SYS_ATTACH"                    17.17 KB      33 行 . . 导入了 "XXXX"."SYS_DICT"                      41.33 KB     383 行 . . 导入了 "XXXX"."SYS_ROLE"                      8.312 KB       6 行 . . 导入了 "XXXX"."SYS_ROLE_MENU"                 11.61 KB     175 行 . . 导入了 "XXXX"."SYS_USER_OFOUTSIDE"            36.89 KB     592 行 . . 导入了 "XXXX"."SYS_USER_ROLE"                 34.42 KB    2580 行 . . 导入了 "XXXX"."SYS_WARNING"                   9.726 KB       1 行 . . 导入了 "XXXX"."TROUBLE_CLUES"                 49.46 KB       3 行 . . 导入了 "XXXX"."ALARM_RECORDS"                     0 KB       0 行 . . 导入了 "XXXX"."EXAMPLE_TEST"                      0 KB       0 行 . . 导入了 "XXXX"."FLOWACCREDIT"                      0 KB       0 行 . . 导入了 "XXXX"."FLOWACCREDITDETAILS"               0 KB       0 行 . . 导入了 "XXXX"."FLOW_BASIC_EXAMPLE"                0 KB       0 行 . . 导入了 "XXXX"."FLOW_NODE_INSTENCE"                0 KB       0 行 . . 导入了 "XXXX"."FLOW_NODE_INSTENCE_NEW"            0 KB       0 行 . . 导入了 "XXXX"."FLOW_NODE_INSTENCE_PIC"            0 KB       0 行 . . 导入了 "XXXX"."FLOW_OPRT_BILL"                    0 KB       0 行 . . 导入了 "XXXX"."FLOW_ROLE_USERS_MANAGE"            0 KB       0 行 . . 导入了 "XXXX"."FOUR_BUILD_DEMAND_REPORT"          0 KB       0 行 . . 导入了 "XXXX"."FOUR_BUILD_DETAIL_REPORT"          0 KB       0 行 . . 导入了 "XXXX"."FOUR_BUILD_EQUIPMENT"              0 KB       0 行 . . 导入了 "XXXX"."FOUR_BUILD_TYPE"                   0 KB       0 行 . . 导入了 "XXXX"."GROUP_HONOR"                       0 KB       0 行 . . 导入了 "XXXX"."INS_BUILDING"                      0 KB       0 行 . . 导入了 "XXXX"."LABOUR_ANNUAL_REPORT"              0 KB       0 行 . . 导入了 "XXXX"."LABOUR_BUILD_ACT"                  0 KB       0 行 . . 导入了 "XXXX"."LABOUR_FEE_COLLECT"                0 KB       0 行 . . 导入了 "XXXX"."LABOUR_FEE_LIST"                   0 KB       0 行 . . 导入了 "XXXX"."LABOUR_QUESTIONNAIRE"              0 KB       0 行 . . 导入了 "XXXX"."LABOUR_UNION_FUNDS"                0 KB       0 行 . . 导入了 "XXXX"."MEMBER_METTING_REGISTER"           0 KB       0 行 . . 导入了 "XXXX"."MESSAGE_SMS_INTF"                  0 KB       0 行 . . 导入了 "XXXX"."MESSAGE_SMS_RECEIVER"              0 KB       0 行 . . 导入了 "XXXX"."MESSAGE_SMS_RECORDS"               0 KB       0 行 . . 导入了 "XXXX"."MESSAGE_SMS_SAMPLE"                0 KB       0 行 . . 导入了 "XXXX"."MESSAGE_SMS_TARIFF"                0 KB       0 行 . . 导入了 "XXXX"."NOTICE_RECEIVER"                   0 KB       0 行 . . 导入了 "XXXX"."NOTICE_RECORDS"                    0 KB       0 行 . . 导入了 "XXXX"."NOTICE_SAMPLE"                     0 KB       0 行 . . 导入了 "XXXX"."ORG_LABOUR"                        0 KB       0 行 . . 导入了 "XXXX"."ORG_PARTY"                         0 KB       0 行 . . 导入了 "XXXX"."PARTY_BUILD_ACT"                   0 KB       0 行 . . 导入了 "XXXX"."PARTY_CENTR_GROUP"                 0 KB       0 行 . . 导入了 "XXXX"."PARTY_FEE"                         0 KB       0 行 . . 导入了 "XXXX"."PARTY_FEE_COLLECT"                 0 KB       0 行 . . 导入了 "XXXX"."PARTY_FEE_LIST"                    0 KB       0 行 . . 导入了 "XXXX"."PSN_HONOR"                         0 KB       0 行 . . 导入了 "XXXX"."PSN_HR_INFO"                       0 KB       0 行 . . 导入了 "XXXX"."PSN_INFO_SIGN"                     0 KB       0 行 . . 导入了 "XXXX"."PSN_PARTY_INFO"                    0 KB       0 行 . . 导入了 "XXXX"."PSN_WARN"                          0 KB       0 行 . . 导入了 "XXXX"."PSN_WORK"                          0 KB       0 行 . . 导入了 "XXXX"."SYS_ROLE_OFFICE"                   0 KB       0 行 . . 导入了 "XXXX"."ZDH_COLSE_METTING_EXECUTE"         0 KB       0 行 . . 导入了 "XXXX"."ZDH_GROUP_SURVEY"                  0 KB       0 行 . . 导入了 "XXXX"."ZDH_PROPOSAL_MANAGER"              0 KB       0 行 . . 导入了 "XXXX"."ZDH_STAFF_REGISTER"                0 KB       0 行 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT 处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE ORA-31684: 对象类型 PROCEDURE:"XXXX"."FLOWUNTREADTOBEGIN" 已存在 ORA-31684: 对象类型 PROCEDURE:"XXXX"."FLOWUNTREADTOPREV" 已存在 处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 处理对象类型 SCHEMA_EXPORT/VIEW/VIEW ORA-31684: 对象类型 VIEW:"XXXX"."VIEWFLOWNODEINSTENCE" 已存在 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 作业 "XXXX"."SYS_IMPORT_FULL_01" 已经完成, 但是有 28 个错误 (于 15:04:56 完成)

和linux成功导入日志对比 发现导入的数据完全一致，报错不影响结果

4 linux恢复到windows问题

 

20180622

系统  xx.xx.x.x

操作系统 [root@localhost backup]# cat/etc/issue

CentOS release 6.5 (Final)

数据库版本

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0- 64bit Production

需要回复数据库文件

[root@localhost backup]# du -sh *

21M xxx_20180622.dmp

16K  xxx_20180622.log

 

恢复到windows7 oracle 11.2.0.1  数据库版本不一致

执行脚本

@echo off

rem

impdp 'xxx/"12345"'  directory=dmp dumpfile=xxx_20180622.dmp logfile=xxx_20180622.log

@forfiles /p e:\backup /s /m * -d -15 /c"cmd /c del /f @file"

报错

;;;

Import: Release 11.2.0.1.0 - Production on 星期五 6月 22 15:45:40 2018

 

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

;;;

连接到: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORA-39000: 转储文件说明错误

ORA-31619: 转储文件"C:\新建文件夹\scyxxt_20180620.dmp" 无效

第一检查脚本问题

1导出什么表空间，导入也要什么表空间。

导出什么用户，导入也要什么用户。

如果没有要新建

新建了账户表空间，发现不是账户脚本问题

2 是否文件问题

windows-----linux和linux---windows

可能传输问题

发现这种错误一般都是文件传输错误

5 总结

对于oracle数据库不同系统版本恢复问题

1      用户备份文件不存在（新建文件）

2      用户没有权限（赋权）

3      用户默认空间不对（修改空间）