DML Operation on a Lost Datafile

Scenario: I have a tablespace called userdata with one datafile associated with it. The disk holding the dbf is not accessible. Can I still perform DML operations on the objects associated with the that tablespace.

Yes, Only in one case, where the blocks holding the objects that need access/modifications are already present in Database Buffer Cache component of SGA. If the blocks are present then modifications will be done in buffer itself, even if commit is executed, it only needs to write redo buffer to Redo Log Files, no where dbf is being touched.

But if the DML operations requires new blocks to be fetched to DBBC from datafile then File Not Exist error will be thrown.

1. Insert a row into salgrade table, so that the required blocks are fetched to DBBC and delete the datafile

sqlplus pay/pay

insert into salgrade values (121212,121212,121212);

commit;

Delete DBF - rm /u01/CHARAN/oracle/11gR1/oradata/userdata01.dbf

update the same row that is already present in DBBC

update salgrade set grade=131313 where losal=121212;

commit;

select * from salgrade where grade=131313;

2. Try to insert many rows so that the current blocks in buffer are filled up and Oracle requires new blocks to be fetched from Data Files

3. Perfom recovery on datafile to get back all the committed data

alter database datafile 4 offline;

Restore the datafile from previous cold backup

alter database recover automatic datafile 4;

Oracle will apply all the available archive logs and then the data in the online redo logs to get back the datafile to the current state

alter database datafile 4 online;

That's it!!!

Regards

Charan