Re: 12c and export/import

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_at_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_at_oradb tmp]$ ls
>
> oe.dmp oe.log
>
> [oracle_at_oradb tmp]$ scp oe.dmp medo:/tmp/
>
> oracle_at_medo's password:
>
> oe.dmp 100% 3700KB 3.6MB/s
>
> [oracle_at_oradb tmp]$
>
>
>
> The next thing to try is importing the file into the 12c instance:
>
>
>
>
>
> [oracle_at_medo ~]$ impdp userid=pdbadmin/admin_at_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_at_medo ~]$ impdp userid=system_at_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/********_at_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 --    Received on Tue Jul 29 2014 - 16:50:06 CEST