ARCHIVELOG Mode

Let's discuss archiving... Archiving in simple words mean copying the redo log files to a different location by the ARCH process.

Basics - Redo Log Files

The (online) redo log files are used by Oracle to record changes made to the database during normal operation. These files are used by Oracle during recovery to reapply the changes made to the database when such changes are not permanently written to the data files at the time of failure. Oracle needs minimum of two log file groups, each group with at least one log file member. Oracle recommends you have more than one member for each group so that the files are mirrored and the chances of failure are minimal.

Database Operation - Archiver Modes

The Oracle database can be configured to run in ARCHIVELOG mode or NOARCHIVELOG mode. The former enables hot backup (backup files while the database is up) and point in time recovery from media failure. Oracle writes to the redo log files in both the modes of operation. In NOARCHIVELOG mode, the online redo log files are overwritten each time a log file is filled and a log switch occurs. In ARCHIVELOG mode, the redo log files are saved before they are overwritten by the LGWR process. If a media failure occurs, in NOARCHIVELOG mode, you have to restore the database from the latest cold backup; in ARCHIVELOG mode you can restore the damaged files from the latest backup and apply all changes made to the database from archived redo logs.

How to enable ARCHIVELOG?

The following 4 steps will enable ARCHIVELOG in a database.

1. Change the initSIDNAME.ora file to include the parameters

LOG_ARCHIVE_START = TRUE

Setting this parameter to TRUE enable automatic archiving, i.e., every time the LGWR process switches log file, the ARCH process archives the online redo log files automatically. If this parameter is not set to TRUE, the DBA has to manually enable archiving the redo log files by using the command archive log start in server manager (svrmgrl)

LOG_ARCHIVE_DEST = /directory_name

The destination directory where the archived redo log files are written. Make sure this directory is created and oracle owns/can write to it.

LOG_ARCHIVE_FORMAT = arch_%s

The format of the archived redo log file. Here the file name start with arch and %s is the log sequence number.

2. Shutdown the database.

$ svrmgrl

SVRMGR> connect internal

SVRMGR> shutdown

3. Mount the database

SVRMGR> startup mount

4. Issue command to turn on Archiving and open the database.

SVRMGR> alter database archivelog;

SVRMGR> alter databse open;

To verify archiving and the destination directory

SVRMGR> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /destination_directory

Oldest online log sequence 7664

Next log sequence to archive 7666

Current log sequence 7666

SVRMGR>

To verify the files are written to the directory and in the desired format

SVRMGR> alter system switch logfile;

Go to the directory_name specified in LOG_ARCHIVE_DEST and see the log file written.

Make sure you get a cold backup after you turn on archiving.

While running the database in ARCHIVELOG mode, always make sure