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

[Chucks_k]

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:

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

[hrishy]

Hi

Run the following query

select file#,name,status,enabled from v$datafile
where file#=2724

and post the results

[Chucks_k]

Hi Hrishy,

This is the output:

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!

[hrishy]

Hi

Just wundering whats the status of

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

and

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

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

[Chucks_k]

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