SkillBuilders | Oracle 9i Administration | Lesson 17: Managing Redo Logs

Instructor's Notes

(If applicable)

Lesson 17: Managing Redo Logs
In this lesson we will cover redo log concepts and a DBA’s responsibilities to redo logs, including archiving online logs, multiplexing online and archive logs and avoiding log switch waits.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

We learned about REDO, online logs and archived logs in the lessons on database architecture. Let's take a moment to review the basics…
The online redo log files are used to record changes made to the database files. As an update is applied to the database, Oracle automatically creates a "redo" record. The redo contains the after-image of the change and is used in the event the change needs to be reapplied, e.g. a recovery operation.
This redo record is written to the redo buffer. When a commit is issued, the redo is flushed to the redo log file by the log writer background process (LGWR).
The logs are used in cyclical rotation. When one redo log file fills a log switch occurs and Oracle begins writing redo to the next log file. At least two log files must exist. Specifically, when a log fills:

A checkpoint is initiated for that log (i.e. the blocks changed by the operations recorded in the log are flushed to disk by the DBWR background process).
A “log switch” occurs - Oracle starts writing to the next log in the rotation.

Other Text:
(Examples or comments displayed on slide, if any).

Online Logs
Redo Buffer
Redo Buffer
LGWR
SGA

Instructor's Notes

(If applicable)

The dynamic performance view V$LOGFILE contains a row for each online log defined to the database.
The V$LOG view contains, for each log group, the size of the logs in the group. A group can contain more than one log file. Log files in a single group are mirror images of each other. This multiplexing concept protects against the loss of a disk device containing an online log. See the section on Multiplexing in this lesson for more information.

Supplemental Notes
- There is no advantage to having different size log files. In fact, there is a disadvantage - tuning is near impossible. The decision of how many and how big your log files are is a tuning issue.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select member
2 from v$logfile;

MEMBER
-----------------------------------
C:\ORACLE\ORADATA\DAVE\REDO01.LOG
C:\ORACLE\ORADATA\DAVE\REDO02.LOG
C:\ORACLE\ORADATA\DAVE\REDO03.LOG

Active online logs

SQL> select group#, bytes
2 from v$log;

GROUP# BYTES
---------- ----------
1 104857600
2 104857600
3 104857600

Group number and size

Instructor's Notes

(If applicable)

If the archive background process is running and the database is in ARCHIVELOG mode, the ARCH background process will write a filled log file to an archive location so that the changes will not be lost when the log file is needed again (and its contents are overwritten). Oracle9i supports multiplexing the archive logs, i.e. writing the archive logs to multiple locations (up to 10).
If the archive process is not active you cannot recover your database after a MEDIA (disk) failure!

Other Text:
(Examples or comments displayed on slide, if any).

LGWR
SGA
Redo Buffer
Online Logs
Archive Logs
When active log fills:
Checkpoint occurs
Log switch occurs
ARCH process writes inactive file to archive location
Mirror Archive
Logs

Instructor's Notes

(If applicable)

Some of the primary responsibilities of the DBA regarding redo logs are to insure the recoverability of the database by:

Insuring that the redo logs are archived (thus preserving the contents)
Multiplexing the online logs (thus protecting against the loss of redo though loss of a disk device)
Multiplexing the archive logs (essentially creating backup copies of the archive redo logs)

Additionally, having enough online logs will prevent the database from waiting for a log as it cycles through the available logs. Adding more log files can fix this problem.
In this lesson we will focus our study on learning the techniques necessary to fulfill the responsibilities stated here. Let's get started…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

To determine the status of archiving on a database:

Check the LOG_ARCHIVE_START parameter value. TRUE causes Oracle to start one or more archive background processes when the instance is started.
Check the LOG_MODE value in V$DATABASE. ARCHIVELOG indicates that the database will not overwrite an online log file until it has been archived.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> show parameter log_archive_start

NAME TYPE VALUE
--------------------- ------------ -----
log_archive_start boolean TRUE

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

Instructor's Notes

(If applicable)

Yet another way to check on the archive status of your database is to use the SQL*Plus command ARCHIVE LOG LIST. This command displays the log mode, whether the automatic archive (ARCH) process is active, the archive log file destination and the current log sequence number.
The two main points we can determine from the output of the ARCHIVE LOG LIST command are:

