Re: Datafile mistery...

On Tue, 12 Aug 2003 20:57:49 +0200, -={Giorgio}=-<leva.md3496_at_mclink.it> wrote:

Comments in-line

[snip]
>
> SQL> alter system switch logfile;
>
> System altered.
>
> SQL> select name from v$datafile
> 2 /
>
> NAME
> --------------------------------------
> /u03/oradata/topo/sys_topo_01.ora
> /u02/oradata/topo/undo_topo_01.ora
> /u04/oradata/topo/users_topo_01.ora
>
> SQL> !rm /u04/oradata/topo/users_topo_01.ora
>
> SQL> alter tablespace users end backup
> 2 /
>
> Tablespace altered. <---- ??????
>
> >>> Shouldn't O check for datafiles availability when
> >>> the tablespace changes of status???
>

Not once you understand that all 'begin backup' and 'end backup' commands do is to make a change to the control file, permitting CKPT at some point in the future to bring the datafile's checkpoint change number up to date. The command makes no alteration to the datafile itself, and therefore there's no need to check anything.

> SQL> Select * from num
> 2 /
>
> ID
> ----------
> 1
> 2
> 3
>
> >>> OK: cache hit
>
> SQL> alter system checkpoint
> 2 /
>
> System altered.
>
> SQL> alter system switch logfile
> 2 /
>
> System altered.
>
> >>> I was expecting O to write something on file header?!
>
> SQL> insert into num values (5)
> 2 /
>
> 1 row created.
>
> SQL> commit
> 2 /
>
> Commit complete.
>
> >>> cache again
>
> SQL> alter system switch logfile
> 2 /
>
> System altered.
>
> SQL> alter system checkpoint
> 2 /
>
> System altered.
>
> >>> AGAIN: there is a committed tran and O
> do not write to the datafile?
>
> SQL> shutdown immediate
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
>
> >>> File header are not touched at Shutdown?!!
> >>> Confusing...
>
> SQL> startup
> ORACLE instance started.
> ...
>
> >>> File header are not touched at Startup?!!
> >>> More confusing...
>
> SQL> Select * from num
> 2 /
> Select * from num
> *
> ERROR at line 1:
> ORA-00376: file 3 cannot be read at this time
> ORA-01110: data file 3: '/u04/oradata/topo/users_topo_01.ora'
>
> >>> I am happy now :)

Welcome to the wonderful world of Linux (or Unix generally). You are suffering from inode problems! It's very difficult ever to demonstrate the loss of a control file or a data file whilst the database is running, because the remove command only does some of the job (and you'll need a proper Linux expert to tell you why). Basically, the rm command certainly makes it look to you as though the file has been deleted, because an ls will show it missing. But Oracle's got the inode open where the file starts, and that can't be *really* dealt with until Oracle hasn't got it open any more. So as soon as you shutdown the database (or take the tablespace offline), the inode is released, the O/S hammers it, and then you have 'missing file' problems.

Try it with your control file sometime: just rm it whilst the database is open. The database will work perfectly forever. Until the next shutdown.

Us Windows users have exactly the same sort of problem, too. Try and delete the datafile in the first place, whilst the database is running, and you get told in no uncertain terms that there's been a sharing violation, and the delete will not be allowed. Crash the instance, and switch off the service, and the delete's a piece of cake.

>
> I tried to reproduce everything ona win/8i machine to
> see if probably there were differences between the two versions
> but unfortunately Win locks down the file for writing when
> you put the tablespace in backup mode, so i couldn't simulate
> a media failure.

There you go... and you can't very easily do it the way you've described on any flavour of Unix either.

Somebody out there will now post a reply about dd or somesuch wonderful Unix/Linux command that stuffs the inodes. But your other alternative is just to imagine that the loss of the datafile has caused an instance crash, and throw in a shutdown abort as part of your testing.

Regards
HJR
>
> Any idea why this behaviour? (should i ask tom? ;)
>
> /G
>
> PS: The only thing i was thinking is that O actually flsuh trans
> on the datafile when the redo log (owning the transaction) has
> to be overwritten, but this is not what i've been reading so far
> on O docs... they say that DBWr actually flushes modified blocks
> when a log switch occurs.
>
> --
> Remove "leva." from my email for private messages...
>
>
>
>
>

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/