Re: startup fine, but v$recover_file has entries

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 10 Nov 2003 20:30:44 +1100
Message-ID: <3faf5ad5$0$3502$afc38c87@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:

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]

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!]

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]

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.

Regards
HJR Received on Mon Nov 10 2003 - 03:30:44 CST