RE: DB freezes ... no indications of any kind
Thanks to all who helped me with this one, looks like we have * bug *. OWS is working on resolution and probably a patch. Once I get a clear research note from dev, I'll update the list if anyone is interested. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: DB freezes ... no indications of any kind
Thanks Arun Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: DB freezes ... no indications of any kind
Thanks Waleed, Actually this happened again last night ... it took me 2 seconds to log in, so no sqlnet problem. The system was dumping trace files like crazy, yet Spotlight didn't show any trouble spots. Let me see what Oracle has to say on this the TAR is already in progress. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: DB freezes ... no indications of any kind
When your database freezes can you log in as internal thro svrmgrl and see what is happening on v$session_Wait -Original Message- Sent: Tuesday, March 12, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Thanks john, The dbms_job has problems on 9012, so I am switching to good old cron ... basically I don't have the statpack info at all ... but I am starting it at 10am today. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Thanks Stephan, No it is not a problem with archiving at all, the filesystem assigned for archiving has may more GB of space than it needs. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 5:03 PM To: Multiple recipients of list ORACLE-L Additionally, if you are running Oracle Archiving, you need to make sure that you allow sufficient time for the copy to be made. Also, if the archive directory is full, the database will hang. Thank You Stephen P. Karniotis ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: DB freezes ... no indications of any kind
Thanks john, The dbms_job has problems on 9012, so I am switching to good old cron ... basically I don't have the statpack info at all ... but I am starting it at 10am today. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: DB freezes ... no indications of any kind
If you are able to login and query the session_Wait
try using the below said query
select s.INDX
SID,u.KSUUDNAM||','||u.KSUSEUNM||','||u.KSUSEMNM||'('||decode(KSUSSOPC,2,'LA
TCH FREE',12,'ENQUEUE',94,'db file sequential read',
95,'db file scattered read',144,'LIBRARY CACHE PIN',75,'buffer busy waits',
145,'library cache lock',146,'library cache load lock',91,'log file
sync',KSUSSOPC)||')' EVENT_WITH_OSUSER,
KSUSSP1R P1RAW,KSUSSP1,KSUSSP2 P2,KSUSSP3 FROM X$KSUSECST s,x$ksuse u
WHERE KSUSSOPC IN(2,12,94,95,144,75,145,146,91) and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
and u.indx=s.indx
-Original Message-
Sent: Tuesday, March 12, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Thanks Arun
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arun Chakrapani
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
And how would I interpret the output of this query?
Thanks Arun
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Tuesday, March 12, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
If you are able to login and query the session_Wait
try using the below said query
select s.INDX
SID,u.KSUUDNAM||','||u.KSUSEUNM||','||u.KSUSEMNM||'('||decode(KSUSSOPC,2,'LA
TCH FREE',12,'ENQUEUE',94,'db file sequential read',
95,'db file scattered read',144,'LIBRARY CACHE PIN',75,'buffer busy waits',
145,'library cache lock',146,'library cache load lock',91,'log file
sync',KSUSSOPC)||')' EVENT_WITH_OSUSER,
KSUSSP1R P1RAW,KSUSSP1,KSUSSP2 P2,KSUSSP3 FROM X$KSUSECST s,x$ksuse u
WHERE KSUSSOPC IN(2,12,94,95,144,75,145,146,91) and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
and u.indx=s.indx
*2
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
*2
RE: DB freezes ... no indications of any kind
It's a beautiful query which only misses FROM clause. I'll save it
for later use. Much later.
-Original Message-
From: Arun Chakrapani [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 12, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: DB freezes ... no indications of any kind
If you are able to login and query the session_Wait
try using the below said query
select s.INDX
SID,u.KSUUDNAM||','||u.KSUSEUNM||','||u.KSUSEMNM||'('||decode(
KSUSSOPC,2,'LA
TCH FREE',12,'ENQUEUE',94,'db file sequential read',
95,'db file scattered read',144,'LIBRARY CACHE
PIN',75,'buffer busy waits',
145,'library cache lock',146,'library cache load lock',91,'log file
sync',KSUSSOPC)||')' EVENT_WITH_OSUSER,
KSUSSP1R P1RAW,KSUSSP1,KSUSSP2 P2,KSUSSP3 FROM X$KSUSECST
s,x$ksuse u
WHERE KSUSSOPC IN(2,12,94,95,144,75,145,146,91) and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
and u.indx=s.indx
-Original Message-
Sent: Tuesday, March 12, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Thanks Arun
Raj
__
Rajendra JamadagniMIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect
that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arun Chakrapani
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Why are you saying FROM is missing? It is there...
Regards
Rafiq
FROM X$KSUSECST
s,x$ksuse u
WHERE KSUSSOPC IN(2,12,94,95,144,75,145,146,91) and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
and u.indx=s.indx
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 12 Mar 2002 10:55:49 -0800
It's a beautiful query which only misses FROM clause. I'll save it
for later use. Much later.
-Original Message-
From: Arun Chakrapani [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 12, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: DB freezes ... no indications of any kind
If you are able to login and query the session_Wait
try using the below said query
select s.INDX
SID,u.KSUUDNAM||','||u.KSUSEUNM||','||u.KSUSEMNM||'('||decode(
KSUSSOPC,2,'LA
TCH FREE',12,'ENQUEUE',94,'db file sequential read',
95,'db file scattered read',144,'LIBRARY CACHE
PIN',75,'buffer busy waits',
145,'library cache lock',146,'library cache load lock',91,'log file
sync',KSUSSOPC)||')' EVENT_WITH_OSUSER,
KSUSSP1R P1RAW,KSUSSP1,KSUSSP2 P2,KSUSSP3 FROM X$KSUSECST
s,x$ksuse u
WHERE KSUSSOPC IN(2,12,94,95,144,75,145,146,91) and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
and u.indx=s.indx
-Original Message-
Sent: Tuesday, March 12, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Thanks Arun
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect
that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arun Chakrapani
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
MOHAMMAD RAFIQ
_
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
This query was totally customized for our production database.
We run this query every 2 secs
This is querying the main knowN events which causes sometimes the database
to freeze
especially the latch free wait you can customize it as you want.
the format comes out like below
SIDEVENT_WITH_OSUSER
P1RAWKSUSSP1P2 KSUSSP3
--
-- -- --
929 ,printstop,TECHSUPP6(LATCH FREE)
DA50D9C8 3662731720 66 0
(sid_of_the_user) the wait event along with the username and machine
SID GIVES U THE SID NAME
EVENT_WITH_OSUSER GIVES THE OSUSER AND THE EVENT IT IS WAITING ON
P1RAW GIVES U THE RAW DATA FOR THAT PARTICULAR EVENT THIS FOR EXAMPLE CAN BE
LINKED IN LATCH FREE TO V$LATCH TO GET THE LATCH NAME IT IS WAITING ON AND
WHERE AND WHY ALSO
KSUSSP1 GIVES U THE P1 VALUE OF SESSION_WAIT WHICH USUALLY GIVES THE
FILE_NUMBER AND OTHER THINGS AND P2 GIVES THE BLOCK NUMBER OR OTHER THINGS
AND IF IT IS LATCH FREE IT WILL GIVE THE LATCH NUMBER.
I am not able to get the descriptions immediately.But I will mail it to you
as to what all it points to
As you might be knowing the v$sesssion_wait links to 2 x$tables X$KSUSECST
and x$ksled, The x$ksled is one way the static table which holds the texts
for all the events which are going on in session_Wait and the X$KSUSECST
gives you the actual waits going based on sids
What I have done is take what i want from x$ksled and designed this small
query for my convenience
If you are having 9i client installed u can put this query on performance
manager to the user defined chart and allow it to query every 2mins or how
many ever minutes u want and u can even start recording this whole event so
that u can trace back just before the freeze occured.
We have a very high oltp system and we had hectic problems once we migrated
from 8.0.6 to 8.1.7 and hence I started writing these queries since our
databases started to freeze without notice.
I will let u know more on this query Sorry about less information given
Once u run this query side by side run this another query which will point
to where latch is waiting for and why
I am able to track as to where the latch is getting screwed but the
whycolumn in this query which I am using I am not able to track why it
waiting for This only tracks the shared_pool,library cache and row cache
objects,It gives u the sql address also which might be screwing u up.
Even this I run every 2 secs in the peak time in order to know where we are
getting screwed.
select /*+ USE_NL(b c) */
a.kslltnum,a.addr,e.KSLLWNAM||'['||e.KSLLWLBL||']'Latch_name_with_label,a.
kslltwhy,a.kslltwhr,c.KSUSESQL,
c.KSUSESQH from x$ksllt a, v$session_Wait b,x$ksuse c,x$kslwsc d,x$ksllw e
where b.p1raw=a.addr
and p2 in(106,105,95)
and b.sid=c.indx and
a.kslltwhr=d.indx
and d.indx=e.indx
and bitand(c.ksspaflg,1)!=0 and bitand(c.ksuseflg,1)!=0
and d.inst_id=userenv('Instance')
and e.inst_id = userenv('Instance')
The above gives the results as below
KSLLTNUM ADDR Latch_name_with_label
KSLLTWHY KSLLTWHR KSUSESQL KSUSESQH
--
-- -- --
95 8000C114 kqreqd: rel enqueue[]
0801 D76DE55C 2392129741
The below said qeury gives u the buffer busy waits along with its file and
block number from this u can later on query the db_objects to find out which
object is getting screwed.
All these queries are made to run every 2 secs in our main production
environment.
and we put all this into recording in 9i performance manager in order to
track what has happened
This 9I's performance manager has helped me a lot in solving some probs
select s.indx Sid,f.fnnam Filename,s.KSUSSP2 BLOCK,s.ksussp3 Waiting_on from
X$KSUSECST s,x$kccfn f
where f.fnnam is not null and f.fntyp=4
and s.KSUSSOPC=75
and s.KSUSSP1=f.fnfno and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
I will let u know once my head is free.
If you can and quite confident on these queries try running them and start
recording the events so that u can analyze it later on .
or take immediate action when something goes wrong
Please try to install the performance manager of 8i which will ease most of
your work
u dont have to run the query manually this will run based on the time u have
set and will start recording too.
-Original Message-
Sent: Tuesday, March 12, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
And how would I interpret the output of this query?
Thanks Arun
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent:
RE: DB freezes ... no indications of any kind
Check MetaLink Doc ID: Note: 176129.1 ALERT: LATCH FREE And FREE_BUFFER_WAITS Cause Performance Degradation/Hang Problem affects 9.0.1.2 and sounds like what you're experiencing. HTH, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Office (919) 466-6723 Fax (919) 466-6783 http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- Sent: Monday, March 11, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Hi all, this is something new on our 9012 db. Once in two days, the DB will freeze up for up to 2-3 minutes ... and then everything becomes normal again. There are no trace files, no entries in alert log nothing but as production is hooked up to the db, they page, us, we get in but we don't see anything. Any ideas on where to start looking? It is 9012 on AIX (64 bit on 64 bit). Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Wagoner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Raj - Consider running statspack. If you have it configured, you can just enter: connect perfstat executes statspack.snap; This should help you to see what is going on inside Oracle. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 11, 2002 7:43 AM To: Multiple recipients of list ORACLE-L Hi all, this is something new on our 9012 db. Once in two days, the DB will freeze up for up to 2-3 minutes ... and then everything becomes normal again. There are no trace files, no entries in alert log nothing but as production is hooked up to the db, they page, us, we get in but we don't see anything. Any ideas on where to start looking? It is 9012 on AIX (64 bit on 64 bit). Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Dennis, the problem is, stats pack s schedules to run every 15 minutes, the problem is I want to know what happens during these 2-3 minutes why oracle is not telling me anything ... Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 11:31 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Raj - Consider running statspack. If you have it configured, you can just enter: connect perfstat executes statspack.snap; This should help you to see what is going on inside Oracle. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: DB freezes ... no indications of any kind
Raj - If you run STATSPACK with the statsauto script, then it runs every 15 minutes, which would be too large an interval for your purposes. However, if you execute statspack.snap; then it executes immediately. When the problem occurs, execute it. If you are concerned, keep a window open with the command typed so you just have to hit enter. Then 2-3 minutes later, execute the command again. If you are more comfortable with utlbstat/utlestat, I guess that is still available in 9i, but not as comprehensive as statspack. Naturally if the database is frozen or hung, then statspack will take awhile to complete. Another thought is Note 61552.1 Diagnosing Database Hanging Issues on Metalink. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 11, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Dennis, the problem is, stats pack s schedules to run every 15 minutes, the problem is I want to know what happens during these 2-3 minutes why oracle is not telling me anything ... Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 11:31 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Raj - Consider running statspack. If you have it configured, you can just enter: connect perfstat executes statspack.snap; This should help you to see what is going on inside Oracle. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Check the frequency of Log Switches, specially that time the db froze Waleed -Original Message- Sent: Monday, March 11, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Hi all, this is something new on our 9012 db. Once in two days, the DB will freeze up for up to 2-3 minutes ... and then everything becomes normal again. There are no trace files, no entries in alert log nothing but as production is hooked up to the db, they page, us, we get in but we don't see anything. Any ideas on where to start looking? It is 9012 on AIX (64 bit on 64 bit). Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Thanks Waleed, Well, I'd say about 3 log switches during 15 minute period before and after the freeze, nothing crazy ... that's what is confusing ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 2:07 PM To: Multiple recipients of list ORACLE-L Check the frequency of Log Switches, specially that time the db froze Waleed ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: DB freezes ... no indications of any kind
Why dont you sent an event which monitors the database and takes a system state dump during the freeze thro ORADEBUG From that I hope u can find out where it is bombing -Original Message- Sent: Monday, March 11, 2002 3:58 PM To: Multiple recipients of list ORACLE-L Thanks Waleed, Well, I'd say about 3 log switches during 15 minute period before and after the freeze, nothing crazy ... that's what is confusing ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 2:07 PM To: Multiple recipients of list ORACLE-L Check the frequency of Log Switches, specially that time the db froze Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Can you please elaborate on that? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Why dont you sent an event which monitors the database and takes a system state dump during the freeze thro ORADEBUG From that I hope u can find out where it is bombing ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: DB freezes ... no indications of any kind
You have to find out: If it's only a problem for users/programs that use sqlnet+listener to connect to the server. if you can connect locally to Oracle while others can not connect remotely then could be network traffic problem. If you can connect internal locally but can not connect locally through the listener then you've got listener problems (overloaded or no free cpu). Also find if there is any scheduled hot backups, triggered cleaning jobs, etc. Also check the O/S log (/var/adm/messages in Unix). Waleed -Original Message- Sent: Monday, March 11, 2002 3:58 PM To: Multiple recipients of list ORACLE-L Thanks Waleed, Well, I'd say about 3 log switches during 15 minute period before and after the freeze, nothing crazy ... that's what is confusing ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 2:07 PM To: Multiple recipients of list ORACLE-L Check the frequency of Log Switches, specially that time the db froze Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
like put a cron job which keeps connecting to the database if there a probs by any chance a freeze see to that in the script it runs ORADEBUG COMMAND WHICH WILL TAKE A DATABASE SYSTEM DUMP I have a feeling u know the command for ora debug If not please use the following script I use the below said script to monitor whenever the database freezes it takes snapshot of the database using system state dump. There are other commands also but i am not able to remember them I will post u once i get those scripts #!/bin/ksh get_pmon=`ps -ef |grep pmon_800P|grep -v grep` . /opt/app/oracle/oracle_env svrmgrl EOF connect internal oradebug setospid $get_pmon oradebug unlimit oradebug dump systemstate 10 exit EOF -Original Message- Sent: Monday, March 11, 2002 5:34 PM To: Multiple recipients of list ORACLE-L Can you please elaborate on that? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, March 11, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Why dont you sent an event which monitors the database and takes a system state dump during the freeze thro ORADEBUG From that I hope u can find out where it is bombing -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB freezes ... no indications of any kind
Raj, Well, I'd say about 3 log switches during 15 minute period before and after the freeze, nothing crazy ... that's what is confusing ... Could you post the Statspack report for the period where you experienced this 'hang' as well as one during 'normal' periods? If you don't want to, have a look at the Top 5 wait events - that would tell you a lot. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
