Recover Manually Dropped Datafile in ASM Scenario - Oracle 11gR2

Create a test tablespace and user eg:-MAC

SQL> show parameter db_n

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

db_name                              string      DB11G1

SQL> show parameter create
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

SQL> create tablespace MAC datafile '+DATA' size 5M;
Tablespace created.

SQL> create user MAC identified by oracle default tablespace MAC quota unlimited on MAC;
User created.
SQL>
SQL>
SQL>
SQL> conn MAC/oracle
ERROR:
ORA-01045: user MAC lacks CREATE SESSION privilege; logon denied

Grant create session privilege to MAC.

SQL> grant create session to MAC;
SQL> conn MAC/oracle;

Connected.

Try to create a sample table and insert a row eg:-MAC_sample

SQL> create table MAC_sample (id number);
create table MAC_sample (id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Grant create table privilege to MAC.

SQL> grant create table to MAC;
Grant succeeded.
SQL> connect MAC/oracle
Connected.
SQL> create table MAC_sample (id number);
Table created.
SQL> insert into MAC_sample values (123);
1 row created.
SQL>
SQL> commit;

Make a note of current scn incase to flashback;

[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 20:27:11 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1300544

Try to drop the datafile manually now, datafiles reside in "+DATA/DB11G1/DATAFILE";

[oracle@db11g ~]$ su - grid
Password:

[grid@db11g ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/grid
[grid@db11g ~]$ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     20473    18340                0           18340              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     20473    18671                0           18671              0             N  FRA/
MOUNTED  HIGH    N         512   4096  1048576      8188     8006             4094            1304              0             N  OCR/
ASMCMD [+] > cd /DATA
ASMCMD [+DATA] > ls
DB11G1/
ASMCMD [+DATA] > cd DB11G1
ASMCMD [+DATA/DB11G1] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileDB11G1.ora
ASMCMD [+DATA/DB11G1] > cd DATAFILE/
ASMCMD [+DATA/DB11G1/DATAFILE] > ls
EXAMPLE.265.997039919
MAC.270.1001189641
SYSAUX.257.997039651
SYSTEM.256.997039649
UNDOTBS1.258.997039651
USERS.259.997039651
ASMCMD [+DATA/DB11G1/DATAFILE] > rm MAC.270.1001189641
ORA-15032: not all alterations performed

ORA-15028: ASM file '+DATA/DB11G1/DATAFILE/MAC.270.1001189641' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

Try to drop forcefully;

ASMCMD [+DATA/DB11G1/DATAFILE] > rm -f MAC.270.1001189641
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/DB11G1/DATAFILE/MAC.270.1001189641' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

Not allowing to drop the datafile since using it currently.
We have to bring down the DB in order to drop the datafile.

Shutdown the DB now

[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 20:28:50 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Now try to drop the datafile manually

[grid@db11g ~]$asmcmd -p

ASMCMD [+] > cd /DATA/DB11G1/DATAFILE

ASMCMD [+DATA/DB11G1/DATAFILE] > ls
EXAMPLE.265.997039919
MAC.270.1001189641
SYSAUX.257.997039651
SYSTEM.256.997039649
UNDOTBS1.258.997039651
USERS.259.997039651

ASMCMD [+DATA/DB11G1/DATAFILE] > rm MAC.270.1001189641

Datafile drop succeeded!!
Now try to startup the DB

SQL> startup;
ORACLE instance started.
Total System Global Area 2471931904 bytes
Fixed Size                  2255752 bytes
Variable Size            1442841720 bytes
Database Buffers         1006632960 bytes
Redo Buffers               20201472 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+DATA/db11g1/datafile/mac.270.1001189641'   -> Data file is missing when trying to open the database

Gives the error datafile 6 is missing,so we have to restore and recover the datafile 6.

Restore and recover the missing datafile

[oracle@db11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 25 20:31:59 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G1 (DBID=1931916594, not open)

RMAN> restore datafile 6;
Starting restore at 25-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
creating datafile file number=6 name=+DATA/db11g1/datafile/mac.270.1001189641
restore not done; all files read only, offline, or already restored
Finished restore at 25-FEB-19

RMAN> recover datafile 6;
Starting recover at 25-FEB-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-FEB-19

Now try to start the DB, since the DB is in mount mode I'll open the database now

[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 20:32:49 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter database open read write;
Database altered.
SQL> select count(*) from MAC.MAC_SAMPLE;
  COUNT(*)
----------
         1

Database successfully opened in read write mode and data is accessible now.