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: {=suggested | filename | AUTO | CANCEL}

/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备份也是不可少的

精彩文章

评论可见,请评论后查看内容,谢谢!!!
 您阅读本篇文章共花了: