Re: will there be data loss?

On Feb 27, 11:33 am, Ben <benal..._at_yahoo.com> wrote:
> 10.2.0.2 EE, AIX 5.2 64bit. RMAN 10.2.0.2
>
> I have run the scenario and know the outcome, but was surprised kind
> of.
>
> database is in archivelog mode. I start up rman and connect to target
> and catalog then take a hot tablespace backup of the users tablespace
> which is made up of one datafile.
> I then proceed to log in via sqlplus and create a new table
>
> balvey.testing( txt varchar2(200), numb1 number) tablespace users;
>
> and insert 4 records and commit
>
> alter system switch logfile
>
> select count(*) from balvey.testing and see the 4 records.
>
> exit sqlplus and using os commands I delete the one and only datafile
> that makes up the tablespace
>
> If I log into the database again and insert records into the newly
> created balvey.testing and commit. Will the data that was inserted
> after deleting the datafile be lost?

This is platform and version dependent. It is common on unix style platforms for Oracle to keep the file open, while using the OS commands affect only the directory entries for the file until it is closed - Oracle still has it, but you can't see it from the OS without knowing inodes. So what will likely happen is the data will be inserted, then the data file will be lost when Oracle is shut down. It is entirely possible to restore the data files to a backup previous to the deletion, then recover the data through applying archived logs. I believe there is even an example on metalink that shows recovery from _before the file was created_.

jg

--
@home.com is bogus.
"It ain't the teat, it's the tumidity" - William F. Buckley, RIH (Rot
In Hell) (but I thought that line was so funny it was my .sig for a
couple years when I used tumidity as a login)