12 Managing Archived Redo Log Files

Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode.

If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archived redo log files (see "Setting Initialization Parameters for Archive Destinations").

To change the archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).

The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG:

When you operate your database in manual ARCHIVELOG mode, you must archive inactive groups of filled redo log files or your database operation can be temporarily suspended.

To operate your database in manual archiving mode:

1. Follow the procedure described in "Changing the Database Archiving Mode ", but replace the ALTER DATABASE statement with the following statement:

   ALTER DATABASE ARCHIVELOG MANUAL;
   

2. Connect to the database as a user with administrator privileges.

3. Ensure that the database is either mounted or open.

4. Use the ALTER SYSTEM statement with the ARCHIVE LOG clause to manually archive filled redo log files. For example, the following statement archives all unarchived redo log files:

   ALTER SYSTEM ARCHIVE LOG ALL;
   

When you use manual archiving mode, you cannot specify any standby databases in the archiving destinations.

Even when automatic archiving is enabled, you can use manual archiving for such actions as rearchiving an inactive group of filled redo log members to another location. In this case, it is possible for the instance to reuse the redo log group before you have finished manually archiving, and thereby overwrite the files. If this happens, the database writes an error message to the alert log.

To avoid any run-time overhead of starting additional ARCn processes:

• Set the LOG_ARCHIVE_MAX_PROCESSES initialization parameter to specify that up to 30 ARCn processes be started at instance startup.

The LOG_ARCHIVE_MAX_PROCESSES parameter is dynamic, so you can change it using the ALTER SYSTEM statement.

The following statement configures the database to start six ARCn processes upon startup:

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=6;

The statement also has an immediate effect on the currently running instance. It increases or decreases the current number of running ARCn processes to six.

Destinations can be local—within the local file system or an Oracle Automatic Storage Management (Oracle ASM) disk group—or remote (on a standby database). When you archive to multiple destinations, a copy of each filled redo log file is written to each destination. These redundant copies help ensure that archived logs are always available in the event of a failure at one of the destinations.

To archive to only a single destination:

• Specify that destination using the LOG_ARCHIVE_DEST initialization parameter.

To archive to multiple destinations:

• Choose to archive to two or more locations using the LOG_ARCHIVE_DEST_n initialization parameters, or to archive only to a primary and secondary destination using the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST initialization parameters.

For local destinations, in addition to the local file system or an Oracle ASM disk group, you can archive to the Fast Recovery Area. The database uses the Fast Recovery Area to store and automatically manage disk space for a variety of files related to backup and recovery. See Oracle Database Backup and Recovery User's Guide for details about the Fast Recovery Area.

Typically, you determine archive log destinations during database planning, and you set the initialization parameters for archive destinations during database installation. However, you can use the ALTER SYSTEM command to dynamically add or change archive destinations after your database is running. Any destination changes that you make take effect at the next log switch (automatic or manual).

The following table summarizes the archive destination alternatives, which are further described in the sections that follow.

The following example makes LOG_ARCHIVE_DEST_4 an alternate for LOG_ARCHIVE_DEST_3:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = 'LOCATION=/disk4/arch';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/arch MAX_FAILURE=1
   ALTERNATE=LOG_ARCHIVE_DEST_4';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ALTERNATE;

SQL> SELECT dest_name, status, destination FROM v$archive_dest;
 
DEST_NAME               STATUS    DESTINATION
----------------------- --------- ----------------------------------------------
LOG_ARCHIVE_DEST_1      VALID     /disk1/arch
LOG_ARCHIVE_DEST_2      VALID     /disk2/arch
LOG_ARCHIVE_DEST_3      VALID     /disk3/arch
LOG_ARCHIVE_DEST_4      ALTERNATE /disk4/arch

REOPEN=n sets the minimum number of seconds before ARCn should try to reopen a failed destination. The default value for n is 300 seconds. A value of 0 is the same as turning off the REOPEN attribute; ARCn will not attempt to archive after a failure. If you do not specify the REOPEN keyword, ARCn will never reopen a destination following an error.

You cannot use REOPEN to specify the number of attempts ARCn should make to reconnect and transfer archived logs. The REOPEN attempt either succeeds or fails.

When you specify REOPEN for an OPTIONAL destination, the database can overwrite online logs if there is an error. If you specify REOPEN for a MANDATORY destination, the database stalls the production database when it cannot successfully archive. In this situation, consider the following options:

• Archive manually to the failed destination.

• Change the destination by deferring the destination, specifying the destination as optional, or changing the service.

• Drop the destination.

When using the REOPEN keyword, note the following:

• ARCn reopens a destination only when starting an archive operation from the beginning of the log file, never during a current operation. ARCn always retries the log copy from the beginning.

• If you specified REOPEN, either with a specified time the default, ARCn checks to see whether the time of the recorded error plus the REOPEN interval is less than the current time. If it is, ARCn retries the log copy.

• The REOPEN clause successfully affects the ACTIVE=TRUE destination state. The VALID and ENABLED states are not changed.

  Something wrong here. A destination can be inactive, or valid, or disabled. There is no ACTIVE status. So I think maybe it should say, "The REOPEN clause sets the destination status to VALID" ...? DL

Several dynamic performance views contain useful information about archived redo log files, as summarized in the following table.

For example, the following query displays which redo log group requires archiving:

SELECT GROUP#, ARCHIVED
   FROM SYS.V$LOG;

GROUP#     ARC
--------   ---
       1   YES
       2   NO

To see the current archiving mode, query the V$DATABASE view:

SELECT LOG_MODE FROM SYS.V$DATABASE;

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

For example:

SQL> ARCHIVE LOG LIST

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\oradata\IDDB2\archive
Oldest online log sequence     11160
Next log sequence to archive   11163
Current log sequence           11163

This display tells you all the necessary information regarding the archived redo log settings for the current instance:

• The database is currently operating in ARCHIVELOG mode.

• Automatic archiving is enabled.

• The archived redo log destination is D:\oracle\oradata\IDDB2\archive.

• The oldest filled redo log group has a sequence number of 11160.

• The next filled redo log group to archive has a sequence number of 11163.

• The current redo log file has a sequence number of 11163.