oracle IMP command - import a database into a clean install

I am trying to import a datbase from a solaris export into my win 2k box.
i used the command:

imp system/manager file=CON0.exp log=import_CON0.log full=y ignore=y commit=y


and it failed to import and killed my user accounts. i believe the full=y is why this happened. so i did a format and ready to try again.

I need to know how to import this database succsessfully.

ideally keeping the existing user accounts etc BUT its the table structure and data that matters the most.


LVL 1
Ken-dohAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

istoicanCommented:
With what command do you export the data?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What was the command to export, and what is the EXACT error message the import was failing?
0
Ken-dohAuthor Commented:
exp system file=CON0.exp log=CON0.log full=y compress =y consistent=y
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ken-dohAuthor Commented:
the exact error messaage was something like:

import terminated successfully : import failed

was not useful at all no error codes, any sugestions what to do and i will past the error in here.
0
Ken-dohAuthor Commented:
the exact error messaage was something like:

import terminated successfully : import failed

was not useful at all no error codes, any sugestions what to do and i will past the error in here.
0
Ken-dohAuthor Commented:
the exact error messaage was something like:

import terminated successfully : import failed

was not useful at all no error codes, any sugestions what to do and i will past the error in here.
0
Ken-dohAuthor Commented:
the import may have worked.

unfortunatly It may have also overwritten my user accounts as i try to login and get told that the user pass is incorrect

thats system / manager account.

is there a way to recover this password or reset it??

I am a bit of an oracle newbie u c

0
Mark GeerlingsDatabase AdministratorCommented:
Is your version of Oracle for Windows as high as (or higher than) the version of Oracle used for the export?  Oracle import will never succeed if the database version of the export is higher than the one you want to import into.

Did you create a new, empty database before running import?  Import will not create a database from scratch.

Also, when you switch operating systems like this, you need to create the tablespaces manually before running import (and then specify "ignore = Y" when you run import), since the data file and path names that worked in Solaris obviously will not work in Windows.
0
Ken-dohAuthor Commented:
I created the default database when i installed.

after checking that I could connect to the default database with system manager I then ran the import:

I did not create any table spaces. what is involved in this??

to import i used:

imp system/manager file=CON0.exp log=import_CON0.log full=y ignore=y commit=y

so what do i need to do about the table spaces?? I think that they were created but all the user accounts were not.

is it possible to do partial imports. just the user groups for example?
0
Ken-dohAuthor Commented:
"""when you switch operating systems like this, you need to create the tablespaces manually before running import """

How do i do this???

do i need to create the user that created the table space??

0
Mark GeerlingsDatabase AdministratorCommented:
No, Oracle users do not own tablespaces.  Users and tablespaces are independent.

How do you create a tablespace?  In SQL*Plus with the "create tablespace..." command.  Or, you can use GUI tools like Oracle Enterprise Manager to do this via a wizard.

Yes, a full import will bring in and/or change the user account information (since this all gets stored in the SYSTEM tablespace which every Oracle database has) even if the import cannot bring in the tables and data because of problems with tablespaces and data file names.

I think your best option at this point is to shutdown and delete this database, then create a new database.  Before running import create a user (that you are sure does not exist in the import file) and give that user DBA privileges.  This will allow you to log in as this user after running import and change the passwords for SYS and SYSTEM to something that you know.  Also before running import, create the tablespaces that are needed manually.

If you don't know which tablespaces those are then run import first to put the SQL commands (but none of the data) into a file you can read with a text editor to see which tablespaces import will try to create.  Use this command for import:
imp system/manager file=CON0.exp log=import_CON0.log full=y rows=n ignore=y indexfile=C:\temp\sql_file.txt

(If you don't have a C:\temp directory, just change that to a directory you do have.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken-dohAuthor Commented:
thanks mark that helps, will let you know how i get on doing this.
0
Ken-dohAuthor Commented:
ok i have this indexfile thing. had a look in notepad

lots of rem ....

but there is

create table coast.address....

so its coast which is the table space that I need to create

is the sql going to be

create tablespace "foo" schema="foouser" datafile = "c:\oracle\foo\foo.dbf" size="1000M"

would that work?


0
Ken-dohAuthor Commented:
ok i solved it

for any one that has to do this in future here is the answer

create the user foouser that is the schema for the tablespace.

then create the table space

create tablespace foo datafile 'c:\oracle\foo.dbf' size 1000M;

then run the import as mentioned above

then the data and tables import :)

thanks for the replys the points go to markgeer as it was his idea many thanks
0
Ken-dohAuthor Commented:
thanks to every one that helped

much appreciated - Experts exchange rox
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.