Oracle 9i, Windows 2003

Our production database archives logs every 2mins and retaining those logs is becoming a problem, I was wondering if its healthy for a database to archive log evry 2mins and if not what do i need to do.

thanks in advance

Unless there is some recovery/ availability requirement behind generating logs this frequently, no, it's probably not a healthy thing. You probably want to resize your redo logs so that log switches happen more like every 15-30 minutes (roughly, depending on your architecture and recovery requirements) for performance reasons.

If the concern with "retaining those logs" is a disk space issue, though, archiving larger logs less frequently isn't going to help. You'd either have to buy more disk or tune whatever process(es) generate so much REDO in order to generate less REDO.

Justin

Ensure that none of your datafiles are in hot backup mode. Keeping them in hot backup mode can generate additional logs.

Thanks
Nitin

In a nutshell you achieve this by Sizing Redo Log Files.
Quote,
A rough guide is to switch logs at most once every twenty minutes.

The smaller your redo logfile is, the quicker it get filled and the frequent log switchs.

What's your current redo logfile size? For example, if you observe a log switch every 2min constantly, consider increase your redo logfiles 10 time in size.

Remember however, increase the size of redo logfiles will not reduce the amount of redo generated. It will help improve your database performance because every 2min log switch is not healthy for your system.

Disk storage shouldn't be a big concern nowadays, because all the cheap disks you can get. If you have problem retaining these archive logfiles, consider do more frequence backup and remove them from disk. Depends on what's your backup method.

Your explanation is incorrect.
Archive_lag_target ensures, you have at least 1 switch per archive_lag_target minutes. If a redolog file fills up, it still switches, whatever you set archive_lag_target at.

--
Sybrand Bakker
Senior Oracle DBA

True, but this thread is not about Standby/DG situation.

I don't see any benefit to force periodic log switch on stand alone instance.

I'll second that. All my databases have very large logs which are intended not to fill up under normal operation for ages and ages. They are actually big enough to cope with a peak load we get every week: previously, we had two or three log switches recorded under the one alert log timestamp! Now, they switch every 10 minutes or so, even under the heaviest load.

So big logs are good for slowing things own under load. But I don't want to sit there with 5 hours of redo sitting in my current log during non-peak-load normal running. Therefore, I set archive_lag_target to 1800 (seconds = 30 minutes), and I know that in the worst possible case, I will only lose 30 minutes of redo.

In short, I see LOADS of advantages for using archive_lag_target even for standalone instances. Actually ESPECIALLY for standalone instances. It allows you to have extremely large logs to cope with peaks and yet STILL have a manageable amount of redo at risk in the current log under normal running conditions. And, as Jarneil said, it gives you a nice, steady and very predictable "pulse" for the database.

Highly recommended, in short. I wouldn't run a database these days WITHOUT setting it.

I did see the link you posted earlier and it doesn't say very much, does it? Apart from

(1) big logs don't affect LGWR performance
(2) small logs will cause extra checkpoints to take place apart from those you wanted to take place by setting FAST_START_MTTR_TARGET

Well, taken together, both those points mean big logs are good news and small ones aren't. Which is exactly what I said above!

Unfortunately, you've completely missed the actual point I was making. It had nothing to do with "recovery time being a concern" nor with 100M being 100M and taking the same amount of time to apply.

It was actually point (2) above. I want logs big enough not to cause rapid log switching. But I have bulk loads. Therefore, I have to have ENORMOUS logs to prevent rapid log switching during those times. In fact, on one database I am connected to right now, I have 2GB redo logs which nevertheless manage to switch every 8 minutes on a Friday night. You can imagine the frequency of log switches we had when those logs were originally created at 5MB each! And the number of redo allocation retries...

Personally, I'd like 8GB logs to get it down to a log switch every 30 minutes or so on a Friday night, but with multiple members and groups, that's just getting silly.

But now I have an enormous log that will take forever and a day to fill up and switch when I'm NOT doing bulk loads. Ordinarily, without a forced log switch, my 2GB log takes 3 days to fill up.

If I were to have a catastrophic hardware failure, I could lose my current redo log. FAST_START_MTTR_TARGET can't do anything to ameliorate that loss: flushing the dirty buffers to disk regularly doesn't protect my data, actually. In fact, there is no way to recover transactions that are sitting in the current redo log if that log is lost. Therefore, having an enormous log full of hours and hours (in my case, about 72 hours'-worth) of redo is a massive data loss risk, and not one I'm prepared to take.

Therefore, ARCHIVE_LAG_TARGET allows me to have huge logs to deal with (2) above, but not to have more than half an hour's data at risk from total loss.

I know why the parameter was invented. I can understand the word "target" in its name, after all. But the WAY it achieves its design goals is simply to force log switches. And forcing log switches is a good thing for everyone to be able to do, when appropriate, even if they're not using Data Guard and standby databases.

You are at liberty, of course, to keep on reserving your opinion, but in this case it's ill-informed and missing the point: loss of the current log is not nice and regular log switches means the effects of that unlikely event happening are completely predictable and (importantly) containable.

That's just one advantage, not "loads". But it's a huge one.