{"id":2673,"date":"2018-02-02T21:28:54","date_gmt":"2018-02-02T12:28:54","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2673"},"modified":"2018-02-02T21:28:54","modified_gmt":"2018-02-02T12:28:54","slug":"data-block-corruption-ora-1578-%ed%95%b4%ea%b2%b0%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2673","title":{"rendered":"DATA BLOCK CORRUPTION : ORA-1578 \ud574\uacb0\ubc29\ubc95"},"content":{"rendered":"

\ubaa8\ub4e0 \uc624\ub77c\ud074 \ub370\uc774\ud0c0 \ube14\ub7ed\uc740sequence \u00a0 \ubc88\ud638(seq)\uc640incarnation \ubc88\ud638(inc)\ub97c \uac16\uace0 \uc788\ub2e4. ORA-1578 \uc5d0\ub7ec\ub294seq=0 \uc774\uace0inc<>0(\uc0c8\ub85c\uc6b4 \ube14\ub7ed\uc774 \uc544\ub2d8)\uc77c \ub54c \ubc1c\uc0dd\ud55c\ub2e4. ORA-1578 \uc5d0\ub7ec\ub294ORA-600[3339] \uc5d0\ub7ec\uc640 \ud568\uaed8 \ubc1c\uc0dd\ud558\uace4 \ud55c\ub2e4.<\/p>\n

 <\/p>\n

ORA-1578 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\uba74 corruption\u00a0\u00a0 \ubc1c\uc0dd\ud55c \ud654\uc77c\ubc88\ud638\uc640 \ube14\ub7ed\ubc88\ud638\ub97c \uc54c\ub824\uc900\ub2e4. \uc5ec\uae30\uc11c\ub294 \uc774 \ub54c\uc758 \ud654\uc77c\ubc88\ud638\ub97cf, \ube14\ub7ed\ubc88\ud638\ub97cb \ub77c\uace0 \ubd80\ub974\uae30\ub85c \ud55c\ub2e4.<\/p>\n

 <\/p>\n

\ud574\uacb0\ubc29\ubc95<\/strong><\/p>\n

 <\/p>\n

1)\uc6b0\uc120 \ud574\uc57c \ud560 \uc77c\uc740 \u00a0 \uc5b4\ub5a0\ud55c \uc624\ube0c\uc81d\ud2b8\uac00corrup \ub418\uc5c8\ub294\uac00\ub97c \uc54c\uc544\ub0b4\ub294 \uac83\uc774\ub2e4.<\/p>\n

\ub2e4\uc74c\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud558\uba74 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

SQL>select segment_name, segment_type<\/p>\n

from dba_extents<\/p>\n

where file_id = f and<\/p>\n

b between block_id and block_id + blocks – 1;<\/p>\n

 <\/p>\n

2)\ub9cc\uc57d \ud574\ub2f9 \uc138\uadf8\uba3c\ud2b8\uac00 \uc778\ub371\uc2a4\uc774\uba74drop \uc2dc\ud0a4\uace0 \ub2e4\uc2dc \uc0dd\uc131\ud558\uba74 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

3)\ub9cc\uc57d \ud574\ub2f9 \uc138\uadf8\uba3c\ud2b8\uac00 \ud14c\uc774\ube14\uc774\uba74corrup \u00a0 \ube14\ub7ed\uc758 \ub370\uc774\ud0c0\ub294 \uc190\uc0c1\ub41c \uac83\uc774\ub2e4.<\/p>\n

 <\/p>\n

4)\ub9cc\uc57d \ud574\ub2f9 \ud14c\uc774\ube14\uc774 \ub4e4\uc5b4\uc788\ub294 \u00a0 \uc5d1\uc2a4\ud3ec\ud2b8 \ud654\uc77c\uc774 \uc788\ub2e4\uba74 \uc190\uc0c1\ub41c \ud14c\uc774\ube14\uc744drop \uc2dc\ud0a4\uace0 \uc784\ud3ec\ud2b8 \ubc1b\ub294 \uac83\uc774 \uc81c\uc77c \uac04\ub2e8\ud55c \ubc29\ubc95\uc774\ub2e4. \ud558\uc9c0\ub9cc \ub9cc\uc57d \uc5d1\uc2a4\ud3ec\ud2b8 \ubc1b\uc740 \ud654\uc77c\uc774 \uc5c6\uac70\ub098 \ubc31\uc5c5\ud574\ub454 \ud654\uc77c\ub3c4 \uc5c6\ub2e4\uba74 \ud574\ub2f9 \ud14c\uc774\ube14\uc5d0 \uc778\ub371\uc2a4\uac00 \uc0dd\uc131\ub418\uc5b4 \uc788\ub294 \uacbd\uc6b0\uc5d0 \ud55c\ud574\uc11c \ub2e4\uc74c\uc758 \ubc29\ubc95\uc744 \uc0ac\uc6a9\ud574\uc11c \ubcf5\uad6c\ub97c \ud558\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n

 <\/p>\n

5)\ub9cc\uc57d \ud574\ub2f9 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \uc778\ub371\uc2a4\uac00 \uc0dd\uc131\ub418\uc5b4 \uc788\ub2e4\uba74 \uc774\ub97c \uc774\uc6a9\ud574\uc11ccorrup \ub41c \ube14\ub7ed\uc744 \ud53c\ud574\uac08 \uc218 \uc788\ub2e4. \uc774 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

 <\/p>\n

– empno, ename, deptno \ub97c \uceec\ub7fc\uc73c\ub85c \uac00\uc9c0\ub294emp \ud14c\uc774\ube14\uc774corrup\ub418\uc5c8\ub2e4\uace0 \uac00\uc815\ud558\uc790. \uadf8\ub9ac\uace0empno\uceec\ub7fc\uc5d0 \uc778\ub371\uc2a4\uac00 \uc0dd\uc131\ub418\uc5b4 \uc788\ub2e4\uace0 \ud558\uc790.\ud074\ub7ec\uc2a4\ud130\ud654\ub418\uc9c0 \uc54a\uc740 \ubaa8\ub4e0 \u00a0 \ud14c\uc774\ube14\uc740 \uc720\ub2c8\ud06c\ud55crowid\ub97c \uac00\uc9c4\ub2e4. rowid\ub97cvarchar2\/hexadecimal \ud615\uc2dd\uc73c\ub85c \ud45c\ud604\ud558\ub824\uba74rowidtochar \ud568\uc218\ub97c \uc774\uc6a9\ud55c\ub2e4.<\/p>\n

 <\/p>\n

SQL>select rowidtochar(rowid) from emp;<\/p>\n

 <\/p>\n

rowid\ub294 \ucd1d18\uc790\ub85c \ube14\ub7ed\uc5b4\ub4dc\ub808\uc2a4(8\uc790), \uc810(1\uc790), \ub85c\uc6b0 \uc5b4\ub4dc\ub808\uc2a4(4\uc790), \uc810(1\uc790), \ud654\uc77c \uc5b4\ub4dc\ub808\uc2a4(4\uc790)\ub85c \uad6c\uc131\ub418\uc5b4 \uc788\ub2e4.<\/p>\n

 <\/p>\n

SQL>select empno, rowid<\/p>\n

from emp<\/p>\n

where empno> 0<\/p>\n

\uc704\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc2e4\ud589\uc2dc\ud0a4\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uacb0\uacfc\ub97c \uc5bb\uac8c \ub41c\ub2e4.<\/p>\n

 <\/p>\n

EMPNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 ROWID<\/p>\n

—–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 ——<\/p>\n

100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000003.0000.0006<\/p>\n

101\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000003.0001.0006<\/p>\n

102\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000003.0002.0006<\/p>\n

103\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000003.0003.0006<\/p>\n

.<\/p>\n

