In  Order to Perform/Test this Scenario…..We should delete/Drop One Non system Datafile.

But This not possible to drop/Delete files when Database is Up and  running ……

“The process cannot access the file because it is being used by another process.”

here I `ll  copy/Replace User datafile with an Old datafile.

In shutdown mode I will manually  copy old file in using “cp” command,and try to Open DB.

C:\Windows\System32>sqlplus  sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 12 09:36:36 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from emp_cp;
select count(*) from emp_cp
                     *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: 'D:\APP1\SRIRAM\ORADATA\SRIHARSHA\USERS01.DBF'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         4 ONLINE  ONLINE
UNKNOWN ERROR                                                         996306
12-MAY-11

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database recover automatic datafile 4;

Database altered.

SQL> alter database datafile 4 online;

Database altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from scott.emp_cp;

  COUNT(*)
----------
       112

SQL> select * from v$recover_file;

no rows selected

SQL>

If archive log not enabled the you may receive 

SQL> alter database datafile 4 offline;
alter database datafile 4 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

🙂