写在前面，手工建一个表空间，然后添加数据，后删除.dbf文件，进行advise failure恢复

I 准备

1 创建表空间

SQL> create tablespace myjia datafile '/home/oracle/cs/myjia01.dbf' size 100m;

Tablespace created.

2 创建表

SQL> create table lxtab1 tablespace myjia as select * from scott.emp;

Table created.

3 验证

SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where TABLE_NAME='LXTAB1';

TABLE_NAME               TABLESPACE_NAME              STATUS
------------------------------ ------------------------------ --------
LXTAB1                   MYJIA                  VALID

4 去底层删除myjia01.dbf

[oracle@jcy1 cs]$ rm myjia01.dbf

5 关库启库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size            2251856 bytes
Variable Size          163578800 bytes
Database Buffers       50331648 bytes
Redo Buffers            5169152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/cs/myjia01.dbf'

II 解决

1 已经启动到mount，控制文件没有丢，而使用advise failure 前提就必须控制文件正常

RMAN>  list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
28         HIGH     OPEN      13-FEB-20     One or more non-system datafiles are missing

RMAN> advise failure;  

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
28         HIGH     OPEN      13-FEB-20     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /home/oracle/cs/myjia01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1       Restore and recover datafile 6  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/test/test/hm/reco_457853972.hm

RMAN> Restore datafile 6 ;

Starting restore at 13-FEB-20
using channel ORA_DISK_1

creating datafile file number=6 name=/home/oracle/cs/myjia01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-FEB-20

RMAN> recover datafile 6;

Starting recover at 13-FEB-20
using channel ORA_DISK_1

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

Finished recover at 13-FEB-20

RMAN> alter database open;

database opened

总结：

RMAN> list failure;        --查看是哪个数据文件丢失

RMAN> ;         --查看建议脚本

RMAN> Restore datafile 6 ;    --根据建议脚本恢复

RMAN> recover datafile 6;

另外还有一种解决，自己去恢复丢失文件

RMAN>  repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/test/test/hm/reco_1044460011.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 6;
   recover datafile 6;
   sql 'alter database datafile 6 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 13-FEB-20
using channel ORA_DISK_1

creating datafile file number=6 name=/home/oracle/cs/myjia01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-FEB-20

Starting recover at 13-FEB-20
using channel ORA_DISK_1

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

Finished recover at 13-FEB-20

sql statement: alter database datafile 6 online
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened