删除一个数据文件，advise failure恢复

写在前面，手工建一个表空间，然后添加数据，后删除.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