Zarar görmüş blockların tamiri (Block recovery)

Senaryo :

Block recovery, bazan veri içeren dosya blocklarında bozulmalar olabiliyor bu durumda nasıl recover işleminin yapılacağını göreceğiz.

-VT archivelog modda ve kullanılabilir yedeğimiz mevcut

- dbf dosyasını hex editor ile bozup (rakam yazan yerleri 00 yapıp kayıt ederseniz bozulacaktır), rman ile bozulan block ları tamir etmeye çalışacağız. (bad sector vb durumların simulasyonu)

Not: rman-03002 failure of backup plus archive log command… hata alınırsa bunun nedeni sistemde olması gereken archivelogların olmamasından kaynaklanıyor genelde elle silindikten sonra bu sorun oluyor.

RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

1 2	RMAN> crosscheck archivelog all; RMAN> delete noprompt expired archivelog all;

komutları ile sorun giderilir.

SQL> alter system switch log file ;
RMAN> sql 'alter system switch log file';

1 2	SQL> alter system switch log file ; RMAN> sql 'alter system switch log file';

RMAN> backup archivelog all delete input ;

1	RMAN> backup archivelog all delete input ;

archive logların yedegini al ve yedeği alınan archivelogları sil. Archive logların şişmesini engeller. Bu şekilde sadece archiveların yedegi alınabiliyor. (Bu yedekleri usb vb bir yerde tutabiliriz.)

RMAN> backup database ;

1	RMAN> backup database ;

İle dbf lerin yedeğini alıp arşiv logların obsolete olması engellenmiş olur.
Hafta sonu da

RMAN> backup database plus archivelog;

1	RMAN> backup database plus archivelog;

Böylece hafta sonundan hafta sonuna kadar archive loglar yedeklenip silinebilir.

Tablespace oluşturuyoruz.

SQL> CREATE TABLESPACE test DATAFILE
  2  '/oracle/oradata/data/test01.dbf' SIZE 512K AUTOEXTEND ON NEXT 100K MAXSIZE 1M
  3  LOGGING
  4  PERMANENT
  5  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  6  BLOCKSIZE 8K
  7  SEGMENT SPACE MANAGEMENT MANUAL
  8  FLASHBACK ON;

SQL> CREATE TABLESPACE test DATAFILE

2 '/oracle/oradata/data/test01.dbf' SIZE 512K AUTOEXTEND ON NEXT 100K MAXSIZE 1M

3 LOGGING

4 PERMANENT

5 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

6 BLOCKSIZE 8K

7 SEGMENT SPACE MANAGEMENT MANUAL

8 FLASHBACK ON;

Tablo oluşturuyoruz.

SQL> CREATE TABLE HR.personel
  2  (
  3  isim VARCHAR2(20),
  4  "soy isim" VARCHAR2(20),
  5  adres VARCHAR2(200),
  6  tel NUMBER(15),
  7  email VARCHAR2(20))
  8  TABLESPACE TEST
  9  LOGGING
  10 NOCOMPRESS
  11 NOCACHE
  12 NOPARALLEL
  13 NOMONITORING;

SQL> CREATE TABLE HR.personel

2 (

3 isim VARCHAR2(20),

4 "soy isim" VARCHAR2(20),

5 adres VARCHAR2(200),

6 tel NUMBER(15),

7 email VARCHAR2(20))

8 TABLESPACE TEST

9 LOGGING

10 NOCOMPRESS

11 NOCACHE

12 NOPARALLEL

13 NOMONITORING;

Bilgi girip tablespace i dolduruyoruz

SQL> insert into hr.personel values('mehmet','derviş','Kadikoy',2165555555,'mehmet at sysdba.org');
SQL> insert into hr.personel select * from hr.personel; (sürekli çalıştırarar tablespace in dolması sağlanır)

1 2	SQL> insert into hr.personel values('mehmet','derviş','Kadikoy',2165555555,'mehmet at sysdba.org'); SQL> insert into hr.personel select * from hr.personel; (sürekli çalıştırarar tablespace in dolması sağlanır)

Tabloya veri girişleri yapıldıktan sonra .
VT kapatılır. ve test01.dbf hex editor ile açılıp rakamlı yerler 0 yapılarak dosyanın bozulması sağlanır.
VT açılıp

SQL> select * from HR.personel ;

1	SQL> select * from HR.personel ;

okuyabildigi alanlardan verileri getirir okuyamadığı yerde de

ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 33)
ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf'

ERROR:

ORA-01578: ORACLE data block corrupted (file # 6, block # 33)

ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf'

Hatasını verir. File 6′ nın 33. bloğu bozulmuş.

SQL> select count(*) from hr.personel;
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 33)
ORA-01110: data file 6: ‘/oracle/oradata/data/test01.dbf’

