恢複acitve日志組有兩種情況
一種是acitve日志組的其中一個成員損壞
一種是acitve日志組所有日志成員都損壞
使用rman恢複acitve日志組的其中一個成員損壞
1、檢視目前日志組狀态以及日志組成員
sys@TEST1107> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIME NEXT_CHANGE# NEXT_TIME
------------------ ------------ ------------------
1 1 22 52428800 512 2 NO CURRENT 1153754
06-NOV-13 2.8147E+14
2 1 20 52428800 512 2 YES INACTIVE 1132964
06-NOV-13 1153041 06-NOV-13
3 1 21 52428800 512 2 YES ACTIVE 1153041
06-NOV-13 1153754 06-NOV-13
sys@TEST1107> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/test1107/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/test1107/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/test1107/redo01.log NO
1 ONLINE /u01/app/oracle/oradata/test1107/redo01a.log NO
2 ONLINE /u01/app/oracle/oradata/test1107/redo02a.log NO
3 ONLINE /u01/app/oracle/oradata/test1107/redo03a.log NO
6 rows selected.
2. ACTIVE 組日志是第3組，OS下删除第三組的其中一個成員
[oracle@rtest ~]$ rm /u01/app/oracle/oradata/test1107/redo03.log
3、模拟斷電，然後起庫，資料庫可正常打開
sys@TEST1107> shutdown abort
ORACLE instance shut down.
sys@TEST1107> startup
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 805306608 bytes
Database Buffers 452984832 bytes
Redo Buffers 8847360 bytes
Database mounted.
Database opened.
4、檢視alert日志
Errors in file /u01/app/oracle/diag/rdbms/test1107/test1107/trace/test1107_lgwr_23973.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/test1107/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
5、檢視資料庫打開後的日志組以及日志成員狀态，發現剛才删掉的那個日志成員的狀态為INVALID 。
1 1 22 52428800 512 2 YES INACTIVE 1153754
06-NOV-13 1174308 06-NOV-13
2 1 23 52428800 512 2 NO CURRENT 1174308
3 1 21 52428800 512 2 YES INACTIVE 1153041
3 INVALID ONLINE /u01/app/oracle/oradata/test1107/redo03.log NO
6、解決辦法為drop後重新添加
sys@TEST1107> alter database drop logfile member '/u01/app/oracle/oradata/test1107/redo03.log';
Database altered.
sys@TEST1107> alter database add logfile member '/u01/app/oracle/oradata/test1107/redo03.log' to group 3;
7、再次檢視日志組以及日志組成員，發現還是INVALID狀态。
8、切換日志，使其強制使用，再次檢視，日志成員已恢複正常。
sys@TEST1107>
sys@TEST1107> alter system switch logfile;
System altered.
sys@TEST1107> /
使用rman恢複acitve日志組的所有成員損壞
1、檢視目前日志組以及日志成員的狀态
1 1 25 52428800 512 2 YES INACTIVE 1175094
06-NOV-13 1175097 06-NOV-13
2 1 26 52428800 512 2 YES ACTIVE 1175097
06-NOV-13 1178906 06-NOV-13
3 1 27 52428800 512 2 NO CURRENT 1178906
2、删除ACTIVE日志組所有成員
[oracle@rtest ~]$ rm /u01/app/oracle/oradata/test1107/redo02.log
[oracle@rtest ~]$ rm /u01/app/oracle/oradata/test1107/redo02a.log
3、模拟斷電，再打開資料庫，報錯
ORA-03113: end-of-file on communication channel
Process ID: 1466
Session ID: 96 Serial number: 3
4、檢視alert告警日志
Wed Nov 06 11:43:25 2013
ARC3 started with pid=23, OS id=1610
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/test1107/test1107/trace/test1107_lgwr_1565.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/test1107/redo02a.log'
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/test1107/redo02.log'
Errors in file /u01/app/oracle/diag/rdbms/test1107/test1107/trace/test1107_ora_1582.trc:
ORA-00313: open failed for members of log group 1 of thread
USER (ospid: 1582): terminating the instance due to error 313
System state dump requested by (instance=1, osid=1582), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/test1107/test1107/trace/test1107_diag_1555.trc
Dumping diagnostic data in directory=[cdmp_20131106114326], requested by (instance=1, osid=1582), summary=[abnormal instance termination].
Instance terminated by USER, pid = 1582
5、直接關閉不行，退出重新以mount模式登進
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
sys@TEST1107> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rtest ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 6 11:54:02 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
idle> shutdown abort
idle> startup mount
6、使用隐藏參數，進行不一緻的恢複
idle> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
7、重新開機，mount方式起庫，使隐藏參數生效
idle> shutdown immediate
ORA-01109: database not open
Database dismounted.
7、使用基于取消的資料庫恢複
idle> recover database until cancel;
Media recovery complete.
8、使用resetlogs方式打開資料庫
idle> alter database open resetlogs;
9、檢視恢複後的日志組及其成員
1 1 1 52428800 512 2 NO CURRENT 1199286
2 1 0 52428800 512 2 YES UNUSED 0
3 1 0 52428800 512 2 YES UNUSED 0
10、切換日志，更改日志組狀态
1 1 4 52428800 512 2 NO CURRENT 1200058
2 1 2 52428800 512 2 YES INACTIVE 1200052
06-NOV-13 1200055 06-NOV-13
3 1 3 52428800 512 2 YES INACTIVE 1200055
06-NOV-13 1200058 06-NOV-13
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)