🍊Create Oracle Corrupt Data Block

ในกรณีที่เกิด Corrupt Data Block บน Oracle เราสามารถทำการ Recovery Data File ได้ จากไฟล์ Backup ของ RMAN ซึ่งเราจะมาจำลองการเกิด Corrupt Data Block ขึ้น ด้วยการ Change Seek ผ่านทาง dd command บน Linux

Requirement

• Create Database with Sample Schema ( Schema Diagrams )

• Enable Archive Log

Get Started

• ทำการ Connect Database ด้วย SQL*Plus

$

sqlplus / as sysdba

• ทำการ Create Tablespace

SQL>

create tablespace corrupt datafile '/u01/app/oracle/oradata/ORCL/corrupt.dbf' size 100m ;

• ทำการ Create User Corrupt

SQL>

create user corrupt identified by corrupt ;

• ทำการ Change Default Tablespace

SQL>

alter user corrupt default tablespace corrupt ;

• ทำการ Grant Privilege

SQL>

grant create session, resource to corrupt ;

• ทำการ Connect Database ด้วย User Corrupt

SQL>

connect corrupt/corrupt ;

• ทำการ Create Table Employee

SQL>

create table emp(eno number(30)) ;

SQL>

begin
for i in 1..10000
loop
insert into emp values(i) ;
end loop ;
end ;
/

PL/SQL procedure successfully completed.

• ทำการ Count Rows ใน Table Employee

SQL>

select count(*) from emp ;

  COUNT(*)
----------
     10000

• ทำการตรวจสอบ Schema

RMAN>

report schema ;

• ทำการ Backup Tablespace Corrupt

RMAN>

backup tablespace corrupt ;

• ทำการ Query Header Block ใน Table DBA Segment

SQL>

select header_block from dba_segments where segment_name = 'EMP' ;

HEADER_BLOCK
------------
         130

• ทำการ Corrupt Data Block

$

dd of=/u01/app/oracle/oradata/ORCL/corrupt.dbf bs=8192 conv=notrunc seek=131 << EOF

> testing corruption
> EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 0.000231208 s, 82.2 kB/s

• ทำการ Flush Buffer Cache

SQL>

alter system flush buffer_cache ;

• ทำการ Connect Database ด้วย User Corrupt

SQL>

connect corrupt/corrupt ;

• ทำการ Query Data ใน Table Employee จะเห็นว่า Oracle Error

SQL>

select count(*) from emp ;

select count(*) from emp
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/corrupt.dbfcode

Solution 1

• ทำการตรวจสอบด้วย Data Recovery Advisor

RMAN>

list failure ;

RMAN>

advise failure ;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
182        HIGH     OPEN      27-NOV-20     Datafile 6: '/u01/app/oracle/oradata/ORCL/corrupt.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 131 in file 6
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_4277806215.hm

• ทำการ Recovery Data Block

RMAN>

repair failure ;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_4277806215.hm

contents of repair script:
   # block media recovery
   recover datafile 6 block 131;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting recover at 27-NOV-20
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 00006
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_11_27/o1_mf_nnndf_TAG20201127T154218_hw1gzc5z_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_11_27/o1_mf_nnndf_TAG20201127T154218_hw1gzc5z_.bkp tag=TAG20201127T154218
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

Solution 2

• ทำการตรวจสอบด้วย DBVERIFY

$

dbv file=/u01/app/oracle/oradata/ORCL/corrupt.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Fri Nov 27 15:53:11 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ORCL/corrupt.dbf
Page 131 is marked corrupt
Corrupt block relative dba: 0x01800083 (file 6, block 131)
Bad header found during dbv:
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: 0xaed20601
 check value in block header: 0x6e6f
 block checksum disabled



DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 19
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 131
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12649
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1028466 (0.1028466)

• ทำการ Recovery Data Block

RMAN>

blockrecover datafile 6 block 131 ;

• ทำการ Validate Tablespace

RMAN>

backup validate tablespace corrupt ;

Solution 3

• ไม่ต้องตรวจสอบเอง ให้ RMAN ทำการ Validate พร้อมทำการ Recovery ไปเลย

RMAN>

run {
backup validate database ;
blockrecover corruption list ;
}

• ทำการ Connect Database ด้วย User Corrupt

SQL>

connect corrupt/corrupt ;

• ลองทำการ Query Data ใน Table Employee อีกครั้งหนึ่ง

SQL>

select count(*) from emp ;

  COUNT(*)
----------
     10000

อ่านเพิ่มเติม : https://bit.ly/3o32RI6