Re: archivelog v/s nonarchivelog

Hari Om wrote:
> what is the major decision factor in deciding whether DB should be in
> Archivelog mode or NonArchivelog mode?

ARCHIVELOG mode allows you to roll forward your latest backup to almost the very latest state of the database, *even in the case of complete media failure* (i.e. you've lost all your datafiles and redo log files, but you still have the backed up datafiles, backed up control files and archived redo log files.) You would only consider operating in NOARCHIVELOG mode if your data is not important, e.g. if it can be re-created easily, or if your backup schedule is frequent enough that you are satisfied with recovering only to the latest backup date in case of a complete media failure.

> What are tradeoffs for each of
> the above modes?

Your individual redo log files are re-used by Oracle in a circular manner. In the event of a log switch (i.e. when the current log file is full and Oracle needs to use/re-use the next log file), if you're in ARCHIVELOG mode, there's one or more background processes called ARCn which back up the previous log file, before it's later on re-used. The only performance tradeoff is the I/O consumed by these ARCn processes in backing up the previous redo log file.

>
> Upto what point after (crash or media failure) can I recover my DB in
> either of the modes?
>
> Example: Your last backup was 09:00 am and then DB crashes at 06:00pm
> (same day). Taking this example into consideration, upto what point
> can I restore my DB?

In case of crash (instance) failure, assuming you still have the datafiles, control files and redo log files intact, you can recover the database to the latest state (all committed data is intact, and all uncommitted transactions are rolled back) regardless of whether you had operated in ARCHIVELOG or NOARCHIVELOG mode.

In case of complete media failure and ARCHIVELOG mode, you can recover the database to the last log switch, assuming the previous redo log file has finished being archived before the crash. How close this is to 06:00pm depends on how much traffic you have right before the crash, and how large your redo log files are.

In case of complete media failure and NOARCHIVELOG mode, you can only recover your database to 09:00am.

Cheers,
Dave

>
> Any related information on this is appreciated
Received on Wed Aug 06 2003 - 16:47:11 CDT