RE: Redos gone crazy--a job for audit?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 09 Oct 2003 09:29:27 -0800
Message-ID: <F001.005D2927.20031009092927@fatcity.com>

log miner should give you what you want ... why not? On last friday something happened and in our database which usually averages about 100x100M archive logs, it started throwing 41 files between 2pm-3pm, 248 between 3pm-4pm, 95 between 4pm-5pm.

Of course we couldn't analyze all files, but an analysis og a 10 minute interval at the beginning of archive franzy shows a clear set of 5 SQLS that repeated about 83000 times in 10 minutes.

Once we gave it to development, they were able to identify the process which was using the code in question and it became easier.

I'd start at-least half hour before the peak time and do a slow analysis.

I have also found that instead of selecting from v$lgmnr_contents, I am more comfortable with doign a CTAS and then perform queries at my leisure for a detailed analysis.

Go for log miner ... at-least it will tell you what caused the problem. HTH
Raj

---

---

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L

Hi, list. Ya, I'm still alive and kickin'.

We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2

The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day.

Even tho this started when a "small" change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!!

I want to know what's in those redo logs.

I initially thought about log miner. However, I'm not sure log miner will give me what I want.

I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user.

audit all by <myuser> by access;
audit update table, insert table, delete table by
<myuser> by access;

Is there anything else that will be going to redo that I can capture with audit??

Thanks for any help.

Barb

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Oct 09 2003 - 12:29:27 CDT