{"id":2768,"date":"2018-02-02T23:02:25","date_gmt":"2018-02-02T14:02:25","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2768"},"modified":"2018-02-02T23:14:43","modified_gmt":"2018-02-02T14:14:43","slug":"block-recovery-%eb%a5%bc-%ec%9c%84%ed%95%9c-corruption-block-%ec%b0%be%ec%95%84%eb%82%b4%ea%b8%b0","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2768","title":{"rendered":"Block Recovery \ub97c \uc704\ud55c Corruption Block \ucc3e\uc544\ub0b4\uae30"},"content":{"rendered":"

 <\/p>\n

1. datafile \uc5d0 block corruption\uc774 \uc77c\uc5b4 \ub0ac\uc744 \uacbd\uc6b0<\/p>\n

dbv file=’\/nas1\/backup\/oradata1\/system01.dbf’ blocksize=8192
\ndbv file=’\/nas1\/backup\/oradata1\/undotbs01.dbf’ blocksize=8192<\/p>\n

dbv file=’\/restore\/oradata\/ora8i\/system01.dbf’ blocksize=8192
\ndbv file=’\/restore\/home3\/home3\/cti_ts1.dbf’ blocksize=8192
\ndbv file=’\/home1\/app\/oracle\/product\/8.1.7\/oradata\/ora8i\/rbs01.dbf’ blocksize=8192<\/p>\n

dbv file=’\/dbf\/hotbackup\/backup2\/control.19092007065048′ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/datafile01.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/datafile02.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/datafile03.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/datafile04.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/datafile05.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/datafile06.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/indx01.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/indx02.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/indx03.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/indx04.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/sysaux01.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/system01.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/undotbs01.dbf’ blocksize=8192
\ndbv file=’\/dbf\/hotbackup\/backup2\/users01.dbf’ blocksize=8192<\/p>\n

DBVERIFY – Verification starting : FILE = \/disk1\/INDEX\/pamridx03.dbf<\/p>\n

Block Checking: DBA = 90216039, Block Type = KTB-managed data block
\n**** actual rows locked by itl 30 = 0 != # in trans. header = 1
\n**** actual free space credit for itl 30 = 0 != # in trans. hdr = 24
\n**** actual rows marked deleted = 3 != kdxlende = 28<\/p>\n

—- end index block validation<\/p>\n

Page 2135655 failed with check code 6401<\/p>\n

DBVERIFY – Verification complete
\nTotal Pages Examined : 3840000
\nTotal Pages Processed (Data) : 0
\nTotal Pages Failing (Data) : 0
\nTotal Pages Processed (Index): 3176746
\nTotal Pages Failing (Index): 1
\nTotal Pages Processed (Other): 12
\nTotal Pages Empty : 663242
\nTotal Pages Marked Corrupt : 0
\nTotal Pages Influx : 0<\/p>\n

2. Block Checking: DBA = 90216039\ub97c \uc774\uc6a9\ud558\uc5ec file#, block#\ub97c \ucc3e\uc544\ub0b8\ub2e4.<\/p>\n

CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS
\nx NUMBER;
\ndigits# NUMBER;
\nresults NUMBER := 0;
\nfile# NUMBER := 0;
\nblock# NUMBER := 0;
\ncur_digit CHAR(1);
\ncur_digit# NUMBER;
\nBEGIN
\nIF upper(imode) = ‘H’ THEN
\ndigits# := length( iblock );
\nFOR x IN 1..digits# LOOP
\ncur_digit := upper(substr( iblock, x, 1 ));
\nIF cur_digit IN (‘A’,’B’,’C’,’D’,’E’,’F’) THEN
\ncur_digit# := ascii( cur_digit ) – ascii(‘A’) +10;
\nELSE
\ncur_digit# := to_number(cur_digit);
\nEND IF;
\nresults := (results *16) + cur_digit#;
\nEND LOOP;
\nELSE
\nIF upper(imode) = ‘D’ THEN
\nresults := to_number(iblock);
\nELSE
\ndbms_output.put_line(‘H = Hex Input … D = Decimal Input’);
\nRETURN;
\nEND IF;
\nEND IF;<\/p>\n

file# := dbms_utility.data_block_address_file(results);
\nblock# := dbms_utility.data_block_address_block(results);<\/p>\n

dbms_output.put_line(‘.’);
\ndbms_output.put_line( ‘The file is ‘ || file# );
\ndbms_output.put_line( ‘The block is ‘ || block# );
\nEND;
\n\/<\/p>\n

SQL> set serveroutput on
\nSQL> exec cdba(‘ 130144565′,’d’);<\/p>\n

The file is 21
\nThe block is 2135655
\nThe file is 31
\nThe block is 121141<\/p>\n

3. \uc704 file#, block#\ub97c \uc774\uc6a9\ud558\uc5ec \uc5b4\ub5a4 \uc778\ub371\uc2a4 \ud639\uc740 \ud14c\uc774\ube14\uc5d0 \ubb38\uc81c\uc778\uc9c0\ub97c \ucc3e\uc544\ub0b8\ub2e4.<\/p>\n

SELECT tablespace_name, segment_type, owner, segment_name
\nFROM dba_extents
\nWHERE file_id = 31
\nand 121141 between block_id AND block_id + blocks – 1;<\/p>\n

TABLESPACE_NAME SEGMENT_TYPE OWNER
\n—————————— —————— ——————————
\nSEGMENT_NAME
\n——————————————————————————–
\nCTI_IDX_TS INDEX CTI
\nCT_SERVICE_LOG_NEW_PK<\/p>\n

SELECT tablespace_name, segment_type, owner, segment_name
\nFROM dba_extents
\nWHERE file_id = 22
\nand &b between block_id AND block_id + blocks – 1;<\/p>\n","protected":false},"excerpt":{"rendered":"

  1. datafile \uc5d0 block corruption\uc774 \uc77c\uc5b4 \ub0ac\uc744 \uacbd\uc6b0 dbv file=’\/nas1\/backup\/oradata1\/system01.dbf’ blocksize=8192 dbv file=’\/nas1\/backup\/oradata1\/undotbs01.dbf’ blocksize=8192 dbv file=’\/restore\/oradata\/ora8i\/system01.dbf’ blocksize=8192 dbv file=’\/restore\/home3\/home3\/cti_ts1.dbf’ blocksize=8192 dbv file=’\/home1\/app\/oracle\/product\/8.1.7\/oradata\/ora8i\/rbs01.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/control.19092007065048′ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/datafile01.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/datafile02.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/datafile03.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/datafile04.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/datafile05.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/datafile06.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/indx01.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/indx02.dbf’ blocksize=8192 dbv file=’\/dbf\/hotbackup\/backup2\/indx03.dbf’ blocksize=8192 dbv […]<\/p>\n","protected":false},"author":1,"featured_media":2338,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[907,906],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/oracle-cloud-logo.png?fit=300%2C194","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2768"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2768"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2768\/revisions"}],"predecessor-version":[{"id":2769,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2768\/revisions\/2769"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2338"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2768"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}