{"id":1086,"date":"2026-11-03T12:05:22","date_gmt":"2026-11-03T09:05:22","guid":{"rendered":"https:\/\/oraclius.com.tr\/?p=1086"},"modified":"2026-11-03T12:05:23","modified_gmt":"2026-11-03T09:05:23","slug":"oracleda-block-corruption-tespit-test-ve-her-durumda-onarim","status":"publish","type":"post","link":"https:\/\/oraclius.com.tr\/en\/oracleda-block-corruption-tespit-test-ve-her-durumda-onarim\/","title":{"rendered":"Oracle’da Block Corruption: Tespit, Test ve Her Durumda Onar\u0131m"},"content":{"rendered":"\n

Ortam: Oracle Database, ASM (External, Normal ve High redundancy diskgroup’lar)<\/em><\/p>\n\n\n\n

Bu yaz\u0131da v$database_block_corruption<\/code> g\u00f6r\u00fcn\u00fcm\u00fcn\u00fcn nas\u0131l doldu\u011funu, laboratuvarda corruption’\u0131n nas\u0131l olu\u015fturuldu\u011funu ve bulunan corruption’\u0131n yedek var\/yok, ARCHIVELOG a\u00e7\u0131k\/kapal\u0131, ASM redundancy, corruption tipi<\/strong> gibi her durumda nas\u0131l giderilece\u011fini anlat\u0131yorum. En sonda t\u00fcm durumlar tek tabloda \u00f6zetleniyor.<\/p>\n\n\n\n

1. Corruption t\u00fcrleri<\/h2>\n\n\n\n

Oracle, veri dosyas\u0131 bloklar\u0131ndaki bozulmalar\u0131 (intra block corruption) ikiye ay\u0131r\u0131r:<\/p>\n\n\n\n

T\u00fcr<\/th>Belirti<\/th>\u00d6rnek<\/th><\/tr>
Fiziksel<\/td>ORA-01578, alert log’da blo\u011fun d\u00f6k\u00fcm\u00fc<\/td>Bozuk header, fractured (ba\u015f\u0131 ve sonu uyumsuz), checksum hatas\u0131, misplaced (ba\u015fka blo\u011fun i\u00e7eri\u011fi), s\u0131f\u0131rlanm\u0131\u015f blok<\/td><\/tr>
Mant\u0131ksal<\/td>Checksum ge\u00e7erli ama i\u00e7erik bozuk: ORA-00600 [4512], [kdBlkCheckError], DBV-200<\/td>Var olmayan transaction’a kilitli sat\u0131r, kullan\u0131lan alan blok boyutuna uymuyor<\/td><\/tr>
NOLOGGING<\/td>ORA-01578 ile birlikte ORA-26040<\/td>NOLOGGING y\u00fcklenen veri, recovery sonras\u0131 bozuk g\u00f6r\u00fcn\u00fcr<\/td><\/tr>
Tablo\/index tutars\u0131zl\u0131\u011f\u0131<\/td>ORA-08102, ORA-01499, ORA-00600 [kdsgrp1]<\/td>Index’te olan sat\u0131r tabloda yok. Blok bozuk de\u011fildir, view’da g\u00f6r\u00fcnmez<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

db_block_checking<\/code> a\u00e7\u0131ksa ve blok diskte zaten mant\u0131ksal olarak bozuksa, blo\u011fa yap\u0131lan sonraki g\u00fcncelleme onu soft corrupt<\/em> i\u015faretler ve art\u0131k ORA-01578 al\u0131rs\u0131n\u0131z.<\/p>\n\n\n\n

2. v$database_block_corruption nas\u0131l dolar?<\/h2>\n\n\n\n

G\u00f6r\u00fcn\u00fcm\u00fc sadece RMAN<\/strong> doldurur (BACKUP<\/code>, BACKUP VALIDATE<\/code>, VALIDATE<\/code>). SQL’de ORA-01578 almak ya da DBV \u00e7al\u0131\u015ft\u0131rmak g\u00f6r\u00fcn\u00fcm\u00fc doldurmaz. Her RMAN taramas\u0131nda yenilenir, onar\u0131lan bloklar listeden d\u00fc\u015fer.<\/p>\n\n\n\n

CORRUPTION_TYPE<\/th>Anlam\u0131<\/th><\/tr>
ALL ZERO<\/td>Blok tamamen s\u0131f\u0131r<\/td><\/tr>
FRACTURED<\/td>Header ve tail uyu\u015fmuyor<\/td><\/tr>
CHECKSUM<\/td>Checksum hatal\u0131<\/td><\/tr>
CORRUPT<\/td>Ba\u015fka bir fiziksel bozulma<\/td><\/tr>
LOGICAL<\/td>Fiziksel yap\u0131 sa\u011flam, i\u00e7erik tutars\u0131z<\/td><\/tr>
NOLOGGING<\/td>NOLOGGING i\u015flemden kalan bozuk blok<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

12c ve sonras\u0131nda RMAN’in buldu\u011fu NOLOGGING bloklar ayr\u0131ca v$nonlogged_block<\/code> g\u00f6r\u00fcn\u00fcm\u00fcnde listelenir.<\/p>\n\n\n\n

3. Lab: corruption olu\u015fturma<\/h2>\n\n\n\n
\n

Sadece test ortam\u0131nda yap\u0131n. Komutlar ASM diskine do\u011frudan yazar.<\/p>\n<\/blockquote>\n\n\n\n

a) Test tablosu<\/strong><\/p>\n\n\n\n

CREATE TABLESPACE corr_ts DATAFILE '+DATA' SIZE 100M;\nCREATE TABLE corr_test TABLESPACE corr_ts AS\n  SELECT level id, rpad('x',100,'x') txt FROM dual CONNECT BY level <= 100000;\nCREATE INDEX corr_test_idx ON corr_test(id) TABLESPACE corr_ts;<\/code><\/pre>\n\n\n\n
b) Bozulacak blo\u011fu se\u00e7<\/strong><\/p>\n\n\n\n
SELECT dbms_rowid.rowid_relative_fno(rowid) file_no,\n       dbms_rowid.rowid_block_number(rowid) block_no\nFROM corr_test WHERE id = 500;<\/code><\/pre>\n\n\n\n
c) Blo\u011fun ASM’deki yerini bul<\/strong> (ASM instance’ta)<\/p>\n\n\n\n
ASM dosya numaras\u0131n\u0131 datafile ad\u0131ndan al\u0131n (+DATA\/DB\/DATAFILE\/corr_ts.280.123456<\/code> i\u00e7in 280<\/strong>). 1 MB AU ve 8K blokta bir AU’da 128 blok vard\u0131r:<\/p>\n\n\n\n
SELECT lxn_kffxp copy_no, disk_kffxp disk_no, au_kffxp au_no\nFROM x$kffxp\nWHERE group_kffxp = <diskgroup_no> AND number_kffxp = 280\n  AND xnum_kffxp = TRUNC(<block_no>\/128);<\/code><\/pre>\n\n\n\n
copy_no<\/code> 0 birincil kopyad\u0131r, 1 ve 2 mirror kopyalard\u0131r. Disk yolunu v$asm_disk.path<\/code> i\u00e7inden disk_no<\/code> ile bulun.<\/p>\n\n\n\n
d) Blo\u011fa \u00e7\u00f6p yaz<\/strong><\/p>\n\n\n\n
printf 'CORRUPT' | dd of=<disk_path> bs=1 conv=notrunc \\\n  seek=$(( AU_NO*1048576 + (BLOCK_NO%128)*8192 + 200 ))<\/code><\/pre>\n\n\n\n
ASM kullanmayan bir lab’da dosya do\u011frudan bozulabilir: seek=$(( BLOCK_NO*8192 + 200 ))<\/code> ve of=<datafile><\/code>.<\/p>\n\n\n\n
e) Cache’i temizle ve do\u011frula<\/strong><\/p>\n\n\n\n
ALTER SYSTEM FLUSH BUFFER_CACHE;\nSELECT \/*+ FULL(t) *\/ count(*) FROM corr_test t;   -- ORA-01578<\/code><\/pre>\n\n\n\n
Normal redundancy’de sadece copy_no=0<\/code> kopyas\u0131n\u0131 bozarsan\u0131z ASM scrub ile onar\u0131m senaryosunu test edersiniz. T\u00fcm kopyalar\u0131 (Normal’de 0 ve 1, High’da 0, 1 ve 2) bozarsan\u0131z Normal\/High de External gibi davran\u0131r.<\/p>\n\n\n\n
4. Corruption var m\u0131? Tespit<\/h2>\n\n\n\n
RMAN<\/h3>\n\n\n\n
RMAN> VALIDATE CHECK LOGICAL DATABASE;\n-- H\u0131zland\u0131rmak i\u00e7in: birden fazla channel, b\u00fcy\u00fck dosyada b\u00f6l\u00fcmleme, tek dosya veya blok aral\u0131\u011f\u0131\nRMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 5 SECTION SIZE 1024M;\nRMAN> VALIDATE CHECK LOGICAL DATAFILE 1 BLOCK 5 TO 20;<\/code><\/pre>\n\n\n\n
CHECK LOGICAL<\/code> olmadan mant\u0131ksal bozulmalar yakalanmaz. Eksik dosya y\u00fcz\u00fcnden hata al\u0131rsan\u0131z SKIP INACCESSIBLE<\/code> ekleyin. \u0130lerlemeyi v$session_longops<\/code> ile izleyebilirsiniz. NOARCHIVELOG modda 10g ve \u00f6ncesinde veritaban\u0131 MOUNT olmal\u0131d\u0131r, 11g ve sonras\u0131nda bu k\u0131s\u0131t yoktur.<\/p>\n\n\n\n
Bozuk blok hangi segmente ait?<\/h3>\n\n\n\n
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#,\n       GREATEST(e.block_id, c.block#) bas_blok,\n       LEAST(e.block_id+e.blocks-1, c.block#+c.blocks-1) son_blok,\n       c.corruption_type aciklama\n  FROM dba_extents e, v$database_block_corruption c\n WHERE e.file_id = c.file# AND e.block_id <= c.block#+c.blocks-1\n   AND e.block_id+e.blocks-1 >= c.block#\nUNION ALL\nSELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#,\n       s.header_block, s.header_block, c.corruption_type||' - SEGMENT HEADER'\n  FROM dba_segments s, v$database_block_corruption c\n WHERE s.header_file = c.file# AND s.header_block BETWEEN c.block# AND c.block#+c.blocks-1\nUNION ALL\nSELECT NULL, NULL, NULL, NULL, c.file#,\n       GREATEST(f.block_id, c.block#), LEAST(f.block_id+f.blocks-1, c.block#+c.blocks-1), 'FREE BLOCK'\n  FROM dba_free_space f, v$database_block_corruption c\n WHERE f.file_id = c.file# AND f.block_id <= c.block#+c.blocks-1\n   AND f.block_id+f.blocks-1 >= c.block#\n ORDER BY 5, 6;<\/code><\/pre>\n\n\n\n
Sonu\u00e7 \u00fc\u00e7 t\u00fcrden biridir: bir segment<\/strong> (tablo, index, LOB…), bir segment header<\/strong> ya da free block<\/strong>. Sonraki ad\u0131mlar buna g\u00f6re de\u011fi\u015fir.<\/p>\n\n\n\n
Di\u011fer kontrol y\u00f6ntemleri<\/h3>\n\n\n\n
Y\u00f6ntem<\/th>Not<\/th><\/tr>
dbv file=... blocksize=8192 userid=...<\/code><\/td>Veritaban\u0131 a\u00e7\u0131kken \u00e7al\u0131\u015f\u0131r. ASM dosyas\u0131 i\u00e7in userid<\/code> zorunludur. Blo\u011fu tek ba\u015f\u0131na inceler, segmente ait olmayan blo\u011fu da bozuk g\u00f6sterebilir. Tablo\/index uyumsuzlu\u011funu bulamaz<\/td><\/tr>
ANALYZE TABLE t VALIDATE STRUCTURE CASCADE [ONLINE]<\/code><\/td>Tablo-index \u00e7apraz kontrol\u00fc yapan en kapsaml\u0131 komuttur. \u0130lk hatada durur. Partition’l\u0131 tabloda INTO INVALID_ROWS<\/code> gerekir<\/td><\/tr>
Full table scan, CREATE TABLE AS SELECT<\/code> veya export<\/td>\u0130lk hatada durur, index’leri kontrol etmez<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
5. Hangi s\u0131rayla deneyece\u011fiz?<\/h2>\n\n\n\n
    \n
  1. Active Data Guard standby’\u0131 var m\u0131? Bozuk blok standby’dan getirilir.<\/li>\n\n\n\n
  2. Yedek ve ARCHIVELOG var m\u0131? RMAN block media recovery (b\u00f6l\u00fcm 6).<\/li>\n\n\n\n
  3. Normal\/High redundancy m\u0131? \u00d6nce ASM scrub (b\u00f6l\u00fcm 7).<\/li>\n\n\n\n
  4. Yedek var ama NOARCHIVELOG mu? Yede\u011fi yan ortama a\u00e7\u0131p sat\u0131rlar\u0131 oradan al\u0131n (b\u00f6l\u00fcm 8).<\/li>\n\n\n\n
  5. Hi\u00e7biri yoksa: DBMS_ROWID.ROWID_CREATE<\/code> ile bozuk b\u00f6lgeyi ay\u0131r\u0131p tabloyu temizleyin (b\u00f6l\u00fcm 9).<\/li>\n<\/ol>\n\n\n\n
    6. Yedek ve ARCHIVELOG varsa: block media recovery<\/h2>\n\n\n\n
    Tablo \u00e7evrimi\u00e7i kal\u0131r, sadece bozuk blok k\u0131sa s\u00fcre eri\u015filemez olur.<\/p>\n\n\n\n
    RMAN> RECOVER CORRUPTION LIST;                       -- view'daki t\u00fcm bloklar\nRMAN> RECOVER DATAFILE 7 BLOCK 1234 DATAFILE 7 BLOCK 1250;   -- se\u00e7ili bloklar\nRMAN> VALIDATE DATAFILE 7 BLOCK 1234;                 -- do\u011frulama<\/code><\/pre>\n\n\n\n
    \u015eartlar ve dikkat edilecekler:<\/p>\n\n\n\n
      \n
    • Veritaban\u0131 ARCHIVELOG<\/strong> modunda olmal\u0131, Enterprise Edition gerekir.<\/li>\n\n\n\n
    • Blok full veya level 0<\/strong> yedekten (ya da flashback log’dan) al\u0131n\u0131r, redo ile g\u00fcncellenir. Level 1 incremental yedek block restore i\u00e7in kullan\u0131lmaz.<\/li>\n\n\n\n
    • Yedekteki blok da bozuksa i\u015fe yaramaz. \u00d6nce RESTORE DATAFILE 7 VALIDATE<\/code> veya VALIDATE BACKUPSET<\/code> ile yede\u011fi kontrol edin.<\/li>\n\n\n\n
    • NOLOGGING<\/strong> bloklarda redo olmad\u0131\u011f\u0131 i\u00e7in bu y\u00f6ntem \u00e7al\u0131\u015fmaz (b\u00f6l\u00fcm 11).<\/li>\n\n\n\n
    • Active Data Guard varsa bozuk blok standby’dan otomatik onar\u0131labilir.<\/li>\n<\/ul>\n\n\n\n
      7. ASM redundancy’ye g\u00f6re<\/h2>\n\n\n\n
      Diskgroup<\/th>Kopya<\/th>Ne yap\u0131l\u0131r?<\/th><\/tr>
      External<\/strong><\/td>1<\/td>ASM d\u00fczeltemez. Yedek, standby (b\u00f6l\u00fcm 6) ya da b\u00f6l\u00fcm 9 gerekir<\/td><\/tr>
      Normal<\/strong><\/td>2<\/td>Bir kopya sa\u011flam ise ASM scrub onarabilir<\/td><\/tr>
      High<\/strong><\/td>3<\/td>Ayn\u0131, daha fazla kopya<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      ALTER DISKGROUP data SCRUB FILE '+DATA\/DB\/DATAFILE\/corr_ts.280.123456' REPAIR POWER LOW;<\/code><\/pre>\n\n\n\n
      Oracle 21c ve sonras\u0131nda RMAN ile her kopyay\u0131 ayr\u0131 do\u011frulayabilirsiniz, b\u00f6ylece hangi kopyan\u0131n bozuk oldu\u011funu g\u00f6r\u00fcrs\u00fcn\u00fcz:<\/p>\n\n\n\n
      RMAN> VALIDATE DATAFILE 5 MIRROR ALL;\nRMAN> VALIDATE DATAFILE 5 MIRROR 2;<\/code><\/pre>\n\n\n\n
      Scrub sonras\u0131 VALIDATE<\/code> ile tekrar kontrol edin. Hata s\u00fcr\u00fcyorsa t\u00fcm kopyalar bozuktur. Veritaban\u0131n\u0131n mant\u0131ksal olarak \u00fcretti\u011fi bozuk bloklar t\u00fcm kopyalara yaz\u0131l\u0131r, bu y\u00fczden Normal\/High burada koruma sa\u011flamaz.<\/p>\n\n\n\n
      8. Yedek var ama ARCHIVELOG kapal\u0131ysa<\/h2>\n\n\n\n
      Block media recovery redo gerektirdi\u011fi i\u00e7in \u00e7al\u0131\u015fmaz. Se\u00e7enekler:<\/p>\n\n\n\n
        \n
      • Yede\u011fi yan ortama a\u00e7.<\/strong> Yedekten ayr\u0131 bir sunucuda (DUPLICATE DATABASE<\/code> veya restore) veritaban\u0131n\u0131 a\u00e7\u0131n, kay\u0131p sat\u0131rlar\u0131 DB link ya da Data Pump ile geri al\u0131n. Sat\u0131rlar yedek tarihindeki halleriyle gelir, sonradan yap\u0131lan de\u011fi\u015fiklikler olmaz.<\/li>\n\n\n\n
      • Kaynak sistemden veya export’tan yeniden y\u00fckle.<\/strong> Tablo bir ETL ile besleniyorsa en temizi budur.<\/li>\n\n\n\n
      • Bunlar yoksa b\u00f6l\u00fcm 9.<\/li>\n<\/ul>\n\n\n\n
        Uzun vadede ARCHIVELOG moda ge\u00e7mek ve d\u00fczenli RMAN yede\u011fi almak tek ger\u00e7ek \u00e7\u00f6z\u00fcmd\u00fcr.<\/p>\n\n\n\n
        9. Hi\u00e7bir y\u00f6ntem yoksa: DBMS_ROWID.ROWID_CREATE ile bozuk b\u00f6lgeyi ay\u0131r\u0131p temizleme<\/h2>\n\n\n\n
        Durum: yedek yok (veya NOARCHIVELOG), standby yok, External redundancy. Bozuk bloktaki sat\u0131rlar kaybolur. Hedefimiz kalan veriyi korumak ve tabloyu yeniden sorunsuz DML al\u0131r hale getirmek.<\/strong><\/p>\n\n\n\n
        Neden blo\u011fa do\u011frudan DELETE, UPDATE veya NULL basam\u0131yoruz?<\/strong> Sat\u0131rlar bozuk blo\u011fun i\u00e7indedir. DML i\u00e7in Oracle blo\u011fu okumak zorundad\u0131r ve ORA-01578 verir. (NULL y\u00f6ntemi sadece LOB i\u00e7in m\u00fcmk\u00fcnd\u00fcr, b\u00f6l\u00fcm 10.) Bunun yerine ROWID_CREATE<\/code> ile bozuk blo\u011fun ROWID aral\u0131\u011f\u0131n\u0131 \u00fcretip tabloyu bu aral\u0131\u011f\u0131 hi\u00e7 okumadan<\/strong> tar\u0131yoruz.<\/p>\n\n\n\n
        Haz\u0131rl\u0131k<\/h3>\n\n\n\n
        Bozuk blok bilgisi v$database_block_corruption<\/code> i\u00e7indeki file#<\/code>, block#<\/code> ve blocks<\/code> s\u00fctunlar\u0131ndan, segment bilgisi b\u00f6l\u00fcm 4’teki sorgudan gelir.<\/p>\n\n\n\n
        -- file# -> rowid i\u00e7indeki g\u00f6reli dosya numaras\u0131\nSELECT file#, rfile# FROM v$datafile WHERE file# = 7;\n\n-- segmentin data_object_id de\u011feri (partition ise subobject_name = partition ad\u0131)\nSELECT data_object_id FROM dba_objects\n WHERE owner = 'SCOTT' AND object_name = 'CORR_TEST' AND subobject_name IS NULL;\n\nDEFINE obj = 73412    -- data_object_id\nDEFINE fno = 7        -- rfile#\nDEFINE bs  = 1234     -- bozuk aral\u0131\u011f\u0131n ilk blo\u011fu (block#)\nDEFINE be  = 1234     -- bozuk aral\u0131\u011f\u0131n son blo\u011fu (block# + blocks - 1)<\/code><\/pre>\n\n\n\n
        Birden fazla bozuk aral\u0131k varsa her biri i\u00e7in a\u015fa\u011f\u0131daki aral\u0131k ko\u015fullar\u0131n\u0131 ekleyin.<\/p>\n\n\n\n
        Ad\u0131m 1. Kaybolacak sat\u0131rlar\u0131n anahtarlar\u0131n\u0131 listele<\/h3>\n\n\n\n
        Tabloya dokunmadan, index \u00fczerinden bozuk bloktaki sat\u0131rlar\u0131n anahtarlar\u0131n\u0131 g\u00f6rebilirsiniz. Bu liste kay\u0131p raporunuzdur.<\/p>\n\n\n\n
        SELECT \/*+ INDEX_FFS(t corr_test_idx) *\/ t.id, t.ROWID\n  FROM scott.corr_test t\n WHERE t.ROWID BETWEEN DBMS_ROWID.ROWID_CREATE(1, &obj, &fno, &bs, 0)\n                   AND DBMS_ROWID.ROWID_CREATE(1, &obj, &fno, &be, 32767);<\/code><\/pre>\n\n\n\n
        Planda INDEX FAST FULL SCAN<\/code> olmal\u0131, TABLE ACCESS<\/code> olmamal\u0131d\u0131r. Aksi halde sorgu ORA-01578 verir. Tabloda hi\u00e7 index yoksa kay\u0131p sat\u0131rlar\u0131 listeleyemezsiniz.<\/p>\n\n\n\n
        Ad\u0131m 2. Sa\u011flam sat\u0131rlar\u0131 kurtar<\/h3>\n\n\n\n
        Bozuk aral\u0131\u011f\u0131n \u00f6ncesini<\/strong> ve sonras\u0131n\u0131<\/strong> ayr\u0131 ayr\u0131 okuyup birle\u015ftiriyoruz. NOT BETWEEN<\/code> veya <><\/code> kullanmay\u0131n, \u00e7\u00fcnk\u00fc bu ko\u015ful her sat\u0131r\u0131 de\u011ferlendirmek i\u00e7in bozuk blo\u011fu da okur ve ORA-01578 verir.<\/p>\n\n\n\n
        CREATE TABLE scott.corr_test_kurtarilan TABLESPACE users NOLOGGING AS\nSELECT \/*+ FULL(t) *\/ * FROM scott.corr_test t\n WHERE t.ROWID < DBMS_ROWID.ROWID_CREATE(1, &obj, &fno, &bs, 0)\nUNION ALL\nSELECT \/*+ FULL(t) *\/ * FROM scott.corr_test t\n WHERE t.ROWID > DBMS_ROWID.ROWID_CREATE(1, &obj, &fno, &be, 32767);<\/code><\/pre>\n\n\n\n
        Salvage tablosunu bozuk dosyadan farkl\u0131 bir tablespace’e<\/strong> koyun. Partition’l\u0131 tabloda her partition’\u0131n kendi data_object_id<\/code> de\u011feri vard\u0131r, i\u015flemi sadece bozuk partition i\u00e7in FROM scott.corr_test PARTITION (p_ocak) t<\/code> ile yap\u0131n.<\/p>\n\n\n\n
        Ad\u0131m 3. Do\u011frula ve g\u00fcvene al<\/h3>\n\n\n\n
        SELECT count(*) FROM scott.corr_test_kurtarilan;<\/code><\/pre>\n\n\n\n
        Sonraki ad\u0131m TRUNCATE oldu\u011fu i\u00e7in salvage tablosu art\u0131k tek kopyad\u0131r. \u00d6nce expdp<\/code> ile d\u0131\u015far\u0131 al\u0131n.<\/p>\n\n\n\n
        Ad\u0131m 4. Bozuk blo\u011fu segmentten \u00e7\u0131kar ve veriyi geri y\u00fckle<\/h3>\n\n\n\n
        TRUNCATE<\/code> extent’leri free space’e iade eder ve bozuk blok tablodan \u00e7\u0131km\u0131\u015f olur. Tablo nesnesi, index’leri, constraint’leri, trigger’lar\u0131, grant’leri ve istatistikleri yerinde kal\u0131r, yeniden olu\u015fturman\u0131z gerekmez.<\/p>\n\n\n\n
        -- Bu tabloya referans veren FK'ler varsa \u00f6nce disable edin, trigger'lar\u0131 da kapat\u0131n\nALTER TABLE scott.corr_test DISABLE ALL TRIGGERS;\nTRUNCATE TABLE scott.corr_test;\nINSERT \/*+ APPEND *\/ INTO scott.corr_test SELECT * FROM scott.corr_test_kurtarilan;\nCOMMIT;\nALTER TABLE scott.corr_test ENABLE ALL TRIGGERS;\nDROP TABLE scott.corr_test_kurtarilan PURGE;<\/code><\/pre>\n\n\n\n
        Partition’l\u0131 tabloda sadece ALTER TABLE ... TRUNCATE PARTITION p_ocak<\/code> yeterlidir, di\u011fer partition’lara dokunulmaz. Segment header bozuksa TRUNCATE de \u00e7al\u0131\u015fmaz (b\u00f6l\u00fcm 10).<\/p>\n\n\n\n
        Ad\u0131m 5. Free space’e d\u00fc\u015fen bozuk blo\u011fu formatla<\/h3>\n\n\n\n
        Truncate sonras\u0131 bozuk blok free space’tedir. Yeniden kullan\u0131l\u0131p formatlanana kadar RMAN ve DBV onu bozuk g\u00f6stermeye devam eder, yedek de ORA-19566 ile hata verir. Hemen temizlemek i\u00e7in blo\u011fu bo\u015f bir tabloya zorla yazd\u0131r\u0131yoruz:<\/p>\n\n\n\n
        -- Bozuk dosyan\u0131n tablespace'inde, SYS olmayan kullan\u0131c\u0131yla\nCREATE TABLE scott.s (n NUMBER, c VARCHAR2(4000)) NOLOGGING PCTFREE 99 TABLESPACE corr_ts;\n\n-- Bozuk blok kullan\u0131l\u0131nca hata verecek trigger (SYS ile)\nCREATE OR REPLACE TRIGGER corrupt_trigger AFTER INSERT ON scott.s FOR EACH ROW\nBEGIN\n  IF DBMS_ROWID.ROWID_BLOCK_NUMBER(:NEW.ROWID) = &bs\n     AND DBMS_ROWID.ROWID_RELATIVE_FNO(:NEW.ROWID) = &fno THEN\n    RAISE_APPLICATION_ERROR(-20000, 'Bozuk blok formatlandi');\n  END IF;\nEND;\n\/\n\n-- Free extent boyutunu \u00f6\u011fren, ayn\u0131 boyutta extent al (blok tabloya girene kadar tekrarla)\nSELECT bytes FROM dba_free_space\n WHERE file_id = &fno AND &bs BETWEEN block_id AND block_id + blocks - 1;\nALTER TABLE scott.s ALLOCATE EXTENT (DATAFILE '+DATA\/DB\/DATAFILE\/corr_ts.280.123456' SIZE 64K);\nSELECT segment_name FROM dba_extents\n WHERE file_id = &fno AND &bs BETWEEN block_id AND block_id + blocks - 1;\n\n-- Sat\u0131r ekle, ORA-20000 gelince blok formatlanm\u0131\u015f demektir\nBEGIN FOR i IN 1..100000000 LOOP INSERT INTO scott.s VALUES (i, 'x'); END LOOP; END;\n\/<\/code><\/pre>\n\n\n\n
        Sonra temizlik:<\/p>\n\n\n\n
        ALTER SYSTEM SWITCH LOGFILE;  -- iki kez\nALTER SYSTEM CHECKPOINT;\nRMAN> VALIDATE DATAFILE 7 BLOCK 1234;\nDROP TABLE scott.s PURGE;\nDROP TRIGGER corrupt_trigger;<\/code><\/pre>\n\n\n\n
        Notlar: ASSM tablespace’te extent alma ad\u0131m\u0131n\u0131 birka\u00e7 kez tekrarlaman\u0131z gerekebilir. Datafile AUTOEXTEND<\/code> kapal\u0131 olmal\u0131d\u0131r. Flashback a\u00e7\u0131kken free bloklar\u0131n formatlanmayabildi\u011fi bilinen bir bug vard\u0131r (Bug 7381632). Blok bir dosya bitmap blo\u011fuysa formatlanamaz.<\/p>\n\n\n\n
        Alternatif: DBMS_REPAIR ile atlatma (veri ta\u015f\u0131madan, ge\u00e7ici \u00e7\u00f6z\u00fcm)<\/h3>\n\n\n\n
        Tabloyu hemen DML’e a\u00e7mak istiyorsan\u0131z bozuk blo\u011fu i\u015faretleyip atlatabilirsiniz. Blok diskte kal\u0131r ve RMAN raporlamaya devam eder.<\/p>\n\n\n\n
        BEGIN\n  DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE', DBMS_REPAIR.REPAIR_TABLE, DBMS_REPAIR.CREATE_ACTION, 'USERS');\nEND;\n\/\nDECLARE c PLS_INTEGER;\nBEGIN\n  DBMS_REPAIR.CHECK_OBJECT('SCOTT', 'CORR_TEST', repair_table_name => 'REPAIR_TABLE', corrupt_count => c);\n  DBMS_REPAIR.FIX_CORRUPT_BLOCKS('SCOTT', 'CORR_TEST', DBMS_REPAIR.TABLE_OBJECT, repair_table_name => 'REPAIR_TABLE', fix_count => c);\n  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCOTT', 'CORR_TEST', DBMS_REPAIR.TABLE_OBJECT, DBMS_REPAIR.SKIP_FLAG);\nEND;\n\/<\/code><\/pre>\n\n\n\n
        Sonra index’leri drop\/create ile yeniden olu\u015fturun. RMAN yede\u011fi i\u00e7in SET MAXCORRUPT FOR DATAFILE 7 TO 1;<\/code> gerekir. Kal\u0131c\u0131 \u00e7\u00f6z\u00fcm yine bu b\u00f6l\u00fcmdeki ROWID_CREATE + TRUNCATE y\u00f6ntemidir.<\/p>\n\n\n\n
        10. Segment tipine g\u00f6re \u00f6zel durumlar<\/h2>\n\n\n\n
        Index.<\/strong> Veri kayb\u0131 yoktur, index tablodan yeniden \u00fcretilir. DROP INDEX<\/code> + CREATE INDEX<\/code> yap\u0131n. REBUILD<\/code> eski index’i okuyabilir ve bozuk blokta tak\u0131labilir.<\/p>\n\n\n\n
        LOB.<\/strong> Bozuk LOB i\u00e7eri\u011fi kaybolur, ama sat\u0131r\u0131n kendisi sa\u011flamd\u0131r. Burada ger\u00e7ekten NULL basabilirsiniz:<\/p>\n\n\n\n
        DECLARE\n  v_buf RAW(32767); v_len NUMBER; v_pos NUMBER; v_amt NUMBER;\nBEGIN\n  FOR r IN (SELECT ROWID rid, doc FROM scott.t_lob) LOOP\n    BEGIN\n      v_len := DBMS_LOB.GETLENGTH(r.doc); v_pos := 1;\n      WHILE v_pos <= v_len LOOP\n        v_amt := 32767;\n        DBMS_LOB.READ(r.doc, v_amt, v_pos, v_buf);\n        v_pos := v_pos + v_amt;\n      END LOOP;\n    EXCEPTION WHEN OTHERS THEN\n      IF SQLCODE IN (-1578, -26040, -22922) THEN\n        UPDATE scott.t_lob SET doc = EMPTY_BLOB() WHERE ROWID = r.rid;  -- veya NULL\n        COMMIT;\n      ELSE RAISE; END IF;\n    END;\n  END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
        CLOB i\u00e7in EMPTY_CLOB()<\/code> ve VARCHAR2 buffer kullan\u0131n. Eski bozuk chunk’lar\u0131 temizlemek i\u00e7in ALTER TABLE ... MOVE LOB(doc) STORE AS (TABLESPACE yeni_ts)<\/code> yap\u0131labilir. LOB segmentinde ORA-01578 ile ORA-26040 birlikte geliyorsa Oracle’\u0131n KB151992 scriptine bak\u0131n.<\/p>\n\n\n\n
        IOT.<\/strong> Index tablonun kendisi oldu\u011fu i\u00e7in index drop\/create y\u00f6ntemi uygulanamaz. DBMS_REPAIR ile atlatma ve Oracle KB146316 scripti kullan\u0131l\u0131r.<\/p>\n\n\n\n
        Segment header.<\/strong> Header bozuksa ROWID_CREATE, TRUNCATE ve DBMS_REPAIR y\u00f6ntemleri \u00e7al\u0131\u015fmaz. Yedekten restore veya Oracle Support gerekir.<\/p>\n\n\n\n
        Free block.<\/strong> Veri kayb\u0131 yoktur. B\u00f6l\u00fcm 9, ad\u0131m 5’teki formatlama ile temizlenir.<\/p>\n\n\n\n
        Temporary segment.<\/strong> Temp tablespace’i yeniden olu\u015fturun, i\u00e7inde kal\u0131c\u0131 veri yoktur.<\/p>\n\n\n\n
        SYSTEM, UNDO, control file, redo.<\/strong> SYSTEM ve veri s\u00f6zl\u00fc\u011f\u00fc i\u00e7in yedek\/restore veya Oracle Support gerekir (hcheck.sql<\/code> ile s\u00f6zl\u00fck tutarl\u0131l\u0131\u011f\u0131 kontrol edilir). UNDO i\u00e7in yeni undo tablespace olu\u015fturup ge\u00e7i\u015f yap\u0131l\u0131r. Control file ve redo i\u00e7in multiplex kopyalar kullan\u0131l\u0131r.<\/p>\n\n\n\n
        Table\/index tutars\u0131zl\u0131\u011f\u0131<\/strong> (ORA-08102, ORA-01499). Tablo do\u011fru kabul edilir, ilgili index drop\/create ile yeniden olu\u015fturulur. ANALYZE ... VALIDATE STRUCTURE CASCADE<\/code> ile do\u011frulay\u0131n.<\/p>\n\n\n\n
        11. NOLOGGING corruption<\/h2>\n\n\n\n
        Bloklar redo \u00fcretmeden y\u00fcklendi\u011fi i\u00e7in block media recovery i\u015fe yaramaz. Tablo veya index’i yeniden olu\u015fturun, veriyi kaynaktan tekrar y\u00fckleyin. Ayn\u0131 sorunu ya\u015famamak i\u00e7in ALTER DATABASE FORCE LOGGING;<\/code> kullan\u0131n.<\/p>\n\n\n\n
        12. Onar\u0131m sonras\u0131 do\u011frulama ve \u00f6nleme<\/h2>\n\n\n\n
        RMAN> VALIDATE CHECK LOGICAL DATABASE;\nSELECT * FROM v$database_block_corruption;   -- bo\u015f olmal\u0131<\/code><\/pre>\n\n\n\n
          \n
        • \u00d6nce k\u00f6k nedeni aray\u0131n: alert log, storage ve OS\/donan\u0131m loglar\u0131. Ayn\u0131 yerde tekrar eden bozulma disk veya controller sorunudur.<\/li>\n\n\n\n
        • DB_BLOCK_CHECKSUM<\/code> a\u00e7\u0131k olsun (TYPICAL veya TRUE), bozulmalar\u0131 I\/O s\u0131ras\u0131nda yakalar.<\/li>\n\n\n\n
        • DB_BLOCK_CHECKING<\/code>‘i a\u00e7madan \u00f6nce mevcut mant\u0131ksal bozulmalar\u0131 onar\u0131n, aksi halde bozuk bloklar bir sonraki g\u00fcncellemede soft corrupt olur.<\/li>\n\n\n\n
        • D\u00fczenli VALIDATE CHECK LOGICAL DATABASE<\/code> ve yede\u011fin kendisini do\u011frulayan RESTORE VALIDATE<\/code> \u00e7al\u0131\u015ft\u0131r\u0131n.<\/li>\n<\/ul>\n\n\n\n
          13. \u00d6zet tablo<\/h2>\n\n\n\n
          Durum<\/th>Y\u00f6ntem<\/th>Veri kayb\u0131<\/th><\/tr>
          Yedek + ARCHIVELOG (her redundancy)<\/td>RECOVER CORRUPTION LIST<\/code> \/ RECOVER ... BLOCK<\/code><\/td>Yok<\/td><\/tr>
          Active Data Guard standby var<\/td>Blo\u011fu standby’dan otomatik\/RMAN ile onar<\/td>Yok<\/td><\/tr>
          Flashback log var<\/td>Block recovery flashback log’dan blok alabilir<\/td>Yok<\/td><\/tr>
          Normal\/High, tek kopya bozuk<\/td>ALTER DISKGROUP ... SCRUB ... REPAIR<\/code><\/td>Yok<\/td><\/tr>
          Normal\/High, t\u00fcm kopyalar bozuk<\/td>External gibi: yedek, standby veya ROWID_CREATE<\/td>Duruma g\u00f6re<\/td><\/tr>
          External, yedek yok<\/td>Yedek\/standby gerekir, yoksa ROWID_CREATE<\/td>Bozuk blok sat\u0131rlar\u0131<\/td><\/tr>
          Yedek var, NOARCHIVELOG<\/td>Yede\u011fi yan ortama a\u00e7, sat\u0131rlar\u0131 geri al<\/td>Yedek tarihinden sonraki de\u011fi\u015fiklikler<\/td><\/tr>
          Hi\u00e7biri yok, tablo<\/td>ROWID_CREATE ile ay\u0131r, CTAS, TRUNCATE, geri y\u00fckle, blo\u011fu formatla<\/td>Sadece bozuk bloktaki sat\u0131rlar<\/td><\/tr>
          Hi\u00e7biri yok, h\u0131zl\u0131 \u00e7\u00f6z\u00fcm<\/td>DBMS_REPAIR FIX + SKIP, sonra index’leri yeniden olu\u015ftur<\/td>Sadece bozuk bloktaki sat\u0131rlar<\/td><\/tr>
          Index<\/td>DROP + CREATE INDEX<\/td>Yok<\/td><\/tr>
          LOB<\/td>Bozuk LOB’u EMPTY\/NULL yap (PL\/SQL), gerekirse LOB MOVE<\/td>Sadece o LOB de\u011ferleri<\/td><\/tr>
          IOT<\/td>DBMS_REPAIR atlatma, KB146316 scripti<\/td>Bozuk bloktaki sat\u0131rlar<\/td><\/tr>
          Mant\u0131ksal corruption (checksum ge\u00e7erli)<\/td>Yedek varsa block recovery, yoksa tablo gibi (ROWID_CREATE)<\/td>Duruma g\u00f6re<\/td><\/tr>
          NOLOGGING<\/td>Veriyi\/index’i yeniden olu\u015ftur veya y\u00fckle<\/td>Yeniden y\u00fcklenir<\/td><\/tr>
          Table\/index tutars\u0131zl\u0131\u011f\u0131<\/td>Index’i drop\/create<\/td>Yok<\/td><\/tr>
          Free block<\/td>Dummy tablo + trigger ile formatla<\/td>Yok<\/td><\/tr>
          Segment header, SYSTEM, dictionary<\/td>Restore veya Oracle Support<\/td>Duruma g\u00f6re<\/td><\/tr>
          UNDO<\/td>Yeni undo tablespace’e ge\u00e7<\/td>Yok<\/td><\/tr>
          Temp<\/td>Temp tablespace’i yeniden olu\u015ftur<\/td>Yok<\/td><\/tr>
          Control file, redo<\/td>Multiplex kopyadan geri al<\/td>Yok<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          Bu yaz\u0131daki y\u00f6ntemlerin bir k\u0131sm\u0131 (\u00f6zellikle ASM \u00fczerinde dd ile corruption olu\u015fturma ve bozuk blok formatlama) sadece test ortam\u0131nda denenmelidir. \u00dcretimde i\u015flem yapmadan \u00f6nce Oracle Support ile g\u00f6r\u00fc\u015fmeniz \u00f6nerilir.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
          Bu yaz\u0131da v$database_block_corruption g\u00f6r\u00fcn\u00fcm\u00fcn\u00fcn nas\u0131l doldu\u011funu, laboratuvarda corruption’\u0131n nas\u0131l olu\u015fturuldu\u011funu ve bulunan corruption’\u0131n yedek var\/yok, ARCHIVELOG a\u00e7\u0131k\/kapal\u0131, ASM redundancy, corruption tipi gibi her durumda nas\u0131l giderilece\u011fini anlat\u0131yorum. En sonda t\u00fcm durumlar tek tabloda \u00f6zetleniyor.<\/p>","protected":false},"author":1,"featured_media":1087,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[],"class_list":["post-1086","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-genel-en"],"_links":{"self":[{"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/posts\/1086","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1086"}],"version-history":[{"count":1,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/posts\/1086\/revisions"}],"predecessor-version":[{"id":1088,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/posts\/1086\/revisions\/1088"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/media\/1087"}],"wp:attachment":[{"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1086"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1086"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oraclius.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1086"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}