Home » RDBMS Server » Server Utilities » Found error in DBV Utility (Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
Found error in DBV Utility [message #456892] Thu, 20 May 2010 00:52 Go to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

I found one day, suddenly my database throws critical errors like ORA-7445,ORA-600. I find the problem sql statement from the dump file generated by error. I also search the net for overcome this situation, result is most of the suggestion are contact to oracle support and generate the SR. which is not possible for me due to some constraint. I tried to crosscheck the block corruption of my dbfile by dbv utility of oralce. i run the below command and got the following error? so please suggest me the solution for what does it mean? is my all pages are corrupted. here i'm pasting my sample of error piece, i got this message for my all pages.starting for 1 to 8605.


[oracle1@WBDevelop bin]$ dbv file=/ora9idb/odb/OH1/oradata/wbdata/wbtab03.dbf

Page 490 is marked corrupt
***
Corrupt block relative dba: 0x000001ea (file 0, block 490)
Bad header found during dbv:
Data in bad block -
 type: 1 format: 0 rdba: 0x1524c303
 last change scn: 0x0618.096b7807 seq: 0x1 flg: 0x0a
 consistency value in tail: 0x096b7807
 check value in block header: 0x12c, block checksum disabled
 spare1: 0x1, spare2: 0x80, spare3: 0x70c
***


Regards

Jimit
Re: Found error in DBV Utility [message #456893 is a reply to message #456892] Thu, 20 May 2010 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, all pages notified like this are corrupt and unusable for Oracle.
Only Oracle can recover the data from this.
And only Oracle can help you to fix ORA-07445 and ORA-00600 errors.
This is why you need a valid contract with Oracle when you use the database for production.

Regards
Michel
Re: Found error in DBV Utility [message #456895 is a reply to message #456893] Thu, 20 May 2010 01:05 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

At the time of running dbv utility of oracle, my database is up and running. should i need to shutdown the database and recheck my dbfiles with dbv utility again. please suggest me the way how can i recover my data from this corrupted pages. suggest some oracle utility or package which can recover this data.

Regards

Jimit
Re: Found error in DBV Utility [message #456897 is a reply to message #456895] Thu, 20 May 2010 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It does not hurt if you do it.
2/ I can't and no one can as I said ONLY Oracle can do it.
3/ There is no available utility or package. There is an utility internal to Oracle you can't have but you can pay Oracle to use it for you.

Regards
Michel
Re: Found error in DBV Utility [message #456898 is a reply to message #456897] Thu, 20 May 2010 01:15 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

Can i do one thing, if i move all my tables to new tablespace, or export the tables,truncate tables and drop tables then import it..... which are included in above problem query which is generating critical errors. Is there any possibility of recovering from error by these way.

Regards

Jimit
Re: Found error in DBV Utility [message #456902 is a reply to message #456898] Thu, 20 May 2010 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot export corrupted blocks.
If ALL your blocks are corrupted, you cannot export anything.

Regards
Michel
Re: Found error in DBV Utility [message #456904 is a reply to message #456898] Thu, 20 May 2010 01:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You can use dbms_repair.fix_corrupt_blocks and dbms_repair.skip_corrupt_blocks procedures to make the objects usable, but the data in them is gone and you may have some interesting results with subsequent queries depending on whether the corrupted blocks are table or index blocks. It is sometimes possible to recover the entire table in this way: it may be that the corrupted blocks do not actually have any rows.
Re: Found error in DBV Utility [message #457073 is a reply to message #456904] Fri, 21 May 2010 00:18 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

Thanks for replies.After getting so depressed answer i search little beat more on dbv utility and found that sometime in linux os u must have to specify the blocksize clause of dbv utility. Then i tried following command.which display that there is no page or block corruption found via oracle. But still i have to search more for how get read of ORA-7445
[oracle1@WBDevelop odb]$ dbv file=/ora9idb/odb/OH1/oradata/wbdata/wbtab03.dbf 
BLOCKSIZE=8192

DBVERIFY: Release 9.2.0.1.0 - Production on Fri May 21 10:42:34 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = /ora9idb/odb/OH1/oradata/wbdata/wbtab0
3.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 31416
Total Pages Processed (Data) : 24676
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5499
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 201
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1040
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
[oracle1@WBDevelop odb]$
Re: Found error in DBV Utility [message #457095 is a reply to message #457073] Fri, 21 May 2010 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-07445 means an insternal error, the only to workaround it is to find the error on Metalink/MOS and apply the workaround or patch.

Thanks to mention this trick about dbv/Linux, it will help.

Regards
Michel
Re: Found error in DBV Utility [message #457233 is a reply to message #457095] Sat, 22 May 2010 04:59 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel and friends

As to overcome the ORA-07445 error we have done following thing, which are work perfectly in our case. It may not be the best solution in each condition. We find the tables from the query generated in dump file, then we create a new tablespace and move all the problematic tables to that tablespace, then we rebuild our indexes of those tables. And amazingly it works. Please let us know your personal take on this solution.

Regards

Jimit
Re: Found error in DBV Utility [message #457252 is a reply to message #457233] Sat, 22 May 2010 09:30 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-07445 is a generic error, you've been lucky to solve your current issue with this solution (but is this durable?) and trace file is mandatory to debug it and most of the time only Oracle can read the dump.

Regards
Michel
Previous Topic: insert only date using sqlldr
Next Topic: Ext file being created is '0' bytes....
Goto Forum:
  


Current Time: Fri Mar 29 07:27:54 CDT 2024