Oracle unix export --> Windows import both versions the same 8.1.7

[exrational]

I performed an import that I think succeeded. This was a clean install (typical install) with just the default database created (by default) and account system manager.

the database was exported from unix using:

exp system file=CON0.exp log=CON0.log full=y compress =y consistent=y

and I imported it to windows using:

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

from the system account:

when i goto connect to the database using system manager i get user / pass incorrect. BUT when i connect using a user account for the imported database I also get user / pass incorrect.

I am pulling my hair out with this one. any help will be really really appreciated

I do not need a full import I only need the table spaces and the data. User accounts are not important so if there is a way of avoiding this issue by not importing user accounts then this may help

i am a bit of a newbie. other than above i did nothing else. is there something I should have done??

[T G Manikandan]

How about resetting those passwords

C;\set oracle_sid
C:\
svrmgrl

svrmgr>connect internal
svrmgr>alter user identified by

Revert

[exrational]

thanks for the very quick reply.
unfortunatly I dont have access to the unix server to perform another export. Only the windows machine and the export file.

is it possible to alter the system account and recover the password / reset it>?

i was thinking
SVRMGR> alter user system....

however oracle is not my strength and I could not think what the command would be. can you help with this?

would this work do you think?

[Yogeeraj_1]

hi,

as oracle user, you would do:
sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Mar 25 14:25:56 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> alter user system identified by manager;

User altered.

SQL>


hth
Yogeeraj

[exrational]

spot on thankyou

changed the password

HOWEVER

the import has not created the schema table that is needed.

the schema table is called coast

and there is a user account coast:

i got errors when i imported the database.

should i create a user called coast and create the table space coast first??

is this why it may have failed?

[T G Manikandan]

Can you post the errors?

When you imp the dmp file you should first examine

show=y will just do a preview without really doing a import which can tell you what you need to do.

If you are importing to user coast.

Create the user coast

svrmgr>create user coast identified by default tablespace temporary tablespace

Then u can do

C:\imp system/manager@ fromuser= touser=coast file= show=y


Please post the logs

[Yogeeraj_1]

hi,

try:
a. create user coast identified by coast default tablespace tbs_coast temporary tablespace temp;

Assuming: default tablespaces tbs_coast and temp already exists.


b. grant connect, resource to coast;


c. imp system/manager fromuser=coast touser=coast file=CON0.exp

NB. i would not recommend using compress=y when exporting!


hth
Yogeeraj

[exrational]

i cant paste the error cause its shooting through a massive import and the errors fly by so fast.

i can pick out user does not exist errors
object does not exist etc etc

so you suggest creating a user:

then creating a table space

then running the import

correct?

[Yogeeraj_1]

hi,

Yes.

You can also specify the parameter "log=coast_exp.log" to capture all the messages.

which you can post here.

Please check and let us know.

hope this helps!
best reagrds
Yogeeraj

[Brian Crabtree]

What could be happening in this case is that the user does not have the appropriate roles on the export database to create the objects that it owns (possible if someone with CREATE ANY TABLE created the objects). You can verify this by running the import with the "log=" option, or creating the user manually and importing with a "fromuser=" and "touser=" to import only that user. The fromuser and touser will not create a user, while the "full=y" will create and modify users automaticlly.

Hope this helps,

Thanks,

Brian

[Steven E. Protter]

If by chance this has been a mirgration from a 32 bit to a 64 bit platform, there are word size issues. If not, ignore this post. Oracle document attached. Just in case.

SEP

[Yogeeraj_1]

hi,

As Brian said, you need to grant the user rights to create tables.

grant create table to coast;

should be OK.

However, the user coast may own other objects.

I would prefer to grant connect and resource system privileges that will give him more "powerful" rights...at least during the import period. (as in my previous post)

you can later revoke that and grant him "create session" and any other desired system privileges. e.g. create table, create procedure, etc.

Hope this helps!
Yogeeraj

[Indira Aramandla · Accepted solution]

When you are given the export dumpm file and you want to import into your database, you should know what schemas (users) exists in the export file, what roles and DB_LINKS. You can find all of these by doing a dummy import with SHOW=Y parameter.
eg: imp system/manager@sid fully=y show=y file=exp_dump_file log=log_file_name. THis command will not import the objects but create a log for you which will contain the users, roles and tables with the data.
Look in the log and then note down the users, roles in the import log. You can open the log file in winword and search for "USER" and "ROLE". This will give you users and roles from the export file. Then create the users and roles in the target database as
create identified by default tablespace temporary tablespace temp;

create role ;

Then do the import as
imp system/manager@sid buffer=1024000 full=y commit=y file=exp_file log=imp_log.log

When the import occurs the messages fly off on the screen, but they will be recorded in the log file. You can verify the log for any errors.

[PAVIC Thierry]

Hi
I'm not sure that you can make a simple operation of exp/imp with a different OS.
I think that you don't use the option compress=y.
Check this way on metalink.
I remeber have some trouble in using compress option with a situation similar as you.

Best regards Thierry

[exrational]

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 :)

the table space can not be created becaue the dbf file was tying to be put in in foo/local/......

windows needs a c:\foo
and thats where it was messing up

[Yogeeraj_1]

hi,

glad to know that you managed to fix the problem.

again, Locally managed tablespaces would be recommended.

best regards
Yogeeraj