?
Solved

oracle IMP command -  import a database into a clean install

Posted on 2003-03-24
15
Medium Priority
?
13,263 Views
Last Modified: 2011-10-03
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.


0
Comment
Question by:Ken-doh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 2

Expert Comment

by:istoican
ID: 8194442
With what command do you export the data?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8194928
What was the command to export, and what is the EXACT error message the import was failing?
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 8194970
exp system file=CON0.exp log=CON0.log full=y compress =y consistent=y
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Ken-doh
ID: 8195009
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8195099
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8195230
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8195956
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8196627
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8201231
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8201773
"""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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 180 total points
ID: 8202598
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8203393
thanks mark that helps, will let you know how i get on doing this.
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 8204100
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8204353
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
 
LVL 1

Author Comment

by:Ken-doh
ID: 8204361
thanks to every one that helped

much appreciated - Experts exchange rox
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question