Re: Datafile mistery...

-={Giorgio}=- <leva.md3496_at_mclink.it> schreef in berichtnieuws bhbd3l$1bnk$1_at_newsreader1.mclink.it...
|
| Hello there,
|
| i was just playing with user-managed bu and recovery
| on a Linux/9i platform to understand better the mechanism.
| Well... few strange things happened, strange if you
| take things for granted...
| Do someone know why the following behaviour?
|
| Let's start:
|
| [Scenario: media recovery on a tablespace lost during
| an online backup with DML activity on it]
|
| SQL> create table num ( id integer) tablespace users;
|
| SQL> insert into num values (1);
| SQL> insert into num values (2);
| SQL> insert into num values (3);
| SQL> commit;
| SQL> Select * from num
| 2 /
|
| ID
| ----------
| 1
| 2
| 3
|
| SQL> alter tablespace users begin backup
| 2 /
|
| Tablespace altered.
|
| 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???
|
| 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 :)
|
| 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.
|
| 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...
|

On Unix you can remove a file while it is open. You don't see the file with "ls" anymore but it actually stays there until all processes that has the file open ends. Then the file really disappears. That happened when you shutdown the database. Until then it was available as normal for Oracle. The "fuser" command shows you which process has a file open. There was another thread in this newgroup recently where this is discussed (search in Google).

And DBWR writes modified blocks to the datafiles indead, not only when a log switch occurs but on other occasions to. It's in the docs (like every n seconds, when there are too many dirty buffers etc.etc.( Received on Tue Aug 12 2003 - 14:17:16 CDT