第一：损坏的redo为非正在使用的redo log；
1、归档模式，不是当前正在日志损坏，数据库打开模式。
模拟损坏：
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1388 52428800512
1 YES INACTIVE
5385552 27-OCT-135385555 27-OCT-13
2
1389 52428800512
1 YES INACTIVE
5385555 27-OCT-135385558 27-OCT-13
3
1390 52428800512
1 NOCURRENT
5385558 27-OCT-13 2.8147E+14
4
1387 52428800512
1 YES INACTIVE
5385548 27-OCT-135385552 27-OCT-13
SQL> !rm redo02.log
SQL> !ls -l
total 3647744
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 17:36 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 17:35 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 17:35 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:35 redo01.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:35 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:35 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:35 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:35 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 17:35 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 17:35 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 16:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:35 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 17:35 users01.dbf
SQL>
日志提示错误：
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_arc2_1758.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/app/oracle/oradata/RHYS/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313
日常出现错误，提示不能获得该文件状态，数据无法进行日志切换
解决办法：
1、注意不需要重启数据库，只需要把日志清除即可。
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> !ls -l
total 3698948
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 17:41 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 17:39 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 17:39 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:40 redo01.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:41 redo02.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:38 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:38 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:39 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:39 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 17:39 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 17:39 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 16:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:39 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 17:39 users01.dbf
SQL>
第一：非正在使用的日志文件损坏
1、归档模式，不是当前正在日志损坏，数据库关闭模式。
演示过程：
SQL> set linesize 200
SQL> select * from v$log;
select * from v$log
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup mount;
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3489
Session ID: 1 Serial number: 5
日志提示：
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3489.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/opt/app/oracle/oradata/RHYS/redo02.log'
Sun Oct 27 17:45:25 2013
ARC1 started with pid=21, OS id=3493
USER (ospid: 3489): terminating the instance due to error 313
Sun Oct 27 17:45:25 2013
System state dump requested by (instance=1, osid=3489), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_diag_3458_20131027174525.trc
Dumping diagnostic data in directory=[cdmp_20131027174525], requested by (instance=1, osid=3489), summary=[abnormal instance termination].
Instance terminated by USER, pid = 3489
解决办法：
在数据库启动的时候清除日志，然后open数据库：
SQL> startup mount;
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance RHYS (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/opt/app/oracle/oradata/RHYS/redo02.log'
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
3、数据库打开模式，非归档模式，非当前日志损坏
模拟过程：
SQL> archive log list;
Database log mode
No Archive Mode
Automatic archival
Disabled
Archive destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 402
Current log sequence 405
SQL> !rm redo02.log
SQL> !ls -l
total 3647744
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 17:54 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 17:54 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 17:54 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:54 redo01.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:54 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 17:54 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:54 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:54 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 17:54 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 17:54 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 16:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 17:54 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 17:54 users01.dbf
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
SQL>
在执行dml语句，以及切换日志都成功，但是数据库启动后报错。
日志文件如下：
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3779.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/opt/app/oracle/oradata/RHYS/redo02.log'
USER (ospid: 3779): terminating the instance due to error 313
System state dump requested by (instance=1, osid=3779), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_diag_3749_20131027175926.trc
Dumping diagnostic data in directory=[cdmp_20131027175926], requested by (instance=1, osid=3779), summary=[abnormal instance termination].
Instance terminated by USER, pid = 3779
解决方案：
只需要把损坏的日志文件清除日志组即可。
SQL> startup mount;
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database;
alter database
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database oepn;
alter database oepn
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database open;
Database altered.
SQL>
四、非归档模式、数据库关闭、不是正在使用的日志文件损坏。
模拟过程：
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1450 52428800512
1 NOCURRENT
5388976 27-OCT-13 2.8147E+14
2
1449 52428800512
1 NOINACTIVE
5388970 27-OCT-135388976 27-OCT-13
3
1447 52428800512
1 NOINACTIVE
5388964 27-OCT-135388967 27-OCT-13
4
1448 52428800512
1 NOINACTIVE
5388967 27-OCT-135388970 27-OCT-13
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !rm redo02.log
SQL> !ls -l
total 3647744
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 18:06 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 18:06 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 18:06 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:06 redo01.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:05 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:05 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:06 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:06 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 18:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 18:06 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 16:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:06 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 18:06 users01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4087
Session ID: 1 Serial number: 5
日志告警
Additional information: 3
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_4087.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/opt/app/oracle/oradata/RHYS/redo02.log'
USER (ospid: 4087): terminating the instance due to error 313
System state dump requested by (instance=1, osid=4087), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_diag_4057_20131027180649.trc
Dumping diagnostic data in directory=[cdmp_20131027180649], requested by (instance=1, osid=4087), summary=[abnormal instance termination].
Instance terminated by USER, pid = 4087
恢复方法：
SQL> startup mount;
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
该方法与第三中方法一样。
总结，对于不是当前使用的归档日志损坏，归档模式需要使用alter database clear unarchived 命令清空日志 组即可。对于非归档模式需要使用alter system clear 日志文件组即可。
第二、当前正在使用的日志文件损坏
1、归档模式，数据库open状态、当前正在使用的日志文件损坏
模拟过程：
QL> !ls -l
total 3698948
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 18:27 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 18:10 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 18:10 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:26 redo01.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:10 redo02.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:10 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:10 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:10 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:10 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 18:26 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 18:26 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 16:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:26 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 18:10 users01.dbf
SQL> archive log list;
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 447
Next log sequence to archive 450
Current log sequence 450
SQL> set linesize 200
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1450 52428800512
1 NOCURRENT
5388976 27-OCT-13 2.8147E+14
2
1
0 52428800512
1 YES UNUSED
5388970 27-OCT-135388976 27-OCT-13
3
1447 52428800512
1 YES INACTIVE
5388964 27-OCT-135388967 27-OCT-13
4
1448 52428800512
1 YES INACTIVE
5388967 27-OCT-135388970 27-OCT-13
SQL> !rm redo01.log
SQL> !ls -l
total 3647744
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 18:28 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 18:10 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 18:10 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:10 redo02.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:10 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 18:10 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:10 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:10 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 18:28 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 18:28 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 16:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 18:28 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 18:10 users01.dbf
SQL>
日志信息提示：
Additional information: 3
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_arc2_4228.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/RHYS/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313
解决方法：
由于这个时候，虽然当前日志是正在被使用的，但是我们可以先进行切换日志之后，然后执行clear操作。
SQL> alter system checkpoint;
System altered.
SQL> !rm redo01.log;
SQL> alter system checkpoint;
System altered.
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance RHYS (thread 1)
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/RHYS/redo01.log'
SQL> alter database switch logfile;
alter database switch logfile
*
ERROR at line 1:
ORA-02000: missing ALL keyword
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1458 52428800512
1 NOINACTIVE
5397246 27-OCT-135397347 27-OCT-13
2
1459 52428800512
1 NOCURRENT
5397347 27-OCT-13 2.8147E+14
3
1456 52428800512
1 YES INACTIVE
5397237 27-OCT-135397240 27-OCT-13
4
1457 52428800512
1 YES INACTIVE
5397240 27-OCT-135397246 27-OCT-13
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
Database opened.
SQL>
2、归档模式，数据库open状态、当前正在使用的日志文件损坏，并且正常关闭数据库。
模拟过程：
SQL> startup
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 459
Current log sequence 459
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1
0 52428800512
1 YES UNUSED
5397246 27-OCT-135397347 27-OCT-13
2
1459 52428800512
1 NOCURRENT
5397347 27-OCT-13 2.8147E+14
3
1456 52428800512
1 YES INACTIVE
5397237 27-OCT-135397240 27-OCT-13
4
1457 52428800512
1 YES INACTIVE
5397240 27-OCT-135397246 27-OCT-13
SQL> !rm redo02.log;
SQL> !ls -ltr
total 3647744
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall44048384 Oct 27 19:21 temp01.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 19:26 redo01.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 19:26 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 19:26 redo04.log
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 19:26 system01.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 19:26 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 19:26 users01.dbf
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 19:26 example01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 19:26 statspack.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 19:26 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 19:26 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 19:26 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 19:27 control01.ctl
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5110
Session ID: 1 Serial number: 5
SQL> select open_mode from v$database;
ERROR:
ORA-03114: not connected to ORACLE
SQL> exit
解决办法，只需要进行启动到mount状态下，然后clear损坏日志即可。
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle-one RHYS]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 27 19:28:33 2013
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
3、归档模式，数据库open状态、当前正在使用的日志文件损坏，并且异常关闭数据库。
模拟过程：
SQL> set linesize 200;
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1460 52428800512
1 NOCURRENT
5397348 27-OCT-13 2.8147E+14
2
1
0 52428800512
1 YES UNUSED
5397347 27-OCT-135397348 27-OCT-13
3
1456 52428800512
1 YES INACTIVE
5397237 27-OCT-135397240 27-OCT-13
4
1457 52428800512
1 YES INACTIVE
5397240 27-OCT-135397246 27-OCT-13
SQL> !rm redo01.log
SQL> !ls -l
total 3647744
-rw-r-----. 1 oracle oinstall 9977856 Oct 27 19:32 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 27 19:29 example01.dbf
-rw-r-----. 1 oracle oinstall62922752 Oct 27 19:29 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall52429312 Oct 27 19:29 redo02.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 19:29 redo03.log
-rw-r-----. 1 oracle oinstall52429312 Oct 27 19:29 redo04.log
-rw-r-----. 1 oracle oinstall52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 19:29 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 19:29 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 27 19:29 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Oct 27 19:29 system01.dbf
-rw-r-----. 1 oracle oinstall44048384 Oct 27 19:21 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 27 19:29 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 27 19:29 users01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
解决办法：
这时候我们有两种办法，一种是使用备份进行恢复，另一种是使用隐含参数。介绍第二种：
SQL> set verify off
SQL> @getsp.sql
Enter value for par: allow_resetlogs_
KSPPINM
KSPPSTVL
KSPPDESC
-------------------------------------------------- -------------------- ------------------------------------------------------------
_allow_resetlogs_corruption
FALSE
allow resetlogs even if it will cause corruption
SQL>
该参数是在数据库 不一致的情况下，重置日志文件。
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area492707840 bytes
Fixed Size
2254544 bytes
Variable Size
322963760 bytes
Database Buffers
163577856 bytes
Redo Buffers
3911680 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/RHYS/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> set linesize 200
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1
1460 52428800512
1 NOCURRENT
5397348 27-OCT-13 2.8147E+14
4
1457 52428800512
1 YES INACTIVE
5397240 27-OCT-135397246 27-OCT-13
3
1456 52428800512
1 YES INACTIVE
5397237 27-OCT-135397240 27-OCT-13
2
1
0 52428800512
1 YES UNUSED
5397347 27-OCT-135397348 27-OCT-13
SQL> alter database open resetlogs;
Database altered.
SQL>
重建实例然后使用expdp和impdp，将数据导出在导入数据库
SQL> create directory expdp as '/opt/app/oracle/oradata';
Directory created.
然后导出数据重建数据库，在导入数据。
总结：对于当前正在使用的日志的损坏，一般通过备份来修复，如果不行只能采用第二种设置隐含参数_allow_resetlogs_corruption来恢复。
|
