Problem:

Alert log contains: 

WARNING! THE SPECIFIED LOGFILE BLOCKSIZE (512) IS LESS THAN THE MEDIA SECTOR SIZE ON DISK (4096). LOGFILE WRITES ARE NOT SECTOR ALIGNED. THIS MAY LEAD TO DEGRADED PERFORMANCE DUE TO READ-MODIFY-WRITE OPERATION

Reason/Reproduce:

The redo log groups have the default block size 512. So as the error mentions, the size of the redo log group member is less than the sector size attribute for the diskgroup.

This is reproducable when disk physical block size is 4096. In AWS environment, for example, r5b instances has this size for attached disks (this may change in future, need to be checked while reading this post).

1. Make sure attached disks has physical size 4096

[root@rac1 ~]# find /sys/block/*/queue -name physical_block_size | while read f ; do echo "$f $(cat $f)" ; done  /sys/block/nvme0n1/queue/physical_block_size 4096 /sys/block/nvme1n1/queue/physical_block_size 4096 /sys/block/nvme2n1/queue/physical_block_size 4096 /sys/block/nvme3n1/queue/physical_block_size 4096 /sys/block/nvme4n1/queue/physical_block_size 4096 /sys/block/nvme5n1/queue/physical_block_size 4096

2. Create diskgroup with the sector size 4096

CREATE DISKGROUP "DG4K" NORMAL REDUNDANCY                                                  FAILGROUP "RAC1" DISK '/dev/flashgrid/rac1.xvdbd' NAME "RAC1$XVDBD" SIZE 10240M   FAILGROUP "RAC2" DISK '/dev/flashgrid/rac2.xvdbd' NAME "RAC2$XVDBD" SIZE 10240M   QUORUM FAILGROUP "RACQ" DISK '/dev/flashgrid/racq.xvdbz' NAME "RACQ$XVDBZ"        ATTRIBUTE 'au_size' = '4M', 'compatible.asm' = '19.0.0.0', 'compatible.rdbms' = '19.0.0.0', 'sector_size'='4096';

Make sure sector size is 4096 for this diskgroup:

SQL> select name,sector_size from v$asm_diskgroup;  NAME			       SECTOR_SIZE ------------------------------ ----------- DATA				       512 GRID				       512 DG4K				      4096 FRA				       512

Check log group block size:

SQL> select group#, blocksize from v$log;      GROUP#  BLOCKSIZE ---------- ---------- 	 1	  512 	 2	  512 	 3	  512 	 4	  512

Add a new logfile member on DG4K:

SQL> alter database add logfile member '+DG4K' to group 4;  Database altered.

Check alert log:

2021-12-09T15:32:04.533204+00:00 WARNING! THE SPECIFIED LOGFILE BLOCKSIZE (512) IS LESS THAN THE MEDIA SECTOR SIZE ON DISK (4096). LOGFILE WRITES ARE NOT SECTOR ALIGNED. THIS MAY LEAD TO DEGRADED PERFORMANCE DUE TO READ-MODIFY-WRITE OPERATIONS. 2021-12-09T15:32:21.080534+00:00 Completed: alter database add logfile member '+DG4K' to group 4

Solution:

You should create a new redo log group (not member, but group) with block size=4K:

SQL> alter database add logfile group 5 ('+DG4K') size 512M blocksize 4096;
 This post is ad-supported
​