12c and export/import

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 29 Jul 2014 03:14:06 +0000 (UTC)
Message-ID: <pan.2014.07.29.03.14.06_at_gmail.com>

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

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

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

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

Received on Tue Jul 29 2014 - 05:14:06 CEST

This message: [ Message body ]
Next message: Mladen Gogala: "Re: 12c and export/import"
Previous message: joel garry: "Re: 12c and export/import"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message