Re: startup fine, but v$recover_file has entries

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3faf5ad5$0$3502$afc38c87_at_news.optusnet.com.au>...
> "yls177" <yls177_at_hotmail.com> wrote in message
> news:c06e4d68.0311100121.7dbd2abf_at_posting.google.com...
> > i did a backup/restore. startup no problems. however, in the course of
> > my application, i get some database errors. therefore, i went to
> > check. and to my surprise, i find entries in V$recover_file.
> >
> > i recovered them, put them online and all is fine.
> >
> > but i am puzzled why there are entries in v$recover_file and startup
> > is no problem.
>
> Some tablespace/datafile names would have been nice, since they often offer
> a clue. A version and O/S is also pretty obligatory for any post.
>
> In the absence of much information, I can't say why your specific
> circumstances gave rise to the situation, but you can always do a little
> experiment like so:

apologies that i didnt put in more information

>
> SQL> alter database datafile 3 offline;
> Database altered.
>
> SQL> connect sys/xxxxxxx_at_lx92 as sysdba
> Connected.
> SQL> startup force
> ORACLE instance started.
>
> Total System Global Area 135336416 bytes
> Fixed Size 451040 bytes
> Variable Size 109051904 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 667648 bytes
> Database mounted.
> Database opened.
>
> [Note the perfectly normal startup]

yes, i got exactly the above... though the figures are a bit different

>
> SQL> select * from hr.employees;
> select * from hr.employees
> *
> ERROR at line 1:
> ORA-00376: file 3 cannot be read at this time
> ORA-01110: data file 3: '/u01/app/oracle/oradata/lx92/example01.dbf'
>
> [Application problems!]

yup, this oracle code 376 appears. however, i didnt do a select at sql>. in my application which is sap, i have this oracle code appearing in my sap system log

>
> SQL> select * from v$recover_file;
>
> FILE# ONLINE ONLINE_
> ---------- ------- -------
> ERROR CHANGE#
> ----------------------------------------------------------------- ----------
> TIME
> ---------
> 3 OFFLINE OFFLINE
> 573847
> 10/NOV/03
>
> [V$recover_file contains an entry]

exactly, its the same file as complained in the oracle code.

>
> This arises from the fact that taking a datafile offline without a
> checkpoint means it's in a possibly inconsistent state, and would thus need
> recovery. But because it *is* offline, SMON doesn't regard it as an
> impediment to opening what's left of the database at startup. What's left of
> the database is consistent, after all. It sort of assumes you'll sort out
> the problem file when you see fit to do so.
>

taking a datafile offline without a checkpoint. hmmm, i did shutdown oracle before doing a backup. i understand your explanation about oracle starting up nicely even though some files are offline. but then , i dont understand, after u shutdown oracle, some datafiles are not checkpointed yet?

> Regards
> HJR
Received on Mon Nov 10 2003 - 09:58:51 CST