11g R2 – Managing Archived Redo Logs

Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Managing Archived Redo Logs

The Oracle Database lets you save Redo Log files that have become full, to one or more offline or archived destinations.  These archived destinations are known as the archived log files.  The redo logs are archived to the archived log files, before a log switch completes.  Archiving is only possible if the database is running in ARCHIVELOG mode.  You can choose to run in AUTOMATIC or MANUAL archiving mode.

An archived redo log file is a copy of one of the members of the filled redo log groups.  The archived log file includes the redo entries, and the unique log sequence number, of the identical member of the redo log group.  The archived redo log files contains a copy of every redo group created since you enabled archiving.

In ARCHIVELOG mode the LGWR cannot reuse or overwrite a redo log file, until it has been archived.  The background process ARCn automates the archiving of redo log files, when automatic archiving is enabled.  The Oracle Database will start multiple copies of the ARCn process as needed to ensure that the archiving of filled redo log files does not fall behind.

Redo log files can be used to:

·         Recover a database

·         Update a standby database

·         Get information about the history of the database, using the LogMiner utility

The choice as to whether to run in ARCHIVELOG mode or NOARCHIVELOG mode, comes down to this.  If you can afford to lose your database with no loss to business, then you can run in NOARCHIVELOG mode.  If the full or partial loss of your database, on a test system represents, hundreds or thousands of man-hours of  development, then you can’t afford to lose your test database, and you have to run the database in ACHIVELOG mode.

NOARCHIVELOG mode

In this mode you have disabled the archiving of the redo log files.  The control file indicates that it is not necessary to archive your redo logs, in order to carry out a log switch.  As soon as a log group becomes available after a log switch, it is available for LGWR to write to.

The redo logs in NOARCHIVELOG mode protect the database from instance failure.  However there is no protection from media failure.  Also only the most recent changes made to the database are available for instance recovery in the redo log files.  If you do experience media failure in NOARCHIVELOG mode, then you can restore the database to the last, valid, and full database backup.  You cannot recover transactions subsequent to that backup.  With no backups in NOARCHIVELOG mode, you stand to lose a partial database, or the entire database.

In NOARCHIVELOG mode, you cannot perform online tablespace backups, nor can you restore online tablespace backups taken from the database; while it was in ARCHIVELOG mode.  The only way to restore a database in NOARCHIVELOG mode is to restore from a full database backup, that was taken from the database, while it was closed, or in some cases mounted.  So your only protection against database loss, is regular full database backups, that have been manually verified.  In the least the verification can be done by checking for errors in the backup log file.  These backup log files, should be stored with the backup files, so that you can establish whether the backup was successful when it was run, and also what was included in the backup files.   

ARHIVELOG mode

In ARCHIVELOG mode, the archiving of the redo log files is enabled.  The control file indicates that a redo log group or file, cannot be reused, until it is archived.

There are advantages to archiving redo log files:

·         A database backup, combined with your redo logs files and archived redo log files, will give you the tools to recover the database, after an Operating system or disk failure.  You can potentially recover all of the committed transactions in such a situation.

·         In ARCHIVELOG mode you can take a backup while the database is open, and during normal operations.

·         You can keep a standby database current with its primary database, by continuously applying the primary databases archived redo log files to the standby database.

You can configure an instance to archive redo log files automatically or manually.  Automatically is convenient and efficient, and the preferred mode.

If all the databases in a distributed database operate in ARCHIVELOG mode, then you can perform coordinated distributed database recovery.  Otherwise the recovery of a distributed database configuration is limited to the last full backup of any database in NOARCHIVELOG mode.

Redo log files and archived redo log files should ideally be on different disk drives, or in the least file systems.  It is also good practice to move copies of the archived log files and full database backups to a permanent offline media, such as tapes.

Setting the initial database archiving mode

The initial archiving mode is set with the CREATE DATABASE statement, with the ARCHIVELOG or NOARCHIVELOG clause.  If you specify ARCHIVELOG mode, then you should have initialization parameters set to control the destinations of the archived redo log files.

Changing the Database Archiving mode

To change the archiving mode, you must be connected to the database as SYSDBA.  To change from NOARCHIVELOG mode to ARCHIVELOG mode:

·         Shut the database down.  All the associated instances must also be shut down.  You cannot change the mode from ARCHIVELOG mode to NOARCHIVELOG mode if there are any data files that need media recovery.

