Re: Redos gone crazy--a job for audit?
Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Every time I have run into this situation, I have used the following approach and it has always worked. I've never validated it in all cases, so take it with a grain of salt. Redo is generated by block changes. Find the session that is generating the most # of block changes (v$sess_io.block_changes). Then trace back to the session info, sql, etc. Also check for tablespaces in hot backup mode. Daniel Barbara Baker wrote: 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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: Redos gone crazy--a job for audit?
Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] 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: [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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
I just repeated this test and found that for normal tables the redo size is 292848 for GTT the redo size is 1581888 (increase of 540%) Thank You Thank You Thank You Thank You Thank You Thank You Boris for mentioning this. 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: Friday, October 10, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] 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: [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). ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Boris, I used your script (well, almost: in your script you create temporary global table, but never use it, so, I modified it). And it shows redo size increase substantially lower (~7 times) in case of using temp table. But, I was running script on 8.1.5. When, running on 9.2 it appears, that you are correct: temp table generates much more redo than permanent table. Both results are shown below: Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production SQL create table t6 (i int) ; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 8780 SQL SQL insert into t6 select obj# from sys.obj$ where rownum = 1; 1 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 157964 SQL = The redo size increase with permanent table is: 157964 - 8780 = 149184 SQL create global temporary table t7 (i int) on commit 2 delete rows; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 162060 SQL SQL insert into t7 select obj# from sys.obj$ where rownum = 1; 1 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 189264 SQL = The redo size increase with global temporary table is: 189264 - 162060 = 27204 Now, on 9.2: Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create table t6 (i int) ; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 7204 SQL SQL insert into t6 select obj# from sys.obj$ where rownum = 1; 9038 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 150252 SQL = The redo size increase with permanent table is: 150252 - 7204 = 143048 SQL create global temporary table t7 (i int) on commit 2 delete rows; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 154032 SQL SQL insert into t7 select obj# from sys.obj$ where rownum = 1; 9039 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 1287624 SQL = The redo size increase with global temporary table is: 1287624 - 154032 = 1133592 which is quite different from the testing results under 8.1.5. I don't have access to Metalink right now to check Bug# 2874489. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from
RE: Redos gone crazy--a job for audit?
Title: RE: Redos gone crazy--a job for audit? 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- From: Barbara Baker [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: Redos gone crazy--a job for audit? 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 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: Redos gone crazy--a job for audit?
Here is a query that will help you find the biggest consumer of redo blocks: select sid, value from (select s.sid,s.value from v$sesstat s, v$statname n where s.statistic#=n.statistic# and n.name='redo blocks written' order by value); However, 50MB redo blocks are wy to small. To paraphrase Orwell, 50MB good, 250MB better, 500MB mch better. On Thu, 2003-10-09 at 13:09, Barbara Baker wrote: 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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
It should have been redo entries, not redo blocks written. The only guy writing redo blocks is LGWR, so the previous query would always give a hume amount for SID=4 and 0 for everybody else. On Thu, 2003-10-09 at 13:09, Barbara Baker wrote: 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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Just a quick thought, are any tablespaces left in Hot Backup Mode? Rich -Original Message- Barbara Baker 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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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.net -- Author: Rich Gesler INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Do you have the database in backup mode? Waleed -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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Well, that was an excellent idea. But sadly, that's not it. (We actually don't use hot backups, but I checked just in case someone mucked with it. No dice.) Thanks. Barb --- Khedr, Waleed [EMAIL PROTECTED] wrote: Do you have the database in backup mode? Waleed -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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [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). __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
Barb, Every time I have run into this situation, I have used the following approach and it has always worked. I've never validated it in all cases, so take it with a grain of salt. Redo is generated by block changes. Find the session that is generating the most # of block changes (v$sess_io.block_changes). Then trace back to the session info, sql, etc. Also check for tablespaces in hot backup mode. Daniel Barbara Baker wrote: 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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Redos gone crazy--a job for audit?
Sample the top sessions from v$sesstat for statname 'redo size' (statistic# 115 in my database) Then joining to v$sql should give you the sql that generates that redo. Waleed -Original Message- Sent: Thursday, October 09, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Well, that was an excellent idea. But sadly, that's not it. (We actually don't use hot backups, but I checked just in case someone mucked with it. No dice.) Thanks. Barb --- Khedr, Waleed [EMAIL PROTECTED] wrote: Do you have the database in backup mode? Waleed -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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [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). __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [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: Redos gone crazy--a job for audit?
You can track the sql which is doing DML while redo being generated. Look at v$sqlarea or v$sql. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 09:09:24 -0800 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 __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [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). _ Frustrated with dial-up? Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] 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: [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).
