Note 863417 - FAQ: Database Archive modes and redo logs

Summary Symptom 1. What is the meaning of NOARCHIVE or ARCHIVE mode? 2. What is a redo log file? 3. Which are the benefits of ARCHIVELOG mode? 4. Which are the benefits of NOARCHIVELOG mode? 5. What is the extra administrative work mentioned? 6. What if the archive log files are not saved? 7. How do I know the mode my database is running? 8. How can I change to ARCHIVELOG mode? Other terms noarchivelog Solution 1. What is the meaning of NOARCHIVE or ARCHIVE mode?               The Oracle database can run in one of two modes: NOARCHIVELOG mode and ARCHIVELOG mode.               On ARCHIVELOG mode the filled online redo log files are archived before they are reused.               On NOARCHIVELOG mode the filled online redo log files are NOT archived. 2. What is a redo log file?               The redo log is a set of files that protect modified data in memory that has not been written to the datafiles. The redo log can consist of two parts: the online redo log and the archived redo log. The online redo log is a set of two or more online redo log files that record all changes made to the database, including both uncommitted and committed changes. Redo entries are temporarily stored in redo log buffers of the system global area, and then they are written sequentially to an online redo log file. See note 793113 for an indication on where to place this files. 3. Which are the benefits of ARCHIVELOG mode?               In ARCHIVELOG mode, the database can be completely recovered from both instance and media failure using the ( archived ) redo log files. You can also perform an incomplete recovery of the database. The database can be backed up while it is open and available for use. However, additional administrative operations are required to maintain the archived redo log. 4. Which are the benefits of NOARCHIVELOG mode?               Under very specific circunstances archivelog might be benefitial( Euro changeover, client copies, reorganizations ... ) because no archived redo log is created, no extra work is required by the database administrator. A backup must be taken before and after this operations are performed.               SAP does NOT support the NOARCHIVELOG mode during normal production operation.               If the database operates in NOARCHIVELOG mode, the database can ONLY be completely recovered from instance failure but not from media failure. This means that, in case of media failure you will lose all work done after the last valid offline backup. The database can be backed up only while it is completely closed. 5. What is the extra administrative work mentioned?               In order to be able to perform a complete or point in time recovery ALL archive log files must be available, therefore it is necessary to back up those files. Please check note 540434 for the absolutly necessary activities. 6. What if the archive log files are not saved?               You can run into several problems: Archive stuck situation. If The filesystem where the log files gets full no more redo logs can be archived. To avoid data loss Oracle does not allow the overwriting of an online redo log that is not archived and the database will stop until the situation is resolved ( for more information see note 391 ) You will lose data in case of media failure. If you perform only offline backups you are on the same situation as running in NOARCHIVELOG mode. If you perform online backups, they need the archive redo log files in order to be properly recovered. 7. How do I know the mode my database is running?               There are several options: Executing brspace without parameters will indicate the Archivelog mode. From sqlplus you can check the view V$DATABASE with the command                     SELECT LOG_MODE FROM V$DATABASE;            or with                     ARCHIVE LOG LIST 8. How can I change to ARCHIVELOG mode?               There are several oracle parameters that you have to set before changing to ARCHIVELOG mode: LOG_ARCHIVE_START. Setting this parameter to TRUE enables automatic archiving of filled redo groups each time an instance is started. If it is not set to TRUE then you have to MANUALLY archive your redo log files when in ARCHIVELOG mode. In oracle 10g this parameter is deprecated. Logs are archived automatically if you are in archivelog mode. LOG_ARCHIVE_DEST. This parameter specifies the directory  where your archive logs will be placed. In the SAP environment there are two possible locations                     /oracle/<SID>/saparch ( old releases )                     /oracle/<SID>/oraarch ( newer releases )                     The typical value is for this parameter is:                     log_archive_dest = <directory>/<file prefix>                     Where <file prefix> is <ORACLE_SID>arch. Note that this is the prefix for the offline redo log file names, not a subdirectory name. LOG_ARCHIVE_FORMAT. This parameter names the archive logs in this format.               After setting the mentioned parameters you have to follow this steps:            Shutdown the database            Backup the database. This backup can be used with the archive logs that you will generate.            Check that the LOG_ARCHIVE_DEST exist and has enough free space.            Startup mount the database ( do not open )            Switch the database's archiving mode with the command                     ALTER DATABASE ARCHIVELOG;            Open the database.            Check that the database is in ARCHIVELOG mode with the commands previosly seen.            Add the archived logs to your backup strategy.               You can also use BRTOOLS to change from NOARCHIVELOG to ARCHIVELOG mode. Header Data Release Status: Released for Customer Released on: 20.02.2007  12:39:17 Master Language: English Priority: Recommendations/additional info Category: FAQ Primary Component: BC-DB-ORA Oracle Secondary Components: BC-DB-ORA-DBA Database Administration with Oracle Affected Releases Release-Independent Related Notes 793113 - FAQ: Oracle I/O configuration 592393 - FAQ: Oracle 540434 - FAQ: Crucial Information for Oracle DB Administration 23070 - Backup and Recovery: Basic Concepts 391 - Archiver stuck