Managing Redo Logs

Page 5.1 5.1

5. Managing Redo Logs

© 2001 SkillBuilders, Inc.

© 2001 SkillBuilders, Inc.

SKILLBUILDERS

V 1.6

Managing Redo Logs

Page 5.2 5.2

5.2 5. 2

Redo Log Basics... Ø A redo log records changes made to the database Ø

Called “redo”

Ø

Contains “after” images of every changed byte

Ø A redo log has 3 components 1.

redo log buffer in SGA

2.

redo log file on disk

3.

LGWR process

LGWR

SGA Redo Buffer

Log Files

© 2001 SkillBuilders, Inc.

Redo Log Basics The on-line redo log files are used to record changes made to the database files. The logs are used in rotation, when one redo log file fills: Øa checkpoint is initiated for that log (i.e. the blocks changed by the changes recorded in the full log are flushed to disk). ØA “log switch” occurs - Oracle starts writing to the next log in the rotation. If the archive background process is running (see the LOG_ARCHIVE_START parameter) and the database is in ARCHIVELOG mode (ALTER DATABASE ARCHIVELOG), the ARCH process will then write the filled log file to an archive location so that the changes will not be overwritten when the log file is needed again. If the archive process is not active you cannot recover your database after a MEDIA failure! Note that you do NOT need the archive process to recover from an INSTANCE failure. Note that the database can hang: Øif a checkpoint for a full log file has not completed and the log file is needed for new changes. Adding more log files may help solve this problem because more time will pass before the log file is needed again, giving CKPT more time to complete. ØIf the database is in ARCHIVELOG mode and the ARCH process has not archived up the log file and the log file is need for new changes. First make sure the ARCH process is started (see init.ora parameter LOG_ARCHIVE_START). If it is, then adding more log files may help solve this problem because more time will pass before the log file is needed again, giving ARCH more time to complete.

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.3 5.3

5.3 5. 3

...Redo Log Basics Ø Redo log files are used in a cyclical fashion Ø When active log fills: Ø

Checkpoint occurs

Ø

Log switch occurs

Ø

ARCH process writes inactive file to archive location (optional)

© 2001 SkillBuilders, Inc.

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.4 5.4

5.4 5. 4

Creating Log Files Ø Initial creation of minimum 2 logs: CREATE DATABASE multi LOGFILE 'C:\Oracle\oradata\multi\redo01.log' SIZE 1024K, 'C:\Oracle\oradata\multi\redo02.log' SIZE 1024K, 'C:\Oracle\oradata\multi\redo03.log' SIZE 1024K MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXLOGHISTORY 1 DATAFILE 'C:\Oracle\oradata\multi\system01.dbf' SIZE 58M REUSE AUTOEXTEND ON NEXT 640K

Ø Additional logs can be added: ALTER DATABASE ADD LOGFILE 'C:\Oracle\oradata\multi\redo03.log' SIZE 1024K;

© 2001 SkillBuilders, Inc.

Creating Log Files The database will not operate without at least two (2) log files. Therefore, the initial creation of log files takes place during CREATE DATABASE. In this example, we see three (3) log files being created, each fixed in size at 1024k. 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. More on that later…

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.5 5.5

5.5 5. 5

Log Switch Delays Ø At log switch time, Oracle can be prevented from writing to next log Ø

Next log has not been archived

Ø

LGWR/ARCH contention - very undesirable

Ø

Database hangs until archiving completes

Ø Can occur if high transaction volume or small log files exist Ø Solutions Ø

Increase log file size

Ø

Add more log files

© 2001 SkillBuilders, Inc.

Log Switch Delays If the Archiver has not finished archiving a log before the log writer needs it you will experience a log switch delay. The log file size can be increased in two different ways. You can either rebuild the control file, or you can create a new, larger log file and then drop the original. The latter is the preferred solution. The easy fix for this is to add another log file. Read on…

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.6 5.6

5.6 5. 6

Adding Log Files Ø Query v$logfile to determine what exists Ø Query v$log to determine size Ø Start the instance in MOUNT mode Ø

Enables modification of control file

New log file recorded in control file SVRMGRL> STARTUP MOUNT Ø

Ø Add another redo log file: ALTER DATABASE ADD LOGFILE 'C:\Oracle\oradata\multi\redo03.log' SIZE 1024K; © 2001 SkillBuilders, Inc.

Adding Log Files Use the ALTER DATABASE SQL command to add additional log files. Because we are modifying the structure of the database, the instance must be started in a MOUNT mode. Having the instance in a mounted state means that the control file is open - the names of the redo logs can be recorded in the control file(s).

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.7 5.7

5.7 5. 7

Multiplexing Log Files... Ø Can create log file groups to increase recoverability Ø Ø

Called multiplexing or mirroring Each member of a group is a mirror of the other members of the same group Group 1

Group 2

Member 1

Member 1

Member 2

Member 2

Ø Put members of single group on separate disks

© 2001 SkillBuilders, Inc.

