OraFAQ Forum: Backup & Recovery » ORA-01578: ORACLE data block corrupted

Home » RDBMS Server » Backup & Recovery » ORA-01578: ORACLE data block corrupted (ORA-01110: data file 3: )

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

ORA-01578: ORACLE data block corrupted [message #571081]

Wed, 21 November 2012 00:58

daulat01
Messages: 13
Registered: May 2011
Location: Delhi

Junior Member

Could you please suggest what i have to do for this error:
ORA-01578: ORACLE data block corrupted (file # 3, block # 42348)
ORA-01110: data file 3: '+DGMETA/metadb/datafile/sysaux.257.783871811'
Wed Nov 21 09:30:00 2012
Corrupt Block Found
TSN = 2, TSNAME = SYSAUX
RFN = 3, BLK = 42348, RDBA = 12625260
OBJN = 50439, OBJD = 50439, OBJECT = MGMT_STRING_METRIC_HISTORY_PK, SUB OBJECT =
SEGMENT OWNER = SYSMAN, SEGMENT TYPE = Index Segment

Database version is :- Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi

Thanks
Daulat

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571093 is a reply to message #571081]

Wed, 21 November 2012 01:25

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Have you backups?
Are you in ARCHIVELOG or NOARCHIVLOG mode?
Have the archived logs since last backup?
How do you backup?

Regards
Michel

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571099 is a reply to message #571093]

Wed, 21 November 2012 01:38

daulat01
Messages: 13
Registered: May 2011
Location: Delhi

Junior Member

No we don't have backup. it's in noarchvelog mode.

But while executing below query.

select segment_name,segment_type,owner
from sys.dba_extents
where file_id=(3)
and (42348) between block_id and block_id + blocks -1

The Output result is :

segment_name segment_type owner
MGMT_STRING_METRIC_HISTORY_PK INDEX SYSMAN

It means crouption is in above segment. plesae do comment on this

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571101 is a reply to message #571099]

Wed, 21 November 2012 01:46

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Disable the PK constraint, drop the index (if not done by the previous disable), recreate the index and reenable the constraint.
Use dbms_metadata.get_ddl to get the index creation script.

Regards
Michel

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571103 is a reply to message #571101]

Wed, 21 November 2012 01:55

daulat01
Messages: 13
Registered: May 2011
Location: Delhi

Junior Member

But while executing the below scripts the status of sysaux tablespace is ONLINE.

select FILE#, status from v$datafile where file#=3;
select TABLESPACE_NAME,dba_tablesSTATUS from paces;

Why it's online. Please do comment

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571107 is a reply to message #571103]

Wed, 21 November 2012 02:14

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no reason for Oracle to put the whole file offline because one block is corrupted.

Regards
Michel

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571191 is a reply to message #571107]

Thu, 22 November 2012 02:24

daulat01
Messages: 13
Registered: May 2011
Location: Delhi

Junior Member

Thanks Michel,
It's resolved.

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571194 is a reply to message #571191]

Thu, 22 November 2012 02:29

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Explain how for futur readers.

Regards
Michel

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571199 is a reply to message #571194]

Thu, 22 November 2012 03:04

daulat01
Messages: 13
Registered: May 2011
Location: Delhi

Junior Member

process for resolution of block crouption in index segment.

1.select segment_name,segment_type,owner from sys.dba_extents where file_id=(3)
and (42348) between block_id and block_id + blocks -1
2.ALTER TABLE SYSMAN.MGMT_STRING_METRIC_HISTORY DROP PRIMARY KEY CASCADE;
3.ALTER TABLE SYSMAN.MGMT_STRING_METRIC_HISTORY ADD (
CONSTRAINT MGMT_STRING_METRIC_HISTORY_PK
PRIMARY KEY (TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP)

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #571207 is a reply to message #571199]

Thu, 22 November 2012 03:18

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks.

Regards
Michel

Report message to a moderator

Previous Topic:	How to check for VSS support
Next Topic:	issue in Monitoring RMAN job Progress (multiple channels)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 21:00:47 CDT 2013

Total time taken to generate the page: 0.22903 seconds