Re: imp hangs then ora-3113 error

I am using the commit=y the connect string looks like: imp USERID=db_aname/dba_password_at_sid etc...

i did check the netstat and it showed the connected port but not anymore then usual (the connected port and a port on 1521 listening). yes, i meant this instance is the only one running on the machine and only used for imports. i had tried restarting which had solved the problem in the past but this time it wouldn't help. the "dead" imps do show up as an oracle session in fact i can even look in the v$sql and see the insert query it is running (but it always hangs on that first query into that table) with the sid and serial of the imp session from v$session.

ok thanks, i'll move the shared_pool_size back up and see if i get the problem again and test somemore and watch the swapinfo. i also try starting up some sessions and see how much more ram gets allocated for it.

thanks all for your ideas...
-richie

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0306181434.7cd47b47_at_posting.google.com>...
> richchri_at_erols.com (richie) wrote in message news:<9a436674.0306180905.732a645b_at_posting.google.com>...
> > For all that care:
> >
> > to get around this i lowered the shared_pool_size in the orainit
> > file freeing up more free ram to other processies (which i guess
> > oracle couldn't then spwan off hence the ora-03113 error). what
> > bothers me is that this database was up and running with no problem
> > for months. it's strickly backup storage. we do not develope on it,
> > no processies use it. so it's not like the ram usage requirements
> > went up in the last few months. now with oracle you allocate the
> > fixed amount of ram it can eat up, is this not correct? so it
> > shouldn't of been trying to use anymore ram then was already allocated
> > for it?
>
> Three things come to mind:
>
> Are you using the commit= on import? What is your connect string?
> I'm wondering if you are using a non-local network transport to access
> your local db, imp becomes very slow because it is writing a huge RBS,
> and the transport times out.

>
> Have you checked to see if sockets are being used up? I've seen odd
> results on other unix platforms, where memory is unexpectedly used up
> by client connections going away (ie, user turns off pc, although I'm
> not sure what you mean by no processes use it - I guess you mean this
> instance is only accessed by imports - the import file has to get
> there somehow?) and leaving the connections. Usually fixed by tcp
> keepalive adjustments, check netstat and related things.
>
> Sometimes Oracle does leak memory. And on some platforms, killing the
> instance and restarting it may leave shared memory used up (usually
> noticed because you can't start up the instance because you can't get
> a shared memory realm, although I can imagine a set of settings
> allowing startup to happen, but with less memory available than
> expected). Do the dead imps show up as Oracle sessions? The fixed
> amount of memory used is the SGA, that does not include processes
> outside of the basic instance startup, ie, sessions. If your sessions
> use up all your memory and start swapping, things can go downhill
> fast. swapinfo -a.
>
> >
> >
> >
> > richchri_at_erols.com (richie) wrote in message news:<9a436674.0306160618.3203e5d1_at_posting.google.com>...
> > > Every now and then I have this problem where i can't import anything
> > > any longer into the database. It will just hang at the same spot.
> > > Then eventually come back with an error shown below. In the past
> > > restarting the database would clear up the issue and the import would
> > > work. However, restarting the database doesn't appear to help
> > > anylonger. There are no errors in the alert<sid>.log or any other log
> > > that I can find. Any help would be greatly appreciated as this issue
> > > is really starting to get on my nerves! Thanks in advance for your
> > > help.
> > >
> > >
> > > . . importing table "ACCESS_FLAGS"
> > > 4 rows
> > > imported
> > > . . importing table "BILL_DETAIL_MAP"
> > > 40 rows
> > > imported
> > > . . importing table "BILL_SECTIONS"
> > > 7 rows
> > > imported
> > > . . importing table "BROWSER_OPTIONS"
> > > 9 rows
> > > imported
> > > . . importing table "BUTTON_WEIGHT"
> > > 30 rows
> > > imported
> > > . . importing table "CALL_RECORDS_200305_1"
> > > IMP-00058: ORACLE error 3113 encountered
> > > ORA-03113: end-of-file on communication channel
> > > IMP-00058: ORACLE error 3114 encountered
> > > ORA-03114: not connected to ORACLE
> > > IMP-00000: Import terminated unsuccessfully
>
> jg
Received on Thu Jun 19 2003 - 06:08:37 CDT