归档模式redo故障的恢复实验

数据库是归档模式

[oracle@ora11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 9 10:39:20 2021

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set line 200
SQL> set line 233;
SQL> col member for a60;
SQL> SELECT a.group#,a.MEMBER,b.thread#,b.status,b.bytes / 1024 / 1024 MB,b.ARCHIVED,b.sequence# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY a.group#;

GROUP# MEMBER THREAD# STATUS MB ARCHIV SEQUENCE#
---------- ------------------------------------------------------------ ---------- -------------------------------- ---------- ------ ----------
1 /u01/app/oradata/SCPRD/redo1.log 1 CURRENT 512 NO 19
2 /u01/app/oradata/SCPRD/redo2.log 1 INACTIVE 512 YES 17
3 /u01/app/oradata/SCPRD/redo3.log 1 INACTIVE 512 YES 18

在另一个终端中破坏redo2.log

[oracle@ora11g SCPRD]$ echo > redo2.log

然后切一下日志，可以正常切换。

SQL> alter system switch logfile;

System altered.

SQL> set line 233;
SQL> col member for a60;
SQL> SELECT a.group#,a.MEMBER,b.thread#,b.status,b.bytes / 1024 / 1024 MB,b.ARCHIVED,b.sequence# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY a.group#;

GROUP# MEMBER THREAD# STATUS MB ARCHIV SEQUENCE#
---------- ------------------------------------------------------------ ---------- -------------------------------- ---------- ------ ----------
1 /u01/app/oradata/SCPRD/redo1.log 1 ACTIVE 512 YES 19
2 /u01/app/oradata/SCPRD/redo2.log 1 CURRENT 512 NO 20
3 /u01/app/oradata/SCPRD/redo3.log 1 INACTIVE 512 YES 18

此时在日志里里已有大量的报错了

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/SCPRD/redo2.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 12
Errors in file /u01/app/oracle/diag/rdbms/scprd/SCPRD/trace/SCPRD_arc1_9544.trc:

再删除一点数据，并将redo3.log删除。

SQL> delete from t1 where rownum<11

10 rows deleted.

SQL> select count(*)from t1;

COUNT(*)
----------
24

SQL> commit;

Commit complete.

在另一终端中删除redo3

[oracle@ora11g SCPRD]$ rm -f redo3.log

切换日志

SQL> alter system switch logfile;

System altered.

SQL> select count(*)from t1;

COUNT(*)
----------
24

SQL> delete from t1 where rownum<11;

10 rows deleted.

SQL> commit;

Commit complete.

GROUP# MEMBER THREAD# STATUS MB ARCHIV SEQUENCE#
---------- ------------------------------------------------------------ ---------- -------------------------------- ---------- ------ ----------
1 /u01/app/oradata/SCPRD/redo1.log 1 ACTIVE 512 YES 19
2 /u01/app/oradata/SCPRD/redo2.log 1 ACTIVE 512 NO 20
3 /u01/app/oradata/SCPRD/redo3.log 1 CURRENT 512 NO 21

日志还是正常的切换了。日志中同样出现了报错。

ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oradata/SCPRD/redo3.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

用rman的advise来看看

RMAN> LIST FAILURE;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2322 CRITICAL OPEN 09-FEB-21 Redo log group 3 is unavailable
2316 CRITICAL OPEN 09-FEB-21 Redo log group 2 is unavailable
2325 HIGH OPEN 09-FEB-21 Redo log file /u01/app/oradata/SCPRD/redo3.log is missing
2319 HIGH OPEN 09-FEB-21 Redo log file /u01/app/oradata/SCPRD/redo2.log is corrupt

RMAN> ADVISE FAILURE;

List of Database Failures
=========================

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /u01/app/oradata/SCPRD/redo3.log was unintentionally renamed or moved, restore it
2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Checkpoint database and clear unarchived redo log group 2
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/scprd/SCPRD/hm/reco_1733228851.hm

检查到了问题，来看看给出的建议是什么。

[oracle@ora11g SCPRD]$ cat /u01/app/oracle/diag/rdbms/scprd/SCPRD/hm/reco_1733228851.hm
# checkpoint & clear unarchived log group
sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/scprd/SCPRD/hm/reco_1317868975.hm'' ); end;";
[oracle@ora11g SCPRD]$ cat /u01/app/oracle/diag/rdbms/scprd/SCPRD/hm/reco_1317868975.hm
begin
/*Force Checkpoint*/
execute immediate 'ALTER SYSTEM CHECKPOINT';
/*Clear the unarchived Log Group*/
execute immediate 'ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2';
end;

给出的是先执行检查点，再clear redo2.log。

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance SCPRD (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oradata/SCPRD/redo3.log'

清除redo3时报错，因为是CURRENT日志。

SQL> SELECT a.group#,a.MEMBER,b.thread#,b.status,b.bytes / 1024 / 1024 MB,b.ARCHIVED,b.sequence# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY a.group#;

GROUP# MEMBER THREAD# STATUS MB ARCHIV SEQUENCE#
---------- ------------------------------------------------------------ ---------- -------------------------------- ---------- ------ ----------
1 /u01/app/oradata/SCPRD/redo1.log 1 INACTIVE 512 YES 19
2 /u01/app/oradata/SCPRD/redo2.log 1 UNUSED 512 YES 0
3 /u01/app/oradata/SCPRD/redo3.log 1 CURRENT 512 NO 21

再来看看rman。

RMAN> LIST FAILURE;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2322 CRITICAL OPEN 09-FEB-21 Redo log group 3 is unavailable
2325 HIGH OPEN 09-FEB-21 Redo log file /u01/app/oradata/SCPRD/redo3.log is missing

RMAN> ADVISE FAILURE;

List of Database Failures
=========================

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available

看来rman对于CURRENT日志丢失无能为力。只能resetlogs，查看数据文件与控制文件的scn是否一致。

SQL> select file#,checkpoint_change#,fuzzy from v$datafile_header;

FILE# CHECKPOINT_CHANGE# FUZZY
---------- ------------------ ------
1 2150552 YES
2 2150552 YES
3 2150552 YES
4 2150552 YES
5 2150552 YES
6 2150552 YES
7 2150552 YES
8 2150552 YES
9 2150552 YES

9 rows selected.

SQL> select file#,checkpoint_change#,last_change# from v$datafile;

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 2150552
2 2150552
3 2150552
4 2150552
5 2150552
6 2150552
7 2150552
8 2150552
9 2150552

9 rows selected.

scn一致的情况下正常关闭数据库，再启动到mount进行恢复。

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 373293960 bytes
Database Buffers 687865856 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL> select file#,checkpoint_change#,fuzzy from v$datafile_header;

FILE# CHECKPOINT_CHANGE# FUZZY
---------- ------------------ ------
1 2150772 NO
2 2150772 NO
3 2150772 NO
4 2150772 NO
5 2150772 NO
6 2150772 NO
7 2150772 NO
8 2150772 NO
9 2150772 NO

9 rows selected.

SQL> select file#,checkpoint_change#,last_change# from v$datafile;

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 2150772 2150772
2 2150772 2150772
3 2150772 2150772
4 2150772 2150772
5 2150772 2150772
6 2150772 2150772
7 2150772 2150772
8 2150772 2150772
9 2150772 2150772

9 rows selected.

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance SCPRD (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oradata/SCPRD/redo3.log'

这里尝试一下清除redo3.log，不能清除，提示redo.log需要归档。
SQL> recover database until cancel;
Media recovery complete.
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.

SQL> select count(*) from t1;

COUNT(*)
----------
14