Home » RDBMS Server » Server Utilities » Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. (11g, R2, Win 2008 server)
icon5.gif  Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468874] Mon, 02 August 2010 06:22 Go to next message
maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai
Member
Greetings Everyone,

Scenario;
> We have 2 schemas for test one in 11g and other in 10g.
> there is a dump file which is exported from 10g and needs to be imported in the 11g schema.

Issue:
> the dump file successfully gets imported in 10g with the below command viz;
D:\>imp myusername/mypassword@orcl file=thedumpfile.dmp log=thedumpfile.log fromuser=dumpfileuser touser=myusername ignore=y

> but the same command gives error in 11g version.
> we tried the command with DATA_ONLY=Y option and it worked.

Query:
> Could someone please explain the difference between the usage of IGNORE and DATA_ONLY
> How can IGNORE=Y in anyways be used in 11g in the above given command.

thanks,
Manish,

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468887 is a reply to message #468874] Mon, 02 August 2010 07:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> but the same command gives error in 11g version.
What error?
Instead of explaining the issue, always please copy and post the session.

>> Could someone please explain the difference between the usage of IGNORE and DATA_ONLY

http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/original_import.htm#i1021478

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468891 is a reply to message #468887] Mon, 02 August 2010 07:49 Go to previous messageGo to next message
maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai
Member
Hello,

Please find the console outputs below.

Import in Oracle 10g with ignore=Y option ( worked)
=============================================================
D:\>imp system/ace123@orcl file=17-keystoretab.dmp log=17-keystoretab.log fromuser=RABOQA touser=acetest ignore=y

Import: Release 10.2.0.4.0 - Production on Mon Aug 2 15:59:59 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by RABOQA, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing RABOQA's objects into ACETEST
. . importing table "KEYSTORETAB" 1 rows imported
Import terminated successfully without warnings.
=============================================================


import in Oracle 11g with IGNORE=Y option (did not worked)
=============================================================

D:\>imp pelsepa/PELSEPAMANAGER@orcl file=17-keystoretab.dmp log=17-keystoretab.log ignore=y fromuser=raboqa touser=pelsepa

