1 、 背景介绍
Redo 日志是 Oracle 数据库至关重要的组成部分，用于记录数据库中所有数据的变更操作。它保证了数据库的事务一致性和可恢复性，在数据库发生故障时，通过重做 Redo 日志中记录的操作，能够将数据库恢复到故障前的状态。然而，在实际运维过程中，可能会因误操作导致 Redo 日志损坏或被删除。本文将主要介绍在 归档模式下 Redo 日志的不同状态（ inactive 、 active 、 current ），详细介绍相应的恢复步骤和技术要点，以便在遇到此类问题时能够快速、有效地进行恢复。
2 、 Online Redo Inactive 场景
当 Redo 日志处于 inactive 状态时，意味着该日志组已不再被当前事务使用，其内容已被归档（在归档模式下）。此时如果该 Redo 日志组损坏或被删除，由于其内容已被安全归档，理论上不会影响数据库的正常运行和数据一致性，但为了避免后续可能出现的问题，仍需进行相应处理。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 109 52428800 512 1 YES INACTIVE 5085400 24-JUN-20 5105406 24-JUN-20
2 1 110 52428800 512 1 YES INACTIVE 5105406 24-JUN-20 5125412 24-JUN-20
3 1 111 52428800 512 1 NO CURRENT 5125412 24-JUN-20 2.8147E+14
SQL> select * from v$logfile where group#=2;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ---------------------------------------------------------------------
2 ONLINE /u01/app/oradata/localdb/redo02.log NO
[root@rac01 ~]# dd if=/dev/null of=/u01/app/oradata/localdb/redo02.log bs=512 count=30
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.000209111 s, 0.0 kB/s
可以正常关闭
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
开启报错
SQL> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 264243224 bytes
Database Buffers 251658240 bytes
Redo Buffers 3780608 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 13719
Session ID: 1 Serial number: 5
alert.log
ARC0: STARTING ARCH PROCESSES
Errors in file /u01/app/diag/rdbms/localdb/localdb/trace/localdb_lgwr_13817.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/localdb/redo02.log'
ORA-27047: unable to read the header block of file —redo group 2 的文件头部损坏
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
Errors in file /u01/app/diag/rdbms/localdb/localdb/trace/localdb_lgwr_13817.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/localdb/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
Errors in file /u01/app/diag/rdbms/localdb/localdb/trace/localdb_ora_13829.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/u01/app/oradata/localdb/redo02.log'
Thu Jun 25 00:53:01 2020
ARC1 started with pid=24, OS id=13855
USER (ospid: 13829): terminating the instance due to error 313
System state dump requested by (instance=1, osid=13829), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/diag/rdbms/localdb/localdb/trace/localdb_diag_13797_20200625005301.trc
Dumping diagnostic data in directory=[cdmp_20200625005301], requested by (instance=1, osid=13829), summary=[abnormal instance termination].
Instance terminated by USER, pid = 13829
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 264243224 bytes
Database Buffers 251658240 bytes
Redo Buffers 3780608 bytes
Database mounted.
SQL>
SQL> set linesize 400
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 109 52428800 512 1 YES INACTIVE 5085400 24-JUN-20 5105406 24-JUN-20
3 1 111 52428800 512 1 NO CURRENT 5125412 24-JUN-20 2.8147E+14
2 1 110 52428800 512 1 YES INACTIVE 5105406 24-JUN-20 5125412 24-JUN-20
inactive 的 redo 损坏
SQL> alter database clear logfile group 2;
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 ('/u01/app/oradata/localdb/redo02.log') size 50M reuse;
SQL> alter database open;
3 、 Online Redo Active 场景
处于 active 状态的 Redo 日志组正在被数据库用于记录当前事务的变更操作，或者虽然事务已提交，但该日志组中的数据尚未完成写入数据文件和归档。此时如果 Redo 日志组损坏或被删除，会导致数据库无法正常进行事务提交和恢复操作，严重影响数据库的可用性和数据一致性。
active redo 丢失模拟实验：
session 1:
begin
for i in 1 .. 5000000 loop
insert into C##MARY.test values(i);
commit;
end loop;
end;
/
session 2:
切几个归档，三个 redo 中有两个状态是 active 、一个是 current
shutdown abort
startup mount
select * from v$log;
select * from v$logfile;
两个 active redo 均已归档
破坏一组 active redo
（一个组两个 member ）
dd if=/dev/null of=/u01/app/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_3_hch7ohvf_.log bs=512 count=30
dd if=/dev/null of=/u01/app/oradata/ORCL/onlinelog/o1_mf_3_hch7ohln_.log bs=512 count=30
shutdown abort
startup 报错信息如下：
alert 日志：
破坏的是 3 号组， seq# 是 3
create pfile='/tmp/pfile.ora' from spfile;
vi /tmp/pfile.ora
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true
这两个参数是 scn 不一致时使用
SQL> startup mount; -- 没加参数，使用默认的spfile 启动
SQL> recover database until cancel;
auto
SQL> alter database open noresetlogs;
报错，还是 redo 3 无法打开，因为 redo 3 的状态是 active ，实例恢复会用到，虽然 redo 3 的归档已经被恢复了，但还是不行，没有办法将 redo 3 的状态由 active 变为 inactive 。数据库在 open 时会自动实例恢复，还是会找 redo 3 ，想通过 redo 3 来前滚。
SQL> startup mount pfile='/tmp/pfile.ora'; -- 使用加了参数的pfile 启动
SQL> recover database until cancel;
auto
SQL> alter database open resetlogs;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 22406
Session ID: 1 Serial number: 30938
重启数据库
SQL> startup mount pfile='/tmp/pfile.ora';
控制文件检查点
select file#,checkpoint_change# from v$datafile;
数据文件检查点
select file#,checkpoint_change# from v$datafile_header;
日志文件检查点
select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
再次以原有的参数文件重启数据库即可。
4 、 Online Redo Current 场景
current 状态的 Redo 日志组是当前正在写入数据的日志组，一旦损坏或被删除，会直接导致数据库无法继续处理事务，数据库将处于挂起状态，严重威胁数据的完整性和一致性。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 113 52428800 512 1 YES INACTIVE 5135415 25-JUN-20 5135418 25-JUN-20
2 1 115 52428800 512 1 NO CURRENT 5135421 25-JUN-20 2.8147E+14
3 1 114 52428800 512 1 YES INACTIVE 5135418 25-JUN-20 5135421 25-JUN-20
session 1:
begin
for i in 1 .. 5000000 loop
insert into C##MARY.test values(i,i);
commit;
end loop;
end;
/
session 2:
alter system switch logfile;
/
/
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 116 52428800 512 1 YES ACTIVE 5171733 25-JUN-20 5188303 25-JUN-20
2 1 115 52428800 512 1 YES ACTIVE 5135421 25-JUN-20 5171733 25-JUN-20
3 1 117 52428800 512 1 NO CURRENT 5188303 25-JUN-20 2.8147E+14
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 264243224 bytes
Database Buffers 251658240 bytes
Redo Buffers 3780608 bytes
Database mounted.
SQL> set linesize 300
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 116 52428800 512 1 YES ACTIVE 5171733 25-JUN-20 5188303 25-JUN-20
3 1 117 52428800 512 1 YES ACTIVE 5188303 25-JUN-20 5237672 25-JUN-20
2 1 118 52428800 512 1 NO CURRENT 5237672 25-JUN-20 2.8147E+14
SQL> select * from v$logfile where group#=2;
/u01/app/oradata/localdb/redo02.log
[root@rac01 ~]# dd if=/dev/null of=/u01/app/oradata/localdb/redo02.log bs=512 count=30
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.000131183 s, 0.0 kB/s
SQL> alter database open;
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/localdb/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
alter.log:
Errors in file /u01/app/diag/rdbms/localdb/localdb/trace/localdb_ora_6637.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/localdb/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
Aborting crash recovery due to error 313
SQL> create pfile ='/tmp/pfile.ora' from spfile;
File created.
vi /tmp/pfile.ora
添加如下内容：
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true
SQL> startup mount pfile='/tmp/pfile.ora'
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 264243224 bytes
Database Buffers 251658240 bytes
Redo Buffers 3780608 bytes
Database mounted.
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5171733
2 5171733
3 5171733
4 5171733
5 5171733
6 5171733
7 5171733
8 5171733
9 5171733
9 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5171733
2 5171733
3 5171733
4 5171733
5 5171733
6 5171733
7 5171733
8 5171733
9 5171733
9 rows selected.
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
116 5171733 5188303
117 5188303 5237672
118 5237672 2.8147E+14
这个明显是 current redo ，因为 next_change# 是无穷大， current redo 的 SEQ# 是 118
SQL> recover database until cancel;
ORA-00279: change 5171733 generated at 06/25/2020 01:48:17 needed for thread 1
ORA-00289: suggestion : /u01/app/arch/1_116_1043809531.dbf
ORA-00280: change 5171733 for thread 1 is in sequence #116
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
要恢复 116 ，这个归档有， 回车
ORA-00279: change 5188303 generated at 06/25/2020 01:48:25 needed for thread 1
ORA-00289: suggestion : /u01/app/arch/1_117_1043809531.dbf
ORA-00280: change 5188303 for thread 1 is in sequence #117
ORA-00278: log file '/u01/app/arch/1_116_1043809531.dbf' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
要恢复 117 ，这个归档有， 回车
ORA-00279: change 5237672 generated at 06/25/2020 01:49:23 needed for thread 1
ORA-00289: suggestion : /u01/app/arch/1_118_1043809531.dbf
ORA-00280: change 5237672 for thread 1 is in sequence #118
ORA-00278: log file '/u01/app/arch/1_117_1043809531.dbf' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
要恢复 118 ，这个归档没有，而且 current redo 已经丢失，输入 cancel ，恢复到此结束
cancel
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: '/u01/app/oradata/localdb/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
此时 resetlogs 打开时有可能会报 ORA-600 [2662] 。
如果报出 ORA-600 [2662] ，需要手工推进 scn
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30310891/viewspace-3110297/ ，如需转载，请注明出处，否则将追究法律责任。
