6

The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could share?

The second half is a guess more specific to my circumstances but if anyone has any thoughts that'd be good. Currently our Master ( main writing box ) is running in archivelog mode complete with standby and table-level backups. The standby is tested twice weekly, and the backups more rarely. Not as evil as some but still not good. So, we've finally persuaded senior management to shell out for a shiny new box. It's lots better and hopefully won't blow up or fry the motherboard like the last one etc etc.

On this box we'll have hourly backups, replicated offsite down redundant fibre optics, and the backups will be tested at least daily, i.e. everything we can throw at it.

In this situation, and if the answer to the first part is positive, is it actually wise to run a DB in noarchivelog mode? If the box, or the discs die, it'll be quicker to start something up to takes it's place than it was previously and we don't mind losing an hours work if it means a significant improvement in speed.

It still seems a little bit dodgy though? I understand that we'd lose an hours work and are less worried about this than anything else we might have missed in our analysis of the implications of not having a standby.

Improve this question
3
  • What edition are you running? Also are you getting rid of the standby? Otherwise you'll need the archived log files for the standby.
    – Jack Douglas
    Commented Jan 25, 2012 at 9:40
  • @JackDouglas For the new box there's no specific "tool" in that sense of the word we're using for the backups, it's running on ZFS so we can just "snapshot" and entire DB at a point in time. We've tested this and it's working. The exact edition will be 11.2.0.1.0
    – Ben
    Commented Jan 25, 2012 at 11:18
  • edition<>version :-) I wanted to know if you had Enterprise Edition for change tracking. I'll update my answer.
    – Jack Douglas
    Commented Jan 25, 2012 at 11:41

1 Answer 1

Reset to default
6

The normal answer is: "Use archivelog mode". Unless you are taking cold backups you will not be able to recover from them without the log files archived during the backup.

If you are taking hourly cold backups and are running in noarchivelog mode, you risk losing an hours worth of committed transactions (which you don't mind). The purpose of archiving the logs is so that you can recover all (or nearly all) of the committed transactions after a media failure.

Whichever mode you choose Oracle still writes logs to the online redo log files - in archivelog mode you enable the archiving of those files as they fill up.

Performance benefits will not normally be significant - here is a list of special cases where there is a difference in redo generation.

Seeing as your 'backups' are ZFS snapshots, you do not need log files for restoring the database - reverting to a snapshot will be similar to recovery after a power loss and will use the snapshotted online redo logs to roll forward to a consistent state. In that specific case, and seeing that you do not mind losing recent data, noarchivelog mode may be acceptable to you.

Not having the standby will still increase your recovery time in the case of hardware failure on the primary however - you may wish to consider that.

Improve this answer
4
  • Yes @Jack, they're incremental and we don't mind losing an hours work IF turning off archivelogging results in a speed improvement of say 5% plus. We'll have to use the backups so rarely and so much of the DB is run off driver tables that we would actually lose very little. But I don't know if in this analysis we've missed anything other than just losing an hours work.
    – Ben
    Commented Jan 25, 2012 at 9:29
  • What tool are you using to export the data for your backups?
    – Jack Douglas
    Commented Jan 25, 2012 at 9:41
  • Thank you for all this. To summarise there aren't any performance benefits unless we run all DML as direct path? So unless we do this, we might just as well keep the standby just in case?
    – Ben
    Commented Jan 25, 2012 at 12:44
  • Yes, except the only other consideration is that you don't have a bunch of archived log files building up that you presumably have to delete if you aren't using them?
    – Jack Douglas
    Commented Jan 25, 2012 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.