X-Received: by 10.42.209.140 with SMTP id gg12mr5466994icb.26.1406645406555; Tue, 29 Jul 2014 07:50:06 -0700 (PDT) X-Received: by 10.182.28.71 with SMTP id z7mr10298obg.16.1406645406437; Tue, 29 Jul 2014 07:50:06 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!195.71.90.67.MISMATCH!news.unit0.net!news.glorb.com!h18no9887332igc.0!news-out.google.com!px9ni0igc.0!nntp.google.com!h18no9887325igc.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Tue, 29 Jul 2014 07:50:06 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=12.47.84.9; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u NNTP-Posting-Host: 12.47.84.9 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: 12c and export/import From: Mark D Powell Injection-Date: Tue, 29 Jul 2014 14:50:06 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: news.cambrium.nl On Monday, July 28, 2014 11:14:06 PM UTC-4, Mladen Gogala wrote: > There is a serious problem with export/import utilities between versions. > > The first command, executed on Oracle 11.2.0.4, Linux x86_64 was: > > > > [oracle@oradb tmp]$ expdp system directory=tmp dumpfile=oe.dmp schemas=oe > > logfile=oe.log > > > > Directory tmp was created as /tmp > > > > Result was the following: > > > > Export: Release 11.2.0.4.0 - Production on Mon Jul 28 22:57:03 2014 > > > > Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights > > reserved. > > Password: > > > > 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 > > Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=tmp > > dumpfile=oe.dmp schemas=oe logfile=oe.log > > ..... > > > > Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS > > . . exported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 > > rows > > ORA-39181: Only partial table data may be exported due to fine grain > > access control on "OE"."PURCHASEORDER" > > . . exported "OE"."PURCHASEORDER" 243.9 KB 132 > > rows > > . . exported "OE"."WAREHOUSES" 12.46 KB 9 > > rows > > . . exported "OE"."CUSTOMERS" 77.98 KB 319 > > rows > > . . exported "OE"."PRODUCT_INFORMATION" 72.77 KB 288 > > rows > > . . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.585 KB 21 > > rows > > . . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.51 KB 288 > > rows > > . . exported "OE"."CATEGORIES_TAB" 14.15 KB 22 > > rows > > . . exported "OE"."INVENTORIES" 21.67 KB 1112 > > rows > > . . exported "OE"."ORDERS" 12.39 KB 105 > > rows > > . . exported "OE"."ORDER_ITEMS" 20.88 KB 665 > > rows > > . . exported "OE"."PROMOTIONS" 5.507 KB 2 > > rows > > Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded > > ****************************************************************************** > > Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: > > /tmp/oe.dmp > > Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Mon Jul > > 28 22:58:16 2014 elapsed 0 00:01:08 > > > > After that, the dump file is copied to the machine running 12c instance: > > > > [oracle@oradb tmp]$ ls > > oe.dmp oe.log > > [oracle@oradb tmp]$ scp oe.dmp medo:/tmp/ > > oracle@medo's password: > > oe.dmp 100% 3700KB 3.6MB/s > > [oracle@oradb tmp]$ > > > > The next thing to try is importing the file into the 12c instance: > > > > > > [oracle@medo ~]$ impdp userid=pdbadmin/admin@local dumpfile=oe.dmp > > schemas=oe directory=tmp log=oe.log sqlfile=oe_schema.sql > > > > Import: Release 12.1.0.1.0 - Production on Mon Jul 28 23:06:43 2014 > > > > Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights > > reserved. > > > > Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - > > 64bit Production > > With the Partitioning, OLAP, Advanced Analytics and Real Application > > Testing options > > ORA-39001: invalid argument value > > ORA-39000: bad dump file specification > > ORA-31640: unable to open dump file "/tmp/oe.dmp" for read > > ORA-27041: unable to open file > > Linux-x86_64 Error: 22: Invalid argument > > Additional information: 2 > > > > > > The errors are "invalid argument value" and "bad dump file > > specification", which means that Oracle 12c cannot interpret Oracle 11g > > dump file. That is vewy, vewy bad. However, the network link import works > > like a charm: > > > > > > [oracle@medo ~]$ impdp userid=system@local network_link=ora11 schemas=oe > > directory=tmp log=oe.log sqlfile=oe_schema.sql > > > > Import: Release 12.1.0.1.0 - Production on Mon Jul 28 23:10:42 2014 > > > > Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights > > reserved. > > Password: > > > > Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - > > 64bit Production > > With the Partitioning, OLAP, Advanced Analytics and Real Application > > Testing options > > Legacy Mode Active due to the following parameters: > > Legacy Mode Parameter: "log=oe.log" Location: Command Line, Replaced > > with: "logfile=oe.log" > > Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": userid=system/********@local > > network_link=ora11 schemas=oe directory=tmp logfile=oe.log > > sqlfile=oe_schema.sql > > 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/SYNONYM/SYNONYM > > Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE > > Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC > > Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT > > Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE > > Processing object type SCHEMA_EXPORT/XMLSCHEMA/XMLSCHEMA > > Processing object type SCHEMA_EXPORT/TABLE/TABLE > > Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT > > Processing object type SCHEMA_EXPORT/TABLE/COMMENT > > Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION > > Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION > > Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX > > Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/ > > FUNCTIONAL_INDEX/INDEX_STATISTICS > > Processing object type SCHEMA_EXPORT/VIEW/VIEW > > Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT > > Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY > > Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT > > Processing object type SCHEMA_EXPORT/TABLE/TRIGGER > > Processing object type SCHEMA_EXPORT/VIEW/TRIGGER > > Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS > > Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Mon Jul > > 28 23:11:50 2014 elapsed 0 00:01:04 > > > > I guess that means that I will have to create more database links. Odd. > > This is an elementary thing, this should have been tested. > > > > > > -- > > Mladen Gogala > > The Oracle Whisperer > > http://mgogala.byethost5.com This is probably a stupid question but since you copied the file did you double check the ownership/permissions? If ftp was used to copy the file was a binary ftp performed? Did you attempt to read the file on the source using 11.2 impdp? HTH -- Mark D Powell --