RMAN备份与恢复系列之redo日志文件恢复

最新推荐文章于 2022-06-15 15:31:45 发布
最新推荐文章于 2022-06-15 15:31:45 发布
阅读量3.6k 收藏 5
点赞数
分类专栏： OCM Training RMAN备份与恢复 文章标签： 数据库 oracle rman
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhuke0203/article/details/71311993
版权

实验环境

  • 操作系统 Redhat5.4 x86
  • 数据库版本 oracle 11gR2 (11.2.0.1.0)
  • 实验前已经做了RMAN全量备份包括controlfile、spfile

实验模拟

1. INACTIVE日志组部分member成员损坏或丢失

案例模拟

模拟group 4 中member disk4b.log文件丢失：

SQL> select group#, sequence#,bytes/1024/1024M, members, status from v$log;

    GROUP#  SEQUENCE#          M    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
     1     11        200      2 INACTIVE
     2     12        200      2 INACTIVE
     3     13        200      2 INACTIVE
     4     14        200      2 INACTIVE
     5     15        200      2 CURRENT

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- -------------------------------------------------- ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log      NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log      NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log      NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log      NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log      NO

10 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk5/disk4b.log

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk5/disk4b.log
ls: /u01/app/oracle/oradata/PROD/disk5/disk4b.log: No such file or directory
案例恢复

首先，在不重启数据库情况下，直接利用rman进行恢复：

[oracle@node1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 11:57:10 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597)

RMAN> list failure;

using target database control file instead of recovery catalog
no failures found that match specification
RMAN> exit

Recovery Manager complete.

在不重启数据库的情况下，暂时检查不到文件丢失，
重启数据库以后，再次尝试：

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

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         679480996 bytes
Database Buffers      150994944 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--数据库可以正常启动，alert日志中会有报错提示信息。

[oracle@node1 dbs]$ rman target / 

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 11:58:39 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597)

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2176       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk5/disk4b.log is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2176       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk5/disk4b.log is missing

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

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk5/disk4b.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Drop and re-create redo log group member /u01/app/oracle/oradata/PROD/disk5/disk4b.log  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3272967096.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3272967096.hm

contents of repair script:
   # drop/recreate redo log group member
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_162888668.hm'' ); end;";

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_162888668.hm'' ); end;
repair failure complete

RMAN> exit


