oracle 联机日志文件不同状态损坏丢失恢复

视图

v$log
v$v$logfile
分为两种场景恢复
1.数据实例还在运行尚未崩溃
2.数据库实例已经关闭

1.数据实例还在运行尚未崩溃

1.1inactive / unused状态下丢失redo文件 处理方式

 alter   database  clear   logfile   group   3;

1.2.active状态下丢失redo文件 处理方式

1.切换损坏redo文件到inactive
2将数据刷新到数据文件。
3.重建日志文件

alter system switch logfile;
alter  system checkpoint;
alter   database  clear   logfile   group   3;

1.3.current状态下丢失redo文件 处理方式

1.切换损坏redo文件到inactive
2将数据刷新到数据文件。
3.重建日志文件，因为系统判断之前丢失redo文件尚未归档，所以需要使用unarchived进行清除重建，重建之后归档日志变为不连续，所以需要进行全备，注意之前所有备份都不能在使用。

alter system switch logfile;
alter  system checkpoint;
SQL> alter   database  clear  unarchived  logfile   group   2;

2.数据库已经崩溃恢复

2.1 inactive / unused状态下丢失redo文件 处理方式

shutdown   abort;模拟数据库崩溃
startup mount ;
 alter   database  clear   logfile   group   3;  
 alter database   open;

2.2active状态下丢失redo文件处理方式

模拟环境
1.创建测试表

create table t as select * from  dba_objects;

2.使用视图v$log，查看redo日志组，

select  *  from v$log;

3.切换redo日志使当前 group 组 current变为active状态，

 alter system switch logfile;

4.查询当前数据总数

 select count(1) from t;
 74544

5.删除一些数据，记录删除后的数据总数。

 delete from t where rownum<1001;
 commit
 在查询数据总数
 select count(1) from t;
 72544

6.在此查看v$log，记录当前current日志组。

select  *  from v$log;

5.模拟数据库崩溃丢失active文件
1.shutdown abort;关闭数据库
2.删除组1 active redo文件

SQL> shutdown abort;                                                                                       
ORACLE instance shut down.
[oracle@instance-ej1hspdt alert]$ rm -rf /opt/oracle/oradata/ORCLCDB/redo01.log

6.尝试启动数据库

SQL> startup                                                                                               
ORACLE instance started.                                                                                   
Total System Global Area  838857584 bytes                                                                  
Fixed Size                  8901488 bytes                                                                  
Variable Size             679477248 bytes                                                                  
Database Buffers          142606336 bytes                                                                  
Redo Buffers                7872512 bytes                                                                  
Database mounted.                                                                                          
ORA-00313: open failed for members of log group 1 of thread 1                                              
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ORCLCDB/redo01.log'                                 
ORA-27037: unable to obtain file status                                                                    
Linux-x86_64 Error: 2: No such file or directory                                                           
Additional information: 7

7.使用数据库崩溃之前ative状态redo归档日志恢复
注意：根据网上教程这一步之后可以加隐藏参数，强制启动数据库，但是这样会丢失数据。
之前current日志保存的delete操作无法恢复。

SQL> recover database until cancel;                                                                        
ORA-00279: change 6998785 generated at 09/11/2019 16:36:26 needed for thread 1                             
ORA-00289: suggestion :                                                                                    
/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_11/o1_mf_1_1_gqkfp4dd_                           
.arc                                                                                                       
ORA-00280: change 6998785 for thread 1 is in sequence #1                                                   
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}                                                  
AUTO                                                                                                       
ORA-00279: change 7000664 generated at 09/11/2019 16:56:36 needed for thread 1                             
ORA-00289: suggestion :                                                                                    
/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_11/o1_mf_1_2_%u_.arc                             
ORA-00280: change 7000664 for thread 1 is in sequence #2                                                   
ORA-00278: log file                                                                                        
'/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_11/o1_mf_1_1_gqkfp4dd                           
_.arc' no longer needed for this recovery                                                                  
ORA-00308: cannot open archived log                                                                        
'/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2019_09_11/o1_mf_1_2_%u_.arc'                           
ORA-27037: unable to obtain file status                                                                    
Linux-x86_64 Error: 2: No such file or directory                                                           
Additional information: 7                                                                                  
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below                             
ORA-01194: file 1 needs more recovery to be consistent                                                     
ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'

8.最后使用数据库崩溃之前current状态redo归档日志恢复

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}                                                  
/opt/oracle/oradata/ORCLCDB/redo02.log                                                                     
Log applied.                                                                                               
Media recovery complete.

9.resetlogs打开面数据库

SQL> alter database open resetlogs;                                                                        
Database altered.

10.查看数据是否完全恢复为数据库崩溃前状态
记录为73544，说明在数据库崩溃之前，至此恢复完毕。

SQL>  select count(1) from t;                                                                              
  COUNT(1)                                                                                                 
----------                                                                                                 
     73544