module8-redo_log; updated June 3, 2013; Some figures shown in these notes are from Oracle document D11321GC11. Reference: Oracle® Database Administrator's Guide 11g Release 2 (11.2) E25494-03.

 

Module 8 – Redo Log Files

 

Objectives

 

These notes cover the purpose of On-line Redo Log Files. 

 

On-Line Redo Log Files

 

Redo Log File Basics

 

Redo Log Files enable the Oracle Server or DBA to redo transactions if a database failure occurs.  This is their ONLY purpose – to enable recovery.

 

Transactions are written synchronously to the Redo Log Buffer in the System Global Area. 

·        All database changes are written to redo logs to enable recovery.

·        As the Redo Log Buffer fills, the contents are written to Redo Log Files. 

·        This includes uncommitted transactions, undo segment data, and schema/object management information. 

·        During database recovery, information in Redo Log Files enable data that has not yet been written to datafiles to be recovered. 

 

Redo Thread

If a database is accessed by multiple instances, a redo log is called a redo thread.

·        This applies mostly in an Oracle Real Application Clusters environment.

·        Having a separate thread for each instance avoids contention when writing to what would otherwise be a single set of redo log files - this eliminates a performance bottleneck.

 

Redo Log File Organization – Multiplexing

 

The figure shown below provides the general approach to organizing on-line Redo Log Files.  Initially Redo Log Files are created when a database is created, preferably in groups to provide for multiplexing.  Additional groups of files can be added as the need arises.

 

 

·        Each Redo Log Group has identical Redo Log Files (however, each Group does not have to have the same number of Redo Log Files).

·        If you have Redo Log Files in Groups, you must have at least two Groups. The Oracle Server needs a minimum of two on-line Redo Log Groups for normal database operation. 

·        The LGWR concurrently writes identical information to each Redo Log File in a Group. 

·        Thus, if Disk 1 crashes as shown in the figure above, none of the Redo Log Files are truly lost because there are duplicates. 

·        Redo Log Files in a Group are called Members. 

o   Each Group Member has an identical log sequence number and is the same size – the members within a group cannot be different sizes. 

o   The log sequence number is assigned by the Oracle Server as it writes to a log group and the current log sequence number is stored in the control files and in the header information of all Datafiles – this enables synchronization between Datafiles and Redo Log Files.

o   If the group has more members, you need more disk drives in order for the use of multiplexed Redo Log Files to be effective.

 

A Redo Log File stores Redo Records (also called redo log entries).

·        Each record consists of "vectors" that store information about:

o   changes made to a database block. 

o   undo block data.

o   transaction table of undo segments.

·        These enable the protection of rollback information as well as the ability to roll forward for recovery.

·        Each time a Redo Log Record is written from the Redo Log Buffer to a Redo Log File, a System Change Number (SCN) is assigned to the committed transaction.

 

 

Where to Store Redo Log Files and Archive Log Files

 

Guidelines for storing On-line Redo Log Files versus Archived Redo Log Files:

1.   Separate members of each Redo Log Group on different disks as this is required to ensure multiplexing enables recovery in the event of a disk drive crash.

2.   If possible, separate On-line Redo Log Files from Archived Log Files as this reduces contention for the I/O path between the ARCn and LGWR background processes. 

3.   Separate Datafiles from On-line Redo Log Files as this reduces LGWR and DBWn contention.  It also reduces the risk of losing both Datafiles and Redo Log Files if a disk crash occurs.

 

You will not always be able to accomplish all of the above guidelines – your ability to meet these guidelines will depend on the availability of a sufficient number of independent physical disk drives.

 

 

Redo Log File Usage

 

Redo Log Files are used in a circular fashion. 

·        One log file is written in sequential fashion until it is filled, and then the second redo log begins to fill.  This is known as a Log Switch.  

·        When the last redo log is written, the database begins overwriting the first redo log again. 

 

 

·        The Redo Log file to which LGWR is actively writing is called the current log file.

·        Log files required for instance recovery are categorized as active log files.

·        Log files no longer needed for instance recovery are categorized as inactive log files.

·        Active log files cannot be overwritten by LGWR until ARCn has archived the data when archiving is enabled.

 

 

Log Writer Failure

 

What if LGWR cannot write to a Redo Log File or Group?  Possible failures and the results are:

