srinivas_ganamur
asked on
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\SF
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,tempf
Regards,
Sri
ARCHIVESFMSLOG20091218.txt
sfms-ora-9064.txt
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\SF
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,tempf
Regards,
Sri
ARCHIVESFMSLOG20091218.txt
sfms-ora-9064.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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?
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?
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.
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.
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.db
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
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.db
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
ASKER
Hi Schwertner,
this db_verify will fix my problem?
One more question that is how to take database offline?
this db_verify will fix my problem?
One more question that is how to take database offline?
ASKER
/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?
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?
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.
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.
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
http://www.oracleutilities.com/Packages/dbms_repair.html
regards
ASKER
no proper solution
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.