ORA-01578; ORACLE data block corrupted

[Former Member]

Hi,

In system log we found database error, how to repair the corrupted data block in ORACLE.

Database error 1578 at FET

ORA-01578; ORACLE data block corrupted (file # 1, block # 56449) #ORA-01110; data file 1;

/oracle/SID/sapdata1/system_1/system.data1

Regards,

R.Ramkumar

[Former Member]

Hi Ram,

Best and speedy option to get into Consistent DB.

1) Restore and recovery of DB from the consistent backup to point in time by applying redo log backups.

2) Please let us know if the DB backup has failed with error like DB inconsistent or etc if yes let us know from when the DB backup is failing.

Thanks,

Avinash

[Former Member]

How to fix Fix Corrupt Blocks: Block corruption In Oracle Database

Oracle corruption is most frequently caused by a bad disk, although there are rare cases of "logical" corruption within the Oracle data blocks.  You can use these steps to find the corrupt data blocks in Oracle.  Oracle also provides a v$database_block_corruption view that you can check:

select * from v$database_block_corruption

If you know the file number and block number you can run the following query to see the exact data block that has corruption:

select
relative_fno,
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = 6
and
437 between block_id and block_id + blocks - 1;

Once you know the exact block ID you can view the corrupt block contents with the block editor BBED utility, working with Oracle technical support to repair the corruption.

For more visit: http://oracle.filerepairtool.net/blog/fix-corrupt-blocks-block-corruption-oracle-database

[Former Member]

Hi,

Open an incident with SAP on immediate basis to avoid further issues!

Regards,

Nick Loy

[Former Member]

