Minimum Size of Redo Logfiles and Their Block Size

I was just wondering what could be minimum size of the redo logfile possible.So instead of looking out for the docs, i thought of trying it out myself on the test environment.

12:03:54 SQL>@ver
FROM v$VERSION

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

12:04:54 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        917   10485760          1 NO  ACTIVE                 2600657 10-NOV-10
         2          1        918   10485760          1 NO  CURRENT                2600659 10-NOV-10
         3          1        916   10485760          1 NO  INACTIVE               2600655 10-NOV-10

Elapsed: 00:00:00.07
12:04:54 SQL>
12:04:55 SQL>
12:04:55 SQL> show parameter block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     16

12:06:04 SQL> alter database add logfile group 4 ('D:\DB\REDO04a.LOG','D:\DB\REDO04b.LOG') size 2M;
alter database add logfile group 4 ('D:\DB\REDO04a.LOG','D:\DB\REDO04b.LOG') size 2M
*
ERROR at line 1:
ORA-00336: log file size 4096 blocks is less than minimum 8192 blocks

Ohhhh, i got an error “ORA-00336: log file size 4096 blocks is less than minimum 8192 blocks

Lets increase the size and see

12:05:22 SQL> alter database add logfile group 4 'D:\DB\REDO04a.LOG' size 3M;
alter database add logfile group 4 'D:\ORACLE\TEST\REDO04a.LOG' size 3M
*
ERROR at line 1:
ORA-00336: log file size 6144 blocks is less than minimum 8192 blocks

12:05:29 SQL> alter database add logfile group 4 'D:\DB\REDO04a.LOG' size 4M;

Database altered.

So, 4MB is the minimum size of the redo logfile.

Interestingly, the error shows “8192 blocks“, means each redo logfile has to have minimum of 8192 blocks and the minimum size is 4MB which leads to the conclusion that each block is of 512 Bytes size..
But my database block size is 8KB??? So am i saying something wrong?? What i did was a simple arithmetic calculation.

The block size of 8Kb is applicable to only the datafiles in the database.The redo log files and the control files use a different block size.I know by now you would definitely be thinking i have gone mad 🙂 😉

Lets check this out –

12:06:55 SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

12:07:33 SQL> select distinct(BLOCK_SIZE) from v$datafile;

BLOCK_SIZE
----------
      8192

The datafile block size definitely matches to db_block_size.What about the controlfile and the redo logfile.

12:09:46 SQL> select distinct(BLOCK_SIZE) from v$controlfile;

BLOCK_SIZE
----------
     16384

Block size for the controlfiles in 16 Kb.
The view v$log doesn't have any column which shows the block size.To check the size we need to dig into internal table "x$kccle" .

x$kccle stands for [K]ernal [C]ache [C]ontrol [L]og File [E]ntries .The column lebsz may be used to show redo logfile block size.

12:10:16 SQL> select distinct(lebsz) from X$kccle;

     LEBSZ
----------
       512

The above confirms block size of redo logfiles to be 512 Bytes, which we had calculated using some simple arithmetic 🙂

To read more on this, refer to below link
http://blog.aristadba.com/?p=124

Thanks to Aman Sir 🙂

Is it the same in 9i version of the database.What is the possible minimum value of the redo logfile?

13:04:33 OFAMRHFL83 >@ver
FROM v$VERSION

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

13:05:09 SQL >alter database add logfile group 4 'D:\ORACLE\ORADATA\REDO04.LOG' size 10K;

Database altered.

Elapsed: 00:00:00.01
13:05:37 SQL >alter database drop logfile group 4;

Database altered.

Elapsed: 00:00:00.00
13:05:41 SQL>alter database add logfile group 4 'D:\ORACLE\ORADATA\REDO04.LOG' size 1K;
alter database add logfile group 4 'D:\ORACLE\ORADATA\REDO04.LOG' size 1K
*
ERROR at line 1:
ORA-00336: log file size 2 blocks is less than minimum 4 blocks


Elapsed: 00:00:00.00
13:05:59 SQL >alter database add logfile group 4 'D:\ORACLE\ORADATA\REDO04.LOG' size 2K;

Database altered.

Elapsed: 00:00:00.00

So in 9i the minimum size is 2k and minimum blocks 4, which again confirms block size to be 512 Bytes.

References:-

http://blog.aristadba.com/?p=124
http://oracle-abc.wikidot.com/x-tables

UPDATE – (13-NOV-2010)
======================