Import: Release 11.2.0.1.0 - Production on Mon Aug 2 04:13:00 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by RABOQA, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing RABOQA's objects into PELSEPA
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "KEYSTORETAB" ("KEYSTORE_ID" VARCHAR2(25) NOT NULL ENABLE, "RE"
"SERVED_BY" VARCHAR2(15), "UPDATE_DATE" DATE, "VERIFIED_BY" VARCHAR2(15), "V"
"ALIDATED_BY" VARCHAR2(15), "STATUS" LONG NOT NULL ENABLE, "KEYSTORE_PASSWOR"
"D_PART" VARCHAR2(100), "KEYSTORE_PASSWORD" VARCHAR2(100) NOT NULL ENABLE, ""
"KEYSTORE" BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INIT"
"IAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RA"
"BOQA" LOGGING NOCOMPRESS LOB ("KEYSTORE") STORE AS (TABLESPACE "RABOQA" EN"
"ABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 6"
"5536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'RABOQA' does not exist
Import terminated successfully with warnings.
=================================================================

Import in Oracle 11g with DATA_ONLY option (worked)
=============================================================
D:\>imp pelsepa/PELSEPAMANAGER@orcl file=17-keystoretab.dmp log=17-keystoretab.log fromuser=raboqa touser=pelsepa data_only=y
Import: Release 11.2.0.1.0 - Production on Mon Aug 2 04:13:42 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by RABOQA, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. . importing table "KEYSTORETAB" 1 rows imported
Import terminated successfully without warnings.
=============================================================

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468897 is a reply to message #468891] Mon, 02 August 2010 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
tablespace 'RABOQA' does not exist

Maybe you should create the tablespace (but as the next import word it proves that the table already exists somewhere else).

Regards
Michel

[Updated on: Mon, 02 August 2010 08:04]

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468901 is a reply to message #468897] Mon, 02 August 2010 08:10 Go to previous messageGo to next message
maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai
Member
Hello Micheal,

The issue does not seem with the tablespace creation as we have used the same dump to import in as mentioned in previous reply.

Thanks and Regards,
Manish Hemnani

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468903 is a reply to message #468901] Mon, 02 August 2010 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The issue does not seem with the tablespace creation

No it is in the tavle creation which is tried to be created in a non-existent tablespace.
Check, you have not the same tablespaces in both databases.

Regards
Michel

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468910 is a reply to message #468903] Mon, 02 August 2010 08:32 Go to previous messageGo to next message
maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai
Member
Hello Mike,

The above import works in 10g(dump) to 10g(db).

But it gives the above logged errors in 10g(dump) to 11g(db)import.

Pl Note; here we have the same schema set, on two different version viz; 11g and 10g.

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468915 is a reply to message #468910] Mon, 02 August 2010 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same schemas but not same tablespaces (once more).
It is NOT a matter of versions.

Regards
Michel

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #468929 is a reply to message #468910] Mon, 02 August 2010 09:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

Did you read the documentation link provided?

Quote:
To import only data (no metadata) from a dump file, specify DATA_ONLY=y.


The table can be imported into "any tablespace".

Quote:
If you specify IGNORE=y, Import overlooks object creation errors when it attempts to create database objects, and continues without reporting the errors.


Import will try to create the object and if object already found, will suppress the error. If tablespace is not found, it will error out.

As Michel said, it is not a matter of versions.
You just don't have the same tablespace.

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #469288 is a reply to message #468929] Wed, 04 August 2010 02:32 Go to previous messageGo to next message
maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai
Member
Hello,
Quote:
IGNORE = Y , Import will try to create the object and if object already found, will suppress the error.


I have a query regarding usage of IGNORE=Y parameter while importing dump in the database. Below are the two cases where we have used IGNORE=Y.

When I try to import a dump (exported from 10g) in Schema_1 (on Oracle 10g) where the table structure is already present and it imports well without showing any error about tablespace.

=============================================================
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by RABOQA, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing RABOQA's objects into ACETEST
. . importing table "KEYSTORETAB" 1 rows imported
Import terminated successfully without warnings.
=============================================================

But when I try to import the same dump in Schema_2 (on Oracle 11g),here too the table structure already exist, the error is thrown regarding the creation of table
==============================================================
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by RABOQA, not by you
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "KEYSTORETAB" ("KEYSTORE_ID" VARCHAR2(25) NOT NULL ENABLE, "RE"
"SERVED_BY" VARCHAR2(15), "UPDATE_DATE" DATE, "VERIFIED_BY" VARCHAR2(15), "V"
"ALIDATED_BY" VARCHAR2(15), "STATUS" LONG NOT NULL ENABLE, "KEYSTORE_PASSWOR"
"D_PART" VARCHAR2(100), "KEYSTORE_PASSWORD" VARCHAR2(100) NOT NULL ENABLE, ""
"KEYSTORE" BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INIT"
"IAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RA"
"BOQA" LOGGING NOCOMPRESS LOB ("KEYSTORE") STORE AS (TABLESPACE "RABOQA" EN"
"ABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 6"
"5536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'RABOQA' does not exist
Import terminated successfully with warnings.
=================================================================

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #469291 is a reply to message #469288] Wed, 04 August 2010 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Expected behaviour, what is your problem?

IGNORE=Y means does not try to execute the CREATE statement is the object already exists.
IGNORE=N means do not check the objects existance and try to create them.

Regards
Michel

[Updated on: Wed, 04 August 2010 02:38]

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #469562 is a reply to message #469291] Thu, 05 August 2010 03:06 Go to previous messageGo to next message
maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai
Member
My problem is when import is done with IGNORE=Y in Oracle 11g it does not suppress the error for table creation (table already exist) as it should and as it does in Oracle 10g.

Regards,
Manish

Report message to a moderator

Re: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g. [message #469571 is a reply to message #469562] Thu, 05 August 2010 03:18 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't understand.

1/ IT IS NOT A MATTER OF VERSIONS. FULL STOP. (repeat this one hundred times)

2/ Read again and again and again the posts until you understand, we can't say more than what we said.

Regards
Michel

Report message to a moderator

Previous Topic: how to handle null values in sql loader
Next Topic: How to load tabbed data lines for specific header
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 05:45:38 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.