Recovery Manager complete.
[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 17:28:16 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk5/disk4b.log
-rw-r----- 1 oracle oinstall 209715712 May  7 17:28 /u01/app/oracle/oradata/PROD/disk5/disk4b.log

SQL> 

--通过重启数据库后，可以正常使用rman进行恢复。

RMAN中使用advise failure 生成的脚本可以进行查看具体的操作内容：

[oracle@node1 ~]$ cat /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_162888668.hm
begin
/*Drop the Log Member*/
execute immediate 'ALTER DATABASE DROP LOGFILE MEMBER ''/u01/app/oracle/oradata/PROD/disk5/disk4b.log''';
/*Add the Log Member (This will create a new file)*/
execute immediate 'ALTER DATABASE ADD LOGFILE MEMBER ''/u01/app/oracle/oradata/PROD/disk5/disk4b.log'' REUSE TO GROUP 4';
end;

因此，我们同样可以按照脚本内容手动进行恢复，inactive 日志组中redo日志文件的恢复(这种方法不需要重启数据库)：

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk5/disk4b.log';

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk5/disk4b.log' reuse to group 4;

Database altered.

SQL> !ls -ls /u01/app/oracle/oradata/PROD/disk5/disk4b.log
205008 -rw-r----- 1 oracle oinstall 209715712 May  7 17:49 /u01/app/oracle/oradata/PROD/disk5/disk4b.log

这种情况下的数据库可以正常启动，数据恢复不会产生数据丢失。

2. INACTIVE日志组全部的member成员都损坏或丢失

案例模拟
[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 18:15:24 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1 INVALID ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     16          2 YES INACTIVE
     2     17          2 YES INACTIVE
     3     18          2 YES INACTIVE
     4     19          2 YES INACTIVE
     5     20          2 NO  CURRENT

SQL> !rm /u01/app/oracle/oradata/PROD/disk2/disk2a.log

SQL> !rm /u01/app/oracle/oradata/PROD/disk3/disk2b.log

SQL> exit
案例恢复
[oracle@node1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 18:17:40 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597)

RMAN> list failure;

using target database control file instead of recovery catalog
no failures found that match specification
--同样需要重启数据库以后才能检测到。
RMAN> exit


Recovery Manager complete.
[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 18:17:55 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

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

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 27465
Session ID: 1 Serial number: 5


SQL> exit
--重启数据库以后发现数据库无法启动，报错。

查看alert日志发现以下报错信息：

ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Sun May 07 18:18:12 2017
ARC0 started with pid=18, OS id=27467 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sun May 07 18:18:13 2017
ARC1 started with pid=19, OS id=27469 
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_27413.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_27413.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk2b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk2/disk2a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_27413.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk2b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk2/disk2a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_27465.trc:
ORA-00313: open failed for members of log group 1 of thread 
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk2/disk2a.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk2b.log'
USER (ospid: 27465): terminating the instance due to error 313
Sun May 07 18:18:13 2017
ARC2 started with pid=20, OS id=27471 
Instance terminated by USER, pid = 27465

再次登录RMAN进行恢复：

[oracle@node1 trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 18:19:34 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     836976640 bytes

Fixed Size                     1339740 bytes
Variable Size                683675300 bytes
Database Buffers             146800640 bytes
Redo Buffers                   5160960 bytes

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2351       CRITICAL OPEN      07-MAY-17     Redo log group 2 is unavailable
2357       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk2b.log is missing
2354       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk2/disk2a.log is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2351       CRITICAL OPEN      07-MAY-17     Redo log group 2 is unavailable
2357       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk2b.log is missing
2354       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk2/disk2a.log is missing

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

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk3/disk2b.log was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD/disk2/disk2a.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Clear redo log group 2  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3329076409.hm
RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3329076409.hm

contents of repair script:
   # clear redo log group
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4284357731.hm'' ); end;";

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4284357731.hm'' ); end;
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened

RMAN>

恢复完成后，登录数据库进行检查：

[oracle@node1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 18:30:29 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> set line 180
SQL> col member for a60
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     16          2 YES INACTIVE
     2      0          2 YES UNUSED
     3     18          2 YES INACTIVE
     4     19          2 YES INACTIVE
     5     20          2 NO  CURRENT

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1 INVALID ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk2/disk2a.log
-rw-r----- 1 oracle oinstall 209715712 May  7 18:25 /u01/app/oracle/oradata/PROD/disk2/disk2a.log

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk3/disk2b.log
-rw-r----- 1 oracle oinstall 209715712 May  7 18:25 /u01/app/oracle/oradata/PROD/disk3/disk2b.log

SQL> 
--之前手动删除的两个文件已经恢复成功。

advise failure 生成的脚本内容如下：

[oracle@node1 ~]$ cat /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3329076409.hm
   # clear redo log group
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4284357731.hm'' ); end;";
[oracle@node1 ~]$ cat /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4284357731.hm
begin
/*Clear the Log Group*/
execute immediate 'ALTER DATABASE CLEAR LOGFILE GROUP 2';
end;

因此，对于invactive redo group中所有member都丢失的情况下，我们还可以通过手动执行以下命令进行修复(由于inactive redo group中的日志已经完成了checkpoint了，因此文件中的内容已经不再需要，可以直接clear了)：

ALTER DATABASE CLEAR LOGFILE GROUP 2

具体步骤如下：

--模拟文件丢失或损坏：
SQL> select * from v$logfile;
SQL> /

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1 INVALID ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1      1         16  209715200    512      2 YES INACTIVE            640479 07-MAY-17       640496 07-MAY-17
     2      1         21  209715200    512      2 NO  CURRENT             682181 07-MAY-17   2.8147E+14
     3      1         18  209715200    512      2 YES INACTIVE            640502 07-MAY-17       640514 07-MAY-17
     4      1         19  209715200    512      2 YES INACTIVE            640514 07-MAY-17       661074 07-MAY-17
     5      1         20  209715200    512      2 YES INACTIVE            661074 07-MAY-17       682181 07-MAY-17

SQL> 
SQL> !rm /u01/app/oracle/oradata/PROD/disk1/disk1a.log /u01/app/oracle/oradata/PROD/disk2/disk1b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 29227
Session ID: 1 Serial number: 5


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--alert日志报错信息如下：
ARC0: STARTING ARCH PROCESSES
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_29174.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/disk1/disk1a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_29174.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/disk1/disk1a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sun May 07 18:45:46 2017
ARC1 started with pid=19, OS id=29232 
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_29227.trc:
ORA-00313: open failed for members of log group 1 of thread 
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/disk1/disk1a.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/disk2/disk1b.log'
USER (ospid: 29227): terminating the instance due to error 313
Instance terminated by USER, pid = 29227


--手动恢复方法：
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 18:46:05 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
SQL> alter database clear logfile group 1;

Database altered.

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk2/disk1b.log
-rw-r----- 1 oracle oinstall 209715712 May  7 18:47 /u01/app/oracle/oradata/PROD/disk2/disk1b.log

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk1/disk1a.log
-rw-r----- 1 oracle oinstall 209715712 May  7 18:47 /u01/app/oracle/oradata/PROD/disk1/disk1a.log

--执行完clear logfile命令以后文件已经自动生成，下面可以直接将数据库启动到open状态。

SQL> alter database open;

Database altered.

SQL>

这种情况下，数据库无法正常启动，数据恢复不会造成数据丢失。

3. ACTIVE非CURRENT日志组的部分member成员都损坏或丢失

案例模拟
SQL> select group#, sequence#, status, archived from v$log;

    GROUP#  SEQUENCE# STATUS           ARC
---------- ---------- ---------------- ---
     1     16 INACTIVE         YES
     2     17 ACTIVE           YES
     3     18 CURRENT          NO
     4     14 INACTIVE         YES
     5     15 INACTIVE         YES

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk3/disk2b.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--数据库可以正常启动，查看alert日志存在以下报错信息：

Sun May 07 20:57:11 2017
ARC0 started with pid=18, OS id=3495 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_3433.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk2b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_3433.trc:
ORA-00321: log 2 of thread 1, cannot update log file header
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk2b.log'
Sun May 07 20:57:12 2017
ARC1 started with pid=19, OS id=3497
案例恢复
[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 20:57:49 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597)

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2945       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk2b.log is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2945       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk2b.log is missing

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

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk3/disk2b.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Drop and re-create redo log group member /u01/app/oracle/oradata/PROD/disk3/disk2b.log  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1592654887.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1592654887.hm

contents of repair script:
   # drop/recreate redo log group member
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2259100001.hm'' ); end;";

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2259100001.hm'' ); end;
repair failure complete