ORA-01578 error appears when the ORACLE data block gets corrupt (file # string, block # string). It generally happens the data block indicated becomes corrupt due to software errors. And to fix it you have to restore the segment that contains the block indicated. For this it may involve the dropping of the segment as well as recreating it. And if any trace of the file is identified than it would report the error in the ORACLE representative. Or else to fix this problem, you can use Oracle Repair Tool. Using this application, you can fix errors related to Oracle in an easy way.

[Former Member]

Database corruption occur when there is any virus attack, human errors, abrupt shutdown of system, etc. These reason can easily corrupt the entire database and data stored in Database becomes inaccessible. To fix your problem follow - How to Detect and Fix Corrupted Table Oracle?

[Reagan]

Hello

Follow these SAP notes

365481 - Block corruptions

923919 - Advanced Oracle block checking features

Regards

RB

[ACE-SAP]

Hi,

Bad thing, I hope you did run the consistency check (see here under note) and that the corruption is not in the DB since long enough that you do not have a clean backup.

At least SAP data should not be directly involved as the corruption is within system tablespace... but there are critical tables in the system tablespace...

Ok, may be it is not a big problem, at first you need to find what is inside that block using that command:

select OWNER, SEGMENT_NAME, SEGMENT_TYPE from DBA_EXTENTS where file_id = 1 and 56449 between block_id and (block_id + blocks - 1);

usnused block (query returns "no row selected")

=> not harmful, Oracle will reformat the block when it will need... but it is not likely to be the problem you have

used by index

=> quite easy, just rebuild the index

used by a table

=> then it can be tougher,

Test a select * from <owner>.<table name> to see if you get an error, if not the block is unused

- unused block bellow HWM

=> table reorg or shrink

- used by a table with data it it

=> here you have a real problem...

solution could be to restore table from a previous clean backup, try to recover the data that can still be accessed

You must also:

- perform a full DB check (DBV + analyze), there might be other corrupted blocks in your DB

- have a full system check => system log, hardware, SAN...

540463 - FAQ: Consistency Checks + Block Corruptions

23345 - Consistency check of ORACLE database

[Former Member]

Hi,

After executing this command,

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 1 and 56449 between block_id AND block_id + blocks -1 ;

TABLESPACE_NAME

SEGMENT_TYPE

OWNER

------------------------------ ------------------ ------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SYSTEM

CLUSTER

SYS

C_OBJ#

Now what activity i have to do..

Regards,

R.Ramkumar

[ACE-SAP]

Hi,

It's getting quite bad, C_OBJ# is a cluster table that did store 17 tables of the Oracle data dictionary...

I hope this is not happening on a production system, anyway you should open a customer message to SAP.

You should perform a full check of your DB using DB13 / BR*Tools

brbackup  -u / -c force -w only_dbv -p init<SID>.sap -m ALL

brconnect  -u / -c -f stats -v cascade -t ALL

You could try to select the content of all the tables from the C_OBJ# cluster.

Get the list of the tables:

select cluster_name, table_name from user_tables where cluster_name =' C_OBJ#' order by 1;

CLUSTER_NAME                   TABLE_NAME

C_OBJ#                         TYPE_MISC$

C_OBJ#                         ATTRCOL$

C_OBJ#                         ASSEMBLY$

C_OBJ#                         LIBRARY$

C_OBJ#                         VIEWTRCOL$

C_OBJ#                         ICOLDEP$

C_OBJ#                         OPQTYPE$

C_OBJ#                         REFCON$

C_OBJ#                         NTAB$

C_OBJ#                         SUBCOLTYPE$

C_OBJ#                         COLTYPE$

C_OBJ#                         LOB$

C_OBJ#                         COL$

C_OBJ#                         TAB$

C_OBJ#                         CLU$

C_OBJ#                         IND$

C_OBJ#                         ICOL$

and then test all of them

select * from sys.TAB$

select * from sys.COL$

[...]

If you are able to list all their content without error you are almost safe, at least no data is lost...

Regards

[Former Member]

Hi,

I just started to test these 3 tables, ICOL$, IND$, COL$, its taking to much time nearly 15 to 20 min. to read all the contents, and it is ending with these many rows selected. My doubt is how I will come to know the error. If there is an error in the table whether it will stop or any notification can we see??

ICOL$

285738 rows selected.

IND$

87098 rows selected.

COL$

966637 rows selected.

Regards,

R.Ramkumar

[Former Member]

Hi,

I have tested all 17 tables, for these tables VIEWTRCOL$, REFCON$, ASSEMBLY$, I got the result as "no rows selected".

Regards,

R.Ramkumar

[ACE-SAP]

Hi

If some data/line was corrupted you should have get an error message on the sql prompt, you can also check in the Oracle alert log.

So if you are able to read all these tables contents without an error you are quite safe.

The solution would be to reorganize that table... but I cannot drive you through that process.

I've never reorganize a system cluster table, you should ask SAP support to help you.

Did you run a full check of your DB as suggested ?

Regards

[janos_mucsi-besze]

Hi Ram,

The problem is, that a SYS object got corrupted. These are not se easy to repair.

One thing must not be forgotten here, you should have a consistent backup which does not contains

the corrupted data.

As 'Yves' wrote, you should check the whole database to see if there are other corruptions present.

This, of course, takes a long time. But this is the only way to check the database.

Anyway, these checks should run weekly or at least once per backup cycle.

Solution, as it is documented in note 365481:

------------------------------------------------------------

3. Does the database object belong to the SYS database user or is it located in the SYSAUX tablespace?

     General rule: The object only belongs to the SYS user if it is in the system tablespace.

              If yes:
Save the corrupt data file and reimport a backup of
the file from a backup that does not contain the corruption. If all of the
archive logs since the creation of the file are available, you can also perform
a CREATE DATAFILE as described in SAP Note 4161. Recover the file up to the
current time. If you do not have either a corruption-free backup or all of the
archive logs since the file creation, your consistency check concept or backup
concept is insufficient.

If you do not have any 'good' backup available, you could try to rebuild the SYSTEM tablespace as documented in note:

748434 New BRSPACE function "dbcreate" - recreate database

In case of a corruption, there isn't any quick and easy solution, at least in most of the cases. And unfortunately, yours is a really bad situation.

Regards,

János