Database Log Mode – When in "Archive Mode", the database will not overwrite an online redo log until it has been archived. This is the desired mode.
Automatic Archival – When "enabled", the archive background process is started, typically with the LOG_ARCHIVE_START=TRUE parameter. This is the desired mode.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Oracle\oradata\dave\archive
Oldest online log sequence 111
Next log sequence to archive 113
Current log sequence 113

Instructor's Notes

(If applicable)

You will want to insure that you are archiving to protect the redo generated by database operations. There are two steps:

Putting the database in "archive log" mode. When in "Archive Mode", the database will not overwrite an online redo log until it has been archived.
Starting the archive (ARCH) background process when the instance starts. If you do the first step but not this step, the database will hang when it cycles to a log that has not been archived (because the database is in archive log mode).

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Archive-related activity in the database is controlled with initialization parameters. Some of the archive-related initialization parameters are:

LOG_ARCHIVE_START: When set to TRUE initiates the ARCH Oracle background process will be started at startup time.
LOG_ARCHIVE_DEST_n: Controls where the redo log files are archived. This will usually be a disk drive different from the one where the database files are stored. "n" is digit 1,2,3…10. Can specify up to 10 locations, including standby databases (via the SERVICE clause of the LOG_ARCHIVE_DEST_n parameter) .
LOG_ARCHIVE_FORMAT: Controls the format of the archive file name. As specified in the Oracle Administration manual, these variables can be used:

%T – Thread number, left zero padded. For example, 001.ARC
%t – Thread number, not padded. For example, 1.ARC
%S – Log sequence number, left zero padded. For example, 00116.ARC
%s – Log sequence number, not padded. For example, 116.ARC

Other Text:
(Examples or comments displayed on slide, if any).

###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=C:\Oracle\oradata\dave\archive'
log_archive_format=%S.arc
log_archive_start=true
Database parameter file contains

Instructor's Notes

(If applicable)

First, insure that the archive background process starts when the database is started. This is done by setting initialization parameter LOG_ARCHIVE_START = TRUE.
If using a text INIT.ORA file (the only option in pre-9i databases), simply edit the text file and restart the database as shown on the previous page.
If you are using a Server Parameter file (SPFILE), execute the ALTER SYSTEM command as shown in the example above. Since LOG_ARCHIVE_START is not a dynamic parameter (it is a static parameter), we need to alter the SPFILE then restart the database (as the example demonstrates).
Next, we will put the database in ARCHIVELOG mode. Turn to the next page for an example.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter system
2 set log_archive_start = true
3 comment = 'dga, 6/6/03'
4 scope = spfile;
System altered.

SQL> shutdown immediate
. . .
SQL> startup
ORACLE instance started.
. . .

Instructor's Notes

(If applicable)

To put your database in ARCHIVELOG mode, shutdown the database in consistent mode (i.e. do not shutdown abort), then use the ALTER DATABASE ARCHIVELOG statement. The example above demonstrates the procedure.
Remember, the ARCHIVE LOG LIST command or dynamic performance view V$DATABASE will show you the current mode of the database.

Other Text:
(Examples or comments displayed on slide, if any).

Shutdown the database cleanly, then mount

SQL> shutdown
SQL> startup mount
ORACLE instance started.
. . .
SQL> alter database archivelog;
Statement processed.
SQL> alter database open;
Statement processed.

Instructor's Notes

(If applicable)

Archiving Workshop
1. Check the ARCHIVELOG mode of your database.
2. Check the value of the LOG_ARCHIVE_START parameter.
3. If the database is not in archive log mode, enable it. If the automatic archival process is not started, start it.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

We have learned that protecting archived redo is one of our main responsibilities because without archived redo we cannot recover the database from a disk failure. To meet this responsibility we should "multiplex" the archived redo logs. When we multiplex the archive logs, the archive background process simultaneously writes the logs to all locations – up to 10 – that we specify in the initialization parameters LOG_ARCHIVE_DEST_n. These logs are mirror images of each other.
By default many databases use the old LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters. These parameters have been deprecated with Oracle9i and replaced by LOG_ARCHIVE_DEST_n, where "n" is 1 to 10. (Note that Standard Edition does not support 10 locations, just two; therefore, LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST have not been deprecated with Standard Edition.)
The example above demonstrates how to multiplex the archive logs on a system that currently used the LOG_ARCHIVE_DEST parameter to specify just one archive destination. Here's a description of the steps:

