Very Computer

I have an oracle db which has 3 redo logs 10MB each. Upon investigation,
the redo logs are very big in size. Hardly 5% of the redo log size is
being used. This causes severe problems when automatic archiving is on.
Every time a log switch occurs, the system hiccups (because it is trying
to archive the redo log file). Is there a way of decreasing the size of
the redo logs. (Perhaps  by creating new ones with a smaller size and
deleting the old ones).

Any help appreciated....

Quote:>I have an oracle db which has 3 redo logs 10MB each. Upon investigation,
>the redo logs are very big in size. Hardly 5% of the redo log size is
>being used. This causes severe problems when automatic archiving is on.
>Every time a log switch occurs, the system hiccups (because it is trying
>to archive the redo log file). Is there a way of decreasing the size of
>the redo logs. (Perhaps  by creating new ones with a smaller size and
>deleting the old ones).

Alternatively, you may try turning on the ARCH process.  If LGWR is
archiving the redo logs, the engine will pause as the archived log is
written to disk.  To turn on ARCH, in the init.ora file set the
following:

  log_archive_start                          =TRUE

And bounce the engine.  You'll see another ora_ process called
ora_arch_<SID>.

--

The Internet: an empirical test of the idea that a million monkeys banging
on a million keyboards can produce Shakespeare.

10mb is not very big. But, if you want to reduce the size of
of redo log files you will basically have to drop/create new ones
with a smaller size. Remember, two groups of redo logfiles must
be online at all times. So basically the steps would be:

1) drop group 3
2) create a new group 3
3) drop group 2
4) create new group 2
5) drop group 1
6) create new group 1

You will also have to force log switches as you go through this process
since you can not drop a log file that is currently being used. The
command
for this is:  alter system switch logfile;  To see which logfile is
currently active
select * from v$log; When you create your new redo log groups it
would be a good idea to also create a member for that group(on a
different
drive).

One thing I would try before resizing your redo files (v.7x)  is to set
initSID.ora
parm checkpoint_process=true and see if you get better performance
during
a checkpoint (which occurs at a log file switch).

M.Landa

SQL> alter system switch logfile;

Arch only picks up and moves the portion of the logfile that was used.

3x10meg is actually pretty 'small' in size.  size is relative however, it
depends on the system, I would say though in general, 3x10meg is small.  Can you
provide more information on why you think the logs are switching before they are
used and why you think ARCH is coping 95% empty space?

Thomas Kyte

Oracle Government
Bethesda MD

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

It's rather simple really:

1)    Figure out what size you want your redo log files (3 Mb ?)
2)    Add three (or more, but at least two) new redo log files with your
wanted size
3)    Force some log switches
4)    drop the old logs (if they're still in use, you'll have to force some
more log switches)

If you're taking cold backups from time to time, you should remember to
modify your scripts accordingly.

If you're using hot backups, you should backup you control file to trace
(just to be on the safe side)

Hope this helps
--
Karl
Oracle Certified DBA

Quote:>I have an oracle db which has 3 redo logs 10MB each. Upon investigation,
>the redo logs are very big in size. Hardly 5% of the redo log size is
>being used. This causes severe problems when automatic archiving is on.
>Every time a log switch occurs, the system hiccups (because it is trying
>to archive the redo log file). Is there a way of decreasing the size of
>the redo logs. (Perhaps  by creating new ones with a smaller size and
>deleting the old ones).

>Any help appreciated....

Are your log files located on the same disk as something else?  If so, you
may want to consider moving them. This may be what is causing your hiccup.
Look at your alert.log file.  If you have the error "checkpoint not
complete", then it means that your log files are spinning and need to be
increased - not decreased in size. Also, look at the parameter "redo log
space wait time" in your v$sysstat table.  If this is a non-zero value you
either need to increase the size of your log files - this can be validated
in the alert.log (see above), or by increasing the value of your log_buffers
- just be sure that this number is divisable by your block size.

Sheilah Scheurich
DBA
My email is wrong - to send me anything you have to remove "want no spam at
all" from my email address.