Redo Block Size

Redo logs or online redo log files are crucial files for any oracle database to be operational with durability of transaction. All change vectors must be written to current redo log (except nologging and few exceptional settings where database is running purely for performance benchmark purpose)

Redo log files always default to a block size that is equal to the physical sector size of the disk. Historically, this has typically been 512 bytes (512B). Beginning with Oracle Database 11g Release 2, you can specify the block size of 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.

So we are going to check if we can try with this cool option. Let’s check the current block size of existing redo log files from block_size column of v$log view.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

———- ———- ———- ———- ———- ———- — ——————– ————- ——— ———— ———

1 1 16 52428800 512 1 YES INACTIVE 1124097 31-JUL-12 1131213 31-JUL-12

2 1 17 52428800 512 1 NO CURRENT 1131213 31-JUL-12 2.8147E+14

3 1 15 52428800 512 1 YES INACTIVE 1122827 25-JUL-12 1124097 31-JUL-12

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——————– ——- ———————————————————— —

3 ONLINE /u01/app/oracle/oradata/mask11g/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/mask11g/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/mask11g/redo01.log NO

Now try to add redo log file with 1024 byte blocksize.

SQL> alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 1024;

alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 1024

*

ERROR at line 1:

ORA-01377: Invalid log file block size

Now try to add redo log file with 2048 byte blocksize.

SQL> alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 2048;

alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 2048

*

ERROR at line 1:

ORA-01377: Invalid log file block size

Let’s check what oracle error 1377 is trying to say

# oerr ora 01377

01377, 00000, “Invalid log file block size”

// *Cause: An invalid value was specified in the BLOCKSIZE clause.

// *Action: Use correct syntax.

That’s pointing the wrong use of blocksize. Now try with 4096 bytes

SQL> alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 4096;

alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 4096

*

ERROR at line 1:

ORA-01378: The logical block size (4096) of file /u01/app/oracle/oradata/mask11g/redo04.log is not compatible with the disk sector size (media sector size is 512 and host sector size is 512)

This time we got different error which is saying that our disk sector size is 512, so we can’t use 4096. Oerr also giving explanation for the same

SQL> !oerr ora 01378

01378, 00000, “The logical block size (%s) of file %s is not compatible with the disk sector size (media sector size is %s and host sector size is %s)”

// *Cause: One of the following occurred:

// (1) An attempt was made to create a file.

// (2) A file was moved to disks with different sector size.

// *Action: Create file or move file to the proper disk.

So now need to check if we are allowed to use 512 bytes block size by manually passing during creation of redo.

SQL> alter database add logfile ‘/u01/app/oracle/oradata/mask11g/redo04.log’ size 50M blocksize 512;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

———- ———- ———- ———- ———- ———- — ——————– ————- ——— ———— ———

1 1 16 52428800 512 1 YES INACTIVE 1124097 31-JUL-12 1131213 31-JUL-12

2 1 17 52428800 512 1 NO CURRENT 1131213 31-JUL-12 2.8147E+14

3 1 15 52428800 512 1 YES INACTIVE 1122827 25-JUL-12 1124097 31-JUL-12

4 1 0 52428800 512 1 YES UNUSED 0 0

Key question about this feature is “Why oracle provided this option?”

Answer would be

New disks are coming with high capacity with more density, so sector size would be high for these disks. So from performance point of view if we allocate redo with 512 bytes blocksize on 4K sector disk then you will incur significant performance degradation when a redo log write is not aligned with the beginning of the 4K physical sector. Because seven out of eight 512B slots in a 4K physical sector are not aligned, performance degradation typically does occur.

Going with higher blocksize will cause more wastage in redo, so it would be choice of architect if they want to go with performance or want to avoid wastage of redo.

SQL> SELECT name, value FROM v$sysstat where name in (‘redo size’,’redo wastage’);

NAME VALUE

—————————————————————- ———-

redo size 16310348

redo wastage 529776


Advertisements

5 Responses to Redo Block Size

  1. Nitan Kainth says:

    So it means we need storage with bigger sector size to use this functionality?? In that case, how can we find out sector of my current storage??

    • There are multiple ways to check

      fdisk -l
      ————-

      Disk /dev/sdb: 8589 MB, 8589934592 bytes
      255 heads, 63 sectors/track, 1044 cylinders
      Units = cylinders of 16065 * 512 = 8225280 bytes ===========> Its showing 512 bytes

      Device Boot Start End Blocks Id System
      /dev/sdb1 1 1045 8388607+ 8e Linux LVM

      max_sectors_kb file
      ———————–

      [root@mask11g queue]# cat /sys/block/sdb/queue/max_sectors_kb
      512

      There could be many more, but these two are easiest one.

      Regards,
      Amit S.

  2. Pingback: Confluence: Productie

  3. Sunita Nandi says:

    Amit I’m proud to say that I have worked as a DBA with you. Amazing work done… keep it up 🙂

Share Your Comment

Please log in using one of these methods to post your comment:

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

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: