tag:blogger.com,1999:blog-34560741.post6089971397454576502..comments2023-11-02T12:22:23.089+03:00Comments on The Momen Blog: Practicing Block Recovery in Oracle DatabaseAsif Momenhttp://www.blogger.com/profile/08802175768050555784noreply@blogger.comBlogger48125tag:blogger.com,1999:blog-34560741.post-86512028200018479662012-12-17T22:27:15.008+03:002012-12-17T22:27:15.008+03:00Can you please tell me how you know the corruption...Can you please tell me how you know the corruption is not already in the backed up file, I realise from your example it obviously isn't but how would you tell in a real situation.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-32171077213676630982012-06-10T17:08:23.464+03:002012-06-10T17:08:23.464+03:00Thanks for this article.
Even I got error
ERROR a...Thanks for this article.<br />Even I got error <br />ERROR at line 1:<br />ORA-01157: cannot identify/lock data file 8 - see DBWR trace file<br />ORA-01110: data file 8: 'C:\MYDB\DATA\test.DBF'Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-66165934551814533342012-05-31T17:16:18.907+03:002012-05-31T17:16:18.907+03:00You have to place the tablespace in offline mode b...You have to place the tablespace in offline mode before making any modifications. Hope this helps.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-81762581578152971432012-05-30T20:27:08.911+03:002012-05-30T20:27:08.911+03:00I opened the data file using ultraedit & chang...I opened the data file using ultraedit & changed the block content from corrupt to norrupt. When I tried to save the file , i showed error message that file my be read only or used by another program or application. Save as with another name. What could be the reason for the error.? Please provide solution to save the corrupted dbf file using ultraedit.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-70697082751479599532011-10-20T19:35:08.450+03:002011-10-20T19:35:08.450+03:00Iresh,
I did not test corrupting datafiles residi...Iresh,<br /><br />I did not test corrupting datafiles residing in ASM. But that should be straight forward. You copy the datafile to OS, modify, and copy back to ASM.<br /><br />What version are you on???Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-28510680806345292932011-10-20T13:00:37.480+03:002011-10-20T13:00:37.480+03:00Thanks Asif .
I would like to know how to corrupt ...Thanks Asif .<br />I would like to know how to corrupt asm file if u have any article for that.<br /><br />On Linux ASM setup i was able to copy file to ext3 file system and follow the same procedure, it worked :)<br /><br />But on windows i am facing problem to copy ASM file to local file system <br />error:<br />"ASMCMD [+data/guidb] > cp diffcrr01.dbf c:\\<br />ASMCMD-08010: no usr name is specified in remote instance connect_string->'c'"<br /><br />Thanks in advanceIreshhttps://www.blogger.com/profile/17646016689876770847noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-89710967391460540062011-09-20T12:49:07.550+03:002011-09-20T12:49:07.550+03:00@pritesh
You should use HexEditor to modify the ...@pritesh <br /><br />You should use HexEditor to modify the datafile. Notepad is NOT an HexEditor. Use any HexEditor like UltraEdit.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-54662005092458379802011-09-20T08:12:12.672+03:002011-09-20T08:12:12.672+03:00hi thanks for a helpful post of blok recovery.plz ...hi thanks for a helpful post of blok recovery.plz clear me it is necessary to open the datafile in ultra edit mode after the tablespace offline to corrupt the block it cant be performed in open the datafile in note pad. i try your post step in note pad and facing the problem when again back the tablespace online.the error maintain below<br /><br />sql>alter tablespace corrupt_ts online;<br />ERROR at line 1:<br />ORA-01157: cannot identify/lock data file 6 - see DBWR trace file<br />ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CORRUPT01.DBF'.<br /><br />plz clear me this error due to use of note pad rahter than ultra edit;<br />the use of ultra edit is must??<br /><br />regards<br />pritesh ranjan<br />priteshranjan23@gmail.compritesh ranjanhttps://www.blogger.com/profile/11366613099365115009noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-73724397309828528732011-03-20T15:34:56.176+03:002011-03-20T15:34:56.176+03:00@Raj
I will try to add the UNIX version also.@Raj<br /><br />I will try to add the UNIX version also.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-44613925413051567662011-03-19T00:52:11.510+03:002011-03-19T00:52:11.510+03:00can you please provide me the link for unix. the l...can you please provide me the link for unix. the link posted in the article doesnt work<br /><br />Thanksrajhttps://www.blogger.com/profile/08576099898046508652noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-16265109311685455702011-03-19T00:50:59.448+03:002011-03-19T00:50:59.448+03:00Hi Asif,
i dont see the same article for UNIX. ca...Hi Asif,<br /><br />i dont see the same article for UNIX. can you give me the link for UNIXrajhttps://www.blogger.com/profile/08576099898046508652noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-4389092222360992102011-02-04T14:48:10.889+03:002011-02-04T14:48:10.889+03:00SQL> alter tablespace corrupt online;
alter tab...SQL> alter tablespace corrupt online;<br />alter tablespace corrupt online<br />*<br />ERROR at line 1:<br />ORA-01157: cannot identify/lock data file 7 - see DBWR trace file<br />ORA-01110: data file 7: '/u01/TESTDB3/oradata/corrupt.dbf'<br /><br /><br />i tried to edit the dbf file using the 'vi editor' on linux!<br /><br />Could you please tell me how to deal with this problem?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-88420024978320241222010-12-31T11:56:13.912+03:002010-12-31T11:56:13.912+03:00Thank you for posting the great post…I was looking...Thank you for posting the great post…I was looking for something like this…I found it quiet interesting, hopefully you will keep posting such blogs….Keep sharingExcel Repairhttp://www.stellarexcelrepair.com/noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-91489110743106095462010-12-13T11:11:14.830+03:002010-12-13T11:11:14.830+03:00Ok Asif, that vas also very helpful, thank you ver...Ok Asif, that vas also very helpful, thank you very much for all your support. Best wishes!!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-34079314130296123232010-12-11T15:33:05.949+03:002010-12-11T15:33:05.949+03:00@Anonymous
We are performing a BLOCK recover and ...@Anonymous<br /><br />We are performing a BLOCK recover and when we say "blockrecover datafile n block x", Oracle will restore and recover only the mentioned block(s). Block restore is implicit with this command.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-67293680076982750682010-12-10T14:09:48.397+03:002010-12-10T14:09:48.397+03:00Yes, thank you very much, it answered my question....Yes, thank you very much, it answered my question. Yeah, I should speak correctly and use recover instead of roll-back when I mean to 'go back' to a previous state.By the way, when we recover, which is our starting SCN?the SCN of the last backup?should not we firstly restore?<br /><br />Thank you very much again.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-66655352275952524322010-12-09T20:44:15.285+03:002010-12-09T20:44:15.285+03:00Glad that you found it helpful.
We did not rollb...Glad that you found it helpful. <br /><br />We did not rollback the block. We recovered the block to the last good state. We made changes to the datafile (in order to corrupt it) using a Hex Editor. These changes are not part of the database and hence will not find place in archivelog. <br /><br />I hope I answered your question.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-84161902702650656202010-12-09T14:59:49.585+03:002010-12-09T14:59:49.585+03:00I found your article very helpful. Thank you very ...I found your article very helpful. Thank you very much. I would like to ask you however one question: when we recover the corrupted block, why do we roll back to the state when the block was not corrupted? I mean, the changes that we make in order to corrupt the block, are they not stored in the archive redo log files?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-41234910821589056312010-10-07T15:14:40.168+03:002010-10-07T15:14:40.168+03:00Make sure that you are not missing any step. All t...Make sure that you are not missing any step. All the best.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-81019649590443452832010-10-04T09:21:25.123+03:002010-10-04T09:21:25.123+03:00Hai Asif,
Again thank u for ur reply . To try new...Hai Asif,<br /><br />Again thank u for ur reply . To try newly i have created new datbase named sil on the same machine itself . Datafile CORRUPT02.DBF belong new database sil and CORRUPT01.DBF belongs to my old database test123 ,tablespace name is same on both the database.<br /><br /> I have tried so many times facing the same error. I am keen to know the reason for the error and practicing block recovery.Meeranhttps://www.blogger.com/profile/05283838001033562846noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-84528838463803222792010-10-02T21:31:59.686+03:002010-10-02T21:31:59.686+03:00@Meeran
Seems you are missing some steps in betwe...@Meeran<br /><br />Seems you are missing some steps in between. In your earlier message you were trying to work with "CORRUPT01.DBF" file where as in the last message you are trying to bring "CORRUPT02.DBF" online.Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-27019836987286571022010-09-29T10:30:12.417+03:002010-09-29T10:30:12.417+03:00Hello Asif Momen,
Very nice Post but There are ma...Hello Asif Momen,<br /><br />Very nice Post but There are many scenarios in which you corrupt your data.In my knowledge RMAN detects only the physically corrupt data blocks on Oracle database. It does not automatically find the logical corruption of the blocks. You must specify the logical block corruption checking using the 'CHECK LOGICAL' option.If RMAN cannot detect logical and physical corruption to Oracle database and fix it than this is cause of severe data loss.In this case you can use Good <a href="http://www.oracledatabaserecovery.com/" rel="nofollow">Oracle Database Recovery</a> Software.Pretthttps://www.blogger.com/profile/06588927900576798475noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-83571422934262298492010-09-29T10:04:42.408+03:002010-09-29T10:04:42.408+03:00Hai Asif,
Thank U for ur reply.
Again i repeate...Hai Asif,<br /><br /> Thank U for ur reply.<br />Again i repeated the same step what u have mentioned in the post.<br />Facing the same error <br /><br />SQL> alter tablespace corrupt_ts online;<br />alter tablespace corrupt_ts online<br />*<br />ERROR at line 1:<br />ORA-01157: cannot identify/lock data file 6 - see DBWR trace file<br />ORA-01110: data file 6: 'C:\MYDB\DATA\CORRUPT02.DBF'<br /><br /><br /> In the alert log file <br /><br />ORA-01157: cannot identify/lock data file 6 - see DBWR trace file<br />ORA-01110: data file 6: 'C:\MYDB\DATA\CORRUPT02.DBF'<br />ORA-27046: file size is not a multiple of logical block size<br />OSD-04000: logical block size mismatch (OS 8192)<br /><br /> How i can resolve this error and continue in doing the block recovery practice.Meeranhttps://www.blogger.com/profile/05283838001033562846noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-48917284511165117022010-09-28T13:15:29.339+03:002010-09-28T13:15:29.339+03:00@Meeran,
When you save a file in UltraEdit it aut...@Meeran,<br /><br />When you save a file in UltraEdit it automatically backs up the original file with a "bak" extension. <br /><br />Since you reverted the modified file with the backed up file, you were able to bring the tablespace online.<br /><br />You should ignore this backed up file and continue as mentioned in the post.<br /><br />Happy testing !Asif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-57364505223009827492010-09-28T12:59:38.002+03:002010-09-28T12:59:38.002+03:00Hai asif,
I followed the same steps as u did to...Hai asif,<br /><br /> I followed the same steps as u did to practice block corruption but facing the same error as claude faced.<br /><br />1.After opening the file with ultra edit pressed crtl + h then ctrl + f typed the word LET ME CORRUPT and changed the word C to N in the file.<br /><br />2.Saved the file by ctl + s ,closed ultra editor<br /> and i could see new file created with name CORRUPT01.DBF.bak <br /><br />3.SQL> alter tablespace corrupt_ts online;<br />alter tablespace corrupt_ts online<br />*<br />ERROR at line 1:<br />ORA-01157: cannot identify/lock data file 8 - see DBWR trace file<br />ORA-01110: data file 8: 'C:\MYDB\DATA\CORRUPT01.DBF'<br /><br />Please correct me if i am doing anything wrong .<br /><br />4.What i observed with new file CORRUPT01.DBF.bak is the original file CORRUPT01.DBF is renamed to CORRUPT01.DBF.bak and created new file CORRUPT01.DBF which contains entry "LET ME NCORRUTPT" and <br />CORRUPT01.DBF.bak contains entry "LET ME CORRUPT".<br /><br />5.I have deleted the file CORRUPT01.DBF and renamed CORRUPT01.DBF.bak as CORRUPT01.DBF.<br /><br /><br />SQL> alter tablespace corrupt_ts online;<br /><br />Tablespace altered.<br /><br />SQL> show user;<br />USER is "SYS"<br />SQL> conn<br />Enter user-name: testcrpt<br />Enter password:<br />Connected.<br />SQL> select * from t1;<br /><br /> RNO OBJECT_NAME<br />---------- ------------------------------<br /> 1 AQ$_AGENT<br /> 2 AQ$_DEQUEUE_HISTORY<br /> 3 AQ$_SUBSCRIBERS<br /> 4 AQ$_RECIPIENTS<br /> 5 AQ$_HISTORY<br /> 6 AQ$_NOTIFY_MSG<br /> 7 AQ$_SIG_PROP<br /> 8 AQ$_MIDARRAY<br /> 9 AQ$INTERNET_USERS<br /> 10 AQ$_NTFN_DESCRIPTOR<br /> 11 AQ$_DESCRIPTOR<br /><br /> RNO OBJECT_NAME<br />---------- ------------------------------<br /> 12 AQ$_REG_INFO<br /> 13 AQ$_POST_INFO<br /> 14 AQ$_REG_INFO_LIST<br /> 15 AQ$_POST_INFO_LIST<br /> 16 AQ$_SUBSCRIBER<br /> 17 AQ$_SUBSCRIBER_T<br /> 18 AQ$_DUMMY_T<br /> 19 AQ$_JMS_USERPROPERTY<br /> 20 AQ$_JMS_USERPROPARRAY<br /> 21 AQ$_JMS_HEADER<br /> 22 AQ$_JMS_MESSAGE<br /><br /> RNO OBJECT_NAME<br />---------- ------------------------------<br /> 23 AQ$_JMS_TEXT_MESSAGE<br /> 24 AQ$_JMS_BYTES_MESSAGE<br /> 25 AQ$_JMS_STREAM_MESSAGE<br /> 26 AQ$_JMS_MAP_MESSAGE<br /> 27 AQ$_JMS_OBJECT_MESSAGE<br /> 28 AQ$_JMS_VALUE<br /> 29 AQ$_JMS_EXCEPTION<br /> 30 AQ$_JMS_NAMEARRAY<br /> 31 AQ$_JMS_MESSAGES<br /> 32 AQ$_JMS_TEXT_MESSAGES<br /> 33 AQ$_JMS_BYTES_MESSAGES<br /><br /> RNO OBJECT_NAME<br />---------- ------------------------------<br /> 34 AQ$_JMS_MAP_MESSAGES<br /> 35 AQ$_JMS_STREAM_MESSAGES<br /> 36 AQ$_JMS_OBJECT_MESSAGES<br /> 37 AQ$_JMS_MESSAGE_PROPERTY<br /> 38 AQ$_JMS_MESSAGE_PROPERTIES<br /> 39 AQ$_JMS_ARRAY_MSGID_INFO<br /> 40 AQ$_JMS_ARRAY_MSGIDS<br /> 41 AQ$_JMS_ARRAY_ERROR_INFO<br /> 42 AQ$_JMS_ARRAY_ERRORS<br /> 99 LET ME CORRUPT<br /><br />43 rows selected.Meeranhttps://www.blogger.com/profile/05283838001033562846noreply@blogger.com