-
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
-
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.
-
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!
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|