?
Solved

Corrupt block relative dba: 0x0280005a

Posted on 2009-12-21
11
Medium Priority
?
1,115 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:srinivas_ganamur
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26095334
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
 
LVL 48

Accepted Solution

by:
schwertner earned 1000 total points
ID: 26096010
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
 

Author Comment

by:srinivas_ganamur
ID: 26102011
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:srinivas_ganamur
ID: 26102015
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26102728
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
 
LVL 48

Expert Comment

by:schwertner
ID: 26103186
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
 

Author Comment

by:srinivas_ganamur
ID: 26103249
Hi Schwertner,

this db_verify will fix my problem?

One more question that is how to take database offline?

0
 

Author Comment

by:srinivas_ganamur
ID: 26103285
/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
 
LVL 48

Expert Comment

by:schwertner
ID: 26104078
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
 
LVL 1

Expert Comment

by:shalowfast
ID: 26146814
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
 

Author Closing Comment

by:srinivas_ganamur
ID: 31668470
no proper solution
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question