RMAN> exit


Recovery Manager complete.

--advise failure 生成的脚本内容：
[oracle@node1 ~]$ cat /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2259100001.hm
begin
/*Drop the Log Member*/
execute immediate 'ALTER DATABASE DROP LOGFILE MEMBER ''/u01/app/oracle/oradata/PROD/disk3/disk2b.log''';
/*Add the Log Member (This will create a new file)*/
execute immediate 'ALTER DATABASE ADD LOGFILE MEMBER ''/u01/app/oracle/oradata/PROD/disk3/disk2b.log'' REUSE TO GROUP 2';
end;

--检查恢复后的情况：
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 20:59:01 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> set line 180
SQL> col member for a60
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     16          2 YES INACTIVE
     2     17          2 YES INACTIVE
     3     18          2 YES INACTIVE
     4     19          2 YES INACTIVE
     5     20          2 NO  CURRENT

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2 INVALID ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> !ls -la /u01/app/oracle/oradata/PROD/disk3/disk2b.log
-rw-r----- 1 oracle oinstall 209715712 May  7 20:58 /u01/app/oracle/oradata/PROD/disk3/disk2b.log

SQL> 
--丢失的文件已经恢复。

这种情况下，数据库可以正常启动，数据恢复不会造成数据丢失。

4. ACTIVE非CURRENT日志组的全部member成员都损坏或丢失

