Archived log size

Guest · October 18, 2011, 7:36am

Message from: smitty_046

What’s the best method to estimate space requirements for enabling archieved
mode. My database is 100g in total size. There’s two main tables that are
40g each.

Historical Messages

Author: smitty_046
Date: Tue Oct 18 05:36:08 PDT 2011

What’s the best method to estimate space requirements for enabling archieved
mode. My database is 100g in total size. There’s two main tables that are
40g each.

__

NormTeamT · October 18, 2011, 8:42am

Hi Smitty_046,

What's the best method to estimate space requirements for
enabling archieved mode. My database is 100g in total size.
There's two main tables that are 40g each.

Size isn't everything! It's what you do with it that counts!

What I mean is this:

The size of your database has no bearing on the size of your redo logs.
Neither does the size of your tables.
Setting the database in archive log mode is a good thing - for a production database - and possibly helpful on a test/development system.
Turning archive log on means that you will be generating a copy of each "used" redo log every time that that particular redo log fills up and is switched out of.
Each archived log will need to be stored somewhere on your server, or possibly transferred over the network to a standby database elsewhere.

What does affect the size of your redo logs and therefore the size of your archived logs and thus the amount of space required on the server are as follows:

How much updating takes place - INSERTs, UPDATEs and DELETEs all create redo information which helps to fill the redo logs.
The housekeeping routines that keep your server clear of old log files, archived logs etc.
RMAN's configuration on deleting archived logs after backups.

In an ideal situation, your (assumed OLTP) system would be creating a new archived log every 30 - 45 minutes. Some DBAs might wish to have less frequent while others more. So you start by the age of DBA process of "picking a number at random" and using that as the size of the redo files when you create the database.

You then run the system in as near as possible the configuration and load that it will see under production conditions. And you monitor how frequently there are new archived logs created.

If it's too fast, then set up different sized redo logs if not frequent enough, reduce the sizes.

Eventually you have comfortably sized redo logs and performance isn't too drastically affected by log switches.

Of course, this is all affected in 11g by the use of flashback recovery areas and various RECOVER parameters that determine exactly how much space your database is allowed to actually use in the FRA. Fun? Sometimes.

If your database is NEVER updated, then running in archive log mode may not be of any benefit.

You'll need to cater also for the times between backups - hot or RMAN - as you need all the archived logs from just before the backup started to just after. RMAN tends to look after this for you but a manual hot backup requires that you do it.

Have fun.

Cheers,
Norm. [TeamT]

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Build Team
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Guest · October 18, 2011, 10:26am

[ Attachment(s) from smitty_046 included below]

Message from: smitty_046

Thanks, thats great information. My log switching is all over the place. Do I
need to adjust my redo file size before I enable archieved mode? 97 switches in
an hour seems quite a bit.

Historical Messages

Author: smitty_046
Date: Tue Oct 18 08:26:14 PDT 2011

Thanks, thats great information. My log switching is all over the place. Do I
need to adjust my redo file size before I enable archieved mode? 97 switches in
an hour seems quite a bit.

__

