Re: performance impact of archivelog
From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 18 Nov 2008 08:27:21 -0700
Message-ID: <4922DED9.90106@evdbt.com>
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...
David Ballester wrote:
Date: Tue, 18 Nov 2008 08:27:21 -0700
Message-ID: <4922DED9.90106@evdbt.com>
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@EvDBT.com mobile = +1-303-885-4526 fax = +1-303-484-3608 Yahoo IM = tim_evdbt
David Ballester wrote:
-- http://www.freelists.org/webpage/oracle-l Received on Tue Nov 18 2008 - 09:27:21 CSTEl 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