12c and export/import

[Mladen Gogala]

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

[Peter Schneider]

Am 29.07.2014 um 05:14 schrieb Mladen Gogala:
> 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:

Did you already try to export with the 12c expdp and parameter "version=12"?

Regards
Peter

--
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you. -- David McCullough Jr.

[Mladen Gogala]

On Tue, 29 Jul 2014 14:33:11 +0200, Peter Schneider wrote:

> Am 29.07.2014 um 05:14 schrieb Mladen Gogala:
>> 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:
>
> Did you already try to export with the 12c expdp and parameter
> "version=12"?
>
> Regards Peter

Version argument is only valid for NETWORK_LINK or SQLFILE, not for files.
I had no need to use version over the link and for SQLFILE, it does
create 11G version of the SQL file. I haven't tried running the file,
though.

[Mark D Powell]

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 --

[Mladen Gogala]

On Tue, 29 Jul 2014 07:50:06 -0700, Mark D Powell wrote:


> 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 --
>

Mark, no stupid questions here, I re-did things 3 times, to make sure.
And the answer is yes: I was able to read the file on the source. And
permissions are OK.

[joel garry]

On Tuesday, July 29, 2014 8:18:36 AM UTC-7, Mladen Gogala wrote:

>
>
> Mark, no stupid questions here, I re-did things 3 times, to make sure.
> And the answer is yes: I was able to read the file on the source. And
> permissions are OK.
>

Using nfs? Maybe the noac bug came back? What are the permissions? Have you ruled out the FGAC?

jg
--
@home.com is bogus.
http://searchoracle.techtarget.com/feature/Oracle-joins-OpenDaylight-SDN-project-will-integrate-Solaris

[Mladen Gogala]

On Tue, 29 Jul 2014 09:08:23 -0700, joel garry wrote:

> On Tuesday, July 29, 2014 8:18:36 AM UTC-7, Mladen Gogala wrote:
>
>
>>
>> Mark, no stupid questions here, I re-did things 3 times, to make sure.
>> And the answer is yes: I was able to read the file on the source. And
>> permissions are OK.
>>
>>
> Using nfs? Maybe the noac bug came back? What are the permissions?
> Have you ruled out the FGAC?
>
> jg

No, no NFS here, just doing the scp. The permissions are 644, world
readable. As for FGAC, I tried with the user SCOTT, the same result.

[Mladen Gogala]

On Tue, 29 Jul 2014 09:08:23 -0700, joel garry wrote:

> On Tuesday, July 29, 2014 8:18:36 AM UTC-7, Mladen Gogala wrote:
>
>
>>
>> Mark, no stupid questions here, I re-did things 3 times, to make sure.
>> And the answer is yes: I was able to read the file on the source. And
>> permissions are OK.
>>
>>
> Using nfs? Maybe the noac bug came back? What are the permissions?
> Have you ruled out the FGAC?
>
> jg

I figured out what the problem is: if I create directory not owned by the
user "oracle", the thing doesn't work. When I created $ORACLE_BASE/tmp
directory and substituted it for /tmp, the import started to work.

[joel garry]

On Tuesday, July 29, 2014 9:33:52 AM UTC-7, Mladen Gogala wrote:

>
> I figured out what the problem is: if I create directory not owned by the
> user "oracle", the thing doesn't work. When I created $ORACLE_BASE/tmp
> directory and substituted it for /tmp, the import started to work.

Maybe it needs world write for the log file? (or group if it shares with tmp ownership)

jg
--
@home.com is bogus.

http://www.cbronline.com/news/tech/software/businessintelligence/oracle-denies-its-14000-in-memory-option-activates-by-default-4328662

[Mladen Gogala]

On Tue, 29 Jul 2014 10:41:35 -0700, joel garry wrote:

> On Tuesday, July 29, 2014 9:33:52 AM UTC-7, Mladen Gogala wrote:
>
>
>> I figured out what the problem is: if I create directory not owned by
>> the user "oracle", the thing doesn't work. When I created
>> $ORACLE_BASE/tmp directory and substituted it for /tmp, the import
>> started to work.
>
> Maybe it needs world write for the log file? (or group if it shares
> with tmp ownership)
>
> jg

Joel, /tmp is world-writable.

[joel garry]

On Tuesday, July 29, 2014 4:08:12 PM UTC-7, Mladen Gogala wrote:

>
> Joel, /tmp is world-writable.
>

but but but you said 644 :-)

jg
--
@home.com is bogus.

http://ask.slashdot.org/story/03/02/11/1047231/what-is-your-best-tech-joke

[Mladen Gogala]

On Tue, 29 Jul 2014 16:26:42 -0700, joel garry wrote:

> On Tuesday, July 29, 2014 4:08:12 PM UTC-7, Mladen Gogala wrote:
>
>
>> Joel, /tmp is world-writable.
>>
>>
> but but but you said 644 :-)
>
> jg

Dump file was 644, the /tmp directory was 777.