Oracle

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.

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

  1. 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>

  2. 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:
        [code]
        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.
        [/code]
        @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

        1. 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

Leave a Reply