Re: exec STATSPACK.SNAP renders ORA-03113

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3fcbb925$0$13682$afc38c87_at_news.optusnet.com.au>...
> "Wilson Guerrero-Coltters" <wcoltters_at_yahoo.com> wrote in message
> news:ff6f1d30.0312011325.3c4906f2_at_posting.google.com...
> > Hi,
> >
> > I'm hitting the same problem here. Statspack just stopped working today.
> > I killed after 20 minutes, all the time near 100% CPU.
> > After several runs I gave up.
> > It freeze here
> >
> > INSERT into stats$latch
> > ( snap_id
> > , dbid
> > , instance_number
> > , name
> > , latch#
> > , level#
> > , gets
> > , misses
> > , sleeps
> > , immediate_gets
> > , immediate_mi
> >
> > But i'm on 9i
> >
> > Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
> > PL/SQL Release 9.0.1.0.0 - Production
> > CORE 9.0.1.0.0 Production
> > TNS for Compaq Tru64 UNIX: Version 9.0.1.0.0 - Production
> > NLSRTL Version 9.0.1.0.0 - Production
>
> Well, you're on the initial release of Oracle 9i Release 1, and although the
> bug-fix has been back-ported to 9.0.1 (according to Metalink, anyway), that
> certainly will require the application of a patch before you get the
> benefit. From memory, I believe they're up to 9.0.1.0.4.
>
> > Any suggestions welcome.
> >
> > PS: We don't have access to metalink
>
> Er, well, then you're stuffed. Because patches can only be gotten from
> Metalink as far as I know. Presumably this isn't a licensed system, then?
>
> Regards
> HJR
It is perfectly legal, if that's what you mean. For what I know it is licensed, it just don't have support contract.

I've been looking for more information related to this issue.

I got ora-03113 when I do a select from v$latch, a core file is generated.
Also have problems with v$latch_children.

Appart from having to apply a patch, do you have any idea why this problem
is produced? I mean, why there was no problem until yesterday?

I'm on unix
Compaq Tru64 UNIX: Version
root_at_cshalpha:/home> uname -a
OSF1 cshalpha V5.1 2650 alpha

so I did a sys_check on the system and found something interesting about
share memory segments:

Shared memory segment with 0 attaches

If I do

root_at_cshalpha:/home> ipcs -mo

Shared Memory:

T      ID        KEY    MODE         OWNER    GROUP NATTCH
m       0      0x624 --rw-rw-rw-      root   system      0
m       1   0x280267 --rw-r--r--      root   system     95
m       2          0 --rw-rw----  oracle9i      dba    143
m       3 0xf5a0ff48 --rw-rw----  oracle9i      dba    143
m       4          0 --rw-rw-rw-  oracle9i      dba      0
m       5          0 --rw-rw-rw-  oracle9i      dba      0
m       6          0 --rw-rw-rw-  oracle9i      dba      0
m       7          0 --rw-rw-rw-  oracle9i      dba      0
m       8          0 --rw-rw-rw-  oracle9i      dba      1

As you notice there are share segment with 0 ATTACHS.

Can this be related to the previous problem? Is this expected Oracle behaviour or abnormal? Can I remove the segments with 0 attachs with ipcrm -m with total impunity?
I read in a previous post by you that the only suggestion was not to run STATSPACK
on this systems, I suppose that includes not to do a select on the related tables. Is this the only problem I should expect? No colateral effects?

Well, that's a lot of questions, sorry for abusing your time.

Regards,
WGC Received on Tue Dec 02 2003 - 11:35:12 CST