Posting the output of 10.2.0.4 db on 64-bit Sun Solaris 10,

bash-3.00$ isainfo -kv
64-bit sparcv9 kernel modules
bash-3.00$ 
12:01:09 EMTEST >@ver

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.00

12:01:15 EMTEST >select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         2         ONLINE  /EMTEST/oradata/EMTEST/redo02.log                    NO
         1         ONLINE  /EMTEST/oradata/EMTEST/redo01.log                    NO
         3         ONLINE  /EMTEST/oradata/EMTEST/redo03.log                    NO

Elapsed: 00:00:00.00
12:01:15 EMTEST >select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        736   52428800          1 NO  INACTIVE            2.3107E+10 13-NOV-10
         2          1        737   52428800          1 NO  INACTIVE            2.3107E+10 13-NOV-10
         3          1        738   52428800          1 NO  CURRENT             2.3117E+10 13-NOV-10

Elapsed: 00:00:00.00
12:01:16 EMTEST >
12:01:32 EMTEST >select distinct lebsz from x$kccle;

     LEBSZ
----------
       512
    
Elapsed: 00:00:00.00
12:02:27 EMTEST >

Hence,512 bytes is not Windows 32bit environment specific.Above in the post i have already attached Aman Sir’s blog link which explains the reason for 512Bytes size of redo logfile.

Advertisements

