Oracle使用RMAN Advisor恢复current redo丢失故障

最新推荐文章于 2025-11-24 10:23:46 发布

原创最新推荐文章于 2025-11-24 10:23:46 发布 · 377 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

在这里插入图片描述
本文主要介绍通过RMAN Advisor恢复current redo丢失的故障。

环境说明：

DB:Oracle 11.2.0.4.0
OS:Red Hat Enterprise Linux Server release 7.5 (Maipo)

准备环境：

查看归档

SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     12
Current log sequence       14

启动归档

[root@cjc-db-01 ~]# mkdir /arch
[root@cjc-db-01 ~]# chown oracle:oinstall /arch
sqlplus / as sysdba
alter system set log_archive_dest_1='location=/arch';
alter system set log_archive_format = "cjc_%t_%s_%r.arc" scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
alter system switch logfile;

创建测试数据

SQL> conn cjc/***
SQL>
create table t1(id number,time varchar2(100));
insert into t1 values (1, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
insert into t1 values (2, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
insert into t1 values (3, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
commit;
col time for a25
select * from t1;
ID TIME
---------- -------------------------
 1 2024-03-28 09:24:31
 2 2024-03-28 09:24:39
 3 2024-03-28 09:24:45

执行rman全备份

rman target /
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level = 0 format '/back/rman/rman_level0_%d_%T_%U.bak' database;
sql 'alter system archive log current';
backup archivelog all format '/back/rman/rman_arch_%d_%T_%U.bak';
backup current controlfile format '/back/rman/rman_con_%d_%T_%U.bak';
release channel c1;
release channel c2;
}

备份后插入新数据

insert into t1 values (4, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
insert into t1 values (5, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
commit;

查看redo信息

SQL> col member for a50
select a.GROUP#,a.STATUS,b.member from v$log a,v$logfile b where a.group#=b.group#;SQL> 
    GROUP# STATUS    MEMBER
---------- ---------------- --------------------------------------------------
 3 CURRENT    /oracle/app/oracle/oradata/cjc/redo03.log
 2 INACTIVE    /oracle/app/oracle/oradata/cjc/redo02.log
 1 INACTIVE    /oracle/app/oracle/oradata/cjc/redo01.log

重命名current redo logfile，模拟故障

[oracle@cjc-db-01 cjc]$ cd /oracle/app/oracle/oradata/cjc
[oracle@cjc-db-01 cjc]$ mv redo03.log redo03.log.1

继续插入数据，仍可以正常提交事物

insert into t1 values (6, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
insert into t1 values (7, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
commit;

手动杀掉pmon进程，模拟异常宕机

[oracle@cjc-db-01 cjc]$ ps -ef|grep pmon
oracle   17623     1  0 12:27 ?        00:00:00 ora_pmon_cjc
oracle   18287 11168  0 12:35 pts/3    00:00:00 grep --color=auto pmon
[oracle@cjc-db-01 cjc]$ kill -9 17623

重新启动数据库，启动失败

SQL> startup
ORACLE instance started.

Total System Global Area 2071076864 bytes
Fixed Size    2254784 bytes
Variable Size  536873024 bytes
Database Buffers 1526726656 bytes
Redo Buffers    5222400 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/app/oracle/oradata/cjc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

查看丢失的是current redo logfile

SQL> col member for a50
select a.GROUP#,a.STATUS,b.member from v$log a,v$logfile b where a.group#=b.group#;SQL> 
    GROUP# STATUS    MEMBER
---------- ---------------- --------------------------------------------------
 1 INACTIVE    /oracle/app/oracle/oradata/cjc/redo01.log
 3 CURRENT    /oracle/app/oracle/oradata/cjc/redo03.log
 2 INACTIVE    /oracle/app/oracle/oradata/cjc/redo02.log

GROUP# 3丢失，理论上最多能恢复到 1187045，也就是GROUP# 2的NEXT_CHANGE#

SQL> select GROUP#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log order by 1;

    GROUP#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
 1    7    1186324 1187037
 2    8    1187037 1187045
 3    9    1187045   2.8147E+14

使用RMAN Advisor工具进行恢复

查看故障，检测到Redo log group 3丢失

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1483       CRITICAL OPEN      28-MAR-24     Redo log group 3 is unavailable
1486       HIGH     OPEN      28-MAR-24     Redo log file /oracle/app/oracle/oradata/cjc/redo03.log is missing

查看建议，生成Repair script

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1483       CRITICAL OPEN      28-MAR-24     Redo log group 3 is unavailable
1486       HIGH     OPEN      28-MAR-24     Redo log file /oracle/app/oracle/oradata/cjc/redo03.log is missing

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

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /oracle/app/oracle/oradata/cjc/redo03.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform incomplete database recovery to SCN 1187045  
  Strategy: The repair includes point-in-time recovery with some data loss
  Repair script: /oracle/app/oracle/diag/rdbms/cjc/cjc/hm/reco_3624337172.hm

查看Repair script，需要执行不完全恢复，scn 1187045,和之前查的一致

[oracle@cjc-db-01 cjc]$ cat /oracle/app/oracle/diag/rdbms/cjc/cjc/hm/reco_3624337172.hm
   # database point-in-time recovery
   restore database until scn 1187045;
   recover database until scn 1187045;
   alter database open resetlogs;

根据建议执行恢复

可以手动恢复，也可以执行repair failure进行恢复

RMAN> restore database until scn 1187045;
RMAN> recover database until scn 1187045;
RMAN> alter database open resetlogs;

查询数据

SQL> col time for a50
SQL> select * from cjc.t1;

ID TIME
---------- -----------------
 1 2024-03-28 12:30:14
 2 2024-03-28 12:30:14
 3 2024-03-28 12:30:14