First, I alter the database to remove the current setting of LOG_ARCHIVE_DEST parameter. This is required because the LOG_ARCHIVE_DEST parameter is mutually exclusive with the LOG_ARCHIVE_DEST_n parameters.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter system
2 set log_archive_dest = ''
3 scope = both;

System altered.

SQL> alter system
2 set log_archive_dest_1 = "location=c:\archive\ mandatory"
3 scope = both;

System altered.

SQL> alter system
2 set log_archive_dest_2 = "location=d:\archive2\ mandatory"
3 scope = both;

System altered.

Instructor's Notes

(If applicable)

Notes continued from the previous page:
- Next, I set LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 parameters to the desired locations. This causes Oracle to create two mirror-image archive logs for each online redo log file. Note that I have specified "mandatory" for both locations. This means that Oracle will not overwrite the online redo log until it have been copied to both locations. You can also specify "optional". At least one location must be specified as mandatory.
- CAUTION
  - Be sure to place your LOG_ARCHIVE_DEST_n on different physical drives. If they are all on one physical drive, a media failure on that drive can cause you to lose all copies of the archived logs, making complete recovery of the database impossible.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The V$ARCHIVE_STATUS view contains the current status of the archive log destinations.
In this example we see that - as a result of the commands executed in the previous example - destinations 1 and 2 are active and mandatory. We can also see the destination directories.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select dest_name, status, binding, destination
2 from v$archive_dest;

DEST_NAME STATUS BINDING
------------------------- --------- --------- LOG_ARCHIVE_DEST_1 VALID MANDATORY LOG_ARCHIVE_DEST_2 VALID MANDATORY LOG_ARCHIVE_DEST_3 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_4 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_5 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_6 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_7 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_8 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_9 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_10 INACTIVE OPTIONAL

DESTINATION
---------------
c:\archive\
d:\archive2\

Instructor's Notes

(If applicable)

Multiplex Archive Logs Workshop
1. Determine the current status of your databases archive logs, with respect to location and multiplexing.
2. If your database is not multiplexing the archive redo log, multiplex the log. Specify two mandatory locations for the log. If the computer you are using for these workshops has two disks available, place the archive logs on separate disks. (If your database is already multiplexing the online logs, try adding another multiplexed location.)
3. Query the V$ARCHIVE_DEST view to insure the configuration multiplexes the archive log.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

If the DBWR background process has not finished checkpoint operation for a log (remember that a log switch causes a checkpoint), and that log is needed by LGWR (i.e. it is the next log in the cycle), the database will suspend all operations until the checkpoint is complete.
You can detect this problem by searching your alert log for the "checkpoint not complete, cannot allocate new log" messages.
Possible solutions to this problem include adding more log files, speeding up checkpoints and decreasing the log file size to increase the number of checkpoints taken.
Note that this is a Tuning issue and covered in more detail in SkillBuilders' Database Performance and Tuning course.

Other Text:
(Examples or comments displayed on slide, if any).

Tuning course covers in more detail

Instructor's Notes

(If applicable)

If you determine that additional logs are necessary, they can easily be added with the ALTER DATABASE ADD LOGFILE command, as shown in the example above. Refer to the next page for an example of adding OMF-managed logs.
Because we are modifying the structure of the database, the instance must be started in a MOUNT mode. Remember that having the instance in a mounted state means that the control file is open - the names of the new redo log file can be recorded in the control file(s).

Other Text:
(Examples or comments displayed on slide, if any).

ALTER DATABASE ADD LOGFILE
'C:\Oracle\oradata\dave\redo03.log' SIZE 100m;
See next page for more info!

Instructor's Notes

(If applicable)

