cesardba

ORA-15347: logical block size 512 of ASM file ‘+FRA’ is too small for disk group sector size 4096

Esta semana um amigo me pediu ajuda para resolver um problema. A empresa onde ele trabalha migrou, há algum tempo, o banco de dados produtivo de on-premise para Azure IaaS. Porém, durante a migração, a equipe anterior não conseguiu configurar a geração de archivelogs no ASM disk group +FRA devido ao erro ORA-15347. Abaixo seguem os detalhes do problema.

Local atual da geração de archivelogs:

SQL> show parameter db_reco

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest     string /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/
db_recovery_file_dest_size     big integer 180G

Foi alterado o local do db_recovery_file_dest para +FRA:

SQL> alter system set db_recovery_file_dest='+FRA';

System altered.

Ao realizar o switch logfile para testes, o ambiente congelava, verifiquei o alertlog em busca de erros:

SQL> alter system switch logfile;

O Alert log apresentou as mensagens de erros ao realizar o switch logfile anteriormente executado:

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_tt00_14240.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15347: logical block size 512 of ASM file '+FRA' is too small for disk group sector size 4096
2023-10-11T23:13:31.960091+00:00


ARC1 (PID:14247): Unable to create archive log file '+FRA'
2023-10-11T23:13:32.390661+00:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_14247.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15347: logical block size 512 of ASM file '+FRA' is too small for disk group sector size 4096

Os erros encontrados apontavam para o tamanho do sector size 4096, abaixo a verificação do tamanho do block size físico e lógico dos discos envolvidos no disk group +FRA.

[root@bk001 block]# lsblk -t
NAME   ALIGNMENT MIN-IO OPT-IO PHY-SEC LOG-SEC ROTA SCHED       RQ-SIZE   RA WSAME
sdf            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdf1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
sdd            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdd1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
sdb            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdb1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
sdi            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdi1      3072   4096      0    4096     512    1 mq-deadline     256 4096    0B
sdg            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdg1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
fd0            0    512      0     512     512    1 deadline        128  128    0B
sde            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sde1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
sdc            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdc1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
sda            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
├─sda2         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sda1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B
sdh            0   4096      0    4096     512    1 mq-deadline     256 4096    0B
└─sdh1         0   4096      0    4096     512    1 mq-deadline     256 4096    0B

Como pude verificar, o tamanho do bloco físico (PHY-SEC) é de 4096 bytes, enquanto o bloco lógico (LOG-SEC) é de 512 bytes. A primeira hipótese que considerei foi que, no ambiente anterior à migração para o Azure, os discos do +FRA utilizavam blocos de 512 bytes. Durante a migração, essa informação não foi validada, e devido a essa mudança a equipe anterior não conseguiu configurar a geração de archivelogs no ASM, optando como alternativa por armazená-los no file system.

Verificando os grupo de redo log do database:

SQL> select sequence#,group#, members,FIRST_CHANGE#, bytes/1024/1024, status from v$log order by 2;

 SEQUENCE#     GROUP# MEMBERS FIRST_CHANGE# BYTES/1024/1024 STATUS
---------- ---------- ---------- ------------- --------------- ----------------
       397    1       1    9758107265   128 INACTIVE
       398    2       1    9758107268   128 INACTIVE
       399    3       1    9758107271   128 CURRENT
       396    4       1    9758100928   128 INACTIVE

Ao verificar o blocksize dos grupos de redo log, verifiquei que estavam com 512 bytes:

SQL> select lebsz from X$kccle;
     LEBSZ
----------
       512
       512
       512
       512
       
SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE from v$log;
GROUP#    THREAD#     SEQUENCE#  BYTES      BLOCKSIZE
---------- ---------- ---------- ---------- ----------
 1         1  	      477681  	 134217728   5121
 2         1  	      477682     134217728   5121
 3         1  	      477683     134217728   5121
 4         1  	      477680     134217728   5121

Como pude observar, os grupos de redo logs foram criados com blocos de 512 bytes no ambiente anterior, antes da migração para o Azure. Devido a essa diferença, não foi possível utilizar o disk group +FRA com blocksize de 4096 bytes, já que o tamanho do bloco do ASM é maior que o bloco definido nos grupos de redo logs.

Agora que ja encontrei o problema, voltei o db_recovery_file_dest para o file system e executei o switch logfile para comprovar que o problema era esse, foi executado sem erros:

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/';

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

Para resolver este problema, criei dois novos grupos de redo log, grupo 5 e 6 no disk group +FRA, por default esses dois novos grupos irão ser criados com o blocksize default que é 4096 do disk group de destino +FRA:

SQL> alter database add logfile group 5 '+FRA/ORCL/redo05.log' size 500M;

Database altered.

SQL> alter database add logfile group 6 '+FRA/ORCL/redo06.log' size 500M;

Database altered.

Os novos grupos 5 e 6 foram criados com blocksize de 4096:

SQL> select lebsz from X$kccle;
     LEBSZ
----------
       512
       512
       512
       512
       4096
       4096
       
SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE from v$log where GROUP#>=5;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE
---------- ---------- ---------- ---------- ----------
 5         1        434          524288000  40961
 6         1        433          524288000  40961

Após a criação dos novos grupos de redolog, executei o checkpoint e switch logfile para efetuar o drop das redo log que não estão no diskgroup +FRA, grupos 1,2,3,4:

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>  select sequence#,group#, members,FIRST_CHANGE#, bytes/1024/1024, status from v$log order by 2;

 SEQUENCE#     GROUP# MEMBERS FIRST_CHANGE# BYTES/1024/1024 STATUS
---------- ---------- ---------- ------------- --------------- ----------------
       397    1       1    9758107265   128 INACTIVE
       398    2       1    9758107268   128 INACTIVE
       399    3       1    9758107271   128 INACTIVE
       402    4       1    9758108684   128 CURRENT
       400    5       1    9758108628   500 INACTIVE
       401    6       1    9758108631   500 ACTIVE
       
 6 rows selected.
 
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 4;

Database altered.

Agora que só existem os dois grupos de redologs 5 e 6 criados no +FRA, iremos alterar o db_recovery_file_dest para o asm no disk group +FRA, e realizar o switch logfile: 

SQL> alter system set db_recovery_file_dest='+FRA';

System altered.


SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;


System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Como pude comprovar através dos logs no alertlog, ao criar o os novos grupos de redologs no disk group +FRA, o problema de tamanho de sector size foi resolvido, este ambiente foi migrado com o block size nos grupos de redo log com 512 bytes no ambiente on-premise para o Azure.

Switch do logfile utilizando o db_recovery_file_dest com +FRA:

[oracle@bk001 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 11 23:37:27 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show parameter db_reco

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest     string +FRA
db_recovery_file_dest_size     big integer 180G



SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

Aler tlog sem erros:

Checkpoint not complete
  Current log# 5 seq# 426 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:46:51.072881+00:00
Thread 1 advanced to log sequence 427 (LGWR switch)
  Current log# 6 seq# 427 mem# 0: +FRA/ORCL/redo06.log
2023-10-11T23:46:51.375067+00:00
ARC3 (PID:14251): Archived Log entry 1435058 added for T-1.S-426 ID 0x63f7acc7 LAD:1
2023-10-11T23:46:54.180623+00:00
Thread 1 advanced to log sequence 428 (LGWR switch)
  Current log# 5 seq# 428 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:46:54.323308+00:00
ARC0 (PID:14242): Archived Log entry 1435059 added for T-1.S-427 ID 0x63f7acc7 LAD:1
2023-10-11T23:46:55.174871+00:00
Thread 1 cannot allocate new log, sequence 429
Checkpoint not complete
  Current log# 5 seq# 428 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:46:57.329243+00:00
Thread 1 advanced to log sequence 429 (LGWR switch)
  Current log# 6 seq# 429 mem# 0: +FRA/ORCL/redo06.log
2023-10-11T23:46:57.557635+00:00
ARC1 (PID:14247): Archived Log entry 1435060 added for T-1.S-428 ID 0x63f7acc7 LAD:1
2023-10-11T23:47:16.093373+00:00
Thread 1 advanced to log sequence 430 (LGWR switch)
  Current log# 5 seq# 430 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:47:16.235492+00:00
ORACLE Instance orcl - Cannot allocate log, archival required
Thread 1 cannot allocate new log, sequence 431
All online logs need archiving
Examine archive trace files for archiving errors
  Current log# 5 seq# 430 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:47:16.321880+00:00
ARC2 (PID:14249): Archived Log entry 1435061 added for T-1.S-429 ID 0x63f7acc7 LAD:1
2023-10-11T23:47:16.416930+00:00
Thread 1 advanced to log sequence 431 (LGWR switch)
  Current log# 6 seq# 431 mem# 0: +FRA/ORCL/redo06.log
2023-10-11T23:47:16.674682+00:00
ARC3 (PID:14251): Archived Log entry 1435062 added for T-1.S-430 ID 0x63f7acc7 LAD:1
2023-10-11T23:47:19.295492+00:00
Thread 1 advanced to log sequence 432 (LGWR switch)
  Current log# 5 seq# 432 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:47:19.426320+00:00
ARC0 (PID:14242): Archived Log entry 1435063 added for T-1.S-431 ID 0x63f7acc7 LAD:1
2023-10-11T23:47:21.693556+00:00
Thread 1 cannot allocate new log, sequence 433
Checkpoint not complete
  Current log# 5 seq# 432 mem# 0: +FRA/ORCL/redo05.log
2023-10-11T23:47:21.955074+00:00
Thread 1 advanced to log sequence 433 (LGWR switch)
  Current log# 6 seq# 433 mem# 0: +FRA/ORCL/redo06.log
2023-10-11T23:47:22.172983+00:00
ARC1 (PID:14247): Archived Log entry 1435064 added for T-1.S-432 ID 0x63f7acc7 LAD:1
2023-10-11T23:47:53.810684+00:00
PL/SQL package SYS.DBMS_RCVMAN version 19.14.00.00 is too new
Oracle must be upgraded to version 08.00.04.00 to work with this package
2023-10-11T23:52:28.037700+00:00
Control autobackup written to DISK device

Espero que esta dica tenha sido útil.

search previous next tag category expand menu location phone mail time cart zoom edit close