1.   At least one Redo Log File in a Group can be written – Unavailable Redo Log Group members are marked as Invalid, a LGWR trace file is generated, and an entry is written to the alert file – processing of the database proceeds normally while ignoring the invalid Redo Log Group members.

2.   LGWR cannot write to a Redo Log Group because it is pending archiving – Database operation halts until the Redo Log Group becomes available (could be through turning off archiving) or is archived.

3.   A Redo Log Group is unavailable due to media failure – Oracle generates an error message and the database instance shuts down.  During media recovery, if the database did not archive the bad Redo Log, use this command to disable archiving so the bad Redo Log can be dropped:

 

ALTER DATABASE CLEAR UNARCHIVED LOG

 

4.   A Redo Log Group fails while LGWR is writing to the members – Oracle generates an error message and the database instance shuts down.  Check to see if the disk drive needs to be turned back on or if media recovery is required.  In this situation, just turn on the disk drive and Oracle will perform automatic instance recovery.

 

Sometimes a Redo Log File in a Group becomes corrupted while a database instance is in operation. 

·        Database activity halts because archiving cannot continue.

·        Clear the Redo Log Files in a Group (here Group #2) with the statement:

 

ALTER DATABASE CLEAR LOGFILE GROUP 2;

 

How large should Redo Log Files be, and how many Redo Log Files are enough? 

 

The size of the redo log files can influence performance, because the behavior of the DBWn and ARCn processes (but not the LGWR process) depend on the redo log sizes.

·       Generally, larger redo log files provide better performance.

·       Undersized log files increase checkpoint activity and reduce performance.

·       It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable.

·       Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes; however more often switches are common when using Data Guard for primary and standby databases.

·       It is also good for the file size to be such that a filled group can be archived to a single offline storage unit when such an approach is used.

·       If the LGWR generates trace files and an alert file entry that Oracle is waiting because a checkpoint is not completed or a group has not been archived, then test adding another redo log group (with its files).

 

 

This provides facts and guidelines for sizing Redo Log files.

·        Minimum size for an On-line Redo Log File is 4MB.

·        Maximum size and Default size depends on the operating system. 

·        The file size depends on the size of transactions that process in the database.

o   Large batch update transactions require larger Redo Log Files, 5MB or more in size. 

o   Databases that primarily support on-line, transaction-processing (OLTP) can work successfully with smaller Redo Log Files. 

·        Set the size large enough so that the On-line Redo Log Files switch about once every 20 minutes. 

o   If your Log Files are 4MB in size and switches are occurring on the average of once every 10 minutes, then double their size!

o   You can specify the log switch interval to 20 minutes (a typical value) with the init.ora command shown here that sets the ARCHIVE_LAG_TARGET parameter in seconds ( there are 1200 seconds in 20 minutes).

 

ARCHIVE_LAG_TARGET = 1200

 

or to set the parameter dynamically

 

ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 1200

 

·        Determine if LGWR has to wait (meaning you need more groups) by:

o   Check the LGWR trace files – the trace files will provide information about LGWR waits.

o   Check the alert_SID.log file for messages indicating that LGWR has to wait for a group because a checkpoint has not completed or a group has not been archived.  

 

The parameter MAXLOGFILES in the CREATE DATABASE command specifies the maximum number of Redo Log Groups you can have – group numbers range from 1 to MAXLOGFILES.

·        Override this parameter only by recreating the database or control files.

·        When MAXLOGFILES is not specified, the CREATE DATABASE command uses a default value specific to each operating system – check the operation system documentation.

·        With Oracle 11g if your exceed the maximum number of Redo Log Groups, Oracle automatically causes the control file to expand in size to accommodate the new maximum number.

 

LGWR writes from the Redo Log Buffer to the current Redo Log File when:

·        a transaction commits

·        the Redo Log Buffer is 1/3 or more full.

·        There is more than 1MB of changed rows in the Redo Log Buffer

·        Prior to DBWn writing modified blocks from the Database Buffer Cache to Datafiles.

 

Checkpoints also affect Redo Log File usage.  

·        During a checkpoint the DBWn background process writes dirty database buffers (buffers that have modified data) from the Database Buffer Cache to datafiles.

·        The CKPT background process updates the control file to reflect that a checkpoint has been successfully completed. 

·        If a log switch occurs as a result of a checkpoint, then the CKPT process updates the headers of the datafiles.

 

Checkpoints can occur for all datafiles in the database or only for specific datafiles.  A checkpoint occurs, for example, in the following situations:

·        when a log switch occurs.

·        when an Oracle Instance is shut down with the normal, transactional, or immediate option.

·        when forced by setting the initialization parameter FAST_START_MTTR_TARGET that controls the number of dirty buffers written by DBWn to datafiles.

·        when a DBA issues the command to create a checkpoint.

·        when the ALTER TABLESPACE [OFFLINE NORMAL | READ ONLY | BEGIN BACKUP] command causes check pointing on specific datafiles.

 

Checkpoint information is also recorded in the alert_SID.log file whenever the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to TRUE.  The default value of FALSE for this parameter does not log checkpoints.

 

About the FAST_START_MTTR_TARGET (Mean Time to Recovery) Parameter

·        Beginning with Oracle 10g, the database self-tunes checkpointing to achieve good recovery times with low impact on normal throughput.

·        The DBA does not need to set any checkpoint-related parameters.

·        This method reduces the time required for cache recovery and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint.

·        DBAs specify a target (bounded) time to complete the cache recovery phase of recovery with the FAST_START_MTTR initialization parameter, and Oracle automatically varies the incremental checkpoint writes to meet that target.

·        Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter.

o   If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle automatically tries to checkpoint as frequently as necessary.

o   Under this condition, the size of the log files should be large enough to avoid additional checkpointing due to under sized log files.

o   The optimal size can be obtained by querying the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view.  The value shown is expressed in megabytes.

 

SQL> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY; 

 

OPTIMAL_LOGFILE_SIZE

--------------------

256    

 

o   You can also obtain sizing advice on the Redo Log Groups page of Oracle Enterprise Manager Database Control.

 

Redo Log File Block Size

Database block size can be between 2K and 32K, but log files default to a block size equal to the disk drive physical sector size (typically 512B).

·        Newer disk drives sometimes use 4KB sector sizes.

·        Oracle automatically detects this and uses a 4Kb default for those disk drives.

·        This can result in significant disk drive wastage.  You can check this with this SQL statement.

 

SELECT name, value

FROM v$sysstate

WHERE name = 'redo wastage';

 

Result:

NAME                             VALUE

---------------------------- ---------

redo wastage                  17941684

 

With Oracle 11g Release 2 you can specify a block size for online redo log files with the BLOCKSIZE keyword in the CREATE DATABASE, ALTER DATABASE, and CREATE CONTROLFILE statements. The permissible block sizes are 512, 1024, and 4096.

 

This example shows use of the BLOCKSIZE parameter to create 512Kb blocks.

 

ALTER DATABASE orcl ADD LOGFILE

  GROUP 4 ('/u01/logs/orcl/redo04a.log','/u01/logs/orcl/redo04b.log')

  SIZE 100M BLOCKSIZE 512 REUSE;

This query shows the blocksize for your database.

SQL> SELECT BLOCKSIZE FROM V$LOG;

 

BLOCKSIZE

---------

      512

 

 

 

Log Switches and Checkpoints

 

This figure shows commands used to cause Redo Log File switches and Checkpoints.

 

 

 

Adding On-line Redo Log File Groups

 

This figure shows the ALTER DATABASE command option used to add Redo Log File Groups.  This simultaneously adds new log files to the new Group 3. 

 

 

 

Adding On-line Redo Log File Members

 

This figure shows the ALTER DATABASE command options to add new Log File Members to existing groups.

·        If the file to be added already exists and is being reused, it must have the same size and you must use the REUSE option in the command immediately after the filename specification. 

·        Size need not be specified when adding to an existing group.

 

 

 

Dropping Redo Log File Groups and Files

 

This is accomplished with the ALTER DATABASE command as shown here:

 

ALTER DATABASE DROP LOGFILE GROUP 3;

 

Remember, you must keep at least two groups of On-line Redo Log Files working.  You also cannot drop an active (current) Group.  Further, the actual operating system files are not deleted when you drop a Group.  You must use operating system commands to delete the files that stored the Redo Logs of the dropped Group.

 

Sometimes an individual Redo Log File will become damaged (invalid).  You can use the following command to drop the file.  Then use the operating system command to delete the file that stored the invalid Redo Log File, and then recreate the Redo Log File.

 

ALTER DATABASE DROP LOGFILE MEMBER

    ' /u01/student/dbockstd/oradata/USER350redo01a.log';

 

 

Changing Redo Log File Sizes

 

Each Redo Log File member in a Group must be identical in size.  If you need to make your Redo Log Files larger, use the following steps.

 

1.  Use the V$LOG view to identify the current active Redo Log Group. 

 

SQL> SELECT group#, status FROM v$log;

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

         4 INACTIVE

 

2.  Drop one or more of the inactive Redo Log Groups keeping at least two current On-line Redo Log Groups.

 

3.  Use operating system commands to delete the files that stored the dropped Redo Log Files.

 

4.  Recreate the groups with larger file sizes.  Continue this sequence until all groups have been resized.

 

Obtaining Redo Log Group and File Information

 

Two views, V$LOG and V$LOGFILE are used to store information about On-line Redo Log files.  The following example queries display information from SIUE's DBORCL database. The files in each group are 64M in size.

 

SELECT group#, sequence#, bytes/1024, members, status

FROM v$log;

 

    GROUP#  SEQUENCE# BYTES/1024    MEMBERS STATUS

---------- ---------- ---------- ---------- ----------------

         1         31      65536          2 INACTIVE

         2         32      65536          2 CURRENT

         3         30      65536          2 INACTIVE

 

Possible Status values for this view are:

·        Unused – the Redo Log Group has never been used – this status only occurs for a newly added Redo Log Group.

·        Current – the active Redo Log Group.

·        Active – the Redo Log Group is active, but not the current Group – it is needed for crash recovery and may be in use for block recovery.  It may not yet be archived.

·        Clearing – the Log is being recreated after an ALTER DATABASE CLEAR LOGFILE command.

·        Clearing_Current – the current Redo Log Group is being cleared of a closed group.

·        Inactive – The Group is not needed for Instance Recovery.

 

COLUMN member FORMAT A45;

COLUMN status FORMAT A10;

SELECT member, status

FROM v$logfile;

 

MEMBER                                   STATUS

---------------------------------------- ----------

/u01/oradata/DBORCL/DBORCLredo01a.log

/u01/oradata/DBORCL/DBORCLredo02a.log

/u01/oradata/DBORCL/DBORCLredo03a.log

/u02/oradata/DBORCL/DBORCLredo01b.log

/u02/oradata/DBORCL/DBORCLredo02b.log

/u02/oradata/DBORCL/DBORCLredo03b.log

 

6 rows selected.

 

Possible Status values for this view are:

·        Invalid – the file cannot be accessed and needs to be dropped and recreated.

·        Stale – the contents of the file are incomplete – drop it and recreate it.

·        Deleted – the file is no longer in use – you can use operating system commands to delete the associated operating system file.

·        Blank – the file is in use.

 

 

Archived Redo Log Files

 

Archived Log Modes

 

A production database should always be configured to operate in ARCHIVELOG mode. 

 

·        NOARACHIVELOG mode:

o   The Redo Log Files are overwritten each time a log switch occurs, but the files are never archived.

o   When a Redo Log File (group) becomes inactive it is available for reuse by LGWR.

o   This mode protects a database from instance failure, but NOT from media failure.

o   In the event of media failure, database recovery can only be accomplished to the last full backup of the database! 

o   You cannot perform tablespace backups in NOARCHIVELOG mode.

 

·        ARCHIVELOG mode –

o   Full On-line Redo Log Files are written by the ARCn process to specified archive locations, either disk or tape – you can create more than one archiver process to improve performance. 

o   A database control file tracks which Redo Log File groups are available for reuse (those that have been archived).

o   The DBA can use the last full backup and the Archived Log Files to recover the database.

o   A Redo Log File that has not been archived cannot be reused until the file is archived – if the database stops awaiting archiving to complete, add an additional Redo Log Group.

 

This figure shows the archiving of log files by the ARCn process as log files are reused by LGWR.

 

Description of Figure 13-1 follows

 

While archiving can be set to either manual or automatic, the preferred setting for normal production database operation is automatic.  In manual archiving, the DBA must manually archive each On-line Redo Log File. 

 

Specifying the Number of ARCn Processes

 

The LOG_ARCHIVE_MAX_PROCESSES parameter in the init.ora file specifies how many ARCn processes are started for a database instance.

·        Usually the parameter does not need to be set or changed - Oracle starts additional ARCn processes as necessary to keep from falling behind on archiving.

·        Default is four ARCn processes – you can specify up to 30 processes.

·        Use additional ARCn processes to ensure automatic archiving of filled redo log files does not fall behind.

·        The LOG_ARCHIVE_MAX_PROCESSES parameter is dynamic and can be changed as shown.

 

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 4;

 

 

Switching from NOARCHIVELOG to ARCHIVELOG

 

1.  Connect to the database with administrator privileges (AS SYSDBA) – shutdown the database instance normally with the command: 

 

Shutdown

Note:  You cannot change from ARCHIVELOG to NOARCHIVELOG if any datafiles require media recovery.

 

2.  Backup the database – it is always recommended to backup a database before making any major changes.

 

3.  Edit the init.ora file to add parameters to specify the destinations for archive log files (the next section provides directions on how to specify archive destinations).

 

4.  Startup a new instance in MOUNT stage – do not open the database – archive status can only be modified in MOUNT stage: 

 

STARTUP MOUNT PFILE=<your pfile location>

 

5.  Issue the command to turn on archiving and then open the database:

 

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

 

6.  Shutdown the database.

 

SHUTDOWN IMMEDIATE

 

7.  Backup the database – necessary again because the archive status has changed.  The previous backup was taken in NOARCHIVELOG mode and is no longer usable.

 

Specifying Archive Destinations and Names

 

Archive Redo Log files can be written to a single disk location or they can be multiplexed, i.e. written to multiple disk locations.

·        Archiving to a single destination was once accomplished by specifying the LOG_ARCHIVE_DEST initialization parameter in the init.ora file – it has since been replaced in favor of the LOG_ARCHIVE_DEST_n parameter (see next bullet).

·        Multiplexing can be specified for up to 31 locations by using the LOG_ARCHIVE_DEST_n parameters (where n is a number from 1 to 31).  This can also be used to duplex the files by specifying a value for the  LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 parameters.

·        When multiplexing, you can specify remote disk drives if they are available to the server.

 

These examples show setting the init.ora parameters for the possible archive destination specifications:

 

1.   Example of Single Destination:

 

LOG_ARCHIVE_DEST = '/u03/student/dbockstd/oradata/archive'

 

2.   Example of Duplex Destinations:

 

LOG_ARCHIVE_DEST_1 = 'LOCATION = /u01/student/dbockstd/oradata/archive'

LOG_ARCHIVE_DEST_2 = 'LOCATION = /u02/student/dbockstd/oradata/archive'

 

3.   Example of Multiplexing Three Archive Log Destinations (for those DBAs that are very risk averse):

 

LOG_ARCHIVE_DEST_1 = 'LOCATION = /u01/student/dbockstd/oradata/archive'

LOG_ARCHIVE_DEST_2 = 'LOCATION = /u02/student/dbockstd/oradata/archive'

LOG_ARCHIVE_DEST_3 = 'LOCATION = /u03/student/dbockstd/oradata/archive'

 

The LOCATION keyword specifies an operating system specific path name.

 

Note:  If you use a LOG_ARCHIVE_DEST_n parameter, then you cannot use the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameters.

 

Specify the naming pattern to use for naming Archive Redo Log Files with the LOG_ARCHIVE_FORMAT command in the init.ora file. 

 

LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

 

where %t = thread number.

      %s = log sequence number.

      %r = reset logs ID (a timestamp value).

 

This example shows a sequence of Archive Redo Log files generated using the LOG_ARCHIVE_FORMAT to specify naming the Redo Log Files – all of the logs are for thread 1 with log sequence numbers of 100, 101, and 102 with reset logs ID 509210197 indicating the files are from the same database.

 

 

/disk1/archive/arch_1_101_509210197.arc,

/disk1/archive/arch_1_102_509210197.arc

 

/disk2/archive/arch_1_100_509210197.arc,

/disk2/archive/arch_1_101_509210197.arc,

/disk2/archive/arch_1_102_509210197.arc

 

/disk3/archive/arch_1_100_509210197.arc,

/disk3/archive/arch_1_101_509210197.arc,

/disk3/archive/arch_1_102_509210197.arc

 

 

Viewing Information on Archive Redo Log Files

 

Information about the status of the archiving can be obtained from the V$INSTANCE dynamic performance view.  This shows the status for the DBORCL database.

 

SELECT archiver

FROM v$instance;

 

ARCHIVE

-------

STARTED

 

 

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

 

Dynamic Performance View

Description

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains 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.

 

 

A final caution about automatic archiving – Archive Redo Log files can consume a large quantity of space.  As you dispose of old copies of database backups, dispose of the associated Archive Redo Log files. 

 

 

 

END OF NOTES