Multiplexing Log Files Adding another member to a redo group is called multiplexing or mirroring redo logs Oracle will automatically write to all members of a group at once Be placing on separate disks, helps insure against losing redo As illustrated above, the redo log files can be easily multiplexed (AKA mirrored). 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. 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.

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.8 5.8

5.8 5. 8

...Multiplexing Log Files Ø Find current groups Ø

SELECT * FROM v$logfile;

Ø Add members to groups 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;

© 2001 SkillBuilders, Inc.

Multiplexing Log Files Querying v$logfile will reveal what log files currently exist and what group they belong to. The v$logfile.STATUS column can contain the following: Ø INVALID - File has not yet been used Ø Blank – File in use Ø STALE – Oracle suspects that the contents are not complete or correct. A STALE log file becomes valid the next time its group is made the active group. Ø DELETED – File is no longer used 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;

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.9 5.9

5.9 5. 9

Redo Log Tuning Ø Put log files on separate disk from data files Ø

Dedicate disk if possible Ø

Disk hardware will be positioned for next write

Ø Log switches are expensive Ø

Checkpoint occurs - SGA is flushed

Ø

Another downside - Hot spots are flushed from memory

Ø Consider: Ø

Time log switches to occur infrequently

Ø

Use off-peak hours if possible

© 2001 SkillBuilders, Inc.

Tuning the redo logging process starts with placing the redo log files on a separate disk from the disk that contain your data files. Remember, redo log activity will increase when updates are made to your data files - thus there is a direct relationship to activity on the data disks and activity on the redo log disks. Keep them separated so that there is no contention. Also, log switches are expensive because a checkpoint operation occurs. All dirty blocks are written to disk; even very active blocks (“hot spots”) are flushed, thus requiring a write and a read if they are accessed again. Consider reducing the number of log switches. The downside: database will take longer to perform instance recovery if database crashes because it has been longer since a checkpoint operation wrote all dirty blocks to disk.

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.10 5.10

5.10 5. 10

Redo Log Tuning Ø Process: 1.

Look in the v$log_history.first_time column or trace files for time of log switch

2. 3.

Increase the size of the log files Set init parameter log_checkpoint_interval to greater than log file size Set init parameter log_checkpoint_timeout = 0 to eliminate timed checkpoints Set init parameter log_checkpoints_to_alert = yes to provide trace info on checkpoints

4. 5.

© 2001 SkillBuilders, Inc.

Use the following script to help determine log switch frequency. This script is supplied as “Logfile_Tuning.SQL”. column group# format 9999 heading grp# column member format a35 column thread# noprint column archive_name format a35 column sequence# format 9999 heading seq# select * from v$log; select * from v$logfile; select * from v$log_history; clear columns The output will look something like this: SQL> select * from v$log; grp# seq# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ----- ----- ---------- ---------- --- ---------------- ------------- --------1 2 3

13 14 15

1048576 1048576 1048576

1 NO 1 NO 1 NO

INACTIVE INACTIVE INACTIVE

299516 16-OCT-00 320331 18-OCT-00 340436 19-OCT-00

4

16

1048576

1 NO

CURRENT

360540 22-OCT-00

continued…

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.11 5.11

SQL> select * from v$logfile; grp# STATUS MEMBER ----- ------- ----------------------------------1 STALE D:\ORACLE\ORADATA\DAVE815\REDO04.LOG 2 STALE

D:\ORACLE\ORADATA\DAVE815\REDO03.LOG

3 STALE

D:\ORACLE\ORADATA\DAVE815\REDO02.LOG

4

D:\ORACLE\ORADATA\DAVE815\REDO01.LOG

SQL> select * from v$log_history; RECID STAMP seq# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# ---------- ---------- ----- ------------- --------- -----------1 407934331 1 137639 10-SEP-00 137781 2 3 4

407934340 407934347 407934355

2 3 4

137781 10-SEP-00 137855 10-SEP-00 137931 10-SEP-00

137855 137931 137993

5 6 7

408008291 408092500 409251690

5 6 7

137993 10-SEP-00 158148 11-SEP-00 178255 12-SEP-00

158148 178255 198452

8 9 10

409308797 409929325 409994390

8 9 10

198452 25-SEP-00 218854 26-SEP-00 239134 02-OCT-00

218854 239134 259307

11

410951254

11

259307 03-OCT-00

279410

RECID

STAMP

seq# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ----- ------------- --------- -----------12 411162562 12 279410 14-OCT-00 299516 13 411330289 13 299516 16-OCT-00 320331 14 15

411410369 411649617

14 15

320331 18-OCT-00 340436 19-OCT-00

340436 360540

15 rows selected. SQL>

© 2001 SkillBuilders, Inc.

V 1.6

Managing Redo Logs

Page 5.12 5.12

5.12 5. 12

Redo Log Workshop

© 2001 SkillBuilders, Inc.

Redo Log Workshop 1. Query v$logfile and v$log to determine which redo log files are currently in use and their size. 2. Add another redo log group file to your database. Make it the same size as the current files. 3. Again query v$logfile to determine which redo log files are currently in use. 4. Multiplex all redo log files. 5. Again query v$logfile to determine which redo log files are currently in use.

© 2001 SkillBuilders, Inc.

V 1.6