原创：数据泵低到高和高到低版本深入实验.docx

遇到了一个问题，环境描述一下，以便日后能重现环境在研究：环境：刚刚装完12c，然后dbca了一个数据库orcl，建立了一个用户zkm。create directory dir1 as /home/oracle/test;create user zkm identified by zkm account unlock default tablespace users;grant connect,create table to zkm;alter user zkm quota 1m on users;create table zkm.test as select * from scott.emp;其实是想做高低版本的导入导出实验，发现高版本导出的dump文件低版本不可以用，这里是从12c到11g。之前我先做了低到高的实验，就是11g到12c。11g上建立zkm这个用户稍微有些不一样，但是没差，这里也弄出来吧！create user zkm identified by zkm account unlock;grant connect,resource to zkm;create table zkm.test as select * from scott.dept;11g上：expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.log12c上：impdp sys/oracle as sysdba directory=dir1 dumpfile=zkm.dmp logfile=zkm.log成功导入。从高到低就不行了，如下：12c上：expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.log然后scp过去11g的系统上11g上：oracletest test$ impdp sys/oracle as sysdba directory=dir1 dumpfile=zkm.dmp logfile=zkm.log version=.0ORA-39001: invalid argument valueORA-39000: bad dump file specificationORA-39142: incompatible version number 4.1 in dump file /home/oracle/test/zkm.dmp关于这个version我加了跟没加一样报错。网上有例子是11g到10g加了有用的，也有的说导出的时候加就行了。于是我就想在12c上重新导出一遍，不一样的地方就是加上version=.0（目标版本）。结果出错了。一下环境12c上：oracleoracle test$ expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.log version=.0Export: Release .0 - Production on Wed Apr 15 15:25:51 2015Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsORA-31626: job does not existORA-31633: unable to create master table ZKM.SYS_EXPORT_SCHEMA_06ORA-06512: at SYS.DBMS_SYS_ERROR, line 95ORA-06512: at SYS.KUPV$FT, line 1031ORA-01536: space quota exceeded for tablespace USERSoracleoracle test$ oerr ora 3163331633, 00000, unable to create master table %s.%s/ *Cause: Job creation failed because a Master Table and its indexes could/ not be created, most commonly due to the pre-existance of a table/ with the same name (job name) in the user schema. Refer to any/ following error messages for clarification./ *Action: Select a different job name, DROP the existing table, or eliminate/ any problems indicated by the following error messages.oracleoracle test$ oerr ora 153601536, 00000, space quota exceeded for tablespace %s/ *Cause: The space quota for the segment owner in the tablespace has/ been exhausted and the operation attempted the creation of a/ new segment extent in the tablespace./ *Action: Either drop unnecessary objects in the tablespace to reclaim/ space or have a privileged user increase the quota on this/ tablespace for the segment owner.第一次不会，第二次怎么就出现这种问题了。然后意思就是在导出的时候有往数据库里面建立表或者其他什么东东的意思喽！实验发现是有的：再导的时候迅速查了一下，导完查的时候会表消失SYSorcl select SEGMENT_NAME,SEGMENT_TYPE from dba_segmenTS where owner=SYSTEM AND SEGMENT_NAME=SYS_EXPORT_SCHEMA_01;SEGMENT_NAMESEGMENT_TYPE- -SYS_EXPORT_SCHEMA_01TABLE暂时不知道表是干什么用的，而且可能不是临时表（不是在临时表空间的），还会消失。群上有人说要是异常结束表可能不会消失，于是我做了如下实验：于是怎样都找不到那个SYSTEM.SYS_EXPORT_SCHEMA_01的表了，加了job_name才会，不知道是不再出现还是出现了但是不知道什么名字不知道在什么地方。或者直接TEST替代掉了，但是导的过程TEST也找不到。想要知道有没有在产生，只要用zkm/zkm，然后指定job_name，能成功说明就没有产生。oracleoracle test$ expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.log job_name=testExport: Release .0 - Production on Wed Apr 15 15:42:52 2015Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsORA-31626: job does not existORA-31633: unable to create master table ZKM.TESTORA-06512: at SYS.DBMS_SYS_ERROR, line 95ORA-06512: at SYS.KUPV$FT, line 1031ORA-00955: name is already used by an existing objectoracleoracle test$ expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.log job_name=helloExport: Release .0 - Production on Wed Apr 15 15:44:47 2015Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsORA-31626: job does not existORA-31633: unable to create master table ZKM.HELLOORA-06512: at SYS.DBMS_SYS_ERROR, line 95ORA-06512: at SYS.KUPV$FT, line 1031ORA-01536: space quota exceeded for tablespace USERS其实导出过程会有一个master表产生，名字和job_name一样，这里指定job_name为test，但是原本导出的就是有test表，所以说已经存在。换成hello就还是一开始的配额问题。这种master表job完成后会消失，手动kill掉表也会消失。(expdp zkm/zkm attach=job_name去KILL_JOB)然后：SYSorcl alter user zkm quota 3m on users; User altered.oracleoracle test$ expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.logStarting ZKM.SYS_EXPORT_SCHEMA_02: zkm/* directory=dir1 dumpfile=zkm.dmp logfile=zkm.log Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 576 KB. . exported ZKM.SYS_EXPORT_SCHEMA_01 146.8 KB 1226 rows. . exported ZKM.TEST 8.75 KB 14 rowsMaster table ZKM.SYS_EXPORT_SCHEMA_02 successfully loaded/unloaded*Dump file set for ZKM.SYS_EXPORT_SCHEMA_02 is: /home/oracle/test/zkm.dmpJob ZKM.SYS_EXPORT_SCHEMA_02 successfully completed at Wed Apr 15 15:56:34 2015 elapsed 0 00:00:40这里发现了这个表SYS_EXPORT_SCHEMA_01。估计是一开始配额不够的时候残留的，没有消失掉。而且SYSorcl select owner_name,job_name from dba_datapump_jobs;OWNER_NAME JOB_NAME- -ZKM SYS_EXPORT_SCHEMA_01一直有这个，不知道怎么弄掉，关了数据库也不行。试下关机，在之前SYSorcl drop table zkm.SYS_EXPORT_SCHEMA_01 purge;Table dropped.SYSorcl alter user zkm quota 1m on users;User altered.关机再开机就没有了。重现下实验，看看是不是SYS_EXPORT_SCHEMA_01是不是一开始配额不够的时候残留的。oracleoracle test$ expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.logStarting ZKM.SYS_EXPORT_SCHEMA_01: zkm/* directory=dir1 dumpfile=zkm.dmp logfile=zkm.log Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS PROCACT_SCHEMA TABLE:ZKM.TESTORA-01536: space quota exceeded for tablespace USERSORA-06512: at SYS.DBMS_SYS_ERROR, line 105ORA-06512: at SYS.KUPW$WORKER, line 11020- PL/SQL Call Stack - object line object handle number name0x71b56670 26217 package body SYS.KUPW$WORKER0x71b56670 11041 package body SYS.KUPW$WORKER0x71b56670 9895 package body SYS.KUPW$WORKER0x71b56670 13063 package body SYS.KUPW$WORKER0x71b56670 3128 package body SYS.KUPW$WORKER0x71b56670 11737 package body SYS.KUPW$WORKER0x71b56670 2059 package body SYS.KUPW$WORKER0x71b8b270 2 anonymous blockProcessing object type SCHEMA_EXPORT/TABLE/TABLEIn procedure CREATE_OBJECT_ROWSIn function GATHER_PARSE_ITEMSIn function CHECK_FOR_REMAP_NETWORKNothing to remapIn procedure BUILD_OBJECT_STRINGSIn procedure BUILD_SUBNAME_LIST with TABLE:ZKM.TESTIn function NEXT_PO_NUMBERFORALLIn procedure DETERMINE_FATAL_ERRORJob ZKM.SYS_EXPORT_SCHEMA_01 stopped due to fatal error at Wed Apr 15 16:11:05 2015 elapsed 0 00:00:36其实一开始就出错了，我那时候只是没看信息，看到有文件就不管了。oracleoracle test$ du -h *8.0Kzkm.dmp4.0Kzkm.logSYSorcl select owner_name,job_name from dba_datapump_jobs;OWNER_NAME JOB_NAME- -ZKM SYS_EXPORT_SCHEMA_01这东西又有了。oracleoracle test$ expdp zkm/zkm attach=SYS_EXPORT_SCHEMA_01Job: SYS_EXPORT_SCHEMA_01 Owner: ZKM Operation: EXPORT Creator Privs: FALSE GUID: 13BFE0F009740A9AE0537E15D30A9CFB Start Time: Wednesday, 15 April, 2015 16:13:06 Mode: SCHEMA Instance: orcl Max Parallelism: 1 Timezone: +00:00 Timezone version: 18 Endianness: LITTLE NLS character set: ZHS16GBK NLS NCHAR character set: AL16UTF16 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND zkm/* directory=dir1 dumpfile=zkm.dmp logfile=zkm.log State: IDLING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /home/oracle/test/zkm.dmp bytes written: 4,096 Worker 1 Status: Instance ID: 1 Instance name: orcl Host name: State: UNDEFINED Object Schema: ZKM Object Type: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed Objects: 1 Total Objects: 1 Worker Parallelism: 1Export出错后任务应该是一直处于等待或者悬挂状态，没有停止。我试着给加大配额看看job有没有继续。加了之后还是那样，看来是属于异常状态，只能手动kill掉了(上面没kill而是重启的因为导出的文件被我删掉了，进不去export交互模式，可以试试linux上直接kill -9)。SYSorcl select count(1) from zkm.SYS_EXPORT_SCHEMA_01; COUNT(1)- 1227Export kill_job Are you sure you wish to stop this job (yes/no): yesSYSorcl select count(1) from zkm.SYS_EXPORT_SCHEMA_01;select count(1) from zkm.SYS_EXPORT_SCHEMA_01ERROR at line 1:ORA-00942: table or view does not exist表也消失了，job已停止表也没了呀！然后开始继续做12c到11g的实验：12c上：expdp zkm/zkm directory=dir1 dumpfile=zkm.dmp logfile=zkm.log version=.011g上：oracletest test$ impdp system/oracle directory=dir1 dumpfile=zkm.dmp logfile=kkkkk.dmp remap_schema=zkm:systemImport: Rel