Author: Norman Dunbar
Date: Tue Oct 18 06:42:38 PDT 2011
Hi Smitty_046, >> What’s the best method to estimate space requirements for >>
enabling archieved mode. My database is 100g in total size. >> There’s two main
tables that are 40g each. Size isn’t everything! It’s what you do with it that
counts! What I mean is this: * The size of your database has no bearing on
the size of your redo logs. * Neither does the size of your tables. * Setting
the database in archive log mode is a good thing - for a production database -
and possibly helpful on a test/development system. * Turning archive log on
means that you will be generating a copy of each “used” redo log every time that
that particular redo log fills up and is switched out of. * Each archived log
will need to be stored somewhere on your server, or possibly transferred over
the network to a standby database elsewhere. What does affect the size of your
redo logs and therefore the size of your archived logs and thus the amount of
space required on the server are as follows: * How much updating takes place -
INSERTs, UPDATEs and DELETEs all create redo information which helps to fill the
redo logs. * The housekeeping routines that keep your server clear of old log
files, archived logs etc. * RMAN’s configuration on deleting archived logs after
backups. In an ideal situation, your (assumed OLTP) system would be creating a
new archived log every 30 - 45 minutes. Some DBAs might wish to have less
frequent while others more. So you start by the age of DBA process of “picking a
number at random” and using that as the size of the redo files when you create
the database. You then run the system in as near as possible the configuration
and load that it will see under production conditions. And you monitor how
frequently there are new archived logs created. If it’s too fast, then set up
different sized redo logs if not frequent enough, reduce the sizes. Eventually
you have comfortably sized redo logs and performance isn’t too drastically
affected by log switches. Of course, this is all affected in 11g by the use of
flashback recovery areas and various RECOVER parameters that determine exactly
how much space your database is allowed to actually use in the FRA. Fun?
Sometimes. If your database is NEVER updated, then running in archive log mode
may not be of any benefit. You’ll need to cater also for the times between
backups - hot or RMAN - as you need all the archived logs from just before the
backup started to just after. RMAN tends to look after this for you but a manual
hot backup requires that you do it. Have fun. Cheers, Norm. [TeamT] Norman
Dunbar Contract Senior Oracle DBA Capgemini Database Build Team Internal : 7 28
2051 External : 0113 231 2051 Information in this message may be confidential
and may be legally privileged. If you have received this message by mistake,
please notify the sender immediately, delete it and do not copy it to anyone
else. We have checked this email and its attachments for viruses. But you should
still check any attachment before opening it. We may have to make this message
and any reply to it public if asked to under the Freedom of Information Act,
Data Protection Act or for litigation. Email messages and attachments sent to or
from any Environment Agency address may also be accessed by someone other than
the sender or recipient, for business purposes. If we have sent you information
and you wish to use it please read our terms and conditions which you can get by
calling us on 08708 506 506. Find out more about the Environment Agency at
www.environment-agency.gov.uk
__

Author: smitty_046
Date: Tue Oct 18 05:36:08 PDT 2011
Message from: smitty_046 What’s the best method to estimate space
requirements for enabling archieved mode. My database is 100g in total size.
There’s two main tables that are 40g each.
_______________________________________ Historical Messages Author: smitty_046
Date: Tue Oct 18 05:36:08 PDT 2011 What’s the best method to estimate space
requirements for enabling archieved mode. My database is 100g in total size.
There’s two main tables that are 40g each. __

__

Attachment(s) from smitty_046

1 of 1 Photo(s)

LogSwitch.jpg

smitty_046 · October 18, 2011, 10:26am

Thanks, thats great information. My log switching is all over the place. Do I need to adjust my redo file size before I enable archieved mode? 97 switches in an hour seems quite a bit.

NormTeamT · October 19, 2011, 10:24am

Hi Smitty_046,

Thanks, that's great information. My log switching is all
over the place. Do I need to adjust my redo file size before
I enable archived mode? 97 switches in an hour seems quite a bit.
Well, I've had a look at your image and to be honest, your hourly average is between 3 and 5 most hours, with the off occasional 8 or 9 or, in one case, 12.

You are therefore switching every 12 - 20 minutes excluding the glitches. Looking further and where you highlight the 97 switches in one hour, the rest of that day is pretty low really.

Looking back over all the data, on occasions where you have a higher average than usual, I see a "surge" for about 1,,2 or 3 hours - which to me implies a data load, or an import or a huge update, or similar.

I would tend to stick with it as it is at the moment, given the choice, but if I really had to make changes, I'd go for 1.5 - 2 times what you have now.

You cannot add a new logfile that is different sized by the way, you have to add new logfile groups and add the files to that. Every logfile in a group has to be the same size. So you would add in new ones, and when switched into, you can the drop the old ones.

One question, well two:

How many logfiles do you have at present?
How big?

The following will help:

select group# as log_group,
       count(*) as members,
       sum(bytes)/count(*)/1024/1024 as mb_each
from v$log
group by group#;

Remember, when in archive log mode, the database will hang if an online redo log is filled and the system cannot switch to another because the other is still archiving.

I would advise at least three groups of two logfiles, hopefully, with each member logfile on a separate disc.