Corrupt block relative dba: 0x0280005a

Hi,

One of my dbf file is corrupted (Please see the attachment). So i have used below query to find the corrupted tables/indexes.

select owner, segment_name, segment_type
from dba_extents
where file_id = 10 and
90 between block_id and block_id + blocks - 1;

This query output is no records.

After that i have used one more below mentioned query to find the objects in dbf file.

SELECT SEGMENT_TYPE,SEGMENT_NAME FROM V$DATAFILE V ,DBA_EXTENTS D WHERE
V.FILE#=D.FILE_ID AND NAME='E:\ORACLE\ORADATA\SFMS\SFMS_BRACH\SFMS_ARCH_NDX1.DBF';

The output of the below query returns no records.

I have used one more query to find corrupted file 10.

SELECT * FROM DBA_ROLLBACK_SEGS WHERE FILE_ID=10;

the output of the above query no records.

So finally i have decided recovery data till cancel (as per some google search),but i dnt have idea how to recovery till cancel method.

So please let me know how to correct my current issue. Also provide me step by step details of recovering data till cancel method. Other then this, if any other method you knows to correct my problem,please reply me ASAP.

I am not able to find this corrupted file 10 in datafile,segmentfile,tempfile.

Regards,
Sri




ARCHIVESFMSLOG20091218.txt
sfms-ora-9064.txt
srinivas_ganamurAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
Oracle provides the DB_VERIFY utility to validate datafiles even when a datafile is offline or the database is unavailable. DB_VERIFY is an external command-line utility that verifies the integrity of database files and the physical structure of an offline database.

0
 
Franck PachotCommented:
Hi,

Did you check in DBA_FREE_SPACE ?

select owner, segment_name, segment_type
from dba_free_space
where file_id = 10 and
90 between block_id and block_id + blocks - 1;

Regards,
Franck.
0
 
srinivas_ganamurAuthor Commented:
Hi Franck,

the output of the your query is:

select owner, segment_name, segment_type
from dba_free_space
where file_id = 10 and
90 between block_id and block_id + blocks - 1;

select owner, segment_name, segment_type
                            *
ERROR at line 1:
ORA-00904: "SEGMENT_TYPE": invalid identifier
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
srinivas_ganamurAuthor Commented:
Hi Schwertner,

Please give me the syntax of db_verify....currently i am working on Oracle 9i...we have to install this utility/default it is there in oracle 9i?
0
 
Franck PachotCommented:
Hi, yes, a bad copy pase, just do:

select * from dba_free_space
where file_id = 10 and
90 between block_id and block_id + blocks - 1;

If you can't find a segment where the block belongs, it may be in free space.

Do you have the same error each time you run that query (with same parameters) ?

Regards,
Franck.
0
 
schwertnerCommented:
This utility could be found in $ORACLE_HOME/bin folder

http://download.oracle.com/docs/html/B12034_01/ch5_admi.htm

Checking the Integrity of the Physical Data Structure
To check the data-structure integrity of off-line databases, use the DB_VERIFY external command-line utility. To start DB_VERIFY enter the following command:

/START-PROGRAM $ORACL920.DBV


You can now enter your command, for example:

file=ora9.dbs.database1.dbf blocksize=4096 feedback=100


For more information about the DB_VERIFY program, see the Oracle9i Administrator's Guide and the Oracle9i Utilities book.



http://oraclelegends.blogspot.com/2007/10/using-dbverify-utility-in-oracle-10g.html
http://www.informit.com/articles/article.aspx?p=19699
0
 
srinivas_ganamurAuthor Commented:
Hi Schwertner,

this db_verify will fix my problem?

One more question that is how to take database offline?

0
 
srinivas_ganamurAuthor Commented:
/START-PROGRAM $ORACL920.DBV

where i have to execute this command? in command prompt or start--run.

in command prompt i have exected, i got the following error

C:\>/START-PROGRAM $ORACL920.DBV
'/START-PROGRAM' is not recognized as an internal or external command,
operable program or batch file.

or i have to login as a sysdba in command prompt?
0
 
schwertnerCommented:
You have to figure out where is your ORACLE_HOME.
It is a direcctory where the Oracle softwere is placed.
In Windowsa check the registry for this (Oracle section)
in Unix use command like
printenv

In the bin directory find the executable
dbv
or something similar and execute it.
0
 
shalowfastCommented:
the package dbms_repair can detect and repair block corruption, as far as i know it is also available for 9i, here you can find a guide to use it:

http://www.oracleutilities.com/Packages/dbms_repair.html

regards
0
 
srinivas_ganamurAuthor Commented:
no proper solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.