Redo logs

well as I mentioned it depends on a few factors, one of our telco customer where I am right now, their call center application has over 6000 connections to oracle (OLTP) and we have setup 10 groups of 250MB redo logs each isolated in raw device. Tansdot if your database is small and you want a fast mean time recovery dont size your redo logs too big

Hi Pando,
I'm just curious, how often do you update your data in your DWH? Is it weekly or everyday?
If it's every day, then I just can't imagine the volume of your data coming to your DWH for you to set that much size of your redo logs.

ok, you just answer my question.
thanks.

it depends on DWH, there are few here, some loads data the first week of every month, some do it every week but weekends, some do daily incremental load etc etc. But all the load all nologging to generate as few redo as possible since all load can be reloaded if the process fails

forgive my ignorance pando...
but why use huge redo logs if your going to set your tables to nologging?

because even using nologging, the minimum redo is generated hell a lot because of the amount of data being loaded

aahh, ok I remember now. It is the rbs which is going to be bypass and not the redo logs.
It was really a damn question pando.
Thanks anyway.

Sizing of REDO logs also deterimes how close to a complete recovery you can perform. I.e. if you have a redo switch and is then archived every 15 minutes, then you can recover your db to 15 minutes prior to the db crash. Have redo logs that switch 3 hours... then that's when you can recover to.

If recovery isn't important via redologs, i.e NOARCHIVELOG mode, then having the redo files larger, rather than smaller is more preferred, I think anyway. I've seen REDO LOGS as large as 500 MB on insert/delete intensive databases, these systems are still switching every 15 minutes. The sizing of the redo logs is about Log Switching time, archiving time and recovery time.

Oh and as for NOLOGGING, we know it's a red herring, since it only minimises redo for DIRECT inserts into the tables/tablespaces. ALL NORMAL DML still generates redo.

Cheers,

[Edited by grjohnson on 10-23-2001 at 07:01 PM]

I agree, the size of the redo logs is dependent on the application plus the recovery required. At the end of the day if you require complete recovery (as little loss as possible) then a cluster running parallel server with mirrored disk sets is probably the option.

There was also a good script called logswitch.sql (from Tim Gorman (Evergreen Database Technologies, Inc.)) which displays not only how many switches per day, but a historial number. Gives you an idea of which days are the heaviest (if you didn't know that already)

Hey pando i am going to start another thread and hopefully people can describe what databases they are looking after, no names etc just size, type, worst problems etc.

Have Fun

There is also a free 60 day trial application called EZSQL which will perform the same functions by which running a query which gives you the number of log swtiches per hour over the last forntight and the numnber of changes recorded.

http://www.ezsql.net/download.htm

Cheers,