Redo Logfiles BlockSize – Revisited in 11gR2

In November 2010 i had written blog “Minimum Size of Redo Logfiles and Their Block Size” where i mentioned that 512Bytes as the size of redo logfiles, which was not the totally correct to say.Oracle will use the underlying disk sector size as the BLOCKSIZE of redo logfiles which can differ from platform to platform.

From 11gR2, Oracle has added BLOCKSIZE column in v$LOG view,which displays the block size of the logfile. :)

14:39:21 SYS@MATRIX> select * from v$version;

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

Elapsed: 00:00:00.10
14:39:32 SYS@MATRIX> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1        351   52428800        512          1 YES INACTIVE               5153979 18-FEB-11      5189563 18-FEB-11
         2          1        352   52428800        512          1 YES INACTIVE               5189563 18-FEB-11      5207754 19-FEB-11
         3          1        354   52428800        512          1 NO  CURRENT                5207790 19-FEB-11   2.8147E+14
         4          1        353   52428800        512          1 YES INACTIVE               5207754 19-FEB-11      5207790 19-FEB-11

Elapsed: 00:00:00.09
14:39:35 SYS@MATRIX>

The Controlfile backup look like :-

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MATRIX" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\APP\ORADATA\MATRIX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\APP\ORADATA\MATRIX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\APP\ORADATA\MATRIX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 'D:\ORACLE\APP\ORADATA\MATRIX\REDO04.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 5 'D:\ORACLE\APP\ORADATA\MATRIX\STDBYREDO.LOG'  SIZE 50M BLOCKSIZE 512,
--   GROUP 6 'D:\ORACLE\APP\ORADATA\MATRIX\STDBYREDO2.LOG'  SIZE 50M BLOCKSIZE 512,
--   GROUP 7 'D:\ORACLE\APP\ORADATA\MATRIX\STDBYREDO4.LOG'  SIZE 50M BLOCKSIZE 512,
--   GROUP 8 'D:\ORACLE\APP\ORADATA\MATRIX\STDBYREDO3.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\APP\ORADATA\MATRIX\SYSTEM01.DBF',
  'D:\ORACLE\APP\ORADATA\MATRIX\SYSAUX01.DBF',
  'D:\ORACLE\APP\ORADATA\MATRIX\UNDOTBS01.DBF',
  'D:\ORACLE\APP\ORADATA\MATRIX\USERS01.DBF',
  'D:\ORACLE\APP\ORADATA\MATRIX\EXAMPLE01.DBF',
  'D:\ORACLE\APP\ORADATA\MATRIX\TEST_01.DBF',
  'D:\ORACLE\APP\ORADATA\MATRIX\TEST_02.DBF'
CHARACTER SET WE8MSWIN1252
;

If you try to create redo logs with a nk sector size, when your machine only supported 512 byte sectors, then log creation will fail with ORA-1377.

14:40:11 SYS@MATRIX> alter database add logfile group 5 ('D:\ORACLE\APP\ORADATA\MATRIX\REDO05A.LOG','D:\ORACLE\APP\ORADATA\MATRIX\REDO05B.LOG') size 50M blocksize 1024;
alter database add logfile group 5 ('D:\ORACLE\APP\ORADATA\MATRIX\REDO05A.LOG','D:\ORACLE\APP\ORADATA\MATRIX\REDO05B.LOG') size 50M blocksize 1024
                                                                                                                                                 *
ERROR at line 1:
ORA-01377: Invalid log file block size


Elapsed: 00:00:00.12
15:22:51 SYS@MATRIX> alter database add logfile group 5 ('D:\ORACLE\APP\ORADATA\MATRIX\REDO05A.LOG','D:\ORACLE\APP\ORADATA\MATRIX\REDO05B.LOG') size 50M blocksize 256;
alter database add logfile group 5 ('D:\ORACLE\APP\ORADATA\MATRIX\REDO05A.LOG','D:\ORACLE\APP\ORADATA\MATRIX\REDO05B.LOG') size 50M blocksize 256
                                                                                                                                                *
ERROR at line 1:
ORA-01377: Invalid log file block size


Elapsed: 00:00:00.07
About these ads
  1. And I guess, I have mentioned the same in my blog post too :) .

    Aman….

    • Hi Sir,

      Thanks for visiting!!!!Yes, just checked back your blog. :)

      Anand

  2. >In November 2011 i had written blog “Minimum Size of Redo Logfiles and Their Block Size”

    That is yet to come ;)

    • Thanks for catching it :D ..Have updated.

      Anand

    • john
    • November 16th, 2011

    Hello
    Assume,
    db_create_file_dest=+DATA
    db_create_online_log_dest_1=+LOGS
    db_create_online_log_dest_2=+FRA

    If I issue, alter database add logfile clause,

    Does it create a new logfile in the +DATA diskgroup, logfile in the +LOGS disk group, and a logfile in the +FRA disk group.
    or
    a new logfile in the +LOGS disk group, and a logfile in the +FRA disk group
    ?

    • Hi,

      When a new log group is added, it would have one member in each +LOGS and +FRA diskgroups. As db_create_online_log_dest_N is set, db_create_file_dest won’t come in play.

      Regards,
      Anand

    • john
    • November 16th, 2011

    Hi Anand,

    Thanks for your help.
    This was my exam question last week and still not sure about the answer.
    Any help is much appreciated.

    Identify the three valid storage options for Grid Infrastructure voting disk and install

    a-)a certified cluster file system (CFS)
    b-)a certified network file system (NFS)
    c-)ASM cluster file system (ACFS)
    d-)Automatic Storage Management (ASM)
    e-)shared disk slices (block or raw device)

    • Hi John,

      The answer of it –> a,b and d. From 11gR2 we can’t use Raw devices. Raw devices are only permitted in case of upgrade from some lower version. ACFS is used for normal regular files, it can’t be used for database home/grid home or ocr and voting disk.

      Anand

    • john
    • November 17th, 2011

    Thanks Anand,Appreciate your assistance.

    I do have one last incorrect question about transparent application failover
    I believe “a” is definetely correct but not sure about the other two.
    I couldnt find the answer in the oracle manuals.
    Your thought ?

    Which three statements are true about services and transparent application failover (TAF)

    A-)TAF has been configured for a service, sessions using that service fail over to a surviving instance when an outage occurs
    B-)The TAF setting on a service can be none, basic, preconnect or postconnect and overrides and TAF setting in the client connection definition.
    C-)TAF can restart a query after failover has completed but for other statements such as insert,update or delete, the application must resubmit the transaction
    D-)The Taf setting for a client connection overrides any TAF setting in the service definition
    E-)Services simplify the deployment of TAF because definining a TAF policy for a service, all connections using this service will automatically have TAF enabled

    • Hi,

      I think the correct ans would be -> a,d and e

      Anand

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 462 other followers

%d bloggers like this: