[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ORA-01578 and ORA-01110 Errors

Posted on 2010-03-31
9
Medium Priority
?
1,578 Views
Last Modified: 2013-12-19
Currently i am facing ORA-01110 and ORA-01578 error problems in my database. The log file showing below error:

Class : AdsArchive
java.sql.SQLException: ORA-01578: ORACLE data block corrupted (file # 10, block # 90)
ORA-01110: data file 10: 'E:\ORACLE\ORADATA\SFMS\SFMS_BRACH\SFMS_ARCH_NDX1.DBF'

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
      at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
      at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
      at sfmsbr.archiving.AdsArchive.archive_ADS(AdsArchive.java:40)
      at sfmsbr.archiving.MhbArchive.archive_MHB(MhbArchive.java:106)
      at sfmsbr.archiving.ArchiveProcess.archive_Branch_Tbls(ArchiveProcess.java:243)
      at sfmsbr.archiving.ArchiveProcess.arcMain(ArchiveProcess.java:159)
      at sfmsbr.archiving.ArchiveProcess.run(ArchiveProcess.java:46)
      at sfmsbr.archiving.ArchiveProcess.<init>(ArchiveProcess.java:39)
      at sfmsbr.reports.Scheduler.main(Scheduler.java:297)
---------------------------------------------------------

For error number ORA-01578:
I have executed dbv/oracle built in repair packages and i have not found any corrupted blocks in database.

I have analyzed all tables/indexes in particular block and i have not found in corrupted objects.

Please let me know how to resolve my problem
0
Comment
Question by:srinivas_ganamur
9 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 750 total points
ID: 29232873
The first thing that I would do is to identify the object involved.

SELECT * FROM DBA_EXTENTS
WHERE FILE_ID = 10 AND 90 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Based on the name of the file, I believe you are going to find an index.  Drop and recreate the index, then run statistics on the table.  See if you still get the error.  If there is any corruption, recreating the index should fix that.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 29242251
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 750 total points
ID: 29242814
>>EE is best professional IT forum, ITTB is best free IT forum

EE is free.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:srinivas_ganamur
ID: 29289199
Hi Johnsone,

I have already executed your query, it returns no rows

SELECT * FROM DBA_EXTENTS
WHERE FILE_ID = 10 AND 90 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Output:
no rows selected.

i have also tried FIX_CORRUPT_BLOCKS / DBMS_REPAIR.CHECK_OBJECT...no luck..still same error is generating log file
0
 

Author Comment

by:srinivas_ganamur
ID: 29289490
i have also tried below querys:

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

output:
no rows selected.

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

Output:
no rows selected
0
 
LVL 8

Expert Comment

by:POracle
ID: 29436651
try this

rman>backup validate datafile 10

then execute this query to find out corrupted block details
sql>select * from v$database_block_corruption


0
 

Author Comment

by:srinivas_ganamur
ID: 29441094
currently we have not configured RMAN
0
 
LVL 8

Expert Comment

by:POracle
ID: 29441894
Ok,

Rman not require any configuration. it is tool like exp/imp
Any way,
I am taking this statement as you have no backup taken with Rman.

Do you have backup of corrupted file (i.e. SFMS_ARCH_NDX1.DBF). And if your database is configured in Archivelog mode then we can restore a GOOD backup of corrupted file and then can recover it.


0
 

Author Closing Comment

by:srinivas_ganamur
ID: 31709507
--
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

612 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