Re: imp hangs then ora-3113 error

richchri_at_erols.com (richie) wrote in message news:<9a436674.0306190308.38817e80_at_posting.google.com>...
> 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.

A couple more things (apologies if they are obvious, but one never knows):

Where xxx is your SID:

Do a ps -ef|grep imp and ps -ef|grep xxx to check if it is indeed running or dead. (or top, which would show it as a top process if it is still running).

export ORACLE_SID=xxx
and then run the imp command without the @xxx. Do ps as above to see if you are doing something different now.

Sessions do not use up a whole lot of memory, if you are swapping it means your SGA is just way too big (or you have unreleased shared memory issues), and need to shrink it (as you've found with the shared_pool). Could your init.ora have unnecessary java pool in it?

If you have multiple imp sessions listed at the same time in Oracle, you may have some severe locking or rollback issues. This should show nothing (as SYSTEM):

• from metalink Note:1039126.6 select substr(a.os_user_name,1,8) "OS User", substr(a.oracle_username,1,8) "DB User", substr(b.owner,1,8) "Schema", substr(b.object_name,1,20) "Object Name", substr(b.object_type,1,10) "Type", substr(c.segment_name,1,5) "RBS", substr(d.used_urec,1,12) "# of Records" from v$locked_object a, dba_objects b, dba_rollback_segs c, v$transaction d, v$session e where a.object_id = b.object_id and a.xidusn = c.segment_id and a.xidusn = d.xidusn and a.xidslot = d.xidslot and d.addr = e.taddr ;

Also you might want to use your favorite tools to see if you are going nuts on latch waits or something.

Are you using the compress=Y option on the export? There are situations where if you are, it would be bad, but I can't think of any that would be fixed by shared_pool sizing. You may want to check for physical fragmentation anyways, just because. Couldn't tell from your post if you are just adding new data or truncating or deleting first. How many call records are there, anyways? Sometimes problems of things getting worse over time are just plain basic DBA issues...

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

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/fri/business/news_1b20viasat.html