DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-00376/01110 even though file is online and does not need recovery

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    ORA-00376/01110 even though file is online and does not need recovery

    Hi all,

    DB:10.2.0.3 in RAC/Dataguard config
    O/S: RHEL 4

    We have just moved datafiles from one ASM diskgroup to another. We had to move the datafiles because the underlying LUN for that
    particular ASM diskgroup was suspect and a new disk rebuild is needed. Once the
    datafile was online we ran a normal application query and got the error:

    Code:
    SQL> select count(*) from plateimages t
    where t.datetime > sysdate -24
    and t.datetime < sysdate -19
    and t.id like 'YR06BGE%'
    --and substr(id,(length(id)-13),4) = '0002' 2 3 4 5 ;
    
    select count(*) from plateimages t
    *
    ERROR at line 1:
    ORA-00376: file 2724 cannot be read at this time
    ORA-01110: data file 2724:
    '+VGROUP36/dbh/datafile/img3_id_2008_02_12.365.648647395'
    
    We checked dba_data_files to confirm that the file was online and available:
    
    SQL> select * from dba_data_files where 
    file_id=2724;
    
    
    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- 
    -----------
    ONLINE_
    -------
    +VGROUP36/dbh/datafile/img3_id_2008_02_12.365.648647395
    2724 IMG3_ID_2008_02_12 AVAILABLE
    1024
    ONLINE
    I can see no other errors and no trace files have been generated. We have a standby configuration and restored the above file from the
    standby but we got the same error.

    Any advise will be much appreciated,

    Thanks in advance,
    Chucks

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Run the following query

    Code:
    select file#,name,status,enabled from v$datafile
    where file#=2724
    and post the results
    Last edited by hrishy; 03-06-2008 at 10:49 AM.

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    This is the output:

    Code:
    QL> select file#,name,status,enabled from v$datafile where file#=2724;
    
         FILE#
    ----------
    NAME
    --------------------------------------------------------------------------------
    STATUS  ENABLED
    ------- ----------
          2724
    +VGROUP36/dbh/datafile/img3_id_2008_02_12.365.648647395
    ONLINE  DISABLED
    So i can see that enabled is set to disabled meaning no sql access is possible even thoough the file is online? Is this the problem?
    How does one enable this?

    Thanks in advance!

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Just wundering whats the status of

    Code:
    SELECT file#, online, error 
    FROM v$recover_file
    where file#=2724
    and

    Code:
    select tablespace_name,status 
    from dba_tablespaces
    where tablepsace_name='IMG3_ID_2008_02_12'

    if the tablespace is offline you need to online it using

    Code:
    alter tablespace IMG3_ID_2008_02_12 online;
    
    select tablespace_name,status 
    from dba_tablespaces
    where tablepsace_name='IMG3_ID_2008_02_12'
    and run your query again


    does the ASM instance alert log show anything regarding this file

    regards
    Hrishy
    Last edited by hrishy; 03-06-2008 at 11:40 AM.

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    After running the second query i saw the tablespace was offline. Hence i onlined the tablespace and everything started working. If it is baffling how this tablespace was offline as it only has one datafile and I switched to online. Maybe this tablespace was offline all the way along. Anyway now the problem is sorted and thank you very much for your help!

    Thanks again,
    Chucks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width