Re: Redo log size and standby database

"Tim Kearsley" <tim.kearsley_at_milton-keynes.gov.uk> wrote in message news:725736ef.0209030212.6ffc326d_at_posting.google.com...
> Hi all,
>
> I've recently taken over responsibility for administration of an OLTP
> database, about 15 Gbytes in size, running Oracle 8.1.7 on AIX 4.3.3.
> Generally, things look in pretty good shape, but one thing I am going
> to need to do in the near future is to create a standby database from
> this live one, to provide at least a read-only facility in the event
> of the live database becoming unavailable, and possibly to provide a
> full failover and activate the standby in the event of real trouble.
>
> My question is this:
>
> The redo logs on the live database are 100 Mbytes, in four groups, no
> multiplexing (just one member per group). Firstly, I'm going to
> mirror the logs and provide at least two members per group. However,
> I'm thinking of reducing the redo log size as log switches seem to
> happen only once per day on average. From what I know about standby
> databases, there is a chance of losing the current log if and when the
> live database becomes unavailable, depending on what causes it to
> become unavailable. In addition, if I remember correctly, there is a
> rule of thumb stating that log switches of a frequency around two per
> hour is a reasonable compromise between short recovery time and
> performance.

Hi Tim

As a rough rule of thumb, yes your redo logs would be reasonably sized all things being equal.

Note that the instance recovery times and the redo log size can now be made into a non-issue by setting an appropriate value for the FAST_START_IO_TARGET in 8i. This parameter basically governs how many IO operations are required to perform instance recovery and makes good old dbwr write down any "older dirty" blocks that would prevent this target from being reached. Although not a guaranteed value, it does allow for a ball park instance recovery time to be achieved. Note also the behaviour of the LOG_CHECKPOINT_TIMEOUT/INTERVAL parameters have changed to better control the performance of checkpoints. It's all worth investigating.

Note also that 9i has simplified things further with the FAST_START_MTTR_TARGET which specifies the number of seconds you want instance recovery to take. Set this to say 120 seconds and dbwr (tries to) ensure that any dirty blocks that would prevent instance recovery from being performed in 120 seconds is written to disk.

So controlling checkpoint overheads and instance recovery times with appropriately size redo logs is not such an issue per se.

With a standby DB in 8i, yes the current online redo log could well be lost during a disaster. Any logs could potentially be lost but the current online log is at greatest risk (as it can't obviously be archived yet). Smaller logs means the potential amount of lost data is reduced. That's why it's important to hire a DBA who, despite the fire that is engulfing the building, despite the walls that are collapsing all around, despite the ticking off the bomb with only seconds to go will put safety aside and issue the alter system switch logfile command ;)

Note again with 9i and the Data Guard improvements to the standby DB, many of these issues are addressed in varying degrees and you can hence hire cowardly DBAs such as myself who in an emergency would say bugger the database, where are my Bowie CDs ...

Cheers

Richard

>
> Does my reasoning sound, well, reasonable, or are there other factors
> to take into account?
>
> Thanks for any advice and opinions.
>
> Tim Kearsley
> DBA, Milton Keynes Council
Received on Tue Sep 03 2002 - 08:55:08 CDT