Re: performance impact of archivelog

There shouldn't be any mystery about choosing "to be ARCHIVELOG, or not to be ARCHIVELOG".

If the database can be rebuilt and reloaded within a time period and to a data state acceptable to its user community, then NOARCHIVELOG mode is OK.  However, this isn't some kind of "free pass" for DBAs.  Ethically and practically, they have to *prove* themselves able to rebuild and reload to the desired point-in-time within the specified amount of time, and that just ain't easy -- it takes more preparation, practice, and work to rebuild/reload than it takes to restore/recover.  Rather the contrary -- the procedures for rebuild/reload have to be designed and built from scratch, tested, and maintained over time; the procedures for restore/recover are standard and largely automated already.

Besides the level of effort attending rebuild/reload, the important point to note is that the decision to rebuild/reload implies that the database in question is not the "source of record" for the data it contains.  This is why it is usually only an option for data warehouse databases, and almost never an option for operational environments.

So, if a database contains data that can't be sourced anywhere, or can't be derived from data available anywhere else, then it is a "source of record".  This is not a static designation -- a kind of "scope creep" can occur within data warehouses previously considered rebuildable and reloadable.  Progressive changes to data processing within a DW might cause new data to appear, suddenly making the data warehouse database a "source of record" in its own right.

<WAR_STORY_ALERT>
Back in the 1990s, I worked on a couple data warehouses that were either too large for the available storage infrastructure to backup, or too busy for any tape subsystem to keep up with.  In particular, I recall one data warehouse on a 4-node OPS cluster based on HP-UX which generated anywhere from 2-3 Tb of archived redo log files per day, from a database that was about 6 Tb of datafiles.  The workload of 2-3 Tb of redo logs throughout the day, plus datafile backups, plus the other systems using the not-inconsiderable backup infrastructure, was overwhelming.  So, that monster ran in NOARCHIVELOG mode because there wasn't any choice, giving all of its DBAs premature gray hair and baldness (even the women).  I know that it too contained data that was considered "source of record", because I remember them having to pull a rabbit out of their .... ummm ... hat ... when a "power-user" left the company and had his account subsequently (and routinely) removed.  Suddenly, the rest of the company valued the data in the deleted account at some obscene dollar figure worth more than the IT personnel and data centers put together.  I suspect that the DBAs on that DW have recognized themselves and the events in question, and I'll soon be getting back in touch with them -- at least I hope so!
</WAR_STORY_ALERT>

Anyway, if a database is a "source of record" for any data, then it is almost certainly a requirement that it be recovered to the point-in-time of any failure, and that requires ARCHIVELOG mode, which is really a lot less effort to manage (than rebuild/reload) anyway.

Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt


David Ballester wrote: 
El lun, 17-11-2008 a las 14:44 -0800, Greg Rahn escribió:

  
And I personally would never run a production database in noarchive
log mode.  Never.

    
 
Hi Greg:

I can understand your opinion about the archive log mode on production
databases, but in very special situations - for example a datawarehouse
with 20 TB of data aprox. renewing a lot of data each hour and 24X7, is
very difficult to maintain a backup in the Oracle standard mode ( hot
backup with rman with archive log mode on ). No window to backup all
data, the nologging inserts making a lot of unrecoverable points... we
are talking about tablespaces of 360GB, who can backup it at reasonable
speed? - I think that in very special cases - other example that comes
to my mind, a instance used as application cache or very volatile data -
the database could be in noarchivelog mode but after a carefully study,
of course. I'm with you, but I say 'For the 99,9% of production
databases I would never run it in noarchive log mode' :)

Best regards


D.

--
http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-l

Other related posts: