Scribd Logo
Upload

Welcome to Scribd!

  • 380 views

    How To Find Block Sizes of All Oracle Database Files?

    Uploaded by

    OrSklAcademy
    We must have known already that block sizes of non-system tablespaces can be controlled and altered, but did you ever think of block sizes of Control files and Redo log files in the database? How to check their block sizes if they have so, can we alter them when required? Well, it is really very imp...

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    How To Find Block Sizes of All Oracle Database Files?

    Uploaded by

    OrSklAcademy
    380 views5 pages
    We must have known already that block sizes of non-system tablespaces can be controlled and altered, but did you ever think of block sizes of Control files and Redo log files in the database? How to check their block sizes if they have so, can we alter them when required? Well, it is really very imp...

    Original Title

    How to find block sizes of all Oracle Database files?

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    We must have known already that block sizes of non-system tablespaces can be controlled and altered, but did you ever think of block sizes of Control files and Redo log files in the database? How to check their block sizes if they have so, can we alter them when required? Well, it is really very imp...

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    380 views5 pages

    How To Find Block Sizes of All Oracle Database Files?

    Uploaded by

    OrSklAcademy
    We must have known already that block sizes of non-system tablespaces can be controlled and altered, but did you ever think of block sizes of Control files and Redo log files in the database? How to check their block sizes if they have so, can we alter them when required? Well, it is really very imp...

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 5

    How to find block sizes of all Oracle

    Database files?

    We must have known already that block sizes of non-system tablespaces


    can be controlled and altered, but did you ever think of block sizes of
    Control files and Redo log files in the database? How to check their block
    sizes if they have so, can we alter them when required? Well, it is really
    very important for us to understand these block level concepts.
    I did not get some chance to look at this area until I faced a performance
    issue on one of the database environments I work. Through the process of
    looking at the issue, I could notice that block size of redo log files is not
    compatible to the underlined attached storage. This problem is one such
    real time example of the topic we discussed earlier How important is disk
    type for Oracle database files?.
    In this blog, we will discuss only block sizes of control files and redo log
    files as modifying block sizes on tablespace level is something I hope we
    would have known it already.
    System consideration:
    We will be using database server for our next phase of discussion with
    following configuration:

    OS: Oracle Enterprise Linux


    Database: Oracle 12c database

    Type: Non-CDB database

    Control file block size:


    Before we actually look at identifying the block size of control file, let us
    see the default block size of the database.
    SQL> show parameter db_block_size
    NAME

    TYPE

    VALUE

    db_block_size integer

    8192

    It is 8KB on my system, alright now let us check the block size of the
    control files on this database server.
    SQL> select BLOCK_SIZE from v$controlfile;
    BLOCK_SIZE
    16384
    16384
    There are two control files on my system each of 16KB size, Aww!!! Should
    it not be the default block size of 8KB? Well, you see that right. Oracle
    says that irrespective of block size supported by OS or storage
    control file block size will always remain 16KB and that cannot be
    altered.
    Will this create any issue? I dont see that this is as problematic until
    unless your minimum block size at your operating system is more than
    16KB ex: 32KB. When minimum block size at the OS level is 4KB or 8KB
    then Oracle can bundle 4 blocks or 2 blocks respectively to create one
    logical block for the control file. Interesting isnt it!!!
    Redo log block size:

    Block size of redo log files are bit different from control files which we
    have seen so far. Let us quickly check the current block size of all the redo
    logs on my server. Note that I did not specify any block size of the redo
    logs when I created this database, so the output below is the default size
    created by DBCA.
    SQL> select BLOCKSIZE from v$log;
    BLOCKSIZE
    512
    512
    512
    Thats 512B on my system. Just like control file, block size of redo log is
    not same as the default database block size(8KB).
    But can we have a redo log files of different block sizes, I tried creating
    two other groups with 1KB , 4KB and 8KB.
    SQL> alter database add logfile group 4 size 5M blocksize 1024;
    ERROR at line 1:
    ORA-01377: Invalid log file block size
    SQL> alter database add logfile group 4 size 5M blocksize 4096;
    ERROR at line 1:
    ORA-01378: The logical block size (4096) of file
    /pdbdata/ORACDB3/onlinelog/o1_mf_4_%u_.log is not compatible with the
    disk
    sector size (media sector size is 512 and host sector size is 512)
    SQL> alter database add logfile group 4 size 5M blocksize 8192;

    ERROR at line 1:
    ORA-01377: Invalid log file block size
    Oracle says that only possible block sizes for redo logs are 512, 1024 and
    4096 bytes.

    For 1024, my database server errors out as invalid block size just

    because my OS platform doesnt support it. So 1024 is valid only on


    few OS platforms.
    For 8192, as database itself doesnt support we see the same error

    as invalid block size.


    For 4096, there is some interesting error showing us the media

    sector size on the disk is 512 so it cannot create a redo log with block
    size as 4096.
    In your environment if redo log block size is 4096 then it means that if
    media sector supports 4096 bytes of block size then oracle automatically
    identifies and creates redo logs with block size as 4096.
    Will this create any issue? Having redo log block size of 4096 bytes
    results in redo log wastage compared to 512 bytes. There will be
    tremendous performance issue if your disk is not aligned with the block
    size of your redo log blocks.
    This was the same reason why one my database environment was
    performing bad, it was because of 4KB of redo log block size and there
    was lot of redo wastage happening in the database. I could recognize the
    redo wastage from V$SYSSTAT.
    SQL> SELECT name, value FROM v$sysstat WHERE name = redo
    wastage';
    NAME

    VALUE

    redo wastage

    17941684

    Key points:

    With 512 bytes of media sectors we still have Control file block size
    as 16KB, as it logically binds 32 sectors to one block. But this is not
    possible in redo log.

    Having 4KB block size of redo log files will not degrade the

    performance of the database always. But there is chance for this to


    happen if your storage disk is not compatible with such setup.
    Changing already existing redo log file block size is possible from

    11gR2 only if redo log group is inactive.


    Each redo log group can have different block sizes if media
    supports, but never a good practice.

    You might also like