Re: Frequency of log switches

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3f866825$0$29372$afc38c87_at_news.optusnet.com.au>...
> Kurt-Erich Finger wrote:
>
> > Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
> > news:<3F853A74.5099_at_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 Received on Fri Oct 10 2003 - 10:47:33 CDT