.<\/p>\n

500\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000004.0000.000A<\/p>\n

501\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000004.0001.000A<\/p>\n

.<\/p>\n

.<\/p>\n

755\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 0000001A.0005.000A<\/p>\n

756\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 0000001A.000C.000A<\/p>\n

 <\/p>\n

\ub9cc\uc57d \uc778\ub371\uc2a4\uac00character \uceec\ub7fc\uc5d0 \ub300\ud55c \uac83\uc774\uc5c8\ub2e4\uba74 \uc704\uc758query \ubb38\uc7a5\uc744 \ub2e4\uc74c\uacfc \uac19\uc774 \ubc14\uafc0 \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

SQL>select empno, rowid<\/p>\n

from emp<\/p>\n

where empno> ”;<\/p>\n

 <\/p>\n

\uc608\ub97c \ub4e4\uc5b4 \ub2e4\uc74c\uacfc \uac19\uc740 \uc5d0\ub7ec\uba54\uc2dc\uc9c0\uac00 \ub5a8\uc5b4\uc84c\ub2e4\uace0 \ud558\uc790.<\/p>\n

 <\/p>\n

01578, 00000, “ORACLE data block corrupted (file # 10, block # 4)<\/p>\n

 <\/p>\n

\uadf8\ub7ec\uba74 \ub2e4\uc74c\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec \u00a0 \uc190\uc0c1\ub41c \ube14\ub7ed\uc5d0 \uc788\ub294employee \uc5d0 \ub300\ud55cempno\ub97c \uad6c\ud560 \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

SQL>select empno from emp<\/p>\n

where empno> 0<\/p>\n

and rowidtochar(rowid) like ‘00000004.%.000A’;<\/p>\n

EMPNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 ROWID<\/p>\n

—–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 —–<\/p>\n

500\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000004.0000.000A<\/p>\n

501\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 00000004.0001.000A<\/p>\n

 <\/p>\n

\uc774\uc81cemp \ud14c\uc774\ube14\uacfc \uac19\uc740 \uad6c\uc870\ub97c \uac16\ub294 \uc0c8\ub85c\uc6b4 \ud14c\uc774\ube14\uc744 \ub9cc\ub4e0\ub2e4.<\/p>\n

 <\/p>\n

SQL>create table temp<\/p>\n

as select * from emp<\/p>\n

where 1 = 2;<\/p>\n

 <\/p>\n

\uadf8\ub9ac\uace0\ub294 \uc190\uc0c1\ub41c \ubd80\ubd84\uc744 \ud53c\ud574\uc11c \u00a0 \uc0c8\ub85c\uc6b4 \ud14c\uc774\ube14\uc5d0 \uc190\uc0c1\ub41c \ud14c\uc774\ube14\uc758 \ub370\uc774\ud0c0\ub97c \ucd94\uac00\ud55c\ub2e4.<\/p>\n

SQL>insert into temp select * from emp where empno < 500;<\/p>\n

SQL>insert into temp select * from emp where empno > 501;<\/p>\n

\uc190\uc0c1\ub41c \ud14c\uc774\ube14\uc744drop \uc2dc\ud0a4\uace0 temp \ud14c\uc774\ube14\uc758 \uc774\ub984\uc744emp\ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n

\uadf8\ub9ac\uace0 \ubc31\uc5c5\ub41c \uc790\ub8cc\ub098 \ubb38\uc11c\uc790\ub8cc\ub97c \ud1b5\ud558\uc5ec \uc190\uc0c1\ub41c \ubd80\ubd84\uc5d0 \ub300\ud55c \uc815\ubcf4\ub97c \ucd94\uac00\ud55c\ub2e4.<\/p>\n

6)\uc190\uc0c1\ub41c \ube14\ub7ed\uc5d0 \uc5ec\ub7ec\uac1c\uc758 \ub85c\uc6b0\uac00 \u00a0 \uc874\uc7ac\ud558\uace0 \uc788\ub2e4\uba74 \ub2e4\uc74c\uc758 \ubc29\ubc95\uc744 \uc774\uc6a9\ud55c\ub2e4.<\/p>\n

 <\/p>\n

