Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / Data block corrupt kurtarma işlemi

Data block corrupt kurtarma işlemi

06:52 AM By Bugra Canbakal 1 Comment

1.AMAÇ VE KAPSAM

Veritabanındaki corrupt blockları bir kaç yöntemle kurtarmamız/temizlememiz mümkündür. Corrupt duruma düşmesinin sebebi genellikle veritabanın çok büyümesi veya yapılan update işlemlerinden sonra başımıza gelmektedir.

2.UYGULAMA

2.1. Corruption hataları veritabanı alert loguna aşağıdaki gibi yansır, eğer çok fazla corruption varsa grep awk 2 lisini kullanıp ayırmanız rman scriptini oluşturmanıza yardım edecektir.
ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.

2.2.  Dbverify toolunu corruption blockların analizi için kullanabilirsiniz. Veritabanın kapalı veya açık olmasını umursamadan analiz yapmanıza yardımcı olan bir programdır.Aşağıda dbv komutunu kısadan örnek verip yazıcam başka bir yazımda daha detaylı bu toolu anlatıcam.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[oracle@sba7 scripts]$dbv FILE=+DATA/sba/datafile/system.414.755019907 FEEDBACK=100
DBVERIFY - Verification complete
 
Total Pages Examined : 56320
Total Pages Processed (Data) : 33533
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6125
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1567
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 15095
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

2.3. Eğer corruption yaşanan tabloyu biliyorsanız veya şüpeleniyorsanız analyze kullanarak sqlden sorgulayabilirsiniz.

ANALYZE TABLE...VALIDATE STRUCTURE

2.4. Rman kullanarak corrupt blockları kurtarmamız mümkün, öncelikle alert loglara corrupt blockumuz nasıl yansımış görelim.

Alert log

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
Reread of blocknum=674703, file=+DATA/sba/datafile/sysaux.414.755019907. found same corrupt data
Reread of blocknum=674703, file=+DATA/sba/datafile/sysaux.414.755019907. found valid data
Hex dump of (file 2, block 674752) in trace file /u01/app/oracle/diag/rdbms/sba/sba1/trace/sba1_ora_4364.trc
Corrupt block relative dba: 0x008a4bc0 (file 2, block 674752)
Bad header found during backing up datafile
Data in bad block:
 type: 1 format: 2 rdba: 0x00609c00
 last change scn: 0x8000.00000000 seq: 0x61 flg: 0x3e
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 computed block checksum: 0x0

2.5. Şimdi gidip veritabanından corruption blockların hangi objeye denk geldiğini görelim. Aşağıdaki sqli alert loglarındaki file ve block id ye göre yazıyoruz.

Oracle PL/SQL
1
2
3
4
5
6
SQL> select segment_type,owner,segment_name
from dba_extents
where file_id = 2 and 674752 between block_id and block_id+blocks -1;
SEGMENT_TYPE       OWNER              SEGMENT_NAME
------------------ ------------------------------ ---------------------------
TABLE PARTITION    SYS                WRH$_EVENT_HISTOGRAM

2.6. Corruption alanımız tabloya denk gelmiş, bu alan eğer indexe denk gelseydi indexi drop edip create etmemiz sorunu çözmemize yeterli olacaktı ama tabloya denk geldiği için şimdi rmani kullanıp restore ettirmemiz gerekmekte.

 

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
RMAN>connect target
2> run {
3>BLOCKRECOVER DATAFILE 2 BLOCK  674752;
 4>}
Starting recover at 13-OCT-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece +RECO/sba/backup/sba_20111012_9781.hlmoukja_1_1
channel ORA_DISK_1: piece handle=+RECO/sba/backup/sba_20111012_9781.hlmoukja_1_1 tag=TAG20111012T201258
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:04:15
starting media recovery
archived log for thread 1 with sequence 6481 is already on disk as file +RECO/sba/archivelog/2011_10_12/thread_1_seq_6481.1896.764370243
archived log for thread 1 with sequence 6482 is already on disk as file +RECO/sba/archivelog/2011_10_12/thread_1_seq_6482.3325.764374871
archived log for thread 1 with sequence 6483 is already on disk as file +RECO/sba/archivelog/2011_10_12/thread_1_seq_6483.3788.764377983
archived log for thread 1 with sequence 6484 is already on disk as file +RECO/sba/archivelog/2011_10_12/thread_1_seq_6484.3653.764379693
archived log for thread 1 with sequence 6485 is already on disk as file +RECO/sba/archivelog/2011_10_13/thread_1_seq_6485.1089.764383057
archived log for thread 1 with sequence 6486 is already on disk as file +RECO/sba/archivelog/2011_10_13/thread_1_seq_6486.2873.764384503
archived log for thread 2 with sequence 6574 is already on disk as file +RECO/sba/archivelog/2011_10_13/thread_2_seq_6574.2333.764419309
archived log for thread 2 with sequence 6575 is already on disk as file +RECO/sba/archivelog/2011_10_13/thread_2_seq_6575.2445.764419967
media recovery complete, elapsed time: 00:35:02
Finished recover at 13-OCT-11

 

2.7. Corruptionların çözümü noktasında DBMS.repair paketini kullanmanız mümkün.

Share this:

  • LinkedIn
  • Twitter

Konuyla ilgili diğer yazılar

  • Restore işleminde datafile pathlerinin yerinin değiştirilmesi
  • Oracle DUL (Data UnLoader) //Life is DUL without it
  • Oracle AMDU
  • Incremental backup icin Block Change Tracking dosyası olusturmak
  • Installation of Oracle RMAN Catalog

Filed Under: Oracle, RMAN Tagged With: analyze structure, block corrupted, blockrecover, data block corruption, data block corruption recover, dbverify, ora-01578, oracle, rman

Comments

  1. AvatarSamet says

    Wednesday October 19th, 2011 at 07:54 AM

    Hayati bir meseleyi sayenizde cozume ulastirdim. Tesekkurler.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.