| Cannot fully import data pump [message #471213] |
Thu, 12 August 2010 20:12  |
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 installedwww.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
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 #471341 is a reply to message #471213] |
Fri, 13 August 2010 08:22  |
 |
LKBrwn_DBA
Messages: 487
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 |
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 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