SQL>create table empnos as<\/p>\n

select empno from emp<\/p>\n

where empno>0<\/p>\n

and rowidtochar(rowid) not like ‘00000004.%.000A’;<\/p>\n

 <\/p>\n

\uc774 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud558\uba74 \uc190\uc0c1\ub41c \u00a0 \ube14\ub7ed\uc5d0 \ud3ec\ud568\ub418\uc9c0 \uc54a\ub294empno \ub4e4\uc744 \uc54c\uc218 \uc788\ub2e4.<\/p>\n

\ub2e4\uc74c\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uacc4\uc18d \uc2e4\ud589\uc2dc\ucf1c \ubcf5\uad6c\ub97c \ud55c\ub2e4.<\/p>\n

 <\/p>\n

SQL>create table temp \u00a0 as select * from emp where 1 = 2;<\/p>\n

SQL>insert into temp<\/p>\n

select emp.empno, emp.ename, emp.deptno<\/p>\n

from emp, empnos<\/p>\n

where emp.empno > 0<\/p>\n

and emp.empno = empnos.empno;<\/p>\n

 <\/p>\n

7)\ub9cc\uc57d \ub370\uc774\ud0c0 \ub515\uc154\ub108\ub9ac\uc758 \ud14c\uc774\ube14\uc774\ub098 \uc778\ub371\uc2a4\uc5d0\uc11c \uc190\uc0c1\ub41c \ube14\ub7ed\uc774 \ubc1c\uc0dd\ud588\ub2e4\uba74 \uc9c0\uc6d0\uc744 \uc694\uccad\ud574\uc57c \ud55c\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"

\ubaa8\ub4e0 \uc624\ub77c\ud074 \ub370\uc774\ud0c0 \ube14\ub7ed\uc740sequence \u00a0 \ubc88\ud638(seq)\uc640incarnation \ubc88\ud638(inc)\ub97c \uac16\uace0 \uc788\ub2e4. ORA-1578 \uc5d0\ub7ec\ub294seq=0 \uc774\uace0inc<>0(\uc0c8\ub85c\uc6b4 \ube14\ub7ed\uc774 \uc544\ub2d8)\uc77c \ub54c \ubc1c\uc0dd\ud55c\ub2e4. ORA-1578 \uc5d0\ub7ec\ub294ORA-600[3339] \uc5d0\ub7ec\uc640 \ud568\uaed8 \ubc1c\uc0dd\ud558\uace4 \ud55c\ub2e4.   ORA-1578 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\uba74 corruption\u00a0\u00a0 \ubc1c\uc0dd\ud55c \ud654\uc77c\ubc88\ud638\uc640 \ube14\ub7ed\ubc88\ud638\ub97c \uc54c\ub824\uc900\ub2e4. \uc5ec\uae30\uc11c\ub294 \uc774 \ub54c\uc758 \ud654\uc77c\ubc88\ud638\ub97cf, \ube14\ub7ed\ubc88\ud638\ub97cb \ub77c\uace0 \ubd80\ub974\uae30\ub85c \ud55c\ub2e4.   \ud574\uacb0\ubc29\ubc95   1)\uc6b0\uc120 \ud574\uc57c \ud560 \uc77c\uc740 \u00a0 \uc5b4\ub5a0\ud55c \uc624\ube0c\uc81d\ud2b8\uac00corrup \ub418\uc5c8\ub294\uac00\ub97c \uc54c\uc544\ub0b4\ub294 \uac83\uc774\ub2e4. \ub2e4\uc74c\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud558\uba74 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"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":[14],"tags":[875,873,874],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2673"}],"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=2673"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2673\/revisions"}],"predecessor-version":[{"id":2674,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2673\/revisions\/2674"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2673"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}