Database error 1578 at FET

[Former Member]

Hi Experts,

Could you please help me with this error 'coz I don't have any knowledge with Oracle SQL.

Error was found in SM21 of our BI test system.

Database error 1578 at FET.

ORA-01578: ORACLE data block corrupted (file # 31, block # 3921)

ORA-01110: data file 31: '/oracle/BDR/sapdata3/sr3_15/sr3.data15'

ORA-26040: Data block was loaded using the NOLOGGING option.

Can you help me resolve the problem?

Below are the info I have as of now.

Corrupted file is /BIC/FZSD_C02~020

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE

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

SAPSR3

/BIC/FZSD_C02~020

INDEX PARTITION

SQL> select tablespace_name

  2  from dba_data_files

  3  where file_name = '/oracle/BDR/sapdata3/sr3_15/sr3.data15'

  4  /

TABLESPACE_NAME

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

PSAPSR3

[Former Member · Accepted solution]

Please read Note 365481 - Block corruptions

In few words first detect object corrupted block belongs to and then take actions according to object type.

Regards

Roman

[Former Member · Accepted solution]

I already have identified the corrupted object (file # 31, block # 3921)

OWNER : SAPSR3

SEGMENT NAME: /BIC/FZSD_C02~020

SEGMENT TYPE: INDEX PARTITION

Could anyone tell me how can I correct the corrupted data block?

[Former Member · Accepted solution]

Please read p.6 of SAP Note specified above:

1. 6. Is it a user SAPR3 index?

              If yes:
You can correct index corruption by reorganization (delete/recreate or "rebuild online" of the index) using brspace or by restoring/recovering the corrupt file. A "rebuild" WITHOUT the "online" option does not work because the "rebuild" tries to read the index, whereas the "rebuild online" reads the table. Even if the index tablespace is set to offline, you can use rebuild online. In most cases, an index reorganization is considerably faster than a restore/recovery unless you QUICKLY have a CHECKED backup available as well as ALL archive logs that have arisen since you created the backup and only a few changes were carried out in the tablespace in question that must be traced. If you are not sure which method is better for you, select the index reorganization, provided that you do not have additional table corruptions.

              Index corruptions are not critical regarding data loss because you can always set up indexes again. However, they may lead to terminations of the application if you try to use the corrupt index.

              For information about rebuild online, see Note 682926. Especially for unique indexes, rebuild online has the advantage over delete/recreate that you can continue to use the system.

              Delete/recreate for a unique index must be carried out when the database is closed. Otherwise, there is a risk of duplicate keys.

              You can recognize unique indexes if the following select returns a 'UNIQUE' (otherwise 'NONUNIQUE'):

              select INDEX_NAME, UNIQUENESS from dba_indexes where index_name =

              '<OBJECTNAME>';

Regards

Roman

[former_member189725]

Please check SAP note Note 547464 - Nologging Option when creating indexes

Refer point 4. What are the disadvantages of using 'nologging' during index creation?

You will need to recreate/rebuild the index /BIC/FZSD_C02~020.

Rebuild the index using the brspace

http://help.sap.com/saphelp_nw70/helpdata/en/75/95536a63b44b4a9e7f278c7d9c2b67/content.htm

Regards

Ratnajit