1

I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf'

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

Now I am stuck on which index is that actual segment name. (I think :/ ) I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

  • ERROR at line 1: ORA-01418: specified index does not exist

Even though if I do "select index_name from dba_indexes", that index name shows up. What am I doing wrong? This is a 10.2 Oracle server.

Thanks in advance!

EDIT If I do MYSRVR.PK_ZXCATSET_CATID the alter completes... but I still don't think my problem is fixed!

3
  • 1
    What Oracle version?
    – Philᵀᴹ
    Apr 25, 2013 at 4:22
  • Hello, for next time: please don't post the same question on two different Stack Exchange sites. If you think you picked the wrong site when you posted (or someone points that out in comments), please use the "flag" link right below the tags, and ask for your question to be migrated in the "other" option. Otherwise, you we end up with duplicates, and potentially answers scatters all over the place which isn't good. Thanks!
    – Mat
    Apr 25, 2013 at 9:20
  • SQL*Plus: Release 10.2.0.2.0 - Production on Thu Apr 25 17:15:44 2013 Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
    – user83598
    Apr 25, 2013 at 17:17

3 Answers 3

2

Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:

[oracle@oca ~]$ rman target=/

RMAN> list faliure;

If there are failures with status OPEN listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:

RMAN> advise failure;

Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:

RMAN> repair failure;

After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure command in RMAN, and if not you can get another advice from DRA.

EDIT:

Since the recovery you performed didn't last long (elapsed time: 00:00:00), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.

In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG mode (check with select log_mode from v$database) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.

2
  • Thanks, was there a step to do before connecting to rman? I get: RMAN> list failure; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "identifier": expecting one of: .....
    – user83598
    Apr 25, 2013 at 17:43
  • doh.... I've never used rman before but I just tried 'RMAN> blockrecover datafile 8 block 22921' and got 'RMAN-03002: failure of blockrecover command at 04/25/2013 17:49:20 RMAN-05009: Block Media Recovery requires Enterprise Edition` Am I sol?
    – user83598
    Apr 25, 2013 at 17:51
0

Ok, the "RMAN> list failure;" thing didn't work, but it did lead me in the right direction. To get things going again I did this:

$  rman target sys/myuser@mytargetsrvr

RMAN> recover datafile 8

// Which gave this

ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 8 - file is in use or recovery

// instead I did this first

RMAN> sql 'alter tablespace MYSRVR_IDX_TB offline';

sql statement: alter tablespace MYSRVR_IDX_TB offline

RMAN> recover datafile 8;

Starting recover at 25-APR-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 25-APR-13

RMAN> sql 'alter tablespace  MYSRVR_IDX_TB online';

sql statement: alter tablespace  MYSRVR_IDX_TB online

Big thanks to Yasir Arsanukaev!

2
  • RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "database": expecting one of: "backupset"
    – user83598
    Apr 25, 2013 at 23:14
  • after i ran through your commands, the data is still corrupted, nothing seems fixed
    – Benny
    May 23, 2016 at 8:11
-2

alter index "MYSRVR"."PK_ZXCATSET_CATID" rebuild tablespace MYSRVR_IDX_TB;

or alter index MYSRVR.PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.