Forum Stats

  • 3,733,243 Users
  • 2,246,736 Discussions
  • 7,856,631 Comments

Discussions

ORA-01115: IO error reading block from file 20 (block # 61922)

Ramky99
Ramky99 Member Posts: 101
Hi all,

I'm faced with this error:

ORA-01115: IO error reading block from file 20 (block # 61922)

So I ran DB verify tool against file 20 and got the following:


dbv file=/xxx/tbl_small_static_xxx_01.dbf blocksize=8192

DBVERIFY: Release 8.1.7.0.0 - Production on Wed Nov 10 11:02:43 2010

(c) Copyright 2000 Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /xxx/tbl_small_static_xxx_01.dbf


DBVERIFY - Verification complete

Total Pages Examined : 16384
Total Pages Processed (Data) : 4588
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 410
Total Pages Empty : 11386
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

..so no probs???

DB is 8.1.7.0.0
OS is AIX 4.3.3.0

What else can I do if verify says all is OK?

Thanks!
Adam

Answers

  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    edited November 2010
    Hi,

    any other errors with ora-01115 error? if yes post

    these are specific to hardware vendor also
    and check *ORA-1115 and ORA-27092 in Alert Log on AIX [ID 179207.1]*

    Thanks
  • Renjith Madhavan
    Renjith Madhavan Member Posts: 228
    edited November 2010
    Use:
    SELECT segment_name , segment_type , owner , tablespace_name
    FROM sys.dba_extents
    WHERE file_id = &bad_file_id
    AND &bad_block_id BETWEEN block_id and block_id + blocks -1
    /

    To identify segment with bad block.

    To create a repair table Use:
    DBMS_REPAIR.ADMIN_TABLES (
    TABLE_NAME => 'REPAIR_TABLE',
    TABLE_TYPE => dbms_repair.repair_table,
    ACTION => dbms_repair.create_action,
    TABLESPACE => 'FIX');
    END;
    /

    To fix use:
    BEGIN
    num_fix := 0;
    DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
    SCHEMA_NAME => 'SCOTT',
    OBJECT_NAME=> 'EMP',
    OBJECT_TYPE => dbms_repair.table_object,
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    FIX_COUNT=> num_fix);
    DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
    END;
    /
  • Suhail Faraaz
    Suhail Faraaz Member Posts: 726
    Copied from http://forums.oracle.com/forums/thread.jspa?threadID=554137
    Hi,
    YOu have to restore datafile and perform recovery.
    
    SQL> conn scott/tiger
    Connected.
    SQL> select * from emp;
    select * from emp
                  *
    ERROR at line 1:
    ORA-01115: IO error reading block from file 4 (block # 28)
    ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'
    ORA-27091: unable to queue I/O
    ORA-27070: async read/write failed
    OSD-04006: ReadFile() failure, unable to read from file
    O/S-Error: (OS 38) Reached the end of the file.
    
    SQL> conn sys as sysdba
    Enter password:
    Connected.
    SQL> alter tablespace USERS offline immediate;
    
    Tablespace altered.
    
    SQL> --copy users datafile from recent valid backup location
    SQL> set autorecovery on
    SQL> recover tablespace USERS;
    ORA-00279: change 1308298 generated at 09/10/2007 11:19:22 needed for thread 1
    ORA-00289: suggestion :
    C:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00140_0632336321.001
    ORA-00280: change 1308298 for thread 1 is in sequence #140
    
    [output cut]
    
    Log applied.
    Media recovery complete.
    
    SQL> alter tablespace USERS online;
    
    Tablespace altered.
    
    SQL> conn scott/tiger
    Connected.
    SQL> select * from emp;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    --------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------
        DEPTNO
    --------------------------------------------------------------------------------
          7369 SMITH      CLERK           7902 17-DEC-80        800
            20
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    In simpler you can run from RMAN also
    RMAN>blockrecover datafile 20 block 61922;
    Thanks
  • Rajesh Lathwal
    Rajesh Lathwal Member Posts: 2,423
    edited November 2010
    Could you paste complete error from alert log file ..

    Also are you getting any other ORA error also ?

    Also see MOS note :

    ORA-1115: I/O ERROR READING BLOCK [ID 1013621.6]

    Regards
    Rajesh
  • Ramky99
    Ramky99 Member Posts: 101
    Strangely I only see ORA 01115 in the application error log, nothing in the alert log.

    SQL> r
    1 SELECT segment_name , segment_type , owner , tablespace_name
    2 FROM sys.dba_extents
    3 WHERE file_id = &bad_file_id
    4* AND &bad_block_id BETWEEN block_id and block_id + blocks -1
    Enter value for bad_file_id: 20
    old 3: WHERE file_id = &bad_file_id
    new 3: WHERE file_id = 20
    Enter value for bad_block_id: 61922
    old 4: AND &bad_block_id BETWEEN block_id and block_id + blocks -1
    new 4: AND 61922 BETWEEN block_id and block_id + blocks -1

    SEGMENT_NAME
    --------------------------------------------------------------------------------
    SEGMENT_TYPE OWNER TABLESPACE_NAME
    ------------------ ------------------------------ ------------------------------
    CUST_LEDGER_HEADER
    TABLE xxx TBL_CUST_LED_xxx


    ..I tried some of the DBMS_REPAIR stuff and I got an error:

    SQL> exec DBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE',TABLE_TYPE => dbms_repair.repair_table,ACTION => dbms_repair.create_action,TABLESPACE => 'FIX');
    BEGIN DBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE',TABLE_TYPE => dbms_repair.repair_table,ACTION => dbms_repair.create_action,TABLESPACE => 'FIX'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 76:
    PLS-00201: identifier 'DBMS_REPAIR.REPAIR_TABLE' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    ..also there is no backup. This DB was recovered yesterday with great pain from a bad export file, which was our only backup :(

    Thanks!
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    Why cant you use RMAN. :)
  • Ramky99
    Ramky99 Member Posts: 101
    CKPT wrote:
    Why cant you use RMAN. :)
    I wish I could .. not my box and the only proper backups they have are too old to recover from.

    Thanks :)
    Adam
  • Ramky99
    Ramky99 Member Posts: 101
    CKPT wrote:
    Why cant you use RMAN. :)
    Tried the suggested anyway:
    rman
    Recovery Manager: Release 8.1.7.0.0 - Production

    RMAN> blockrecover datafile 20 block 61922;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01005: syntax error: found "identifier": expecting one of: "allocate, alter, beginline, catalog, change, connect, create, crosscheck, configure, debug, delete, drop, exit, endinline, host, {, library, list, mount, open, print, register, release, replace, report, renormalize, reset, resync, rman, rpctò"
    RMAN-01008: the bad identifier was: blockrecover
    RMAN-01007: at line 1 column 1 file: standard input


    ??

    thanks!
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    edited November 2010
    Hi,

    can you post the result of

    select FILE#,error,change#,online_status from v$recover_file where file#=20;
    if no errors then no need to worry. it might due to hardware vendors also :)

    Thanks
  • Ramky99
    Ramky99 Member Posts: 101
    CKPT wrote:
    Hi,

    can you post the result of

    select FILE#,error,change#,online_status from v$recover_file where file#=20;
    if no errors then no need to worry. it might due to hardware vendors also :)

    Thanks
    SQL> select FILE#,error,change# from v$recover_file where file#=20;

    no rows selected


    Thanks :)
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    Hi,

    no errors, you can close the thread as answered. THanks :)
  • Ramky99
    Ramky99 Member Posts: 101
    CKPT wrote:
    Hi,

    no errors, you can close the thread as answered. THanks :)
    But there was / is an error when we take certain transactions in the system, so there must be something wrong right?

    Thanks!
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    Hi,

    are you facing any errors still?
    i asked to post what are the other errors with ora-01115.

    THank
  • Ramky99
    Ramky99 Member Posts: 101
    CKPT wrote:
    Hi,

    are you facing any errors still?
    i asked to post what are the other errors with ora-01115.

    THank
    I only get ORA-01115 in application log, nothing in alert log strangely.... anywhere else it might show?

    Thanks!
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    Hi,

    how ever you have checked by view no block corruption found, these are mostly due to OS vendor problems..
    Thanks
  • Ramky99
    Ramky99 Member Posts: 101
    I have some more errors now when I try to rebuild and index:

    alter index cust_ledger_rates_idx1_pk rebuild
    *
    ERROR at line 1:
    ORA-01115: IO error reading block from file 21 (block # 38986)
    ORA-01110: data file 21:
    '/home/itcdata/fsr1_10/itcprd01/tbls/tbl_cust_led_xxx_02.dbf'
    ORA-27092: skgfofi: size of file exceeds file size limit of the process
    Additional information: 131071
    Additional information: 131073

    Are these the ones referred to earlier? Any ideas?

    Thanks!!
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    edited November 2010
    Hi,

    Have you checked *ORA-1115 and ORA-27092 in Alert Log on AIX [ID 179207.1]*
    *OERR: ORA-27092 { Message text depends on version } [ID 124822.1]*
    post what is the size of the datafile 20?
    Thanks

    Edited by: CKPT on Nov 10, 2010 9:34 PM
  • Ramky99
    Ramky99 Member Posts: 101
    Sorry to sound dumb but what is that? Is there a link to a known issue somewhere?

    Thanks!
    Adam
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    edited November 2010
    Set the ulimit file limit for the user to unlimited or at least more than the maximum datafile size

    Do you have metalink login id with valid CSI then you can logon to http://support.oracle.com
    and enter that article id

    Edited by: CKPT on Nov 10, 2010 9:42 PM
This discussion has been closed.