Home » RDBMS Server » Backup & Recovery » Redo log sizing
Redo log sizing [message #144478] Wed, 26 October 2005 13:01 Go to next message
eric777
Messages: 5
Registered: October 2005
Junior Member
Our redo logs currently only 3 and 20M each.
Daily activity averages 2-3 Gb but redo logs rarely
switch more than 4 times per hour even at peak times.
Occasionally - maybe due to data loads they switch a lot
but over the past 86 days looking at stats for each hour
during peak times only they switched over 4 times on 46 occasions
out of 1118 representing less than 5%.

However, they have switched on occasions over 200 times in one hour
but it is rare.

Question is firstly do I need to resize redo logs for maximum activity

Secondly we are planning to move to archive logging and I need to know the following ...

Is it better if we can continue to have nightly cold backups to do this rather than having hot backups as we do not operate
on a 24-7 basis.
for sizing archivelogs - for the above scenario - we only need to keep archive logs for a day ?
Is it best to try and size online redo logs to last a day so that we may not need to use archive logs unless we have
a media hit on both sets of online redo logs ?

There seems to be a lot of info about archived logging but not much on sizing
and things to be careful about when moving from noarchivelog to archivelog mode.

for example if you allow for a XGb of archive log space on disk and only want to keep it for a day how do you
ensure the space is reused after backing up so that the disk space does not continue to be used up until full and end up hanging the database ?


Re: Redo log sizing [message #144522 is a reply to message #144478] Wed, 26 October 2005 20:49 Go to previous messageGo to next message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
The rule of thumb is to have log switches around every 20 minutes or so. 4 times an hour is fine. 200 times in one hour could well be killing your applications when it does happen - if it happens out of working hours then you may be able to put up with it.

Once you're in archivelog mode, the amount of archived redo you retain depends on how much space you can afford and your backup policy. If you take a nightly backup and delete all the archive logs that you just backed up, then you're relying on that tape being readable to recover your database.

I generally take nightly full backups, including archive logs - but still hold at least a week's worth of archive log on disk. That way, if anything needs recovered, I can use any tape from the last week. I would also make sure that your archive log destination is a physically different device from any of the database files - otherwise you risk the loss of a disk taking out data and the log files you need to recover that data.

Dougie McGibbon
Re: Redo log sizing [message #144601 is a reply to message #144522] Thu, 27 October 2005 06:34 Go to previous messageGo to next message
eric777
Messages: 5
Registered: October 2005
Junior Member
Thanks.

If you know of a large dataload which is going to hit your redo logs unecessarily - can you safely turn off archiving for a while and then turn it on again ?

Secondly - if I increase the size of the redo logs so to avoid the occasional heavy hits on them, is there not a danger that the daily impact will be that they will take longer to switch as they are larger and at those times there will be more of an impact on performance ?

Is it a good idea if disk space and redo generation is within manageable limits - ie a few Gb per day - to size the redo logs and numbers of them to contain a day's worth of data.

Also for the archive logs - do you recommend writing them to tape from disk on a more frequent basis than daily - say hourly, so that in the worst case, you only lose an hour's worth of transactions if your redo and archived logs are lost ?

Re: Redo log sizing [message #144699 is a reply to message #144601] Thu, 27 October 2005 13:08 Go to previous message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
Quote:



If you know of a large dataload which is going to hit your redo logs unecessarily - can you safely turn off archiving for a while and then turn it on again ?


You can do, but this breaks the sequence for recovery. You won't be able to recover past the time when you turned them off. Unless it's causing you real problems - leave archiving on.

Quote:


Secondly - if I increase the size of the redo logs so to avoid the occasional heavy hits on them, is there not a danger that the daily impact will be that they will take longer to switch as they are larger and at those times there will be more of an impact on performance ?


Log switches don't take long - it's when you get one every 20 seconds you'll run into problems. The archiving of the redo log may take a while - but this shouldn't affect the DB performance as much as excessive log switching would.

Quote:


Is it a good idea if disk space and redo generation is within manageable limits - ie a few Gb per day - to size the redo logs and numbers of them to contain a day's worth of data.

Also for the archive logs - do you recommend writing them to tape from disk on a more frequent basis than daily - say hourly, so that in the worst case, you only lose an hour's worth of transactions if your redo and archived logs are lost ?


It all depends on what your recovery policy states. How much data can you afford to loose ? If the answer is none, then set up duplicate destinations so that you have more than one copy of the archived logs. Disk space is cheap nowadays, even if you are generating GB's of log a day, you can still afford to store a few days on line - and they compress quite well too.

Dougie McGibbon
Previous Topic: RMAN incremental
Next Topic: remove RMAN contents
Goto Forum:
  


Current Time: Fri Mar 29 06:59:24 CDT 2024