案例模拟
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     27          2 NO  CURRENT
     2     26          2 YES ACTIVE
     3     23          2 YES INACTIVE
     4     24          2 YES ACTIVE
     5     25          2 YES ACTIVE

SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk5/disk5a.log /u01/app/oracle/oradata/PROD/disk1/disk5b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/PROD/disk1/disk5b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/PROD/disk5/disk5a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
案例恢复
[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 19:07:12 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597, not open)

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2618       CRITICAL OPEN      07-MAY-17     Redo log group 5 is unavailable
2624       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk1/disk5b.log is missing
2621       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk5/disk5a.log is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2618       CRITICAL OPEN      07-MAY-17     Redo log group 5 is unavailable
2624       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk1/disk5b.log is missing
2621       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk5/disk5a.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 /u01/app/oracle/oradata/PROD/disk1/disk5b.log was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD/disk5/disk5a.log was unintentionally renamed or moved, restore it

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

RMAN> repair failure;

Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1391128552.hm

contents of repair script:
   # database point-in-time recovery
   restore database until scn 703398;
   recover database until scn 703398;
   alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 07-MAY-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/disk5/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk5/example_01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/disk1/exam_01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/disk5/test
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PROD/disk1/free_list
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/PROD/disk2/users_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T185932_djxzjnfp_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T185932_djxzjnfp_.bkp tag=TAG20170507T185932
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 07-MAY-17

Starting recover at 07-MAY-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 07-MAY-17

database opened
repair failure complete

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 19:11:26 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> !ls /u01/app/oracle/oradata/PROD/disk1/disk5b.log
/u01/app/oracle/oradata/PROD/disk1/disk5b.log

SQL> !ls /u01/app/oracle/oradata/PROD/disk5/disk5a.log
/u01/app/oracle/oradata/PROD/disk5/disk5a.log

SQL> set line 180
SQL> col member for a60
SQL> select group#, sequence#, status, archived from v$log;

    GROUP#  SEQUENCE# STATUS           ARC
---------- ---------- ---------------- ---
     1      1 CURRENT          NO
     2      0 UNUSED           YES
     3      0 UNUSED           YES
     4      0 UNUSED           YES
     5      0 UNUSED           YES

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL>

这种情况下，数据库无法正常启动，恢复会存在部分数据丢失，自上次检查点到故障点这段时间内的数据全部丢失无法恢复，同时恢复完成后redo的sequence号从1开始重新计数。

5. ACTIVE CURRENT日志组的部分member成员损坏或丢失

案例模拟
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 21:53:40 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> set line 180
SQL> col member for a60
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1      1          2 YES INACTIVE
     2      2          2 YES INACTIVE
     3      3          2 NO  CURRENT
     4      0          2 YES UNUSED
     5      0          2 YES UNUSED

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk3/disk3a.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--数据库可以正常启动，alert日志信息：
Sun May 07 21:55:09 2017
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7502.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk3a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7502.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk3/disk3a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
案例恢复
[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 21:55:03 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597)

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
3596       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk3a.log is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
3596       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk3a.log is missing

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

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk3/disk3a.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Drop and re-create redo log group member /u01/app/oracle/oradata/PROD/disk3/disk3a.log  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4125969759.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4125969759.hm

contents of repair script:
   # drop/recreate redo log group member
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2702791668.hm'' ); end;";

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2702791668.hm'' ); end;
repair failure complete

RMAN> 

-- advise failure 生成的脚本内容：
[oracle@node1 ~]$ cat /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_4125969759.hm
   # drop/recreate redo log group member
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2702791668.hm'' ); end;";
[oracle@node1 ~]$ 
[oracle@node1 ~]$ cat /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2702791668.hm
begin
/*Drop the Log Member*/
execute immediate 'ALTER DATABASE DROP LOGFILE MEMBER ''/u01/app/oracle/oradata/PROD/disk3/disk3a.log''';
/*Add the Log Member (This will create a new file)*/
execute immediate 'ALTER DATABASE ADD LOGFILE MEMBER ''/u01/app/oracle/oradata/PROD/disk3/disk3a.log'' REUSE TO GROUP 3';
end;
[oracle@node1 ~]$ 

[oracle@node1 ~]$ ls -la /u01/app/oracle/oradata/PROD/disk3/disk3a.log 
-rw-r----- 1 oracle oinstall 209715712 May  7 21:55 /u01/app/oracle/oradata/PROD/disk3/disk3a.log
--丢失的日志文件已经恢复。

这种情况下，数据库可以正常启动，不会造成数据丢失。

6. ACTIVE CURRENT日志组全部member成员损坏或丢失

案例模拟
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 21:28:31 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL> set line 180
SQL> col member for a60
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     21          2 YES INACTIVE
     2      0          2 YES UNUSED
     5      0          2 YES UNUSED
     4     19          2 YES INACTIVE
     3     23          2 NO  CURRENT

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk4/disk3b.log /u01/app/oracle/oradata/PROD/disk3/disk3a.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5953
Session ID: 1 Serial number: 5


SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 7 21:30:22 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     836976640 bytes

Fixed Size                     1339740 bytes
Variable Size                683675300 bytes
Database Buffers             146800640 bytes
Redo Buffers                   5160960 bytes

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
3369       CRITICAL OPEN      07-MAY-17     Redo log group 3 is unavailable
3375       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk3a.log is missing
3372       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk4/disk3b.log is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
3369       CRITICAL OPEN      07-MAY-17     Redo log group 3 is unavailable
3375       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk3/disk3a.log is missing
3372       HIGH     OPEN      07-MAY-17     Redo log file /u01/app/oracle/oradata/PROD/disk4/disk3b.log is missing

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

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk3/disk3a.log was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD/disk4/disk3b.log was unintentionally renamed or moved, restore it

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

RMAN> repair failure;

Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2962123198.hm

contents of repair script:
   # database point-in-time recovery
   restore database until scn 728553;
   recover database until scn 728553;
   alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 07-MAY-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/disk5/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk5/example_01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/disk1/exam_01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/disk5/test
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PROD/disk1/free_list
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/PROD/disk2/users_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T210642_djy6z2tt_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T210642_djy6z2tt_.bkp tag=TAG20170507T210642
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-MAY-17

Starting recover at 07-MAY-17
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_07/o1_mf_annnn_TAG20170507T212324_djy7ydsq_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_07/o1_mf_annnn_TAG20170507T212324_djy7ydsq_.bkp tag=TAG20170507T212324
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_07/o1_mf_1_22_djy8gfp9_.arc thread=1 sequence=22
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_07/o1_mf_1_22_djy8gfp9_.arc RECID=72 STAMP=943392717
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-MAY-17

database opened
repair failure complete

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 21:33:13 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

SQL>select status from v$instance;

STATUS
------------
OPEN

SQL> set line 180
SQL> col member for a60
SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1      1          2 NO  CURRENT
     2      0          2 YES UNUSED
     3      0          2 YES UNUSED
     4      0          2 YES UNUSED
     5      0          2 YES UNUSED

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk1a.log        NO
     1     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk1b.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk2/disk2a.log        NO
     2     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk2b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk3b.log        NO
     3     ONLINE  /u01/app/oracle/oradata/PROD/disk3/disk3a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk4/disk4a.log        NO
     4     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk4b.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk5/disk5a.log        NO
     5     ONLINE  /u01/app/oracle/oradata/PROD/disk1/disk5b.log        NO

