Saiba como utilizar o RMAN para recuperação de blocos corrompidos no Oracle

Recuperação de blocos corrompidos com o RMAN

No dia-dia de um DBA, um usuário do sistema pode reportar que ocorreu o erro ORA-01578.

Ao saber disso, o DBA deve dar uma olhada no alert.log e procurar por um erro similar a este:

Corrupt block relative dba: 0x01c00083 (file 7, block 131)
Bad header found during buffer read
Data in bad block:
 type: 116 format: 5 rdba: 0x20676e69
 last change scn: 0x7075.72726f63 seq: 0x74 flg: 0x69
 spare1: 0x73 spare2: 0x74 spare3: 0xa
 consistency value in tail: 0xea160602
 check value in block header: 0x6e6f
 block checksum disabled
Reading datafile '/u01/oradata/users_teste.dbf' for corruption at rdba: 0x01c00083 (file 7, block 131)
Reread (file 7, block 131) found same corrupt data (no logical check)
Mon Oct 05 23:56:56 2015
Corrupt Block Found
         CONT = 0, TSN = 8, TSNAME = USERS_TESTE
         RFN = 7, BLK = 131, RDBA = 29360259
         OBJN = 93473, OBJD = 93473, OBJECT = EMP_TESTE, SUBOBJECT =
         SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6232.trc  (incident=289009):
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: '/u01/oradata/users_teste.dbf'
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_289009/orcl_ora_6232_i289009.trc
Mon Oct 05 23:56:57 2015
Sweep [inc][289009]: completed
Mon Oct 05 23:56:57 2015
Hex dump of (file 7, block 131) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_289009/orcl_m000_6320_i289009_a.trc

Levando em conta que você é um bom DBA e está com o seu backup em dia ☺, podemos utilizar o RMAN para fazer a recuperação deste bloco corrompido.

Validando a tablespace para popular a V$DATABASE_BLOCK_CORRUPTION com a lista de blocos corrompidos:

RMAN> validate tablespace users_teste;
Starting validate at 06-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u01/oradata/users_teste.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              61           1281            2550298   

  File Name: /u01/oradata/users_teste.dbf

  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      2              1219            

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6422.trc for details
Finished validate at 06-OCT-15

Verificando a lista de blocos que estão corrompidos:

RMAN> select * from v$database_block_corruption order by 1,2,3,4;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         7        131          1                  0 CORRUPT            0

Verificando qual objeto está corrompido:

RMAN> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAMEFROM DBA_EXTENTSWHERE FILE_ID = 7 AND 131 BETWEEN BLOCK_IDAND BLOCK_ID+BLOCKS -1;

SEGMENT_TYPEOWNER||'.'||SEGMENT_NAME
--------------- ------------------------------------------------------------------------
TABLE             SCOTT.EMP_TESTE

Recuperando os blocos do backup

Como é um bloco só, podemos recuperar da seguinte forma:

blockrecover datafile 7 block 131;

Outra forma seria:

blockrecover corruption list;

RMAN> blockrecover datafile 7 block 131;
Starting recover at 06-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece +FRA01/ORCL/BACKUPSET/2015_10_05/nnndf0_tag20151005t234117_0.334.892338095
channel ORA_DISK_1: piece handle=+FRA01/ORCL/BACKUPSET/2015_10_05/nnndf0_tag20151005t234117_0.334.892338095 tag=TAG20151005T234117
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-OCT-15

Validando a tablespace após a recuperação:

RMAN> validate tablespace users_teste;

Starting validate at 06-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u01/oradata/users_teste.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              61           1281            2550298   

  File Name: /u01/oradata/users_teste.dbf

  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1               
  Index      0              0               
  Other      0              1218            

Finished validate at 06-OCT-15

Utilizando o DBVerify para validar o datafile após a recuperação:

[oracle@oracle01 trace]$ dbv file=/u01/oradata/users_teste.dbf blocksize=8192 feedback=10000

DBVERIFY: Release 12.1.0.1.0 - Production on Tue Oct 6 00:24:50 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oradata/users_teste.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1218
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 61
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2550298 (0.2550298)

Alguns pontos:

• Caso você tenha um Active Data Guard, existe o auto-block media recovery, que tentará fazer a recuperação automática do bloco corrompido.
• Caso seja um índice, é possível apenas fazer o rebuild deste índice.
• É possível utilizar a package DBMS_REPAIR para reparar e recuperar blocos corrompidos.

Scripts utilizados para realizar os testes:

CREATE TABLESPACE "USERS_TESTE" DATAFILE '/u01/oradata/users_teste.dbf' 
  SIZE 10M
  AUTOEXTEND ON NEXT 1M MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "USERS_TESTE_IDX" DATAFILE '/u01/oradata/users_teste_idx.dbf' 
  SIZE 10M
  AUTOEXTEND ON NEXT 1M MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

create table scott.emp_teste tablespace users_teste as select * from scott.emp; 

CREATE index "SCOTT"."EMP_TESTE_IDX01" on  scott.emp_teste("EMPNO") tablespace users_teste_idx;

SELECT header_block FROM dba_segments WHERE segment_name=’EMP_TESTE’;

dd of=/u01/oradata/users_teste.dbf bs=8192 conv=notrunc seek=131 << EOF
forcando corrupcao
EOF

alter system flush buffer_cache;

SQL> select * from scott.emp_teste;

select * from scott.emp_teste
       *
ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: '/u01/oradata/users_teste.dbf'

Abraço

Referências

• https://oracle-base.com/articles/misc/detect-and-correct-corruption