7 thoughts on “Minimum Size of Redo Logfiles and Their Block Size

  1. Hi,

    Evenif you set blocksize at database level, It’s your OS which provide actual blocksize. The OS blocksize of Windows 32 bit environment is 512 bytes. The minimum permissible size of online redo log for 10g is 4MB. So 4MB/512 ~ 8192 blocks.
    Controlfile and online redo log blocksize is also picked from OS, but how oracle calculates that I need to explore.

    DB_BLOCK_SIZE strictly controls only the size for system tablespaces, other tablespaces may have different block size.

    Regards,
    Santosh

    1. Hi Santosh,

      Well, the 512 Bytes is not specific to Windows 32 bit environment.Even Sun Solaris 10,64 bit OS will show 512 Bytes from x$kccle internal table.

      As far as “how oracle calculates that I need to explore.”, i have already attached Aman Sir’s blog link.You can find the reason for it.

      Regards,
      Anand

      1. Hi Anand,

        Unfortunately, either I am misinterpretating what you are trying to say(my fault) or you didn’t get my point. There is clear distinction between our versions. Please forgive me, I’m interfering you again.

        Please help me in understanding following behaviour:

        desectest:tstdpsec:/oracle/ora10g>df -g /dpsecure/data1
        /dpsecure/data1    <b>(dpsecpool/data1)*:       131072 block size</b>           512 frag size
        52428800 total blocks    7375761 free blocks  7375761 available        7375783 total files
         7375761 free files     67174415 filesys id
             zfs fstype       0x00000004 flag             255 filename length
        
        The block size of the partition is 128k
        
        <b>I'm using Solaris 5.10 and my database has 4 online redo logs having size of 40M. The DB_BLOCK_SIZE=8k</b>
        
        So 1 OS block will contain 16 DB Blocks.
        SQL> select member from v$logfile;
        
        MEMBER
        ----------------------------------------------
        /dpsecure/data1/axdpsec/redo_axdpsec_4a.log
        /dpsecure/data2/axdpsec/redo_axdpsec_4b.log
        /dpsecure/data3/axdpsec/redo_axdpsec_3a.log
        /dpsecure/data1/axdpsec/redo_axdpsec_3b.log
        /dpsecure/data2/axdpsec/redo_axdpsec_2a.log
        /dpsecure/data3/axdpsec/red0_axdpsec_2b.log
        /dpsecure/data1/axdpsec/redo_axdpsec_1a.log
        /dpsecure/data2/axdpsec/redo_axdpsec_1b.log
        
        8 rows selected.
        
        SQL> select lebsz from X$kccle;
        
             LEBSZ
        ----------
               512
               512
               512
               512
        
        SQL> !
        $ cd /dpsecure/data1/axdpsec/
        $ ls -lrt *.log
        -rw-r-----   1 ora10g   oinstall 41943552 Nov 12 15:51 redo_axdpsec_3b.log
        -rw-r-----   1 ora10g   oinstall 41943552 Nov 12 15:51 redo_axdpsec_4a.log
        -rw-r-----   1 ora10g   oinstall 41943552 Nov 12 17:07 redo_axdpsec_1a.log
        
        Now I'm adding one more redo log which has 1 byte more size than 4MB. Let's see what happens:
        
        4MB + 1 Byte = 4194305 Bytes
        
        SQL> alter database add logfile group 12('/dpsecure/data1/axdpsec/redo_axdpsec_12.log') size 4194305;
        
        Database altered.
        
        So it should consume 8192+1 blocks, right?
        
        SQL> select lebsz from X$kccle;
        
             LEBSZ
        ----------
               512
               512
               512
               512
                 0
                 0
                 0
                 0
                 0
                 0
                 0
               512
        
        12 rows selected.
        
        Tried querying on database level:
        
        SQL> select GROUP#,bytes from v$log;
        
            GROUP#      BYTES
        ---------- ----------
                 1   41943040
                 2   41943040
                <b>12    4194816</b>
                 4   41943040
                 3   41943040
        
        So approx 4194816 / 512 ~ 8226 DB Blocks
        
        On OS level:
        
        $ cd /dpsecure/data1/axdpsec/
        $ ls -lrt *.log
        -rw-r-----   1 ora10g   oinstall 41943552 Nov 12 15:51 redo_axdpsec_3b.log
        -rw-r-----   1 ora10g   oinstall 41943552 Nov 12 15:51 redo_axdpsec_4a.log
        -rw-r-----   1 ora10g   oinstall 4195328 Nov 12 17:13 redo_axdpsec_12.log
        -rw-r-----   1 ora10g   oinstall 41943552 Nov 12 17:13 redo_axdpsec_1a.log
        
        So around 4195328 / 131072 ~ 33 OS blocks allocated for log group 12.
        

        @Well, the 512 Bytes is not specific to Windows 32 bit environment.Even Sun Solaris 10,64 bit OS will show 512 Bytes from x$kccle internal table.

        I don’t have, but if you’ve HP-UX system then check on that.

        @ As far as “how oracle calculates that I need to explore.”, i have already attached Aman Sir’s blog link.You can find the reason for it.

        :). I have left a comment there and after waiting for 2 days for moderation, it has been published by Aman Sir. Please re-visit the blog entry.

        With best regards,
        Santosh

      2. Hi,

        1. If i am seeing correctly,even in your example the redo logfile’s block size is 512Bytes.Now,it could be either it is hard coded internally, or there is some other mechanism (need to find out other than as suggested by Aman Sir) with which oracle defines the block size for the redo logfile.

        2. Even if you add a extra byte whole 512bytes (1 block size) will be allocated to the redo logfile.

        4194816 – 4194304 = 512Bytes

        If you added 513bytes to 4MB which is 4194817 and create a logfile 2 blocks of 512bytes (redo logfile block size) will be allocated.

        3. ‘I don’t have, but if you’ve HP-UX system then check on that” — Interestingly the example which you have shown is from Sun Solaris10 (which you say “you don’t have”).It would have been great if you would have done the same from HP-UX instead of Sun Solaris.I am not having a HP-UX system currently.

        4. “after waiting for 2 days for moderation, it has been published by Aman Sir.”. — I believe you need not mention when or how much time it took for moderation. Everyone is busy with their professional and personal life.Writing a blog is not to showoff anyone’s knowledge(might be for few),its a way of sharing as well as enhancing owns knowledge.Its about sharing one’s own view which might be not be totally correct but others might help them with it.No-one spends all time sitting awaiting for comments or doing moderation.

        5. I have got checked the “lebsz” column value for a HP-UX env and yes its 1024Bytes.So, definitely there is another way with which Oracle decides the size.You must have attached the forum link http://forums.oracle.com/forums/thread.jspa?threadID=557236 which you have attached in Aman Sir’s blog.

        6. I would update the blog with the forum link which i found useful.

        Regards,
        Anand

  2. Hi,

    I am not having any system currently check the reason for varying block size of the redo logfiles depending on the OS.But with little more search, i think it depends on the MINIMUM physical I/O size of the OS. For HP-UX, the minimum physical I/O possible is 1Kb which is the block size for the redo logfile.

    Any input from your side is welcomed.

    Regards,
    Anand

    1. Hi,

      For the time being I would say:

      It’s hard coded for a particular platform, evenif we have different block size.If you ask on what grounds it is set, I would say I don’t know. I will discuss the same with my colleagues and if I find any convincing answer I will let you know.

      As far as I know JFS and UFS (on HP-UX) perform IO in the chunk of 64k. I may be wrong.

      Regards,
      S.K>

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