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.trcLevando 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-15Verificando 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 0Verificando 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_TESTERecuperando 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-15Validando 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-15Utilizando 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
