RMAN restoring datafile out of ASM Why is RMAN restoring the datafiles out of ASM and how to restore a backup into ASM?

Here is one output. The tablespace triscaltb was restored in /u01/app/oracle/product/12.1.0/dbhome_1/dbs/MISSING00013

RMAN&gt; report schema;

Report of database schema for database with db_unique_name TRISCALDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               YES     +DATA/TRISCALDB/DATAFILE/system.258.1016190249
3    600      SYSAUX               NO      +DATA/TRISCALDB/DATAFILE/sysaux.257.1016190057
4    135      UNDOTBS1             YES     +DATA/TRISCALDB/DATAFILE/undotbs1.260.1016190491
5    250      PDB$SEED:SYSTEM      NO      +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.267.1016191021
6    5        USERS                NO      +DATA/TRISCALDB/DATAFILE/users.259.1016190489
7    510      PDB$SEED:SYSAUX      NO      +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.266.1016191021
8    260      TRISCALPDB:SYSTEM    NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.1016192445
9    520      TRISCALPDB:SYSAUX    NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.1016192441
10   5        TRISCALPDB:USERS     NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.1016192445
11   1243     TRISCALPDB:EXAMPLE   NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.1016192435
13   0        TRISCALTB            NO      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/MISSING00013

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       +DATA/TRISCALDB/TEMPFILE/temp.265.1016190837
2    20       PDB$SEED:TEMP        32767       +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012019-08-13_11-21-09-am.dbf
3    20       TRISCALPDB:TEMP      32767       +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/triscalpdb_temp012019-08-13_11-47-56-am.dbf
Imagine the following.

On 2019-08-19 18:00:00 you started a full online database backup that took 5 minutes. This backup included all the datafiles listed above.

Backuppieces created from this operation:


arch1.bkp - backup of archivelogs that existed before starting the full database backup + the last archivelog that was created as a result of the implicit logswitch
db1.bkp - backup of datafiles
arch2.bkp - backup of archivelogs that were created during the full database backup + the last archivelog that was created as a result of the implicit logswitch
c1.bkp - controlfile autobackup created at the end of the backup process


Then at 2019-08-19 18:10:00 someone dropped the TRISCTALTB tablespace.
At 2019-08-19 18:15:00 a scheduled archivelog backup was started, which took 1 minute, and created the following backuppieces:


arch3.bkp - backup of archivelogs created since the last backup + the last archivelog that was created as a result of the implicit logswitch
c2.bkp - controlfile autobackup created at the end of the backup process


At 2019-08-19 18:20:00 you realize that TRISCALTB tablespace is missing. You check the alert log, and see the tablespace was dropped at 2019-08-19 18:10:00. You decide to do an incomplete database recovery and specify the point of time as 2019-08-19 18:09:59, right before the  tablespace was dropped.

You then stop the instance, delete all files, check the backups, start the instance in nomount mode, and restore the controlfile from the latest controlfile backup: c2.bkp. The problem is, that controlfile backup was taken after the TRISCALTB was dropped, so there is no datafile 13 in the list of datafiles.

You perform the restore and recovery until 2019-08-19 18:09:59. This restores all datafiles except datafile 13 from db1.bkp, restore and applies the archivelogs from arch2.bkp and arch3.bkp. When the recovery is complete, you then open the database with alter database open resetlogs;.

That is the point where such datafile entries, with MISSING in their name are created. Datafile 13 does not exist in the restored controlfile. Datafile 13 exists in the dictionary in SYSTEM tablespace at the point of time it was restored and recovered to. When you open the database, it recognizes the mismatch between the controlfile and the dictionary, and creates an offline datafile entry in the controlfile with the name of MISSING and the datafile number (00013) under the default location ($ORACLE_HOME/dbs). No, RMAN did not restore datafile 13 at all, it is not in the filesystem, datafile 13 is just a controlfile entry at this point.

Even though you had no errors during the restore and recovery, it is not yet complete. If you see these MISSING datafiles, you need to restore and recover them.

At this point you can do the following in RMAN:

Restore the datafile:

run
{
  set newname for datafile 13 to '+DATA';
  restore datafile 13;
}


Switch to the restored copy:

switch datafile 13 to copy;


Recover the restored datafile:

recover datafile 13;


Finally bring it online:

alter database datafile 13 online;