实验环境

操作系统 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日志组数据恢复会造成部分数据丢失。