Home » RDBMS Server » Backup & Recovery » ORA-01578: ORACLE data block corrupted (ORA-01110: data file 3: )
ORA-01578: ORACLE data block corrupted [message #571081] Wed, 21 November 2012 00:58 Go to next message
daulat01
Messages: 19
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
Re: ORA-01578: ORACLE data block corrupted [message #571093 is a reply to message #571081] Wed, 21 November 2012 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58917
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
Re: ORA-01578: ORACLE data block corrupted [message #571099 is a reply to message #571093] Wed, 21 November 2012 01:38 Go to previous messageGo to next message
daulat01
Messages: 19
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
Re: ORA-01578: ORACLE data block corrupted [message #571101 is a reply to message #571099] Wed, 21 November 2012 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58917
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
Re: ORA-01578: ORACLE data block corrupted [message #571103 is a reply to message #571101] Wed, 21 November 2012 01:55 Go to previous messageGo to next message
daulat01
Messages: 19
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
Re: ORA-01578: ORACLE data block corrupted [message #571107 is a reply to message #571103] Wed, 21 November 2012 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58917
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
Re: ORA-01578: ORACLE data block corrupted [message #571191 is a reply to message #571107] Thu, 22 November 2012 02:24 Go to previous messageGo to next message
daulat01
Messages: 19
Registered: May 2011
Location: Delhi
Junior Member
Thanks Michel,
It's resolved.
Re: ORA-01578: ORACLE data block corrupted [message #571194 is a reply to message #571191] Thu, 22 November 2012 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58917
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain how for futur readers.

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted [message #571199 is a reply to message #571194] Thu, 22 November 2012 03:04 Go to previous messageGo to next message
daulat01
Messages: 19
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)
Re: ORA-01578: ORACLE data block corrupted [message #571207 is a reply to message #571199] Thu, 22 November 2012 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58917
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks.

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted [message #595402 is a reply to message #571207] Tue, 10 September 2013 23:02 Go to previous messageGo to next message
daulat01
Messages: 19
Registered: May 2011
Location: Delhi
Junior Member
Hi , I am getting below an error in Oracle database on RAC in 10g.
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 1, block # 560935)
ORA-01110: data file 1: '+DGDIXIE/dixie/datafile/system.260.647197957'
ORA-06512: at "SYS.DBMS_STATS", line 18544
ORA-06512: at "SYS.DBMS_STATS", line 18873
, block # )


@Note: While fetching data through the below query. We are getting same an error message.
select segment_name,segment_type,owner
from sys.dba_extents
where file_id=(1)
and (560935) between block_id and block_id + blocks -1

Could you please help me on this.
Thanks,

[Updated on: Tue, 10 September 2013 23:04]

Report message to a moderator

Re: ORA-01578: ORACLE data block corrupted [message #595407 is a reply to message #595402] Wed, 11 September 2013 00:58 Go to previous message
Michel Cadot
Messages: 58917
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have a corruption in SYSTEM tablespace.
You have to use the underlying tables of the view to get the result.
Use SQL*Plus and execute the following query and post the result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

select * from sys.uet$
where file# = 1
  and 560935 between block# and block#+length-1
/

Regards
Michel

[Updated on: Wed, 11 September 2013 00:59]

Report message to a moderator

Previous Topic: New Install, What Version?
Next Topic: how to do replication of an instance into another one
Goto Forum:
  


Current Time: Thu Aug 28 02:15:49 CDT 2014

Total time taken to generate the page: 0.14497 seconds