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 [email protected]> 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 [email protected]> 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 [email protected]>
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 [email protected]> 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 [email protected]> 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
10 thoughts on “Redo Logfiles BlockSize – Revisited in 11gR2”
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
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
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
>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 😀 ..Have updated.
Anand
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