·         Back up the database.  This is your last cold backup of the database in NOARCHIVELOG mode, this backup is very important, because we will recover from this backup if something goes wrong during the change to ARCHIVELOG mode.

·         Edit the initialization parameters to  include the parameters that specify the destinations for the archived log files.

·         Start the instance, and mount it.  To enable or disable archiving, the database must be mounted, but not open.

·         Change the archiving mode, and then open the database for normal database access.

SQL> alter database archivelog;

SQL> alter database open;

·         Shut down the database.

SQL> shutdown immediate;

·         Back up the database.  Any previous backups taken in NOARCHIVELOG mode, are no longer valid.  Changing the mode updates the control file, so all the data files and control files need to be backed up.

Manual Archiving

Although automatic archiving is the preferred method, it is possible to run your database in manual archive mode.  Automatic is by default, but manual has to be specified.

To operate your database in manual archive mode, you follow all the steps are above.  When you get to the “alter database archivelog”, you just add the “manual” keyword.  The rest of the process is the same.

SQL> alter database archivelog manual;

When your database is operating in manual archive mode, you need to manually archive inactive groups of filled redo log files.  If you don’t do this, then your database operation can be temporarily suspended, until you run te commands to archive the filled redo log files.  You can manually archive the redo log files when the database is in MOUNTED mode, or in OPEN mode.  This statement will archive all unarchived log files:

SQL> alter system archive log all;

In manual archiving mode, you can’t specify any standby databases in the archiving destinations.

Even when automatic archiving is enabled, you can still use manual archiving.  You can manually re-archive an inactive redo log group to another location.  It is possible for the database to overwrite the redo log group that you are rewriting, before the process has finished.  This will render your re-archiving action invalid.  In a case like this an explanatory message will be written to the alert log.  

LOG_ARCHIVE_MAX_PROCESSES

The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the number of ARCn processes that the database initially starts.  The default if not set is 4 processes.  There is usually no need to redefine this parameter, because the Oracle Database will define additional ARCn processes as needed, to ensure that the automatic processing of filled redo log files does not fall behind.

However to avoid any run time overhead that could be incurred by starting additional ARCn processes, you can set this initialization parameter up to a value of 30.  The LOG_ARCHIVE_MAX_PROCESSES initialization parameter is dynamic, and has immediate effect once the statement is issued.

SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=6 scope =both;

In this case the current number of running ARCn processes, will be immediately adjusted upwards or downwards to 6.

Setting initialization parameters for Archive destinations

Redo log files can be archived to a single destination, or to multiple destinations.  Destinations can be on the local file system.  Or in an ASM disk group, or to a remote destination, like the file system on a Standby database.  When you archive to multiple destinations, then a copy of each full redo log file is written to each of the defined destinations.  These redundant copies, ensures that archive logs are available in the event of a failure, at one of the destinations.

To archive to only a single destination, you can use the LOG_ARCHIVE_DEST parameter, although it is better to use the LOG_ARCHIVE_DEST_1 parameter.  To archive to multiple destinations you can use the LOG_ARCHIVE_DEST_n parameters.  To archive to two destinations it is possible to archive to the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters, although it is still better to use the LOG_ARCHIVE_DEST_n parameters.

For local destinations, you can also archive to the Fast Recovery Area; besides the local file system, or a Oracle ASM disk group.  The Oracle Database uses the Fast Recovery Area to automatically manage disk space for a number of files related to backup and recovery.

You would have set the archive destinations during the planning and creation of the database.  You can dynamically add or change archive log file destinations after the database is running using the ALTER SYSTEM statement.  All the archive log file destination changes that you make, will come into effect at the next log switch, regardless of whether it is automatic or manual.

Archive destination alternatives:

Initialization parameter	Host	Example
LOG_ARCHIVE_DEST_n n = interger from 1 to 31.  Archive destinations 1 to 10 are available for local or remote locations.  Archive destinations 11 to 31 are available for remote locations only.	Local or remote	LOG_ARCIVE_DEST_1 = ‘LOCATION=/u01/test/arc’ LOG_ARCIVE_DEST_5 = ‘SERVICE=StdbyDB’
LOG_ARCHIVE_DEST, LOG_ARCHIVE_DUPLEX_DEST	Local only	LOG_ARCIVE_DEST=’/u01/arc’ LOG_ARCIVE_DUPLEX_DEST=’/u05/arc’

  

