Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!rip!chi1.webusenet.com!news.webusenet.com!newsfeed-east.nntpserver.com!nntpserver.com!news.maxwell.syr.edu!postnews1.google.com!not-for-mail
From: kurt-erich.finger@hte-company.de (Kurt-Erich Finger)
Newsgroups: comp.databases.oracle.server
Subject: Re: Frequency of log switches
Date: 10 Oct 2003 08:47:33 -0700
Organization: http://groups.google.com
Lines: 97
Message-ID: <5208be2f.0310100747.151a44da@posting.google.com>
References: <5208be2f.0310072327.14f5c0d9@posting.google.com> <3F853A74.5099@yahoo.com> <5208be2f.0310092327.449dab5a@posting.google.com> <3f866825$0$29372$afc38c87@news.optusnet.com.au>
NNTP-Posting-Host: 212.65.13.19
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1065800854 22855 127.0.0.1 (10 Oct 2003 15:47:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 10 Oct 2003 15:47:34 +0000 (UTC)
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:245038

"Howard J. Rogers" <hjr@dizwell.com> wrote in message news:<3f866825$0$29372$afc38c87@news.optusnet.com.au>...
> Kurt-Erich Finger wrote:
> 
> > Connor McDonald <connor_mcdonald@yahoo.com> wrote in message
> > news:<3F853A74.5099@yahoo.com>...
> >> Kurt-Erich Finger wrote:
> >> > 
> >> > Oracle 8.1.7 on Linux 2.4
> >> > 
> >> > Hello,
> >> > 
> >> > I am trying to implement a back-up/recovery procedure in my company.
> >> > To test it, I took a copy of the production database, added
> >> > 
> >> > log_checkpoint_interval = 10000
> >> > log_archive_dest=/data/arc
> >> > log_archive_format='arc%s.log'
> >> > log_archive_start=True
> >> > 
> >> > to the init-file and started the instance with
> >> > ALTER DATABASE ARCHIVELOG
> >> > 
> >> > The archived logs are generated, however, at a high frequency.
> >> > Before I switched on archiving there were only some log switches
> >> > per day. Switching on archivelog I got new logs (and archived logs)
> >> > every minute when the the size of the log-files was 10mb and log
> >> > switches every 20 minutes when the size of the log-files was 50mb.
> >> > The frequencies seem to be very high taking into account that there is
> >> > no (user) activity on the instance.
> >> > 
> >> > How can I reduce the log switches to reasonable numbers?
> >> > (increasing log_checkpoint_interval did not help)
> >> > 
> >> > Cheers,
> >> > Kurt-Erich
> >> 
> >> Take a look at v$sesstat joined to v$statname, and you get how much redo
> >> each session is generating
> >> 
> >> hth
> >> connor
> > 
> > hello,
> > 
> > 1. select * from v$backup tells me "Not Active" for all tablespaces.
> >    The database is not in hot backup mode.
> >    (If I do ALTER TABLESPACE BEGIN BACKUP I see "Active" for that
> >    tablespace)
> > 
> > 2. select v$sesstat.sid,v$sesstat.value,v$statname.name
> >    from v$sesstat,v$statname
> >    where v$sesstat.statistic#=v$statname.statistic#
> > 
> >    =>
> >    SID        VALUE   NAME
> > 3      30714744       redo wastage
..
> > 
> > 
> > Any further ideas?
> > 
> > best regards
> > 
> > Kurt-Erich
> 
> Just a wild stab in the dark here... but is your temporary tablespace
> actually temporary? And nologging?
> 
> Select * from dba_tablespaces and have a look.
> 
> If you have a brand-new tempfile style of temporary tablespace, that's
> created as CONTENTS=TEMPORARY and NOLOGGING. But if you have an
> old-fashioned datafile-style temporary tablespace, then it might have
> CONTENTS=PERMANENT (yuk) and/or LOGGING enabled.
> 
> If so, alter tablespace temp temporary to fix the first issue. And alter
> tablespace temp nologging to fix the second. And then see how your log
> switches go.
> 
> Regards
> HJR

Howard,

1. the temporary tablespace is CONTENTS=TEMPORARY and LOGGING
   I will fix this.

2. In one schema I have 6 jobs that refresh views and materialized views.
   I found that the frequency of log switches decreases when I increase (delete)
   some jobs. With no jobs I see no log switches (the instance is idle)

   Have I overlooked something in the manuals concerning archived redo logs
   and active jobs?

Regards

Kurt-Erich