10 rows selected.

SQL>

这种情况下数据恢复会存在部分数据丢失，同时sequence号重新开始计数。

总结：

redo日志组中部分member丢失的恢复都不会造成数据丢失。
在所有member都丢失的情况下：
非active日志组数据恢复不会造成数据丢失。
active日志组数据恢复会造成部分数据丢失。

kevin-ke
关注
专栏目录
oracle之rman恢复redolog
mensuo1111的专栏
07-05 2592
一、环境搭建 1、  删除（移走）redologfile [oracle@rac3orcl]$ ls control01.ctl  control03.ctl redo02.log  rhf.dbf       system01.dbf  undotbs01.dbf control02.ctl  redo01.log    redo03.log  sysaux01.dbf  temp0
RMAN备份与恢复之控制文件丢失
DBA@Robin
01-14 5668
常言道，“知其然，更要知其所以然”。但是没有知其然，何谈知其所以然……
rman恢复的方式搭建dataguard后redo log 的处理
changyanmanman的专栏
05-08 2818
转载：http://www.dbdream.org/?p=548 搭建DATA GUARD，利用RMAN备份恢复备库时，由于RMAN不会备份REDO文件，还原后的备库缺少REDO日志文件，对于用归档日志同步数据的备库，影响不是很大，依然可以应用日志同步主库的数据变更，但是如果主/备库切换的话，就不行了，对于这样控制文件里记录了REDO的信息，但是在存储上却没有对应的文件的备库，此时删除控制文
RMAN学习9——丢失联机redolog的恢复
ceclar123的专栏
07-19 4013
1、丢失非当前的联机redolog （1）查看哪些redolog是非当前文件 SQL> select GROUP#, ARCHIVED,STATUS, FIRST_TIME from V$log;     GROUP#  ARC STATUS           FIRST_TIME ---------- --- ---------------- --------------
rman-Redolog file丢失的恢复
過往雲烟
01-28 157
Redolog file丢失:([color=red]下面的这些语句一定要在sqlplus中执行,不是在rman中执行[/color]) (sqlplus/nolog) 1.[code="java"]shutdown immediate;[/code] 2.[code="java"]startup mount;[/code] 3.[code="java"]recover database ...
RMAN备份恢复（日志文件）
congwei2855的博客
03-16 497
如果日志文件丢失会有几种情况 ...
RMAN使用日志序列号恢复重做日志文件（op-db）
weixin_39568073的博客
08-24 206
backup database plus archivelog; list backup of database; select group#,sequence#,bytes/1024/1024,members,status from v$log; UNUSED：从未写入在线重做日志。这是刚刚添加的重做日志的状态 CURRENT：当前重做日志。这意味着重做日志处于活动状态。重做日志可以打开或关闭。 ACTIVE：日志处于活动状态但不是当前日志。它是崩溃恢复所必需的。它可能用于...
顶级DBA漫谈Oracle Rman备份与恢复
09-01
以下是Oracle RMAN备份与恢复的相关知识点： 备份恢复概述 备份恢复是DBA管理员的基本技能之一，旨在保护数据库中的数据免受意外损坏或丢失。 Oracle RMAN提供了强大的备份和恢复功能，帮助DBA管理员快速恢复...
Oracle19c rac备份数据通过rman恢复到单实例
最新发布
07-02
- 使用以下SQL脚本更改redo日志文件的位置： ```sql set pagesize 999 set linesize 999 select 'alter database rename file ''' || member || ''' to ''' || replace(member, '+DATA/XZDB/ONLINELOG/', '/u01...
RMAN备份与恢复实战演练
11-11
【RMAN备份与恢复实战演练】 RMAN（Recovery Manager）是Oracle数据库提供的一种强大的备份和恢复工具，尤其适用于大型企业级数据库环境。本实战演练将引导初学者全面了解并熟练掌握RMAN的使用，包括其参数配置、...
ORACLE RMAN备份恢复指南
07-26
本文旨在为瑞航公司当前所使用的Oracle RMAN备份恢复机制提供详尽的指导，涵盖全量备份、增量备份、数据恢复等多个方面，尤其关注于数据丢失、控制文件丢失、参数文件丢失、密码文件丢失、redo文件丢失以及表空间...
rman 恢复部分归档日志
weixin_33904756的博客
08-21 2593
rac环境下恢复部分归档日志。 恢复全部归档日志文件restore archivelog all 恢复从第5个归档日志起restore archivelog from logseq 5;只恢复 5到10这四个归档日志文件restore archivelog from logseq 5 until logseq 10;恢复7天内的归档日志--默认恢复所有线...
RMAN备份数据库_使用RMAN备份归档redo日志
jetliu05的博客
06-15 1833
rman备份归档日志
RMAN输出到log文件中
' 忍一时风平浪静 退一步海阔天空 '
09-12 3455
一、RMAN输出： 1）只输出到log日志中： $rman LOG /u01/app/oracle/rman.log 2）输出到屏幕和log日志中： $rman | tee /u01/app/oracle/rman.log 二、rman备份副本： 1、使用configure  。。。 backup copies 2、在run块中使用set backup copies 3、在ba
oracle rman 备份时redo log file 会变多吗,为什么热备份期间产生的Redo要比正常时多...
weixin_31159439的博客
04-04 338
我们还要知道的是，在数据库处于热备份(使用Begin Backup进行备份时)状态时，会产生了比平常更多的日志。这是因为在热备份期间，Oracle为了解决SPLIT BLOCK的问题，需要在日志文件中记录修改的行所在的数据块的前镜像(image)，而不仅仅是修改信息。为了理解这段话，我们还要简单介绍一下SPLIT BLOCK的概念。在这种情况下，可能出现如下状况。当拷贝数据文件的同时，数据库正好向...
【12c】12c RMAN新特性之UNTIL AVAILABLE REDO--自动恢复到REDO终点的步骤简化
coco3600的博客
04-09 339
【12c】12c RMAN新特性之UNTIL AVAILABLE REDO--自动恢复到REDO终点的步骤简化 在Oracle 12...
RMAN 备份中的redo log切换- bakcup ...plus archivelog命令
文档搬运工
03-29 949
原文地址： https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/backing-up-database.html#GUID-5A2AEEA4-CB81-421A-8C4A-805B92DBD8FA 9.7.1.2 About Online Redo Log Switching Automatic onli...
12.2 新特性：RMAN 自动恢复到 REDO 终点的步骤简化
Enmotech的博客
04-02 729
在 Oracle 12.2 之前，当我们需要恢复数据库到某个时间点的时候，需要确定 SCN，或者日志序列号，或者一个时间点，以便尽可能多的应用归档日志，进而尽可能多的恢复数据。从12.2开始，RMAN 新增参数：RECOVER DATABASE UNTIL AVALIABLE REDORMAN 将会根据控制文件信息和归档日志/在线日志/归档日志备份集的物理可用性，将数据库恢复到最后一个可用的归档日
oracle归档日志切换时间,oracle rman开始备份归档日志的时候，是不是会自动切换redo？...
weixin_28717939的博客
04-04 192
oracle rman开始备份归档日志的时候，是不是会自动切换redo？ 下面我们做个测试：rman备份归档日志一开始想应该不会切换redo,除非是在rman 中指定 alter system archive log current或者是backup database plus archivelog 这两种情况下都会切换redo的以下是测试过程 会切换redo:C:\Documents and S...
ORACLE RMAN备份与恢复实战手册
"ORACLE RMAN备份恢复指南涵盖了RMAN的全量、增量备份和恢复技术，以及在数据丢失、控制文件丢失、参数文件丢失、密码文件丢失、redo日志丢失和表空间损坏等场景下的处理方法。" 本文旨在详细介绍ORACLE数据库中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值