Keywords that you can use with LOG_ARCIVE_DEST_n

Keyword	Indicates	Example
LOCATION	A local file system or an Oracle ASM disk group.	LOG_ARCHIVE_DEST_n=’LOCATION=/u01/arc’ LOG_ARCHIVE_DEST_n=’LOCATION=+DGROUP1’
LOCATION	The Fast Recovery Area	LOG_ARCHIVE_DEST_n= ‘LOCATION=USE_DB_RECOVERY_FILE_DEST’
SERVICE	Remote archival through Oracle Net Service Name.	LOG_ARCHIVE_DEST_n=’SERVICE=stdby01’

When you use the LOCATION keyword, then you should also specify one of the following:

·         A valid path name in your local operating Systems  file system

·         An Oracle ASM disk group

·         The keyword USE_DB_RECOVERY_FILE_DEST, to indicate the fast recovery area

If you specify the SERVICE keyword, then supply a Net service name that Oracle Net can resolve to a connect descriptor for a standby database.

When using the LOG_ARCHIVE_DEST_n initialization parameter.

·         Specify from 1 to 31 archiving locations.  If you are archiving to a standby database, then use the SERVICE keyword to specify a valid net service name.

·         Optionally set the LOG_ARCHIVE_FORMAT initialization parameter using %t to indicate the thread number, as part of the file name.  %s to include the log sequence number.  %r to indicate the resetlogs ID.  Use capital letters to pad the file name to the left with zeroes. 

If the COMPATIBLE initialization parameter is set 10.0.0 or higher, then the database requires the specification of resetlogs ID (%r).  When you include the LOG_ARCHIVE_FORMAT parameter.  The default for this parameter is Operating system dependent.  On UNIX the default format is:

LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

The incarnation of a database changes when you open it with the RESETLOGS option.  Specifying %r causes the database to capture the resetlogs ID in the archived redo log file name.

Example of LOG_ARCHIVE_FORMAT:

LOG_ARCHIVE_FORMAT=arch_%t_%s_%r.arc

/u01/test/arc/arch_1_200_879877987.arc

Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST, you can specify a maximum of two locations.  You can specify a primary location and an optional secondary location.  All destinations must be local.  Whenever the database archives a redo log, it archives to all the destinations that are defined.

If you configure a Fast Recovery Area, by setting the DB_RECOVERY_FILE_DEST AND DB_RECOVERY_FILE_DEST_SIZE , and do not specify any local archived destinations, then the Oracle Database automatically selects the Fast Recovery Area as a local archive destination and sets LOG_ARCHIVE_DEST_1 to use DB_RECOVERY_FILE_DEST.

You must ensure that there is enough space at all times for the archive log destinations.  If the database encounters a disk full errors, as it attempts to archive a log file, a fatal error occurs, and the database stops responding.  Check the alert log for the disk full messages.

Understanding Archive Destination Status

Each archive destination has a set of variables associated with it that determine its status:

·         VALID/INVALID: This indicates whether the disk location or service name information is specified and valid.

·         ENABLED/DISABLED: This indicates if the database can use the location, and if the location is available.

·         ACTIVE/INACTIVE: This will indicate if there was a problem while accessing the destination.

To get information about a destination, query v$archive_dest where dest_name = ‘LOG_ARCHIVE_DEST_1’; for example.

SQL>  select dest_name, status, destination, process, valid_now

from v$archive_dest

where dest_name='LOG_ARCHIVE_DEST_1';

You can control the availability state of the archive destination parameters by setting this set of initialization parameters:  LOG_ARCHIVE_DEST_STATE_n (n is from 1 to 31).

·         ENABLE: This indicates that the database can use the destination.

·         DEFER: Indicates that the destination is not currently enabled or is temporary disabled.

·         ALTERNATE: This indicates that the destination is an alternative destination.  The availability state of an ALTERNATIVE destination will default to DEFER.  If its parent destination fails, then the status of the ALTERNATIVE destination will change to ENABLE.  ALTERNATE can’t be specified for LOG_ARCHIVE_STATE_DEST_11 through LOG_ARCHIVE_STATE_DEST_31.

Specifying Alternate Destinations

You can specify that an archive destination is an alternate archive destination, in the event of a failure of another defined archive destination.  Both local and remote destinations can be alternate destinations.

