Archive vs noarchive log mode

A production database's likelihood to lose data is greater in noarchive mode, although archive log mode is not a practical choice for all databases" and it has greater hardware requirements and therefore greater cost due to the cost for log storage.

A standby database environment requires archive log mode.

Noarchive log mode

If the database is in noarchive log mode, online redo logs can (and will) be overwritten without making sure they are saved. This implies that a database cannot be recovered even if backups were made.

Archive log mode

If the database is in log archive mode, the database makes sure that online redo logs are not overwritten before they have been archived.

log_archive_dest_n (or, deprecated on EE log_archive_dest ) specify where the archived redo logs go.

Backup after a structural change in the database

After a structural change to the database (such as adding a datafile), a backup is required.

Switching from noarchive to archivelog mode

The database is currently in noarchivelog mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

Of course, arch is not started. It would wouldn't make sense, after all.

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE

Now, trying to put the db in archivelog:

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted EXCLUSIVE and not open for this operation

Ok, we shut the database down und mount it only (that is we don't open it) to change the log mode. After having changed it, we open the database:

adpdb:/users/dba/oracle/dba >sqlplus "/ as sysdba"


SQL*Plus: Release 9.2.0.2.0 - Production on Tue Jul 29 19:15:42 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  104694832 bytes
Fixed Size                   730160 bytes
Variable Size              54525952 bytes
Database Buffers           49152000 bytes
Redo Buffers                 286720 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE
SQL>

Still, the arch process has to be started. This can be done via the init.ora file (and a subsequent restart):

log_archive_dest     = /backup/adpdb/logarch/adpdb_
log_archive_format   = %S.arc
log_archive_start    = true

The important parameter is log_archive_start.

Additionally, arch can be started manually:

alter system archive log start;

Differences concerning backups

No archive log	Archive log
Must backup entire database.	Can backup parts of database (datafiles tablespaces)
DB must be shut down.	hot backups possible
Only entire DB can be restored	Tablespaces can be restored
In case of a failure, all changes sinces the last backup will be lost	All commited transactions will be restorable

Thanks

Thanks to Miklos Sarkozi and Carey Patterson for correcting a few typos on the page.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger