12c and export/import from a lower version

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 tried with the SCOTT schema, got the same result.
I guess that means that I will have to create more database links. Odd.

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to 12c and export/import from a lower version

  1. mgogala says:

    I figured out what the problem is: the directory must be owned by the user oracle. In 11G, it worked with /tmp. In 12c, it doesn’t. On the other hand, if I create the directory TMP as $ORACLE_BASE/tmp instead of /tmp, the import works. Go figure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s