Thinking in Resizing Redo Log

Due to an incident in a critical environment, the following two plans are brought onto the table for open discussion. Furthermore, it is investigated thoroughly that the following two plans are the only options of the workaround.

I have extracted the core idea from the real plans to make it much easier to be understood in a minute.

1)      Plan A – To ADD more online Redo Log Groups

Currently, there are three groups with two members each with 50MB. After adding 3 more groups with the same size as existing log groups, there will be 6groups with two members each with 50MB.

Pros:

  • Easy to implement, and no log switching during the configuration is needed.

Cons:

  • There are too many physical files on disk.

 

2)      Plan B – To RESIZE current online Redo Log Members 

Adds three 3 new groups with larger redo member size, for instance, two members with 100MB each. Then performs log switching followed by deleting the existing three old groups alternatively.

Hypothetically, log_checkpoint_* and other settings are modified to the best suited values.

Pros:

  • The architecture (i.e. 2*3 redo log structure) will be same as other systems and compliant to the organization’s policy.
  • Reduces the frequency of log switching

Cons:

  • Negative performance impact during the configuration due to log switching and subsequent check point activities
  • Instance recovery time increases.

 

3)      Plan C – Plan B + Plan A

Increases redo member size and add new groups.

Pros:

  • Better performance with increased size of existing members and additional 3 new groups.

Cons:

  • See Cons of Plan B.

 

Thinking in a Nutshell:

1)      At time point T1, transaction log entries (e.g. A completed & committed transaction XACT1) have been written to redo log file

2)      At time point T2, in current environment, this batch of redo information has been archived to archived log file after a log switch. Whereas in contrast, in Plan B, it might just store in redo log file which means it has not been archived to archived log file yet.

3)      At time point T3, redo log file corrupted unexpectedly.

4)      At time point T4, in current environment, transaction XACT1 can be recovered from the archived log file. However, in Plan B, transaction XACT1 cannot be recovered because the redo log file containing this information was corrupted before archiving.

5)      There is a compromise between high performance and high availability.

6)      Reasonable value of redo log member size and check point setting should be discussed and decided to achieve the best performance and availability.

Simple thing is not always the easy thing. The more you think the more you learn, why not share your thoughts here?🙂

Tags: , , , , ,

One Response to “Thinking in Resizing Redo Log”

  1. dumpster rental raleigh Says:

    Way cool! Some extremely valid points! I appreciate you writing this post
    plus the rest of the website is really good.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: