Home » RDBMS Server » Enterprise Manager » Cannot fully import data pump
Cannot fully import data pump [message #471213] Thu, 12 August 2010 20:12 Go to next message
mescotty
Messages: 3
Registered: August 2010
Junior Member
I have an 11g data pump supplied by another party.
I am on Windows 7(x64)
I have experience using other databases, but not Oracle. The complexity of it all is a bit overwhelming...

I downloaded and installed
www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
win64_11gR2 release.

I used the Database Configuration Assistant to create a database:
Template: Data Warehouse
Name/SID: database0
Password: password0

I then used the 'database0' Enterprise Manager:
Logged in as SYSTEM/password0 (Normal)
Import from Export Files
Entire Files
Host Credentials: myself (am Windows administrator)
All the rest defaults

The job appears to finish successfully.
When I look at the schema (using razorsql), most tables seem to be there.
However,a significant number are not.

When I open data pump in a text editor, those missing tables are clearly there - definitions and data.

When I look in the import.log, there are errors of the type:


error in creating database file '/db02/oradata/database0/stuff.dbf'
file create error, unable to create file
unable to open file
(OS 3) The system cannot find the path specified.
Failing sql is:
CREATE TABLESPACE "STUFF" DATAFILE '/db01/oradata/database0/stuff.dbf'

-- followed by the associated table creation errors.

So, does this mean that unix paths are hardcoded into the data pump, and is therefore incompatible with import into a Windows based system?

Or are the paths symbolic, internal representations used by Oracle, and these errors are a symptom of an earlier, undisclosed problem?

The thing is, when I view the schema, the tablespace "STUFF" exists, just none of its tables.


Many thanks for any insight.

Re: Cannot fully import data pump [message #471215 is a reply to message #471213] Thu, 12 August 2010 20:20 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
If this is going to be resolved "quickly" you are going to have to use command like sqlplus.
Are you ready to work with command line sqlplus?
Re: Cannot fully import data pump [message #471341 is a reply to message #471213] Fri, 13 August 2010 08:22 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 413
Registered: July 2003
Location: WPB, FL
Senior Member
What BlackSwan is implying is that a) The datapump export file you are using comes from a Unix environment, b) You are doing FULL import.

Due to the fact you are trying to import Unix (tablespace) definitions into WinDoze OS, you need to pre-create the tablespaces and therefore will have to use sqlplus to do it.

Good luck!

PS: Or maybe if you configured Enterprise Manager when you created the database, then you can create the tablespaces using the EM Utility.
.

[Updated on: Fri, 13 August 2010 10:40] by Moderator

Report message to a moderator

Re: Cannot fully import data pump [message #471367 is a reply to message #471341] Fri, 13 August 2010 11:22 Go to previous messageGo to next message
mescotty
Messages: 3
Registered: August 2010
Junior Member
I have to do what I have to do. So yes.

I guess what is needed is a tutorial on unix to windows transfers.
Do either of you have a link handy?

Google found some, with the usual "join this" or "pay that" (experts-exchange).

Thanks again.
Re: Cannot fully import data pump [message #471368 is a reply to message #471367] Fri, 13 August 2010 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
Open Command Window
COPY lines below

lsnrctl status
SET
lsnrctl service

PASTE lines above into Command Window
COPY commands & results then PASTE all back here


I/we need to understand what really exists on your system,
before any solution can be formulated.

[Updated on: Fri, 13 August 2010 11:36]

Report message to a moderator

Re: Cannot fully import data pump [message #471383 is a reply to message #471368] Fri, 13 August 2010 13:31 Go to previous message
mescotty
Messages: 3
Registered: August 2010
Junior Member
In the immortal words of Inigo Montoya, "I hate waiting".

So, based on the premise that manually creating tablespaces will bypass the file path problem...

1) Dropped the database and created a new one.
2) Copied the failing SQL statements (10x 'CREATE TABLESPACE') from the import log in a text editor.
Unfortunately, many seemed truncated:

CREATE TABLESPACE ... EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEF
CREATE TABLESPACE ... EXTENT MANAGEMENT LOCAL AUTOALLOCA
CREATE TABLESPACE ... EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT N

etc., so I removed everything following 'AUTOALLOCATE', hoping for reasonable defaults.

3) Changed the unix file paths into windows equivalents
4) Created tablespaces manually using 'sqlplus'
5) Repeated the data pump import.

The import completed with non-critical warnings of the type:
Invalid Objects by Schema / Owner's Invalid Object Count / 14 object(s) are invalid in the SYS schema.

However, when I view the database contents with a sql browser, everything looks wonderful!

Thanks everybody for your help.

Previous Topic: Changed sysman password, now emctl start oms fails, please help!
Next Topic: database control url is null
Goto Forum:
  


Current Time: Fri Apr 18 06:09:34 CDT 2014

Total time taken to generate the page: 0.14467 seconds