RE: DBMS_REPAIR Package
Title: Message I dug a little more and found my answer.I found that Oracle uses DBMS_REPAIR just to put a band-aid on the corrupt blocks. It basically identifies the corrupt block and marks it so oracle skips over it like it's not even there. -Original Message-From: Stefick Ronald S Contr ESC/HRIDD Sent: Thursday, July 17, 2003 8:55 AMTo: Multiple recipients of list ORACLE-LSubject: DBMS_REPAIR Package Hasanyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify andfoundseveral corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED]210-565-2540
Re: RE: DBMS_REPAIR Package
are there any practical uses for dbms_repair? or is it just a stop gap measure to use if your not able to do a recovery at that time? From: Stefick Ronald S Contr ESC/HRIDD [EMAIL PROTECTED] Date: 2003/07/17 Thu AM 10:39:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: DBMS_REPAIR Package I dug a little more and found my answer. I found that Oracle uses DBMS_REPAIR just to put a band-aid on the corrupt blocks. It basically identifies the corrupt block and marks it so oracle skips over it like it's not even there. -Original Message- Sent: Thursday, July 17, 2003 8:55 AM To: Multiple recipients of list ORACLE-L Has anyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify and found several corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED] 210-565-2540 Title: Message I dug a little more and found my answer.I found that Oracle uses DBMS_REPAIR just to put a band-aid on the corrupt blocks. It basically identifies the corrupt block and marks it so oracle skips over it like it's not even there. -Original Message-From: Stefick Ronald S Contr ESC/HRIDD Sent: Thursday, July 17, 2003 8:55 AMTo: Multiple recipients of list ORACLE-LSubject: DBMS_REPAIR Package Hasanyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify andfoundseveral corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED]210-565-2540
Re: DBMS_REPAIR Package
Title: Message Hi Stefick, Running stored procedures/packages is somewhat difficult with a shutdown database, so the database needs to be opened. Depending on the type of corrupted block in question answers whether or not the block is actually repaired (eg. bitmap block in ASSM segments, freelist blocks...) or simply marked as corrupt and thus could be made "skippable" meaning that your FTS will now work and skip the stuffed buggers. That said, I would recommend restoring you stuffed datafile, perform a database recovery and hope the corruption wasn't duplicated in your backup(s). dbms_repair should be used if all else fails (or you don't really mind losing that "bit" of your database). Good Luck Richard Foote - Original Message - From: Stefick Ronald S Contr ESC/HRIDD To: Multiple recipients of list ORACLE-L Sent: Thursday, July 17, 2003 11:54 PM Subject: DBMS_REPAIR Package Hasanyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify andfoundseveral corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED]210-565-2540
RE: DBMS_REPAIR Package
If you ran dbverify with the database up, it's very likely that there is nothing wrong with the datafiles. If dbv reads a block in transition, it will appear corrupt. Run it again, and there will either be no errors, or they will appear to be in different blocks. Personally, I think it a colossal waste of time to run dbverify. In 9 years of DBA'ing, I've experienced maybe 3 or 4 instances of a corrupt block, and it was always an index. The solution was to drop/rebuild the index. Jared Stefick Ronald S Contr ESC/HRIDD [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/17/2003 07:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: DBMS_REPAIR Package I dug a little more and found my answer. I found that Oracle uses DBMS_REPAIR just to put a band-aid on the corrupt blocks. It basically identifies the corrupt block and marks it so oracle skips over it like it's not even there. -Original Message- Sent: Thursday, July 17, 2003 8:55 AM To: Multiple recipients of list ORACLE-L Has anyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify and found several corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED] 210-565-2540 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_REPAIR Package
There's a good argument for separating tables/indexes. [EMAIL PROTECTED] wrote: If you ran dbverify with the database up, it's very likely that there is nothing wrong with the datafiles. If dbv reads a block in transition, it will appear corrupt. Run it again, and there will either be no errors, or they will appear to be in different blocks. Personally, I think it a colossal waste of time to run dbverify. In 9 years of DBA'ing, I've experienced maybe 3 or 4 instances of a corrupt block, and it was always an index. The solution was to drop/rebuild the index. Jaredbegin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
Re: DBMS_REPAIR Package
Hi! If dbv reads a block in transition, it will appear corrupt. In 9i, I believe dbv reads a block multiple times, if it doesn't get a consistent (non-corrupted) image first time. If it gets the block ok during next attempts, block isn't reported corrupt. But if several subsequent attempts fail, a dbverify output statistic Total Pages In Flux is incremented (might be that dbv is comparing the block image just read to image of same block read in previous attempt). So that means, 9i can give correct results on open datafiles as well. (I haven't tested or verified it, its based on 9.2 docs: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch13.htm #1006796) Personally, I think it a colossal waste of time to run dbverify. No, it can be useful in several cases: validating backups, verifying restored files etc.. If at all, it can be ore like waste of IO bandwith. Also, in 9i you can do segment level dbverify, that can be quite useful in some cases. In 9 years of DBA'ing, I've experienced maybe 3 or 4 instances of a corrupt block, and it was always an index. The solution was to drop/rebuild the index. Ok, you've been lucky. I've seen them in data, index, rollback, even temp segements. And in few cases the index rebuild was definitely not a solution, because the index was a global index on one billion row table, thus it was definitely easier to restore recover in our case, than allocate huge amounts of temp space and waste a lot of IO and CPU resources for rebuilding. Cheers, Tanel. Jared Stefick Ronald S Contr ESC/HRIDD [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/17/2003 07:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: DBMS_REPAIR Package I dug a little more and found my answer. I found that Oracle uses DBMS_REPAIR just to put a band-aid on the corrupt blocks. It basically identifies the corrupt block and marks it so oracle skips over it like it's not even there. -Original Message- Sent: Thursday, July 17, 2003 8:55 AM To: Multiple recipients of list ORACLE-L Has anyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify and found several corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED] 210-565-2540 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_REPAIR package usage
Thanks for your help! I will definitely try that later today! :D It's really nice to have a full team of people helping me out when I am in a "confused" phrase. And I really do hope that there is not any "I know it's off-topic, but I just couldn't resist .." spam over there. I use my delete keys a lot more frequent than before (it is only my opinion. I am not starting any war here..) :P Winnie yong huang [EMAIL PROTECTED] on 03/24/2001 05:04:00 AM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Hi, Winnie, How did you find the file# to be 9 (unless you messed with your original error message)? I suggest you post your message to news:comp.databases.oracle.server (or http://news.interbulletin.com/cgi-bin/ibwrn/post/comp.databases.oracle.server if your company doesn't have a news server). Hopefully it will attract attention of Jonathan Lewis, the Oracle 8i expert, and several (former) Oracle employees such as Kyle Hailey, Howard Rogers and Anjo Kolk. The good thing about that newsgroup is nobody is audacious enough to post spam messages like "tomorrow I'll post the chocolate recipe here". Yong Huang [EMAIL PROTECTED] --- [EMAIL PROTECTED] wrote: Yong, Thanks a lot for all the research! :D The file# that actaully contains this block is 9. My database is not that big at all. I did do some research myself and some Oracle analysts in the World Wide Support does suggest that the influxed blocks are very likely to be a fractured block. But I reallly have no idea how it got in there... . Winnie yong huang [EMAIL PROTECTED] on 03/23/2001 04:01:21 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: DBMS_REPAIR package usage Hi, Winnie, Just a little more research. I wonder how you can have an rdba that big, 0x24070020, which is 604438560 in decimal. SQL var a number; SQL exec :a := dbms_utility.data_block_address_file(604438560); PL/SQL procedure successfully completed. SQL print A - 144 SQL exec :a := dbms_utility.data_block_address_block(604438560); PL/SQL procedure successfully completed. SQL print A - 458784 This is done on 8.1.6. It says the block is in file 144, block 458784. Why does your error say file=0? Anyway, in case you do have a file numbered 144, check to see if there's an object there. If it's indeed file 0, the dba should be the same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS can confirm this. However, that file# 0 may be just an indicator that that information is lost, as multiple other 0's look like. I believe dbv reports an error when it encounters a fractured block, i.e., the first two bytes of tail (0003 in your case) does not match the last two bytes of rdba (0020). We know how a fractured block is created during hot backup. But I don't understand why an offlined datafile (as you said in another email) can contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. Yong Huang [EMAIL PROTECTED] you wrote: I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does n ot treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don' t see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of th e DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ __ Do You Yahoo!? G
comp.databases.oracle.server (Was: Re: DBMS_REPAIR package usage)
Hi, Murali, You don't subscribe to a newsgroup such as comp.databases.oracle.server as you do to a mailing list. If your company has a news (NNTP) server, configure your browser to user it and type the URL news:comp.databases.oracle.server to read/post messages. Most companies use "news" or "snews" as the news server name. Try: telnet news 119 help quit to find out. Or ask your Help Desk. If your company does not have a news server, read Question 2 at http://groups.google.com/googlegroups/help.html. I use news.interbulletin.com currently. If you insist on using a public news server instead of a Web gateway, www.jammed.com/~newzbot/sorted-speed.html does a fairly good job on listing public servers, which come and go at their will. To use one of them, type the URL news://[the IP of the server]/comp.databases.oracle.server in your browser. News readers don't use these URLs. Yong Huang [EMAIL PROTECTED] you wrote: How does one subscribe to this ? Murali Vallath Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 24 Mar 2001 05:05:21 -0800 Hi, Winnie, How did you find the file# to be 9 (unless you messed with your original error message)? I suggest you post your message to news:comp.databases.oracle.server (or http://news.interbulletin.com/cgi-bin/ibwrn/post/comp.databases.oracle.server if your company doesn't have a news server). Hopefully it will attract... __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_REPAIR package usage
Hi Yong, How does one subscribe to this ? Murali Vallath Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 24 Mar 2001 05:05:21 -0800 Hi, Winnie, How did you find the file# to be 9 (unless you messed with your original error message)? I suggest you post your message to news:comp.databases.oracle.server (or http://news.interbulletin.com/cgi-bin/ibwrn/post/comp.databases.oracle.server if your company doesn't have a news server). Hopefully it will attract attention of Jonathan Lewis, the Oracle 8i expert, and several (former) Oracle employees such as Kyle Hailey, Howard Rogers and Anjo Kolk. The good thing about that newsgroup is nobody is audacious enough to post spam messages like "tomorrow I'll post the chocolate recipe here". Yong Huang [EMAIL PROTECTED] --- [EMAIL PROTECTED] wrote: Yong, Thanks a lot for all the research! :D The file# that actaully contains this block is 9. My database is not that big at all. I did do some research myself and some Oracle analysts in the World Wide Support does suggest that the influxed blocks are very likely to be a fractured block. But I reallly have no idea how it got in there... . Winnie yong huang [EMAIL PROTECTED] on 03/23/2001 04:01:21 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: DBMS_REPAIR package usage Hi, Winnie, Just a little more research. I wonder how you can have an rdba that big, 0x24070020, which is 604438560 in decimal. SQL var a number; SQL exec :a := dbms_utility.data_block_address_file(604438560); PL/SQL procedure successfully completed. SQL print A - 144 SQL exec :a := dbms_utility.data_block_address_block(604438560); PL/SQL procedure successfully completed. SQL print A - 458784 This is done on 8.1.6. It says the block is in file 144, block 458784. Why does your error say file=0? Anyway, in case you do have a file numbered 144, check to see if there's an object there. If it's indeed file 0, the dba should be the same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS can confirm this. However, that file# 0 may be just an indicator that that information is lost, as multiple other 0's look like. I believe dbv reports an error when it encounters a fractured block, i.e., the first two bytes of tail (0003 in your case) does not match the last two bytes of rdba (0020). We know how a fractured block is created during hot backup. But I don't understand why an offlined datafile (as you said in another email) can contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. Yong Huang [EMAIL PROTECTED] you wrote: I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does n ot treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don' t see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of th e DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services
Re: DBMS_REPAIR package usage
Hi, Winnie, Just a little more research. I wonder how you can have an rdba that big, 0x24070020, which is 604438560 in decimal. SQL var a number; SQL exec :a := dbms_utility.data_block_address_file(604438560); PL/SQL procedure successfully completed. SQL print A - 144 SQL exec :a := dbms_utility.data_block_address_block(604438560); PL/SQL procedure successfully completed. SQL print A - 458784 This is done on 8.1.6. It says the block is in file 144, block 458784. Why does your error say file=0? Anyway, in case you do have a file numbered 144, check to see if there's an object there. If it's indeed file 0, the dba should be the same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS can confirm this. However, that file# 0 may be just an indicator that that information is lost, as multiple other 0's look like. I believe dbv reports an error when it encounters a fractured block, i.e., the first two bytes of tail (0003 in your case) does not match the last two bytes of rdba (0020). We know how a fractured block is created during hot backup. But I don't understand why an offlined datafile (as you said in another email) can contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. Yong Huang [EMAIL PROTECTED] you wrote: I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does n ot treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don' t see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of th e DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_REPAIR package usage
Yong, Thanks a lot for all the research! :D The file# that actaully contains this block is 9. My database is not that big at all. I did do some research myself and some Oracle analysts in the World Wide Support does suggest that the influxed blocks are very likely to be a fractured block. But I reallly have no idea how it got in there... . Winnie yong huang [EMAIL PROTECTED] on 03/23/2001 04:01:21 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Hi, Winnie, Just a little more research. I wonder how you can have an rdba that big, 0x24070020, which is 604438560 in decimal. SQL var a number; SQL exec :a := dbms_utility.data_block_address_file(604438560); PL/SQL procedure successfully completed. SQL print A - 144 SQL exec :a := dbms_utility.data_block_address_block(604438560); PL/SQL procedure successfully completed. SQL print A - 458784 This is done on 8.1.6. It says the block is in file 144, block 458784. Why does your error say file=0? Anyway, in case you do have a file numbered 144, check to see if there's an object there. If it's indeed file 0, the dba should be the same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS can confirm this. However, that file# 0 may be just an indicator that that information is lost, as multiple other 0's look like. I believe dbv reports an error when it encounters a fractured block, i.e., the first two bytes of tail (0003 in your case) does not match the last two bytes of rdba (0020). We know how a fractured block is created during hot backup. But I don't understand why an offlined datafile (as you said in another email) can contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. Yong Huang [EMAIL PROTECTED] you wrote: I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does n ot treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don' t see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of th e DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_REPAIR package usage
I would not decide whether a block is corrupted or not, just using dbv utility. dbv reports corruption, even when analyze, exp and FTS goes through fine without any problem. dbv reported a data dictionary corruption in our case. We ran analyze, exp and FTS, no problem. But still dbv was reporting corruption even after the database was down. Further the database has to be down or the tablespace has to be offline normal for dbv to work somewhat correctly (?) Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Winnie_Liu@in fonet.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: DBMS_REPAIR package usage com 03/22/01 04:22 PM Please respond to ORACLE-L To all, I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does not treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don't see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of the DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
RE: DBMS_REPAIR package usage
since does not change datablock u can run it against online datafiles. but it would report blocks as corrupted which are being changed. offline or shutdown is the best way. if u cant bring it down, try running analyze table validate structure cascade -Mandar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 5:12 PM To: Multiple recipients of list ORACLE-L Subject: Re: DBMS_REPAIR package usage I would not decide whether a block is corrupted or not, just using dbv utility. dbv reports corruption, even when analyze, exp and FTS goes through fine without any problem. dbv reported a data dictionary corruption in our case. We ran analyze, exp and FTS, no problem. But still dbv was reporting corruption even after the database was down. Further the database has to be down or the tablespace has to be offline normal for dbv to work somewhat correctly (?) Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Winnie_Liu@in fonet.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: DBMS_REPAIR package usage com 03/22/01 04:22 PM Please respond to ORACLE-L To all, I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does not treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don't see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of the DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You m
RE: DBMS_REPAIR package usage
I do run the dbv against a down database. The same result happened. If FTP, copy are all okey, it means that the block is not a media corrupted block. It does not stop it from being a software corrupted block. If exp works fine, it only tells us that there is currently no data/object in that "corrupted block". Oracle support did tell me that if Oracle tried to create or reclaim the corrupted block, it will reformat the block. I cannot find out if it is true of not since it is impossible for me to force Oracle to create an object in those corrupted blocks! (too fragmented!) Winnie Mandar Ghosalkar [EMAIL PROTECTED]@fatcity.com on 03/22/2001 04:21:34 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: since does not change datablock u can run it against online datafiles. but it would report blocks as corrupted which are being changed. offline or shutdown is the best way. if u cant bring it down, try running analyze table validate structure cascade -Mandar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 5:12 PM To: Multiple recipients of list ORACLE-L Subject: Re: DBMS_REPAIR package usage I would not decide whether a block is corrupted or not, just using dbv utility. dbv reports corruption, even when analyze, exp and FTS goes through fine without any problem. dbv reported a data dictionary corruption in our case. We ran analyze, exp and FTS, no problem. But still dbv was reporting corruption even after the database was down. Further the database has to be down or the tablespace has to be offline normal for dbv to work somewhat correctly (?) Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Winnie_Liu@in fonet.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: DBMS_REPAIR package usage com 03/22/01 04:22 PM Please respond to ORACLE-L To all, I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does not treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don't see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of the DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to
