oralce advise 使用

根据丢失数据文件场景模拟使用advise
前提：数据文件之前有相关备份
参考视图

select  *  from v$ir_failure;----------查看failure提示信息
select  * from v$ir_failure_set; ----------------查看advise_id和failure_id
select  *  from v$ir_manual_checklist;-------------查看advise消息提示
select *  from v$ir_repair;---------------查看恢复脚本

1.删除数据文件

[oracle@instance-ej1hspdt fd]$ rm -rf /opt/oracle/oradata/ORCLCDB/users01.dbf

2.查看数据库advise错误提示。

RMAN> list failure  all;                                                                                                         
Database Role: PRIMARY                                                                                                           
List of Database Failures                                                                                                        
=========================                                                                                                        
Failure ID Priority Status    Time Detected Summary                                                                              
---------- -------- --------- ------------- -------                                                                              
6502       HIGH     OPEN      11-SEP-19     One or more non-system datafiles are missing

3.生成恢复脚本

RMAN> advise failure;                                                                                                            
Database Role: PRIMARY                                                                                                           
List of Database Failures                                                                                                        
=========================                                                                                                        
Failure ID Priority Status    Time Detected Summary                                                                              
---------- -------- --------- ------------- -------                                                                              
6502       HIGH     OPEN      11-SEP-19     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=237 device type=DISK                                                                                     
analyzing automatic repair options complete                                                                                      
Mandatory Manual Actions                                                                                                         
========================                                                                                                         
no manual actions available                                                                                                      
Optional Manual Actions                                                                                                          
=======================                                                                                                          
1. If file /opt/oracle/oradata/ORCLCDB/users01.dbf was unintentionally renamed or moved, restore it                              
Automated Repair Options                                                                                                         
========================                                                                                                         
Option Repair Description                                                                                                        
------ ------------------                                                                                                        
1      Restore and recover datafile 7                                                                                            
  Strategy: The repair includes complete media recovery with no data loss                                                        
  Repair script: /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/hm/reco_122451071.hm

4.查看恢复脚本，也可以使用系统命令cat 查看

RMAN> repair failure preview;                                                                                                    
Strategy: The repair includes complete media recovery with no data loss                                                          
Repair script: /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/hm/reco_122451071.hm                                                       
contents of repair script:                                                                                                       
   # restore and recover datafile                                                                                                
   sql 'alter database datafile 7 offline';                                                                                      
   restore ( datafile 7 );                                                                                                       
   recover datafile 7;                                                                                                           
   sql 'alter database datafile 7 online';

5.执行repair failure恢复,或者手动执行相关命令

RMAN> repair failure;                                                                                                            
Strategy: The repair includes complete media recovery with no data loss                                                          
Repair script: /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/hm/reco_122451071.hm                                                       
contents of repair script:                                                                                                       
   # restore and recover datafile                                                                                                
   sql 'alter database datafile 7 offline';                                                                                      
   restore ( datafile 7 );                                                                                                       
   recover datafile 7;                                                                                                           
   sql 'alter database datafile 7 online';                                                                                       
Do you really want to execute the above repair (enter YES or NO)? yes                                                            
executing repair script                                                                                                          
sql statement: alter database datafile 7 offline   
..............................
..............................
starting media recovery                                                                                                          
archived log for thread 1 with sequence 45 is already on disk as file /opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_1
0/o1_mf_1_45_gqgo6kjt_.arc                                                                                                       
archived log for thread 1 with sequence 46 is already on disk as file /opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_1
0/o1_mf_1_46_gqhc42r5_.arc                                                                                                       
archived log for thread 1 with sequence 47 is already on disk as file /opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_1
1/o1_mf_1_47_gqhlcy50_.arc                                                                                                       
archived log for thread 1 with sequence 48 is already on disk as file /opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_1
1/o1_mf_1_48_gqhq2ps9_.arc                                                                                                       
archived log file name=/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_10/o1_mf_1_45_gqgo6kjt_.arc thread=1 sequence=45
archived log file name=/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_10/o1_mf_1_46_gqhc42r5_.arc thread=1 sequence=46
media recovery complete, elapsed time: 00:00:06                                                                                  
Finished recover at 11-SEP-19                                                                                                    
sql statement: alter database datafile 7 online                                                                                  
repair failure complete