Example:

SQL> alter system set LOG_ARCHIVE_DEST_6 = ‘LOCATION=/u06/arch’;

SQL> alter system set LOG_ARCHIVE_DEST_4 =

         ‘LOCATION=/u04/arch ALTERNATE= LOG_ARCHIVE_DEST_6’;

SQL> alter system set set LOG_ARCHIVE_DEST_6 = ALTERNATE;

LOG_ARCHIVE_DEST_4 will have the status of VALID, and LOG_ARCHIVE_DEST_6 will have the status of ALTERNATE.

Log Transmission Modes.

There are two modes of transporting archived logs to their destination.

·         Normal Transmission mode:  Transmitting archived redo log files to a local disk.  Archiving should not contend with other files required by the instance, and should be quite efficient.  You can specify the destination with LOG_ARCHIVE_DEST_n or LOG_ARCHIVE_DEST initialization parameters.

·         Standby Transmission mode:  Typically to a remote standby database, but can be to a local standby database.  Standby transmission involves transmitting the files through a network to the standby database.

Sometimes archived log destinations can fail, which is especially problematic if you are running in automatic archiving mode, which is the default.  There are ways to minimize the impact of issues associated archive log destination failure.

Specifying Mandatory and Optional Destinations

There are two initialization parameters, that effect this configuration.  LOG_ARCHIVE_DEST_n, and LOG_ARCHIVE_MIN_SUCCEED_DEST=n.  There is a set of rules that applies after a destination failure, to determine if LGWR, can go ahead and overwrite a redo log file.

·         Omitting the MANDATORY attribute with the definition of LOG_ARCHIVE_DEST_n, means that the value defaults to OPTIONAL.

·         There must be at least one local archive log destination, where you can define it to be MANDATORY or OPTIONAL.

·         The MANDATORY attribute can only be specified for the initialization parameters, LOG_ARCHIVE_DEST_1 through LOG_ARCHIVE_DEST_10.

·         When you specify the value for LOG_ARCHIVE_MIN_SUCCEED_DEST, there will be at least one archive destination that will be treated as MANDATORY, because the minimal or default value for this initialization parameter is 1.

·         The value of LOG_ARCHIVE_MIN_SUCCEED_DEST can’t be greater than the number of destinations.  It also can’t be greater than the number of MANDATORY destinations, plus the number of local OPTIONAL destinations.

·         If you defer a MANDATORY destination, there is a possibility that the database can overwrite the redo log file, without transferring the archived log to the standby database.  In this case you will need to manually copy it across to the Standby database site.

If you decide to duplex the archived logs, then you can establish which destinations are MANDATORY and OPTIONAL, by specifying these attributes in the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST initialization parameters.  Keeping in mind the following restrictions:

·         Any destination declared by LOG_ARCHIVE_DEST is MANDATORY

·         Any destination declared by LOG_ARCHIVE_DUPLEX_DEST is optional, provided that LOG_ARCHIVE_MIN_SUCCEED_DEST=1.  If LOG_ARCHIVE_MIN_SUCCEED_DEST=2, then LOG_ARCHIVE_DUPLEX_DEST is MANDATORY.

There is a tight relationship between the initialization parameters LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_MIN_SUCCEED_DEST=n.  Even if all your destinations were defined as OPTIONAL, it does not mean that the Oracle Database will be able to overwrite the redo log files, in the case of a archive destination failure.

If you were archiving to three destinations, all specified as OPTIONAL destinations in the LOG_ARCHIVE_DEST_n parameters.  The value of LOG_ARCHIVE_MIN_SUCCEED_DEST will directly affect the resultant behaviour.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=1:  The Oracle database can re-use redo log files if one of the OPTIONAL destinations succeed, ie 1 out of 3.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=2:   The Oracle database can re-use redo log files if at least two of the destinations succeed.  

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=3:  The database can reuse redo log files, only if all three of the optional destinations were successfully written to. 

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=>3:  This would generate an error.

Lets say you had a mix of MANDATORY and OPTIONAL destinations, and you were not shipping to a standby database.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=1:  The database will use the value of 2, because there are two MANDATORY destinations.  The value of this parameter is ignored in this case.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=2:  The Oracle Database can re-use redo log files, only if the destinations that failed were OPTIONAL destinations.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=3:  In this case, the two MANDATORY destinations need to succeed, plus at least one of the OPTIONAL destinations.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=4: In this case all four of the defined destinations have to succeed, regardless of whether they are defined as MANDATORY or OPTIONAL.

·         LOG_ARCHIVE_MIN_SUCCEED_DEST=>4: This would generate an error.

Re-Archiving to a Failed Destination

If you do not specify the REOPEN keyword with the LOG_ARCHIVE_DEST_n parameter, then ARCn will not attempt to archive after a failure.  The default value for REOPEN is 300 seconds.  If you set it to ‘0’, then it is the same as turning off the REOPEN attribute.  REOPEN sets the minimum number of seconds that ARCn should wait before trying to re-open or write to the failed archive destination again.

REOPEN does not specify the number of attempts, the REOPEN attempt after the specified number of seconds to wait, will either succeed or fail.

When you specify, REOPEN for an OPTIONAL destination, the Oracle Database will overwrite redo log files, in the case of a failed write to the archived log destination.

If you specify REOPEN for a MANDATORY destination, then the database will still stall when the archive destination fails.  The database will wait for the problem to be resolved, with the archiving destination, and then it will continue again.  The following considerations are important in this scenario:

·         Archive manually to the failed destination, to see if you can clear the problem.

·         Change the destination by deferring the destination, specifying the destination as OPTIONAL, or changing the service.

·         Drop the destination.

The following things should be kept in consideration when using the REOPEN attribute:

·         ARCn reopens a destination when starting an archive operation from the beginning of the log file.  ARCn will always retry the log copy from the beginning of the copy.

·         The REOPEN attribute affects the ACTIVE=TRUE destination state.  The VALID and ENABLED states are not changed.

Controlling Trace output generated by the Archivelog Process

Background processes will always write to a trace file, under the appropriate conditions.  You can control the output that is generated and written to a trace file for the archivelog process.  This is controlled by the LOG_ARCHIVE_TRACE initialization parameter.  The default is ‘0’.  This is a dynamic parameter, so you can change it like this for example:

SQL> alter system set log_archive_trace=12 scope=both;

The result of this statement, is that output will be generated for levels 8 and 4.  This change will take effect at the start of the next archiving operation.

The following is a list of possible trace levels for LOG_ARCHIVE_TRACE:

Trace Level	Meaning
0	Disable archive log tracing, this is the default
1	Track Archival of redo log file
2	Track archival status for each archive log destination
4	Track archival operational phase
8	Track archive log destination activity
16	Track detailed archive log destination activity
32	Track archive log destination parameter modifications
64	Track ARCn process state activity
128	Track FAL(Fetch Archive Log) server related activities
256	Supported in a future release
512	Tracks Asynchronous LGWR activity
1024	RFS physical client tracking
2048	ARCn /RFS heartbeat tracking
4096	Track real-time apply
8192	Track redo apply activity (Media recovery or physical standby)

You can combine tracing levels by specifying a value that is equal to the sum of the individual levels that you would like to trace.

Archived Redo Log views

Dynamic Performance View	Description
V$database	Shows if the database is in ARCHIVELOG mode or NOARCHIVELOG mode, and if MANUAL archiving has been specified.
V$archived_log	Displays historical archive log information from the control file.  If you use a recovery catalog, then the RC_ARCHIVED_LOG view contains similar information
V$archive_dest	Describes the current instance, all archive destinations, the current value, mode and status of these destinations
V$archive_processes	For an instance, displays information about the state of the various archive processes
V$backup_redolog	Information about any backups of archived logs.   If you use a recovery catalog, then the view RC_BACKUP_REDOLOG will contain similar information
V$log	Displays all redo log groups, for the database, and indicates which need to be archived
V$log_history	Contains log history information, such as which logs have been archived, and the SCN range for each archived log

The ARCHIVE LOG LIST command

This is a SQL*Plus command.  It displays archiving information for the connected instance.

SQL> archive log list

The display rendered gives you a lot of information:

·         You can see if the database is currently operating in archive mode or not

·         You can see if automatic or manual archiving is enabled

·         It will display the archived redo log destination

·         The sequence number of the oldest filled redo log file

·         The sequence number of the next redo log file group to archive

·         The sequence number of the current redo log file group

Mark Tiger,

Need a Database Health Check, Remote Monitoring, Support, Maintenance, or a Security Audit?

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.