一、非正在使用的redo log损坏
1.1 归档模式，不是当前正在日志损坏，数据库打开模式。
模拟损坏：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 951
Next log sequence to archive 953
Current log sequence 953
SQL>
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 952 52428800 512 1 YES INACTIVE 4059877 24-JAN-18 4064774 24-JAN-18
2 1 953 52428800 512 1 NO CURRENT 4064774
24-JAN-18 2.8147E+14
3 1 951 52428800 512 1 YES INACTIVE 4021213 24-JAN-18 4059877 24-JAN-18
SQL> select member from v$logfile;
MEMBER
————————————————–
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL>SQL> !rm -rf
/u01/app/oracle/oradata/DBdb/redo03.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log
切换日志：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
查看alert日志提示错误
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc3_2018.trc:
ORA-00313: ( 1) ?
ORA-00312: 3 1:
‘/u01/app/oracle/oradata/DBdb/redo03.log’
ORA-27037: ??
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
Master archival failure: 313
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc0_2012.trc:
ORA-00313: ( 1) ?
ORA-00312: 3 1:
‘/u01/app/oracle/oradata/DBdb/redo03.log’
ORA-27037: ??
日常出现错误，提示不能获得该文件状态，数据无法进行日志切换
解决办法：
1、注意不需要重启数据库，只需要把日志清除即可。
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> SQL>
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL> select member from v$logfile;
MEMBER
—————————————————————-
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
1.2 归档模式，不是当前正在日志损坏，数据库关闭模式。
演示过程：
—先检查日志状态：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 961 52428800 512 1 YES INACTIVE 4075623 25-JAN-18 4075626 25-JAN-18
2 1 962 52428800 512 1 NO CURRENT 4075626
25-JAN-18 2.8147E+14
3 1 960 52428800 512 1 YES INACTIVE 4075620 25-JAN-18 4075623 25-JAN-18
SQL> select member from v$logfile;
MEMBER
———————————————————
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
—关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
—删除日志
SQL> !rm -rf
/u01/app/oracle/oradata/DBdb/redo01.log
SQL>
重启数据库：
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication
channel
Process ID: 14178
Session ID: 1 Serial number: 5
SQL>
报错，检查日志，如下：
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14158.trc:
ORA-00313: ??????? 1 (???? 1) ???
ORA-00312: ???? 1 ?? 1:
‘/u01/app/oracle/oradata/DBdb/redo01.log’
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
解决：在数据库启动的时候清除日志，然后open数据库：
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database clear logfile group
1;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 0 52428800 512 1 YES UNUSED 4075623 25-JAN-18 4075626 25-JAN-18
2 1 962 52428800 512 1 NO CURRENT 4075626
25-JAN-18 2.8147E+14
3 1 960 52428800 512 1 YES INACTIVE 4075620 25-JAN-18 4075623 25-JAN-18
SQL> select member from v$logfile;
MEMBER
——————————————————–
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
注意，如果删除的日志未归档则加一个参数alter database clear(unarchived)
logfile group 1;
1.3 数据库打开模式，非归档模式，非当前日志损坏
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
—查询：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 0 52428800 512 1 YES UNUSED 4075623 25-JAN-18 4075626 25-JAN-18
2 1 962 52428800 512 1 NO CURRENT 4075626
25-JAN-18 2.8147E+14
3 1 960 52428800 512 1 YES INACTIVE 4075620 25-JAN-18 4075623 25-JAN-18
SQL> select member from v$logfile;
MEMBER
———————————————————————————-
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
—删除日志，模拟故障：
SQL> !rm -rf
/u01/app/oracle/oradata/DBdb/redo03.log
—切换日志：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
注意：
在执行dml语句，以及切换日志都成功，数据库日志也没有报错如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication
channel
Process ID: 14645
Session ID: 1 Serial number: 5
日志信息如下：
Thu Jan 25 05:18:19 2018
Thread 1 advanced to log sequence 995 (LGWR
switch)
Current log# 2 seq# 995 mem# 0: /u01/app/oracle/oradata/DBdb/redo02.log
Thread 1 advanced to log sequence 996 (LGWR
switch)
Current log# 1 seq# 996 mem# 0: /u01/app/oracle/oradata/DBdb/redo01.log
Thu Jan 25 05:18:30 2018
Thread 1 advanced to log sequence 997 (LGWR
switch)
Current log# 3 seq# 997 mem# 0: /u01/app/oracle/oradata/DBdb/redo03.log
Thu
Jan 25 05:19:52 2018
Thread 1 advanced to log sequence 998 (LGWR
switch)
Current log# 2 seq# 998 mem# 0: /u01/app/oracle/oradata/DBdb/redo02.log
Thread 1 advanced to log sequence 999 (LGWR
switch)
Current log# 1 seq# 999 mem# 0: /u01/app/oracle/oradata/DBdb/redo01.log
但是数据库启动后，如下：
ALTER DATABASE OPEN
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14625.trc:
ORA-00313: ??????? 3 (???? 1) ???
ORA-00312: ???? 3 ?? 1:
‘/u01/app/oracle/oradata/DBdb/redo03.log’
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14625.trc:
解决：只需要把损坏的日志文件清除日志组即可。
SQL> alter database clear logfile group
3;
Database altered.
SQL> alter database open;
Database altered.
—验证：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———-
———- ———- ———- ———- — ———- ————-
———— ———— ————
1 1 999 52428800 512 1 NO CURRENT 4077012
25-JAN-18 2.8147E+14
2 1 998 52428800 512 1 NO INACTIVE 4077009
25-JAN-18 4077012 25-JAN-18
3 1 0 52428800 512 1 NO UNUSED 4076978
25-JAN-18 4077009 25-JAN-18
SQL> select member from v$logfile;
MEMBER
——————————————————————–
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
1.4非归档模式、数据库关闭、不是正在使用的日志文件损坏
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———-
———- ———- — ———- ————- ———— ————
————
1 1 1002 52428800 512 1 NO CURRENT 4077582 25-JAN-18 2.8147E+14
2 1 1001 52428800 512 1 NO INACTIVE 4077579
25-JAN-18 4077582 25-JAN-18
3 1 1000 52428800 512 1 NO INACTIVE 4077575
25-JAN-18 4077579 25-JAN-18
SQL> select member from v$logfile;
MEMBER
——————————————————————————————————————————————————————————————————–
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> !rm -rf
/u01/app/oracle/oradata/DBdb/redo02.log
SQL>
—删除日志，模拟故障
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
—重启：
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication
channel
Process ID: 14823
Session ID: 1 Serial number: 5
SQL>
日志报错：
ALTER DATABASE OPEN
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14803.trc:
ORA-00313: ??????? 2 (???? 1) ???
ORA-00312: ???? 2 ?? 1:
‘/u01/app/oracle/oradata/DBdb/redo02.log’
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or
directory
解决方案：clear日志组2
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database clear logfile group
2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
—验证：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1002 52428800 512 1 NO CURRENT 4077582
25-JAN-18 2.8147E+14
2 1 0 52428800 512 1 NO UNUSED 4077579
25-JAN-18 4077582 25-JAN-18
3 1 1000 52428800 512 1 NO INACTIVE 4077575
25-JAN-18 4077579 25-JAN-18
SQL> select member from v$logfile;
MEMBER
————————————————————————
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
总结，对于不是当前使用的归档日志损坏，归档模式需要使用alter database clear unarchived 命令清空日志 组即可。对于非归档模式需要使用alter
system clear 日志文件组即可。
二、当前正在使用的redo log损坏
2.1 归档模式，数据库open状态、当前正在使用的日志文件损坏
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
—检查：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1002 52428800 512 1 NO CURRENT 4077582
25-JAN-18 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 4077579 25-JAN-18 4077582 25-JAN-18
3 1 1000 52428800 512 1 YES INACTIVE 4077575 25-JAN-18 4077579 25-JAN-18
SQL> select member from v$logfile;
MEMBER
————————————————————————-
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
—触发检查点
SQL> alter system checkpoint;
System altered.
—切换日志组
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
后台日志报错，如下：
Thu Jan 25 05:41:44 2018
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc2_15008.trc:
ORA-00313: ( 1) ?
ORA-00312: 1 1: ‘/u01/app/oracle/oradata/DBdb/redo01.log’
ORA-27037: ??
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
Master archival failure: 313
ARCH: Archival stopped, error occurred.
Will continue retrying
ORACLE Instance DBdb – Archival Error
ORA-00313: ( 1) ?
ORA-00312: 1 1:
‘/u01/app/oracle/oradata/DBdb/redo01.log’
ORA-27037: ??
Linux-x86_64 Error: 2: No such file or
directory
解决：由于这个时候，虽然当前日志是正在被使用的，但是我们可以先进行切换日志之后，然后执行clear操作。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1002 52428800 512 1 NO INACTIVE 4077582
25-JAN-18 4078722 25-JAN-18
2 1 1003 52428800 512 1 NO INACTIVE 4078722
25-JAN-18 4078725 25-JAN-18
3 1 1004 52428800 512 1 NO CURRENT 4078725
25-JAN-18 2.8147E+14
SQL> alter database clear unarchived logfile group 1;
Database altered.
—验证：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1005 52428800 512 1 NO CURRENT 4078821
25-JAN-18 2.8147E+14
2 1 1003 52428800 512 1 YES INACTIVE 4078722 25-JAN-18 4078725 25-JAN-18
3 1 1004 52428800 512 1 YES ACTIVE 4078725 25-JAN-18 4078821 25-JAN-18
SQL> select member from v$logfile;
MEMBER
———————————————————————-
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
2.2 归档模式，数据库open状态、当前正在使用的日志文件损坏，并且正常关闭数据库
—查询：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1005 52428800 512 1 YES ACTIVE 4078821 25-JAN-18 4078866 25-JAN-18
2 1 1006 52428800 512 1 NO CURRENT 4078866
25-JAN-18 2.8147E+14
3 1 1004 52428800 512 1 YES ACTIVE 4078725 25-JAN-18 4078821 25-JAN-18
SQL> select member from v$logfile;
MEMBER
—————————————————————————–
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
—删除日志组2，模拟故障：
SQL> !rm -rf
/u01/app/oracle/oradata/DBdb/redo02.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo03.log
—关闭数据库，重启：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication
channel
Process ID: 15253
Session ID: 1 Serial number: 5
日志报错，信息如下：
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Thu Jan 25 05:50:23 2018
ARC0 started with pid=20, OS id=15255
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_15233.trc:
ORA-00313: ??????? 2 (???? 1) ???
ORA-00312: ???? 2 ?? 1:
‘/u01/app/oracle/oradata/DBdb/redo02.log’
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
解决：只需要进行启动到mount状态下，然后clear损坏日志即可。
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL>
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance DBdb (thread 1) needs to be archived
ORA-00312: online log 2 thread 1:
‘/u01/app/oracle/oradata/DBdb/redo02.log’
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
—验证：
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1009 52428800 512 1 NO CURRENT 4079417
25-JAN-18 2.8147E+14
2 1 1008 52428800 512 1 YES ACTIVE 4079414 25-JAN-18 4079417 25-JAN-18
3 1 1007 52428800 512 1 YES ACTIVE 4078867 25-JAN-18 4079414 25-JAN-18
SQL> select member from v$logfile;
MEMBER
———————————————————————-—
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
2.3 归档模式，数据库open状态、当前正在使用的日志文件损坏，并且异常关闭数据库
—检查：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1009 52428800 512 1 NO CURRENT 4079417
25-JAN-18 2.8147E+14
2 1 1008 52428800 512 1 YES ACTIVE 4079414 25-JAN-18 4079417 25-JAN-18
3 1 1007 52428800 512 1 YES ACTIVE 4078867 25-JAN-18 4079414 25-JAN-18
SQL> select member from v$logfile;
MEMBER
————————————————-
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
—删除日志组1，模拟故障
SQL> !rm -rf
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
—模拟异常关机：
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
—重启：
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-00313: open failed for members of log
group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/DBdb/redo01.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
SQL>
后天日志报错：
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Errors in file
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_15477.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/DBdb/redo01.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
Aborting crash recovery due to error 313
解决：这时候我们有两种办法，一种是使用备份进行恢复，另一种是使用隐含参数。介绍第二种：
SQL> conn / as sysdba
Connected.
SQL>
SQL> startup mount;
ORA-01081: cannot start already-running
ORACLE – shut it down first
SQL>
SQL> select status from v$instance;
STATUS
———-
MOUNTED
SQL> select ksppinm, ksppstvl from
x$ksppi x, x$ksppcv y where (x.indx =
y.indx) and (translate(ksppinm, ‘_’, ‘#’)) like ‘_allow_resetlogs_corruption‘;
KSPPINM KSPPSTVL
—————————— ——————————
_allow_resetlogs_corruption FALSE
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>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
—查看查询及日志情况：
SQL> select ksppinm, ksppstvl from
x$ksppi x, x$ksppcv y where (x.indx =
y.indx) and (translate(ksppinm, ‘_’, ‘#’)) like ‘_allow_resetlogs_corruption’;
KSPPINM KSPPSTVL
——————————
——————————
_allow_resetlogs_corruption TRUE
SQL>
SQL> select * from
v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- —
———- ————- ———— ———— ————
1 1 1009 52428800 512 1 NO CURRENT 4079417
25-JAN-18 2.8147E+14
3 1 1007 52428800 512 1 YES ACTIVE 4078867 25-JAN-18 4079414 25-JAN-18
2 1 1008 52428800 512 1 YES ACTIVE 4079414 25-JAN-18 4079417 25-JAN-18
SQL> select member from v$logfile;
MEMBER
—————————————————————–
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
—尝试1：
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 DBdb (thread 1)
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/DBdb/redo01.log’
—尝试2：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid
after an incomplete database recovery
—恢复在resetlogs:
SQL> recover database until cancel;
ORA-00279: change 4079417 generated at
01/25/2018 05:53:46 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%u_.arc
ORA-00280: change 4079417 for thread 1 is
in sequence #1009
Specify log: {=suggested |
filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log ‘/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%u_.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
ORA-00308: cannot open archived log ‘/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%u_.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS
would get error below
ORA-01194: file 1 needs more recovery to be
consistent
ORA-01110: data file 1:
‘/u01/app/oracle/oradata/DBdb/system01.dbf’
–resetlogs方式打开数据库：
SQL> alter database open resetlogs;
Database altered.
SQL>
—检查：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———-
———- ———- ———- ———- — ———- ————- ————
———— ————
1 1 1 52428800 512 1 NO CURRENT 4079418
25-JAN-18 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
SQL> select member from v$logfile;
MEMBER
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls
/u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
重建实例然后使用expdp和impdp，将数据导出在导入数据库
SQL> create directory expdp as ‘/opt/app/oracle/oradata’;
Directory created.
然后导出数据重建数据库，在导入数据。
总结：对于当前正在使用的日志的损坏，一般通过备份来修复，如果不行只能采用第二种设置隐含参数_allow_resetlogs_corruption来恢复。
总结
—查询redo信息
col member for a50
set lines 120
SELECT
thread#,
a.sequence#,
a.group#,
TO_CHAR (first_change#, ‘9999999999999999’) “SCN”,
a.status,
MEMBER
FROM
v$log a, v$logfile b
WHERE
a.group# = B.GROUP#
ORDER BY a.sequence# DESC;
1) inactive redo异常
alter
database clear unarchived
logfile group 1;
ALTER
DATABASE drop logfile group 1;
Alter
database open;
alter
database add logfile group 1 (‘/oracle/app/oracle/oradata/cus/redo01.log’) size
200M reuse;
2) current/active redo异常，数据库正常关闭
alter
database clear unarchived logfile group 1;
ALTER
DATABASE drop logfile group 1;
recover
database until cancel;
alterdatabaseopenresetlogs;
3）current/active redo异常，数据库异常关闭
alter
database clear
unarchived logfile group 1;
ALTER DATABASE drop logfile group 1;
recover database until cancel;
如果恢复不了，只能设置_allow_resetlogs_corruption参数跳过检查
alter system set
“_allow_resetlogs_corruption”=true scope=spfile;
shutdown immediate;
startup mount
alter database open resetlogs;
