RE: corrupted block
-Original Message- Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. -- Sunspots. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: corrupted block
Rachel, Were you running the validate command on your backups? It would be interesting to see if that wasn't cutting the mustard either. Brian Spears -Original Message- Sent: Wednesday, February 26, 2003 9:59 PM To: Multiple recipients of list ORACLE-L I wish it was someone trying to do that. This is what I get after I restore a good rman backup to a bad disk. I have hundreds of these messages (or similar ones) in that alert log file sigh. My data center operations people are insisting that it CAN'T be hardware problems. Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. --- Jonathan Lewis [EMAIL PROTECTED] wrote: That's the cutest corruption I've ever seen - it looks like someone has been practising there C programming with How to write direct to an Oracle data file without using Oracle Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 19:14 Here you go: *** Corrupt block relative dba: 0x024a (file 9, block 10) Bad header found during buffer read Data in bad block - type: 32 format: 0 rdba: 0x20202020 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20 consistency value in tail: 0x20202020 check value in block header: 0x2020, block checksum disabled spare1: 0x20, spare2: 0x20, spare3: 0x2020 *** Reread of rdba: 0x024a (file 9, block 10) found same corrupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Spears, Brian 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: corrupted block
it's not a hardware problem. the fact that the filesystem failed at 6AM this morning is merely a collective hallucination yes, it went down hard. My database was not on it, I had insisted they move all the files. They didn't move the Oracle binaries though (there is no hardware problem) so we are down anyway, while they reinstall Oracle to a different filesystem I'm getting tired of recovering this database. Over and over and over again. I've got this recovery scenario down pat, let's move on to a new one to try --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. -- Sunspots. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: corrupted block
6 AM --- A new shift comes on. Or is that when the janitor shows up and needs a place to plug in his vacuum cleaner? I am always suspicious of hardware/system failures that fall right on the hour. I once had an Oracle database on an AIX system. Every morning, when I came in, it was just starting up. Turns out that the SA like to have a clean system so at the start of his shift he would hit the RESET button on his RS/6000. No warning or notification, no graceful shutdown, just punch the big red button. He also thought that backing up the entire database once a week would take too long so he would back up one RAID device every other day. With three RAID devices that gave him a weekly backup. I finally got him educated but he didn't pass it on to his subs. He was in Jamaica (mon) for a week when we had a power outage. The UPS worked and we were able to keep going but two days later we lost all the files on our RAID devices. They located him in Jamaica, flew him back, he walked into the computer run, flipped the switch on the RAID devices' UPS, and they flew him back to Jamaica. His sub had reset the computer's UPS but had ignored that blinking red light over in the corner. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100 cc: @yahoo.com Subject: RE: corrupted block Sent by: root 02/27/2003 10:10 AM Please respond to ORACLE-L it's not a hardware problem. the fact that the filesystem failed at 6AM this morning is merely a collective hallucination yes, it went down hard. My database was not on it, I had insisted they move all the files. They didn't move the Oracle binaries though (there is no hardware problem) so we are down anyway, while they reinstall Oracle to a different filesystem I'm getting tired of recovering this database. Over and over and over again. I've got this recovery scenario down pat, let's move on to a new one to try --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. -- Sunspots. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: corrupted block
Brian, I can ask. When I try to do something Oracle on the production boxes I get my hand slapped and am told we pay the hosting company to do that. When I ask, I sometimes get the info I need. Rachel --- Spears, Brian [EMAIL PROTECTED] wrote: Rachel, Were you running the validate command on your backups? It would be interesting to see if that wasn't cutting the mustard either. Brian Spears -Original Message- Sent: Wednesday, February 26, 2003 9:59 PM To: Multiple recipients of list ORACLE-L I wish it was someone trying to do that. This is what I get after I restore a good rman backup to a bad disk. I have hundreds of these messages (or similar ones) in that alert log file sigh. My data center operations people are insisting that it CAN'T be hardware problems. Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. --- Jonathan Lewis [EMAIL PROTECTED] wrote: That's the cutest corruption I've ever seen - it looks like someone has been practising there C programming with How to write direct to an Oracle data file without using Oracle Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 19:14 Here you go: *** Corrupt block relative dba: 0x024a (file 9, block 10) Bad header found during buffer read Data in bad block - type: 32 format: 0 rdba: 0x20202020 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20 consistency value in tail: 0x20202020 check value in block header: 0x2020, block checksum disabled spare1: 0x20, spare2: 0x20, spare3: 0x2020 *** Reread of rdba: 0x024a (file 9, block 10) found same corrupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Spears, Brian 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: corrupted block
Ome of our sys admins once assigned two file systems to the same area of disk which as you might expect caused a multitude of problems. I don't believe the I/O system complained at all when one file system would overwrite blocks written by another. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 27, 2003 7:10 AM To: Multiple recipients of list ORACLE-L it's not a hardware problem. the fact that the filesystem failed at 6AM this morning is merely a collective hallucination yes, it went down hard. My database was not on it, I had insisted they move all the files. They didn't move the Oracle binaries though (there is no hardware problem) so we are down anyway, while they reinstall Oracle to a different filesystem I'm getting tired of recovering this database. Over and over and over again. I've got this recovery scenario down pat, let's move on to a new one to try --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. -- Sunspots. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: MacGregor, Ian A. 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: corrupted block
I rounded... it was actually something like 6:04AM :) --- Thomas Day [EMAIL PROTECTED] wrote: 6 AM --- A new shift comes on. Or is that when the janitor shows up and needs a place to plug in his vacuum cleaner? I am always suspicious of hardware/system failures that fall right on the hour. I once had an Oracle database on an AIX system. Every morning, when I came in, it was just starting up. Turns out that the SA like to have a clean system so at the start of his shift he would hit the RESET button on his RS/6000. No warning or notification, no graceful shutdown, just punch the big red button. He also thought that backing up the entire database once a week would take too long so he would back up one RAID device every other day. With three RAID devices that gave him a weekly backup. I finally got him educated but he didn't pass it on to his subs. He was in Jamaica (mon) for a week when we had a power outage. The UPS worked and we were able to keep going but two days later we lost all the files on our RAID devices. They located him in Jamaica, flew him back, he walked into the computer run, flipped the switch on the RAID devices' UPS, and they flew him back to Jamaica. His sub had reset the computer's UPS but had ignored that blinking red light over in the corner. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100 cc: @yahoo.com Subject: RE: corrupted block Sent by: root 02/27/2003 10:10 AM Please respond to ORACLE-L it's not a hardware problem. the fact that the filesystem failed at 6AM this morning is merely a collective hallucination yes, it went down hard. My database was not on it, I had insisted they move all the files. They didn't move the Oracle binaries though (there is no hardware problem) so we are down anyway, while they reinstall Oracle to a different filesystem I'm getting tired of recovering this database. Over and over and over again. I've got this recovery scenario down pat, let's move on to a new one to try --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. -- Sunspots. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com
Re: corrupted block
We had a session with an expert on Monday and he recommended export to \dev\nul to detect errors in the database. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:41 PM I had the same belief that RMAN catches the corruption earlier, but not NOW. We had a database crash two months back and while performing the recovery(RMAN) one of the restored data file was corrupted. *BIG SHOCK* to everyone..We ran the dbverify on the restored files, the corruption showed up easily.. But not a single clue on the RMAN backup logs'. We asked Oracle Support, if rman checks for corruption in the data files when the data files are being backed up.. He said No.. Oracle Ver: 8.1.7.2/Sun 2.6 ..Ponnusamy At 11:40 AM 2/24/2003 -0800, DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: Ponnusamy 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: Yechiel Adar 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
Re: corrupted block
On Wed, 26 Feb 2003, Yechiel Adar wrote: We had a session with an expert on Monday and he recommended export to \dev\nul to detect errors in the database. Well the expert isn't going to find any corruptions in indexes that way. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton 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: corrupted block
I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. So far I've got 9 corrupted datafiles and over 40 corrupted objects. fortunately most are indexes. it's going to be a good day. NOT --- Yechiel Adar [EMAIL PROTECTED] wrote: We had a session with an expert on Monday and he recommended export to \dev\nul to detect errors in the database. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:41 PM I had the same belief that RMAN catches the corruption earlier, but not NOW. We had a database crash two months back and while performing the recovery(RMAN) one of the restored data file was corrupted. *BIG SHOCK* to everyone..We ran the dbverify on the restored files, the corruption showed up easily.. But not a single clue on the RMAN backup logs'. We asked Oracle Support, if rman checks for corruption in the data files when the data files are being backed up.. He said No.. Oracle Ver: 8.1.7.2/Sun 2.6 ..Ponnusamy At 11:40 AM 2/24/2003 -0800, DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: Ponnusamy 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: Yechiel Adar INET: [EMAIL PROTECTED]
Re: corrupted block
An export is a great method for catching corruptions in tables. However, it does not read indexes, so it misses those corruptions. Analyze and dbv will. Yechiel Adar wrote: We had a session with an expert on Monday and he recommended export to \dev\nul to detect errors in the database. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:41 PM I had the same belief that RMAN catches the corruption earlier, but not NOW. We had a database crash two months back and while performing the recovery(RMAN) one of the restored data file was corrupted. *BIG SHOCK* to everyone..We ran the dbverify on the restored files, the corruption showed up easily.. But not a single clue on the RMAN backup logs'. We asked Oracle Support, if rman checks for corruption in the data files when the data files are being backed up.. He said "No".. Oracle Ver: 8.1.7.2/Sun 2.6 ..Ponnusamy At 11:40 AM 2/24/2003 -0800, DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: Ponnusamy 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: corrupted block
-Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: corrupted block
Welcome to my week :) Rachel Carmichael wrote: I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. So far I've got 9 corrupted datafiles and over 40 corrupted objects. fortunately most are indexes. it's going to be a good day. NOT --- Yechiel Adar [EMAIL PROTECTED] wrote: We had a session with an expert on Monday and he recommended export to \dev\nul to detect errors in the database. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:41 PM I had the same belief that RMAN catches the corruption earlier, but not NOW. We had a database crash two months back and while performing the recovery(RMAN) one of the restored data file was corrupted. *BIG SHOCK* to everyone..We ran the dbverify on the restored files, the corruption showed up easily.. But not a single clue on the RMAN backup logs'. We asked Oracle Support, if rman checks for corruption in the data files when the data files are being backed up.. He said No.. Oracle Ver: 8.1.7.2/Sun 2.6 ..Ponnusamy At 11:40 AM 2/24/2003 -0800, DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: Ponnusamy 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).
RE: corrupted block
here's the fun part in this: this is being handled by the hosting company who manages our production data center. apparently rman detects corruption on the restore and writes error messages to the alert log, not the rman log. Except the monitoring software didn't look for the word corrupt --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: corrupted block
Rachel - Do you actually have the error text from the alert log? Looks like I have something to add to my Perl script... :) Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] | -- -Original Message- Carmichael Sent: Wednesday, February 26, 2003 12:21 PM To: Multiple recipients of list ORACLE-L here's the fun part in this: this is being handled by the hosting company who manages our production data center. apparently rman detects corruption on the restore and writes error messages to the alert log, not the rman log. Except the monitoring software didn't look for the word corrupt --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Brian McGraw 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: corrupted block
Brian, Can I get a copy of that Perl script once you've added that check? Rachel --- Brian McGraw [EMAIL PROTECTED] wrote: Rachel - Do you actually have the error text from the alert log? Looks like I have something to add to my Perl script... :) Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] | -- -Original Message- Carmichael Sent: Wednesday, February 26, 2003 12:21 PM To: Multiple recipients of list ORACLE-L here's the fun part in this: this is being handled by the hosting company who manages our production data center. apparently rman detects corruption on the restore and writes error messages to the alert log, not the rman log. Except the monitoring software didn't look for the word corrupt --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Brian McGraw 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: corrupted block
Here you go: *** Corrupt block relative dba: 0x024a (file 9, block 10) Bad header found during buffer read Data in bad block - type: 32 format: 0 rdba: 0x20202020 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20 consistency value in tail: 0x20202020 check value in block header: 0x2020, block checksum disabled spare1: 0x20, spare2: 0x20, spare3: 0x2020 *** Reread of rdba: 0x024a (file 9, block 10) found same corrupted data --- Brian McGraw [EMAIL PROTECTED] wrote: Rachel - Do you actually have the error text from the alert log? Looks like I have something to add to my Perl script... :) Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] | -- -Original Message- Carmichael Sent: Wednesday, February 26, 2003 12:21 PM To: Multiple recipients of list ORACLE-L here's the fun part in this: this is being handled by the hosting company who manages our production data center. apparently rman detects corruption on the restore and writes error messages to the alert log, not the rman log. Except the monitoring software didn't look for the word corrupt --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Brian McGraw 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: corrupted block
Understand that this is all secondhand reporting: I don't believe maxcorrupt was set. It appears that the backup itself is fine, that the problem is hardware corruption. The hosting company DBAs are telling me that 'no errors were written into the RMAN logs' (this is a direct quote). However there are tons of errors (see my reply to Brian McGraw) in the alert log, and they were not checking for those errors. It looks like RMAN is finding corrupted disk blocks when it does the backup and writing this information to the alert log. Which is fine, I'm just annoyed that they weren't checking for that, as they are supposedly experienced RMAN users. As best as I can determine, we had a massive failure of the disk subsystem. I had the level 0 backup from Sunday morning restored to another file system and then ran analyze table validate structure on the objects with no errors. When we did the same thing on the original file system, the analyze found corrupted blocks everywhere. A database with 117 tablespaces and corruption in over 10 datafiles, when the datafiles are all on the same mount point (don't ask, I don't get to choose, I'm told it's SAN, don't worry your little head over I/O contention) and when that file system mysteriously disappeared from the server for awhile a few hours before corruption started, leads me to presume massive hardware problems. But I'm not supposed to be involved in this, I'm just the development DBA. So how come it's MY problem? ARGH! --- Freeman Robert - IL [EMAIL PROTECTED] wrote: What do you mean by the Rman log rachel? Are you talking about the v$backup_corruption view? From the Oracle RMAN Reference: If the server session encounters a datafile block during a backup that has already been identified as corrupt by the database, then the server session copies the corrupt block into the backup and Oracle logs the corruption in the control file as either a logical or media corruption. RMAN copies the block in case the user wants to try to salvage the contents of the block. If RMAN encounters a datafile block with a corrupt header that has not already been identified as corrupt by the database, then it writes the block to the backup with a reformatted header indicating that the block has media corruption. and then, an interesting note: RMAN cannot detect all types of block corruption. Of course, if MAXCORRUPT isn't set, the one corruption should kill off the entire backup as long as that corruption is detected. Was it the origional backup that was corrupted or was it data after it got to the tape (because of media corruption/failure)? RF -Original Message- From: Rachel Carmichael To: Multiple recipients of list ORACLE-L Sent: 2/26/2003 12:21 PM Subject: RE: corrupted block here's the fun part in this: this is being handled by the hosting company who manages our production data center. apparently rman detects corruption on the restore and writes error messages to the alert log, not the rman log. Except the monitoring software didn't look for the word corrupt --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
RE: corrupted block
What do you mean by the Rman log rachel? Are you talking about the v$backup_corruption view? From the Oracle RMAN Reference: If the server session encounters a datafile block during a backup that has already been identified as corrupt by the database, then the server session copies the corrupt block into the backup and Oracle logs the corruption in the control file as either a logical or media corruption. RMAN copies the block in case the user wants to try to salvage the contents of the block. If RMAN encounters a datafile block with a corrupt header that has not already been identified as corrupt by the database, then it writes the block to the backup with a reformatted header indicating that the block has media corruption. and then, an interesting note: RMAN cannot detect all types of block corruption. Of course, if MAXCORRUPT isn't set, the one corruption should kill off the entire backup as long as that corruption is detected. Was it the origional backup that was corrupted or was it data after it got to the tape (because of media corruption/failure)? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/26/2003 12:21 PM here's the fun part in this: this is being handled by the hosting company who manages our production data center. apparently rman detects corruption on the restore and writes error messages to the alert log, not the rman log. Except the monitoring software didn't look for the word corrupt --- Stephen Lee [EMAIL PROTECTED] wrote: -Original Message- I'm dealying with the same RMAN not checking corruption -- on 9.2.0.1 and Solaris. and it's a data warehouse. I've seen it detect corruption, and not detect it. I think it detects some kinds, but not all kinds. It seems to do better with finding it in archived log files than in data files. But that observation is based on a tiny sample of empirical data, so it shouldn't be taken as fact. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Freeman Robert - IL 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: corrupted block
That's the cutest corruption I've ever seen - it looks like someone has been practising there C programming with How to write direct to an Oracle data file without using Oracle Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 19:14 Here you go: *** Corrupt block relative dba: 0x024a (file 9, block 10) Bad header found during buffer read Data in bad block - type: 32 format: 0 rdba: 0x20202020 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20 consistency value in tail: 0x20202020 check value in block header: 0x2020, block checksum disabled spare1: 0x20, spare2: 0x20, spare3: 0x2020 *** Reread of rdba: 0x024a (file 9, block 10) found same corrupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: corrupted block
dd if=$HOME/.profile of=/u01/oradata/PROD/blahblah01.dbf bs=8192 seek=10 or sqlplus / as sysdba spool /u01/oradata/PROD/blahblah01.dbf ... spool off it doesn't take much! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 2:14 PM That's the cutest corruption I've ever seen - it looks like someone has been practising there C programming with How to write direct to an Oracle data file without using Oracle Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 19:14 Here you go: *** Corrupt block relative dba: 0x024a (file 9, block 10) Bad header found during buffer read Data in bad block - type: 32 format: 0 rdba: 0x20202020 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20 consistency value in tail: 0x20202020 check value in block header: 0x2020, block checksum disabled spare1: 0x20, spare2: 0x20, spare3: 0x2020 *** Reread of rdba: 0x024a (file 9, block 10) found same corrupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Tim Gorman 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: corrupted block
I wish it was someone trying to do that. This is what I get after I restore a good rman backup to a bad disk. I have hundreds of these messages (or similar ones) in that alert log file sigh. My data center operations people are insisting that it CAN'T be hardware problems. Of course, we took the EXACT SAME BACKUP, restored it to another filesystem and have NO corruption in the database. But it can't possibly be hardware problems. It's just Oracle playing games with my mind. --- Jonathan Lewis [EMAIL PROTECTED] wrote: That's the cutest corruption I've ever seen - it looks like someone has been practising there C programming with How to write direct to an Oracle data file without using Oracle Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 19:14 Here you go: *** Corrupt block relative dba: 0x024a (file 9, block 10) Bad header found during buffer read Data in bad block - type: 32 format: 0 rdba: 0x20202020 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20 consistency value in tail: 0x20202020 check value in block header: 0x2020, block checksum disabled spare1: 0x20, spare2: 0x20, spare3: 0x2020 *** Reread of rdba: 0x024a (file 9, block 10) found same corrupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: corrupted block
See Note:61685.1 (metalink) Good luck Waleed -Original Message- Sent: Monday, February 24, 2003 11:09 AM To: Multiple recipients of list ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos 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: Khedr, Waleed 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: corrupted block
Suzy, Just more questions: Are your sure that this corruption has made it to the disk? It could be memory related. Can you export the table to /dev/null to double check the corruption? What do you get when reading that particular block using dba_extents? - Kirti -Original Message- Sent: Monday, February 24, 2003 10:09 AM To: Multiple recipients of list ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: corrupted block
Rama Velpuri's book had the answer to how to copy rows from a table when a corrupted block exists. The downside is the table is roughly 18GB, and has LONG. So my next question, is there any way to determine by trace file when the block corruption occurred? I'm still under the assumption that all backups will have the corrupted block. Suzy Vordos wrote: I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos 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: Suzy Vordos 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: corrupted block
I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: corrupted block
Have you tried copying it into a new table? Assuming that you have tried and failed, try creating a new table something like this: Create new_table as (select * from old_table where substr(rowid,1,8) != 02457856); I believe that that's the way the rowid was set up in Oracle 7.3.4 but my understand comes from a script that Dave Hungle, [EMAIL PROTECTED] , DBCORP Information Systems Inc. posted here. HTH Suzy Vordos lvordos To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @qwest.com cc: Sent by: rootSubject: corrupted block 02/24/2003 11:09 AM Please respond to ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name: SunOS Node name: kanadb-co1 Release: 5.6 Version: Generic_105181-17 Machine: sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos 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: Thomas Day 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
Re: corrupted block
Hi, If you can afford to forget the data in the corrupted block you can use the event 10231 to skip the corrupted block during table scan. Set the event and you can do a CTAS with a new table name and then you can rename that as original table after dropping the original table. Here is the syntax: alter session set events '10231 trace name context forever, level 10' If you want to see the contents of that skipped blocks, you can use the event 10232 which dumps the contents of that blocks to the trace files. And if you are comfortable in reading block dumps, you can write a simple INSERT statement to put those rows in to the new table. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: corrupted block
Suzy - Here is an article that explains it well. Hopefully this will work with 7.3.4. http://www.fortunecity.com/skyscraper/oracle/699/orahtml/oramag/16tech.html Once you get past the immediate crisis, there are a couple of ways to detect block corruption more quickly. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 10:09 AM To: Multiple recipients of list ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos 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: DENNIS WILLIAMS 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: corrupted block
Hi Suzi, The first thing I would suggest is to determine if it is actualy in use by the database (ie allocated to an object)... dbv has an "os perspective" on the file and hence does not understand what objects contain what blocks. Metalink note Doc ID: 28814.1 has some good basic information on block corruptions as well. A query such as: SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = AFN and BL between block_id AND block_id + blocks - 1; will help to answer this first question... once that question is answered, you can move on to other options (what do do about it)... Perhaps it is simply an index and can be dropped / recreated... If not, the note discussed various ways to extract the good data in the case of a table. (dbms_repair, events, select using rowids to exclude the block,etc) There is also always the question of why. In this case it may be quite difficult to figure out why based on its existence for some time. Additions and corrections welcomed! Regards, Paul I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. Iknow which table and datafile it is, and it's the only table in theaffected tablespace. The database is in archivelog mode so I can recover the datafile, but Iam not certain when the block corruption occurred. There were noproactive measures in place to quickly report a corrupted block. So Iassume it may have been there a long time, and was just found throughanalyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how wouldI copy all non-corrupted blocks from this table into a new table? Here is the trace file:ORACLE data block corrupted (file # 24, block # 57856)Dump file/dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trcOracle7 Server Release 7.3.4.3.0 - ProductionWith the distributed, replication, parallel query and Spatial DataoptionsPL/SQL Release 2.3.4.3.0 - ProductionORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4System name: SunOSNode name: kanadb-co1Release: 5.6Version: Generic_105181-17Machine: sun4uInstance name: kana03aPRedo thread mounted by this instance: 1Oracle process number: 10Unix process pid: 13163, image: oraclekana03aP*** 2003.02.24.02.49.42.000*** SESSION ID:(24.1317) 2003.02.24.02.49.41.000***Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found duringbuffer readon disk type:0. ver:0. dba: 0x inc:0x seq:0xincseq:0xEntire contents of block is zero - block never writtenReread of block=6000e200 file=24. blocknum=57856. found same corupteddata-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Suzy VordosINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: corrupted block
Thanks Kirti. Interesting, dba_extents doesn't return rows for block_id=57856. However, export to /dev/null does report the corruption. Does this indicate disk or memory corruption? Deshpande, Kirti wrote: Suzy, Just more questions: Are your sure that this corruption has made it to the disk? It could be memory related. Can you export the table to /dev/null to double check the corruption? What do you get when reading that particular block using dba_extents? - Kirti -Original Message- Sent: Monday, February 24, 2003 10:09 AM To: Multiple recipients of list ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Suzy Vordos 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: corrupted block
Suzy, The big question is whether or not the block actually contains data. It appears that it does not, if I am reading the last few lines correctly. This means you are in luck. Use a non-full table scan query to extract the data, drop the tablespace and remove the datafile. Recreate the tablespace/datafile/table/indexes and reload the data. I would also recommend having the physical disk(s) checked to see if it is a physical problem. Why a non-full table scan? It will read all of the blocks (used or empty) below the high water mark, much like the analyze has done (I think). The block indicated is empty, but below the highwatermark, so it will be read (or attempted) in a full table scan. This will then cause the fts to fail. Using an index will cause only the populated blocks to be read. Dan FInk Suzy Vordos wrote: I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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: corrupted block
or a later version 28814.1 which has a section salvaging data from tables --- Khedr, Waleed [EMAIL PROTECTED] wrote: See Note:61685.1 (metalink) Good luck Waleed -Original Message- Sent: Monday, February 24, 2003 11:09 AM To: Multiple recipients of list ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name: SunOS Node name:kanadb-co1 Release: 5.6 Version: Generic_105181-17 Machine: sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos 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: Khedr, Waleed 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). __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: corrupted block
I would add to my previous post, that the things that were supposed to allow me to skip the corrupt block did not work. I guess the moral being: Don't believe everything you read on Metalink (or elsewhere). That's why I eventually resorted to using the primary key index to grab one row at a time from the table. It was S-L-O-W, but I got all the data. I looked around for the script I wrote, but it looks like I have either deleted it or put in a place that I can't find now. Now I'm a bit curious to know how I did it. -Original Message- If you can afford to forget the data in the corrupted block you can use the event 10231 to skip the corrupted block during table scan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: corrupted block
oops. See http://metalink.oracle.com/ note=34371.1 Need to convert the file# and block# to hex. Thomas Day tday6 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @csc.comcc: Sent by: rootSubject: Re: corrupted block 02/24/2003 12:19 PM Please respond to ORACLE-L Have you tried copying it into a new table? Assuming that you have tried and failed, try creating a new table something like this: Create new_table as (select * from old_table where substr(rowid,1,8) != 02457856); I believe that that's the way the rowid was set up in Oracle 7.3.4 but my understand comes from a script that Dave Hungle, [EMAIL PROTECTED] , DBCORP Information Systems Inc. posted here. HTH Suzy Vordos lvordos To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @qwest.com cc: Sent by: rootSubject: corrupted block 02/24/2003 11:09 AM Please respond to ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name: SunOS Node name: kanadb-co1 Release: 5.6 Version: Generic_105181-17 Machine: sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos 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
RE: corrupted block
Suzy, I think it is memory related. May be un-caught memory leak or similar.. Did you get any ORA-600 errors? The trace file reports 'Entire contents of block is zero - block never written'. DBWR, at some point would have crashed the database if it attempted writing to the corrupted block. Not sure if and when that may have happened, but I would guess that this block does not contain any rows. Can you read the entire table via one of its indexes? If it is successful, you can safely pull data off to another table. - Kirti -Original Message- Sent: Monday, February 24, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Thanks Kirti. Interesting, dba_extents doesn't return rows for block_id=57856. However, export to /dev/null does report the corruption. Does this indicate disk or memory corruption? Deshpande, Kirti wrote: Suzy, Just more questions: Are your sure that this corruption has made it to the disk? It could be memory related. Can you export the table to /dev/null to double check the corruption? What do you get when reading that particular block using dba_extents? - Kirti -Original Message- Sent: Monday, February 24, 2003 10:09 AM To: Multiple recipients of list ORACLE-L I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade). Last night I analyzed the tables and a corrupted block was found. I know which table and datafile it is, and it's the only table in the affected tablespace. The database is in archivelog mode so I can recover the datafile, but I am not certain when the block corruption occurred. There were no proactive measures in place to quickly report a corrupted block. So I assume it may have been there a long time, and was just found through analyze (tables hadn't been analyzed since Dec-2000). So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? Here is the trace file: ORACLE data block corrupted (file # 24, block # 57856) Dump file /dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc Oracle7 Server Release 7.3.4.3.0 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.3.0 - Production ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4 System name:SunOS Node name: kanadb-co1 Release:5.6 Version:Generic_105181-17 Machine:sun4u Instance name: kana03aP Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 13163, image: oraclekana03aP *** 2003.02.24.02.49.42.000 *** SESSION ID:(24.1317) 2003.02.24.02.49.41.000 *** Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during buffer read on disk type:0. ver:0. dba: 0x inc:0x seq:0x incseq:0x Entire contents of block is zero - block never written Reread of block=6000e200 file=24. blocknum=57856. found same corupted data -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Suzy Vordos 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: Deshpande, Kirti 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
RE: corrupted block
Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: corrupted block
I'm not aware of the MAXCORRUPT parameter. There were two blocks involved. We think it was caused by an incompatibility between an OS driver and some piece of new storage hardware. The symptoms were that any query (including an export) that scanned table would be going along then suddenly get an object does not exist error. dbv correctly identified the blocks. rman not only didn't detect them, but backed up and restored the corruption. It was originally filed as a severity 1 TAR. The TAR people promptly reassigned it to severity 2, then did absolutely nothing with it. After I figured out a solution, I waited another day to see if anything would ever be done with the TAR. It wasn't, so I called back and talked to a supervisor. I let her know what the situation was, and was told that this would be referred to the rman group -- a statement on which I have bet no money. -Original Message- Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: corrupted block
I had the same belief that RMAN catches the corruption earlier, but not NOW. We had a database crash two months back and while performing the recovery(RMAN) one of the restored data file was corrupted. *BIG SHOCK* to everyone..We ran the dbverify on the restored files, the corruption showed up easily.. But not a single clue on the RMAN backup logs'. We asked Oracle Support, if rman checks for corruption in the data files when the data files are being backed up.. He said No.. Oracle Ver: 8.1.7.2/Sun 2.6 ..Ponnusamy At 11:40 AM 2/24/2003 -0800, DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: Ponnusamy 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: corrupted block
Nope, SQL-Backtrack. I need to dig into those docs to see if that is a feature or configuration issue. DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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: Suzy Vordos 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: corrupted block
This might work, we can afford to forget the data. We are in the process of purging old data and this row already meets the criteria for purge. Thanks to everyone for your input! Haven't tried anything yet as I had to drop this issue to work on a more urgent matter (if you can imagine that). Soon as the corruption problem is solved I'll report what was done. Suzy K Gopalakrishnan wrote: Hi, If you can afford to forget the data in the corrupted block you can use the event 10231 to skip the corrupted block during table scan. Set the event and you can do a CTAS with a new table name and then you can rename that as original table after dropping the original table. Here is the syntax: alter session set events '10231 trace name context forever, level 10' If you want to see the contents of that skipped blocks, you can use the event 10232 which dumps the contents of that blocks to the trace files. And if you are comfortable in reading block dumps, you can write a simple INSERT statement to put those rows in to the new table. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: Suzy Vordos 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: corrupted block
Metalink Note 130605.1 is worth reading about setting Maxcorrupt for an RMAN whole database backup (and checking alert.log for corruption messages). Metalink Note 207413.1 describes RMAN incorrectly reporting block corruption. Bugs 2068275, 1849726, and 1802432 may be interesting (upgrading Oracle 7 to Oracle 8i could contain a nasty surprise). Have Fun :) Ponnusamy wrote: I had the same belief that RMAN catches the corruption earlier, but not NOW. We had a database crash two months back and while performing the recovery(RMAN) one of the restored data file was corrupted. *BIG SHOCK* to everyone..We ran the dbverify on the restored files, the corruption showed up easily.. But not a single clue on the RMAN backup logs'. We asked Oracle Support, if rman checks for corruption in the data files when the data files are being backed up.. He said "No".. Oracle Ver: 8.1.7.2/Sun 2.6 ..Ponnusamy At 11:40 AM 2/24/2003 -0800, DENNIS WILLIAMS wrote: Stephen RMAN ignored your corrupted block? Ya gotta tell us more man! We're relying on it to catch everything. Did you have the MAXCORRUPT parameter set? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I think more recent versions of Oracle have options for skipping corrupt blocks with exports. One possible way: If you have a valid primary key index on the table, and the index is in a good tablespace, you might be able to cycle through all the primary keys, select the row corresponding to that primary key and insert it into a new table. I was able to do this about a month ago with a 8.1.7 database. In my case, I think it was a block header that was corrupt, not data; so I got all the data OK. It was rather slow, grabbing and inserting one row at a time; but I got all the data. As long as I didn't do anything that would cause a table scan of any kind, I could get the data. By the way, rman not only failed to spot the corruption, but backed it up AND restored the corruption! My initial attempt was to just rename the datafile at the file system level, then recover it from the previous backup. I could relate another one of those TAR non-support -- total and complete NON-support! -- on this one. -Original Message- So my question is, if all backups contain the corrupted block, how would I copy all non-corrupted blocks from this table into a new table? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: DENNIS WILLIAMS 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).