SQL> select count(*) from hr.personel;

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 33)

ORA-01110: data file 6: ‘/oracle/oradata/data/test01.dbf’

Sorgusunu çalıştırırsak direk hatayı verir.

Bu hata disk üzerindeki bad sector den de olabilir. (diskteki fiziksel bozulma), 2 background process in aynı anda aynı yere yazmasından da kaynaklanıyor olabilir bu durumda alertlog un incelenmesi gerekir.

alertORATR.log a düşen hata

Wed Jun 1 14:14:42 2011
Hex dump of (file 6, block 33) in trace file /oracle/admin/oratr/udump/oratr_ora_227.trc
Corrupt block relative dba: 0x01800021 (file 6, block 33)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x01800021
last change scn: 0x0000.0024654d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x654d0601
check value in block header: 0x7df7
computed block checksum: 0xd052
Reread of rdba: 0x01800021 (file 6, block 33) found same corrupted data
Wed Jun 1 14:14:49 2011
Corrupt Block Found
TSN = 8, TSNAME = TEST
RFN = 6, BLK = 33, RDBA = 25165857
OBJN = 53626, OBJD = 53626, OBJECT = PERSONEL, SUBOBJECT =
SEGMENT OWNER = HR, SEGMENT TYPE = Table Segment

Wed Jun 1 14:14:42 2011

Hex dump of (file 6, block 33) in trace file /oracle/admin/oratr/udump/oratr_ora_227.trc

Corrupt block relative dba: 0x01800021 (file 6, block 33)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x01800021

last change scn: 0x0000.0024654d seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x654d0601

check value in block header: 0x7df7

computed block checksum: 0xd052

Reread of rdba: 0x01800021 (file 6, block 33) found same corrupted data

Wed Jun 1 14:14:49 2011

Corrupt Block Found

TSN = 8, TSNAME = TEST

RFN = 6, BLK = 33, RDBA = 25165857

OBJN = 53626, OBJD = 53626, OBJECT = PERSONEL, SUBOBJECT =

SEGMENT OWNER = HR, SEGMENT TYPE = Table Segment

SQL> analyze table hr.personel validate structure ;

1	SQL> analyze table hr.personel validate structure ;

aynı hatayı verir.

SQL> analyze table hr.personel validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 33)
ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf'

SQL> analyze table hr.personel validate structure

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 33)

ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf'

dbv file=test01.dbf logfile=test01.txt
test01.txt içeriği

1 2	dbv file=test01.dbf logfile=test01.txt test01.txt içeriği

DBVERIFY: Release 10.2.0.2.0 - Production on Wed Jun 1 14:25:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = test01.dbf
Page 33 is marked corrupt
Corrupt block relative dba: 0x01800021 (file 6, block 33)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01800021
last change scn: 0x0000.0024654d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x654d0601
check value in block header: 0x7df7
computed block checksum: 0xd052
DBVERIFY - Verification complete

Total Pages Examined : 256
Total Pages Processed (Data) : 117
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 18
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 120
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2391554 (0.2391554)

DBVERIFY: Release 10.2.0.2.0 - Production on Wed Jun 1 14:25:17 2011

DBVERIFY - Verification starting : FILE = test01.dbf

Page 33 is marked corrupt

Corrupt block relative dba: 0x01800021 (file 6, block 33)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x01800021

last change scn: 0x0000.0024654d seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x654d0601

check value in block header: 0x7df7

computed block checksum: 0xd052

DBVERIFY - Verification complete

Total Pages Examined : 256

Total Pages Processed (Data) : 117

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 18

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 120

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 2391554 (0.2391554)

sorunsuz dbf lerde Total Pages Marked Corrupt 0 dır.

Blockları tamir etmek için;
note: blockrecover elimizde yedek varsa işe yarar.

RMAN> blockrecover datafile 6 block 33;

1	RMAN> blockrecover datafile 6 block 33;

Starting blockrecover at 01-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

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 /oracle/rman2/dbf_ORATR_752680336_108.rman
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oracle/rman2/dbf_ORATR_752680336_108.rman tag=DBF_MANUEL_01062011
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:05
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 01-JUN-11

Starting blockrecover at 01-JUN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=158 devtype=DISK

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 /oracle/rman2/dbf_ORATR_752680336_108.rman

channel ORA_DISK_1: restored block(s) from backup piece 1

piece handle=/oracle/rman2/dbf_ORATR_752680336_108.rman tag=DBF_MANUEL_01062011

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:05

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 01-JUN-11

SQL> select count(*) from hr.personel;
COUNT(*)
----------
12288

SQL> select count(*) from hr.personel;

COUNT(*)

----------

12288