redo log file size

[qingbo]

When people talk about redo log size, they always recommend making it large enough to switch every 30 minutes or 15 minutes. The reason is that if it is too small, it increase I/O load by issuing more checkpoints and push ARCH process to do more work. However, how about redo log file size too big? Suppose you got a log file of 1G and it switches twice a day. What are the risks you face here? My understanding is that if you do a shutdown abort and then bring the database up, the instance recovery time will increase significantly. But what if you make it checkpoint every 15 minutes (set log_checkpoint_timeout to 900, for example). (1) Will you experience the same amount of time during instance recovery? (2) If you lost your current log file group, did you lose 15 minutes of transactions or 1G of transactions?

[jmodic]

If you check point every 15 minutes your instance recovery will be fast, it will have to roll forward 15 minutes worth of transactions at most, so in efect the same as if you would have smaler redo logs that can store 15 minutes of transactions.

If you loose your current log file group (1GB) you will loose all transactions recorded in that redo, regardles of the checkpoint frequency, as you have to peform media recovery up to the lost redo log in this case.

BTW, your question "If you lost your current log file group, did you lose 15 minutes of transactions or 1G of transactions?" is a little dubious. You can(theoretically) lost any amount of transaction data, regardles of your redo log file size and checkpoint frequency. Suppose you have a realy huge transaction that spans 10 of your redo logs. Soon after it is commited and before the next log switch occurs your database crashes and you loose the current log grup containing the COMMIT of this huge transaction. After you perform incomplete media recovery your whole huge transaction will be rolled back, so you'll loose 10 GB worth of transaction.

HTH,