Re: imp hangs then ora-3113 error

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

--
@home.com is bogus.  "Though I consider you a right-wing nut case on
most issues, I'm 100 percent with you against this big-media power
grab."
http://www.signonsandiego.com/news/uniontrib/tue/opinion/news_1e17safire.html