How to recover a corrupted/damaged/lost NONSYSTEM datafile (Database in ARCHIVELOG mode)

The following scenario describes what happens when a nonsystem datfile is lost. Before proceeding be sure to have a complete backup of your database and be sure your database in ARCHIVELOG mode.

SQL> show parameter instance_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      test

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
SQL>

Connect to RMAN to check information about datafiles

[oracle@alpha datafile]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 13 21:02:30 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST (DBID=2300947395)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3    610      SYSAUX               NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4    60       UNDOTBS1             YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5    10       EXAMPLE              NO      /u01/app/oracle/oradata/TEST/datafile/example.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
RMAN>

Query the dictionary tables and see in which datafiles some tables are located.

SQL> col owner format a15
SQL> col table_name format a40
SQL> /
OWNER           TABLE_NAME                               TABLESPACE_NAME
--------------- ---------------------------------------- ------------------------------
SCOTT           DEPT                                     USERS
SCOTT           EMP                                      USERS
SCOTT           BONUS                                    USERS
SCOTT           SALGRADE                                 USERS

SQL> select count(*) from scott.dept;
  COUNT(*)
----------
         4
SQL> select count(*) from scott.emp;
  COUNT(*)
----------
        14

What does it happens when I delete the datafile where USERS tablespace is based on.

[oracle@alpha datafile]$ ls -lrt
total 1523444
-rw-r----- 1 oracle oinstall   5251072 Mar 13 13:46 o1_mf_users_g8lf33c1_.dbf
-rw-r----- 1 oracle oinstall  10493952 Mar 13 13:46 example.dbf.back
-rw-r----- 1 oracle oinstall  10493952 Mar 13 15:31 example.dbf
-rw-r----- 1 oracle oinstall  62922752 Mar 13 19:31 o1_mf_temp_g8lf4w5d_.tmp
-rw-r----- 1 oracle oinstall  62922752 Mar 13 21:05 o1_mf_undotbs1_g8lf34lb_.dbf
-rw-r----- 1 oracle oinstall 817897472 Mar 13 21:05 o1_mf_system_g8lmhryv_.dbf
-rw-r----- 1 oracle oinstall 639639552 Mar 13 21:06 o1_mf_sysaux_g8lf08dm_.dbf
[oracle@alpha datafile]$ mv o1_mf_users_g8lf33c1_.dbf o1_mf_users_g8lf33c1_.dbf.corrupt

I'm still able to query the dictionary tables for example but...

SQL> select count(*) from dba_tables;
  COUNT(*)
----------

      2343

I obtain an error when I try to select some rows from the SCOTT.EMP table

select count(*) from scott.emp;

ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the USERS tablespace. RMAN says its size is 0.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3    610      SYSAUX               NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4    60       UNDOTBS1             YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5    10       EXAMPLE              NO      /u01/app/oracle/oradata/TEST/datafile/example.dbf
6    0        USERS                NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp

Because the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> restore datafile 6;

Starting restore at 13-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp tag=TAG20190313T134524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-19

RMAN> recover datafile 6;

Starting recover at 13-MAR-19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 13-MAR-19

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

You can see in alert log how the restore and recovery process proceeds

Wed Mar 13 21:19:51 2019
alter database datafile 6 offline
Completed: alter database datafile 6 offline
Wed Mar 13 21:20:06 2019
Full restore complete of datafile 6 /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf.  Elapsed time: 0:00:00
  checkpoint is 2214185
  last deallocation scn is 3
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
 datafile 4
Wed Mar 13 21:20:22 2019
Media Recovery Start
Wed Mar 13 21:20:22 2019
Serial Media Recovery started
Wed Mar 13 21:20:22 2019
Media Recovery failed with error 1124
ORA-283 signalled during: alter database recover if needed
 datafile 4
...
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
 datafile 6
Wed Mar 13 21:20:33 2019
Media Recovery Start
Wed Mar 13 21:20:33 2019
Serial Media Recovery started
Wed Mar 13 21:20:33 2019
Recovery of Online Redo Log: Thread 1 Group 3 Seq 12 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_3_g8lf4pjj_.log
  Mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_g8lf4plq_.log
Wed Mar 13 21:20:33 2019
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_1_g8lf4oc2_.log
  Mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_1_g8lf4ofn_.log
Wed Mar 13 21:20:33 2019
Media Recovery Complete (test)
Completed: alter database recover if needed
 datafile 6
alter database datafile 6 online
Completed: alter database datafile 6 online
Now I am able to query SCOTT.EMP table

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

  COUNT(*)
----------
        14

We can see recovered file

[oracle@alpha datafile]$ ls -lrt *user*.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 13 21:21 o1_mf_users_g8mglp3n_.dbf
Check the size of USERS tablesspace in RMAN

RMAN> report schema;

Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3    610      SYSAUX               NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4    60       UNDOTBS1             YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5    10       EXAMPLE              NO      /u01/app/oracle/oradata/TEST/datafile/example.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp