Re: Oracle IMP Hangs

comments embedded and suggestion at end. [Quoted] "Mike Morris" <morrisma_at_ei.dupont.com> wrote in message news:3b2c6993.0403010625.1e5070ca_at_posting.google.com...
> More help is definately needed!
> I am (fairly obviously) new to oracle.
> The imp process certainly "seems" to hang and has to be killed to
> recover the terminal.
> This is what is in the alert log:
>
> Mon Mar 1 09:07:49 2004
> CREATE TABLESPACE "TOOLS" DATAFILE '/u02/oradata/cyrel/tools01.dbf'
> SIZE 8388608 AUTOEXTEND ON NEXT 327680 MAXSIZE 32767M DEFAULT
> STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096
> PCTINCREASE 0) ONLINE PERMANENT
> Mon Mar 1 09:07:49 2004
> ORA-1543 signalled during: CREATE TABLESPACE "TOOLS" DATAFILE
> '/u02/oradata/...

this means that the import is trying to create a tools tablespace when one already exists...

> Mon Mar 1 09:07:49 2004
> CREATE TABLESPACE "RBS" DATAFILE '/u02/oradata/cyrel/rbs01.dbf' SIZE
> 541065216 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M DEFAULT
> STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 8 MAXEXTENTS 4096
> PCTINCREASE 50) ONLINE PERMANENT
> ORA-1543 signalled during: CREATE TABLESPACE "RBS" DATAFILE
> '/u02/oradata/cy...

and equally the RBS tablespace already exists...

<snip other preexisting tablespaces>

> Mon Mar 1 09:07:51 2004
> CREATE PUBLIC ROLLBACK SEGMENT RBS0 STORAGE(INITIAL 524288 NEXT 524288
> MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
> ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS0
> STORAGE(INITIA...
Rollback segment RBS0 already exists...

> Mon Mar 1 09:07:51 2004
> CREATE PUBLIC ROLLBACK SEGMENT RBS1 STORAGE(INITIAL 524288 NEXT 524288
> MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
> ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS1
> STORAGE(INITIA...
and RBS1...
<snip others>
> Mon Mar 1 09:07:53 2004
> ORACLE Instance cyrel - Can not allocate log, archival required
> Mon Mar 1 09:07:53 2004
> ARCH: Connecting to console port...
> Thread 1 cannot allocate new log, sequence 441
> All online logs needed archiving
> Current log# 2 seq# 440 mem# 0: /u02/oradata/cyrel/redo02.log

and here is why the session is 'hanging', actually ALL activity against this db will have stopped at this point.

>
> What have I done wrong / differently from successful imports?????
> Mike Morris
<snip>

> > > The command I use:
> > > imp name/pwd_at_instance full=y file=xxx.dmp

It looks to me as if your database export contains a FULL database export. Thus you will recreate everything in your old tablespace if you import the full dump file. However your target database already has already been created. Most likely you don't want to recreate all of these objects, but use the existing ones. The import parameter IGNORE=Y will allow you to do this. You might also consider if you really want to import the full database, or just a specific schema (in which case look at FROMUSER and TOUSER). if it is any comfort then it is likely that you would have hit the problem causing your database to hang sooner or later anyway. This is caused because the database is in archivelog mode which is as it should be, and either 1. automatic archival is not set - in which case setting it would be a very good mode.
2. your log_archive_dest is situated on a slow disk in which case moving it would be good.

I'll wager a virtual beer that you have problem 1. Archiving redo logs is a damn good idea and you really ought to read up on it.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK