Sorry for not noticing this email thread earlier, so I apologize for chiming in late...
--- The original poster (Srinivas) disqualified RMAN as a possibility because he said that they were "already getting backups via BCVs". My question is: are you splitting off BCVs and then calling it good, without backing up the BCV to tape? That is not good! Or, are you subsequently backing up the BCV copy to tape? If the former, then you are in deep doo-doo because a BCV copy is most assuredly not a "backup" in any way, shape, or form. The reasons should be obvious... If the latter, then that is the entry point to do the backups with RMAN, which is the very best method for detecting block corruption and archived redo log file corruption without generating extra busy work for the system. First, the split-off BCVs can be registered within RMAN as a DATAFILECOPY (using the CATALOG command), which allows RMAN to utilize it in a restore/recovery situation if it is needed. That way, you can continue to use BCV functionality to get the split-off file image copy, but RMAN knows about it and can utilize that datafile image copy in a restore scenario. Either way, backing that DATAFILECOPY to tape is something that has to be done -- why not with RMAN? There is a BACKUP DATAFILECOPY command in RMAN just for this purpose. That way, you are able to check for data block corruption while you are doing something that you have to do anyway -- copy the BCV to tape. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 23, 2002 11:58 AM > Royce, > > Just small correction > > It is > > Analyze table table_name validate structure cascade; > > which checks for corruption of table and indexes based on that table and > error is written in alertSID log as well as producing trace file. > In my experience to check table/index level corruption this is is the best > way but a very resource intensive and time consuming if table is large and > put exclusive lock on table (prior to 9i) as it has no ONLINE option....... > > Regards > Rafiq > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Mon, 23 Sep 2002 08:58:42 -0800 > > Agreed ..... though I have had disappointing results with 'ANALYZE ... > VALIDATE STRUCTURE ...' not detecting corruption .... only later to have an > application (i.e. user) encounter data block (or index block) corruption. My > last incidence ... I simply did a count(*) .. full table scan ... which > narrowed down the specific table involved. See Alert.log and sql dialog > below ....................... As a quick check for corruption ... I don't > see an easy way to get around exercising block-by-block reads. I also would > be interested in alternative methods. My databases are smaller (than the 500 > Gbytes) than that of Srinivas' ..... but I am supporting a LINUX --> > NettApps environment that will occasionally 'hang' and upon restart (reboot > of OS) I have been 'honored' with 'Fractured block found during > media/instance recovery' ...... Thanks for further input and comments > ....... > > > > > Recovery of Online Redo Log: Thread 1 Group 1 Seq 3974 Reading mem 0 > Mem# 0 errs 0: /u001/oradata/rhyp01/redo01.log > *** > Corrupt block relative dba: 0x0200f242 (file 8, block 62018) > Fractured block found during media/instance recovery > Data in bad block - > type: 6 format: 2 rdba: 0x0200f242 > last change scn: 0x04e3.814c2697 seq: 0x1 flg: 0x02 > consistency value in tail: 0x1ec20603 > check value in block header: 0x0, block checksum disabled > spare1: 0x0, spare2: 0x0, spare3: 0x0 > *** > Reread of rdba: 0x0200f242 (file 8, block 62018) found same corrupted data > Sun Sep 15 07:06:11 2002 > Thread recovery: finish rolling forward thread 1 > Thread recovery: 5409 data blocks read, 3710 data blocks written, 50221 redo > blocks read > Crash recovery completed successfully > Sun Sep 15 07:06:11 2002 > > On RESTART: > > Sun Sep 15 10:24:30 2002 > Database mounted in Exclusive Mode. > Completed: alter database mount > Sun Sep 15 10:24:30 2002 > alter database open > Sun Sep 15 10:24:31 2002 > Thread 1 opened at log sequence 3975 > Current log# 2 seq# 3975 mem# 0: /u001/oradata/rhyp01/redo02.log > Successful open of redo thread 1. > Sun Sep 15 10:24:31 2002 > SMON: enabling cache recovery > SMON: enabling tx recovery > Sun Sep 15 10:24:33 2002 > Completed: alter database open > > I then checked all tables in 'file 8' until corruption was detected > (below is excerpt of results) > > > Sql+ rhyp01> > select count(*) from RSRYP.MDB_LOTS > * > ERROR at line 1: > ORA-01578: ORACLE data block corrupted (file # 8, block # 62018) > ORA-01110: data file 8: '/u001/oradata/rhyp01/dbm_data01.dbf' > Sql+ rhyp01> > > > > Thnx & OK Bye > To me, it's a good idea to always carry > two sacks of something when you walk > around. That way, if anybody says, > "Hey, can you give me a hand?" ... .. > You can say, "Sorry, got these sacks" > - Jack Handey > > Chris Royce > Oracle DBA > (602) 244 3735 > [EMAIL PROTECTED] > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > -----Original Message----- > WILLIAMS > Sent: Monday, September 23, 2002 9:08 AM > To: Multiple recipients of list ORACLE-L > > > Srinivas > I think that as a minimum, to detect corruption you must read all data > blocks. There are several methods, most involve an Oracle process that will > error if a corrupt block is encountered. Export to /dev/null was mentioned > already. I have used ANALYZE TABLE VALIDATE STRUCTURE CASCADE, which will > also check out the indexes and verify they point to good blocks, but is > quite resource intensive. If you use RMAN to back up your database, then as > a bonus it always verifies your data blocks and will error out when it > encounters a bad block. > I haven't used dbv personally, but assume that dbv was one of the most > efficient methods of checking your datafiles since it runs outside Oracle > and therefore doesn't tie up your Oracle resources. Perhaps you can spell > out the problems you are encountering with dbv and what you are trying to > accomplish in more detail. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > -----Original Message----- > Sent: Monday, September 23, 2002 5:03 AM > To: Multiple recipients of list ORACLE-L > > > Hello all, > > DB: 8i > OS: solaris 2.7 > > can somebody post me reply for this. > > is there any quick way to find which datablocks are > corrupted in my oracle database . > > ( other than dbverify and rman backup. ) > > b'coz we have BCV backup already implemented and we > cannot do a dbv every week for 500 gig production > database . > > thanks in advance, > srinivas > > __________________________________________________ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: kommareddy sreenivasa > 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.com > -- > 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). > << winmail.dat >> > > > > > _________________________________________________________________ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mohammad Rafiq > 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.com -- 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).
