redo日志文件丢失恢复
Redo日志文件分为在线Redo日志文件和归档Redo日志文件。rman只会备份归档Redo日志文件，不会备份在线Redo日志文件。 如果在线Redo日志损坏，那么需要分情况进行恢复：
第一种 ：inactive redo异常ORA-00316 ORA-00327 ，执行命令重建
ALTER DATABASE CLEAR LOGFILE GROUP 1;
操作步骤如下： 1、查看redo日志文件前状态：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ----------
1 1 1 209715200 512 1 YES INACTIVE 5238153 15-MAR-22 0
2 1 2 209715200 512 1 NO CURRENT 5348030 17-MAR-22 0
3 1 0 209715200 512 1 YES UNUSED 0 0
2、删除redo日志文件组1（模拟丢失）:
rm -f /u01/app/oracle/oradata/CDB1/redo01.log
3、用CLEAR命令重建该日志文件（mount状态下执行）
SQL>alter database clear logfile group 1;
如果是该日志组还没有归档，则需要用
SQL>alter database clear unarchived logfile group 1;
4、打开数据库，重新备份数据库
SQL>alter database open;
第二种：正常关闭数据库ACTIVE、current redo异常ORA-00316 ORA-01623
I redo日志ACTIVE状态，构造数据，切换日志：
SQL> create table test01 as select * from dba_tables;
SQL> insert into test01 value select * from dba_tables;
2180 rows created.
SQL> insert into test01 value select * from test01;
8719 rows created.
SQL> alter system switch logfile;
模拟文件丢失：删除日志组1的文件
rm -f /u01/app/oracle/oradata/CDB1/redo01.log
查看日志文件状态为ARC，表示已归档，尝试用
SQL>alter database clear logfile group 1;
SQL>alter database open;
*语句使用说明： 如果损坏的重做日志文件尚未归档，请在语句中使用 UNARCHIVED 关键字。
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
此语句清除损坏的重做日志并避免归档它们。清除的重做日志即使没有存档也可以使用 如果清除备份恢复所需的日志文件，则无法再从该备份恢复。数据库在警报日志中写入一条消息，描述您无法从中恢复的备份
II redo日志CURRENT状态，删除current redo日志文件（构造文件丢失）：
SQL> ! rm -f /u01/app/oracle/oradata/CDB1/redo02.log
startup打开数据库日志报错：
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/CDB1/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
查看日志状态：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- --
1 1 23 209715200 512 1 YES INACTIVE
3 1 24 209715200 512 1 YES INACTIVE
2 1 25 209715200 512 1 NO CURRENT
清除重建日志文件（提示需要归档）：
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance cdb1 (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/CDB1/redo02.log'
清除未归档重建日志文件
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
第三种：数据库异常关闭current/active redo异常ORA-00316 ORA-01624 ORA-01194
I 模拟active redo丢失异常关闭
删除active redo日志文件（构造文件丢失）
SQL> ! rm -f /u01/app/oracle/oradata/CDB1/redo02.log
SQL> select group#,members,sequence#,archived,status from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS
1 1 26 YES ACTIVE
2 1 27 YES ACTIVE
3 1 28 NO CURRENT
强制关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
打开数据库到mount状态，重建恢复第二组redo日志失效
SQL> startup mount;
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance cdb1 (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/CDB1/redo02.log'
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance cdb1 (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/CDB1/redo02.log'
尝试不完全恢复，输入sequence #28对应日志位置
SQL> recover database until cancel
ORA-00279: change 5471839 generated at 03/22/2022 17:23:49 needed for thread 1
ORA-00289: suggestion : /u02/oradata/CDB1/archivelog/2022_03_22/o1_mf_1_28_%u_.arc
ORA-00280: change 5471839 for thread 1 is in sequence #28
--sequence #28对应日志组3
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
1 1 26 209715200 512 1 YES ACTIVE
3 1 28 209715200 512 1 NO CURRENT
2 1 27 209715200 512 1 YES ACTIVE
Specify log: {
/u01/app/oracle/oradata/CDB1/redo03.log ---输入日志组3位置
Log applied.
Media recovery complete.
打开数据库，resetlogs重置日志
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
II 模拟current redo丢失异常关闭
慎用，不到万不得已不要开这个参数allow_resetlogs_corruption，尝试不一致情况下开启数据库
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
recover database until cancel; --此步骤输入cancel
alter database open resetlogs;
startup force mount
alter database open resetlogs;
alter system set "_allow_resetlogs_corruption"=false scope=spfile;
alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
shutdown immediate
startup
开启后，最好通过expdp进行逻辑的导出再导入
当然，rman备份也是不可少的
精彩文章
您阅读本篇文章共花了： 大家都在找：
oracle：oracle服务器是一个开放的,全面综合的数据库管理系统对吗
数据库：数据库管理系统
linux：linux操作系统
发表评论