Oracle Managed Files (OMF) gives the database the ability to manage database files, including datafiles, online redo log files, and control files. Oracle will create, name, and locate physical files for you and will delete them from the operating system automatically when you drop objects the file is associated with.
OMF is an optional feature and, even when configured, is not mandatory.
OMF is configured with initialization parameters. To configure OMF-managed online redo logs, set the DB_CREATE_ONLINE_LOG_DEST_n parameters. See the next page for an example.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

This example demonstrates the use of the Oracle9i feature Oracle Managed Files (OMF) to add OMF-managed redo logs.
First, insure the DB_CREATE_ONLINE_DEST_n parameters are set correctly. DB_CREATE_ONLINE_DEST_n specifies the location of the online logs. Up to 5 can be specified so that, like the archive logs, you can multiplex the online logs.
In this example, when the ALTER SYSTEM ADD LOGFILE command is issued, Oracle creates two logs – mirror images of each other – of 100Mb in size.
Be sure that all the online logs are the same size. Tuning is made more difficult (impossible?) is the logs are different sizes. Add the SIZE parameter to the ALTER SYSTEM command if the database does not use 100Mb log files:
ALTER SYSTEM ADD LOGFILE SIZE 50M;
Turn to the next page for more information on multiplexing the online logs.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter system set
2 db_create_online_log_dest_1='C:\ORACLE\ORADATA\DAVE\'
3 scope=both;

System altered.

SQL> alter system set
2 db_create_online_log_dest_2='D:\ORACLE\ORADATA\DAVE\'
3* scope=both;

System altered.
Different location for a multiplexed online log

SQL> alter database add logfile;

Database altered.
Adds and multiplexes 100Mb online redo log

Instructor's Notes

(If applicable)

As illustrated above, the online redo log files can be easily multiplexed (also known as "mirrored logs"). Thus, each group can have any number of members, where each member in the group is a mirror-image of another member. Ideally the members would be placed on separate disk devices. This technique greatly reduces the chance of losing a log file and being unable to recover the database.

Supplemental Notes
- Note that multiplexing generally results in faster commits, not slower! This is because Oracle signals the completion of a commit as soon as any one of the mirrored log files has been written.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Multiplexing the online logs when using OMF is easy. Just set the DB_CREATE_ONLINE_LOG_DEST_n parameters (where "n" is 1, 2, 3, 4 or 5). This was demonstrated in the previous example. Then simply execute the ALTER DATABASE ADD LOGFILE command. Oracle takes care of the rest.
To manually add new members to existing groups, we will need to know what groups and members currently exist. Querying V$LOGFILE will reveal what log files currently exist and what group they belong to.
Use the ALTER DATABASE ADD LOGFILE MEMBER statement to add members. Note that SIZE is not specified; the log file size is obtained from other members in the group.
Supplemental Notes
- To drop an entire group, we can issue the following command:
- ALTER DATABASE DROP LOGFILE GROUP 3;
- To drop an individual online redo logfile member:
- ALTER DATABASE DROP LOGFILE ‘C:\Oracle\oradata\multi\redo03b.log’;
- Note that you cannot drop a logfile group or a member of a group if the group is the active group. You must first force a log file switch:
- ALTER SYSTEM SWITCH LOGFILE;
- You may have to force a checkpoint too so the the redo in the log is not needed for crash recovery:
- ALTER SYSTEM CHECKPOINT;

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter database add logfile;
Database altered.
OMF does automatically

Find existing groups & members
1 SELECT group#, member
2* FROM v$logfile
SQL> /
GROUP# MEMBER
---------- ---------------------------------
1 C:\ORACLE\ORADATA\DAVE\REDO01.LOG
2 C:\ORACLE\ORADATA\DAVE\REDO02.LOG
3 C:\ORACLE\ORADATA\DAVE\REDO03.LOG

Manually add new members; OMF not used
ALTER DATABASE ADD LOGFILE MEMBER 'C:\Oracle\oradata\multi\redo01b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\Oracle\oradata\multi\redo02b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\Oracle\oradata\multi\redo03b.log' TO GROUP 3;

Instructor's Notes

(If applicable)

Redo Log Workshop
1. Use V$LOG and V$LOGFILE to determine the number and size of the existing log files.
2. Add another multiplexed OMF log file to the database.

Other Text:
(Examples or comments displayed on slide, if any).