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

You have enough space left in the archive destination directory. If this directory is full, the database will hang and no one can connect to the database. A DBA can connect to the database as internal user. If this happens, the easiest thing to do is to move some of the archived log files to tape or a different location.
You copy the archived redo files to a tape or backup storage media at least twice a day. After copying, the files can be removed from the disk (archive destination directory). You may set up a cron job which compresses the archive log files, copy them to the backup storage media and delete the files.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.