Oracle IMP Hangs
Mike Morris
similarly configured (but more capable) solaris box. The import starts
fine but hangs after a varying number of tables are imported.
The command I use:
imp name/pwd@instance full=y file=xxx.dmp
I have the /etc/system set as required for oracle.
I have used this technique dozens of times and never had this happen.
Any suggestions would be appreciated!
Mike Morris
Mark C. Stock
"Mike Morris" <morr...@ei.dupont.com> wrote in message
news:3b2c6993.04022...@posting.google.com...
check the alert log for error messages
make sure there are no locks on the tables being imported
;-{ mcs
Ron
Hello Mike,
Are you sure it hangs? As a start point - check alert log messages and
check what top oracle user processes are doing .
Also, you can use imp parameter FEEDBACK=<n_rows> to see if import is
progressing.
Please, let us know if more help is needed.
Regards,
Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html
"Mike Morris" <morr...@ei.dupont.com> wrote in message
news:3b2c6993.04022...@posting.google.com...
Mike Morris
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/...
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...
Mon Mar 1 09:07:49 2004
CREATE TABLESPACE "TEMP" DATAFILE '/u02/oradata/cyrel/temp01.dbf'
SIZE 71303168 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M DEFAULT
STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0) ONLINE TEMPORARY
ORA-1543 signalled during: CREATE TABLESPACE "TEMP" DATAFILE
'/u02/oradata/c...
Mon Mar 1 09:07:49 2004
CREATE TABLESPACE "USERS" DATAFILE '/u02/oradata/cyrel/users01.dbf'
SIZE 109051904 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
DEFAULT STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS
4096 PCTINCREASE 0) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "USERS" DATAFILE
'/u02/oradata/...
Mon Mar 1 09:07:49 2004
CREATE TABLESPACE "INDX" DATAFILE '/u02/oradata/cyrel/indx01.dbf'
SIZE 56623104 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M DEFAULT
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "INDX" DATAFILE
'/u02/oradata/c...
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...
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...
Mon Mar 1 09:07:51 2004
CREATE PUBLIC ROLLBACK SEGMENT RBS2 STORAGE(INITIAL 524288 NEXT 524288
MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS2
STORAGE(INITIA...
Mon Mar 1 09:07:51 2004
CREATE PUBLIC ROLLBACK SEGMENT RBS3 STORAGE(INITIAL 524288 NEXT 524288
MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS3
STORAGE(INITIA...
Mon Mar 1 09:07:51 2004
CREATE PUBLIC ROLLBACK SEGMENT RBS4 STORAGE(INITIAL 524288 NEXT 524288
MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS4
STORAGE(INITIA...
Mon Mar 1 09:07:51 2004
CREATE PUBLIC ROLLBACK SEGMENT RBS5 STORAGE(INITIAL 524288 NEXT 524288
MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS5
STORAGE(INITIA...
Mon Mar 1 09:07:51 2004
CREATE PUBLIC ROLLBACK SEGMENT RBS6 STORAGE(INITIAL 524288 NEXT 524288
MINEXTENTS 8 MAXEXTENTS 4096) TABLESPACE "RBS"
ORA-1535 signalled during: CREATE PUBLIC ROLLBACK SEGMENT RBS6
STORAGE(INITIA...
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
What have I done wrong / differently from successful imports?????
Mike Morris
"Ron" <sup...@dbainfopower.com> wrote in message news:<ltydnTsNB81...@comcast.com>...
Mark C. Stock
| 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:
|
you've got diskspace problems, ie., no space left for archive logs:
| 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
this will cause the database to hang (for any transaction) until disk space
is available for archving
are you familiar with oracle archiving and managing the archive logs?
;-{ mcs
Niall Litchfield
> 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@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
Mike Morris
For some reason, when the database instances were created, they did
not get their "log_archive_start" parameter included in the init.ora
file. When the line:
log_archive_start = true
was added to the init file and the system was restarted, imports
succeeded.
Mike
"Ron" <sup...@dbainfopower.com> wrote in message news:<ltydnTsNB81...@comcast.com>...
Ron
Glad to hear :-)