redo日誌損壞
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;
alter database open resetlogs;
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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2150752/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle REDO損壞Oracle Redo
- REDO日誌損壞,非歸檔模式資料檔案恢復模式
- 損壞聯機日誌 恢復
- 重做日誌檔案損壞測試
- Oracle online redo log日誌 (當前或非當前日誌) 損壞之後的DB恢復Oracle
- 聯機日誌檔案損壞問題
- INACTIVE日誌組損壞的修復
- 聯機日誌損壞恢復實驗
- 損壞聯機日誌的恢復方法
- 當前聯機日誌檔案損壞
- 線上日誌損壞
- REDO日誌管理
- redo重做日誌管理
- MySQL 日誌 undo | redoMySql
- oracle - redo 損壞或刪除處理方法Oracle
- 一次日誌檔案損壞的恢復
- Sql server日誌損壞後的資料恢復(轉)SQLServer資料恢復
- 當前日誌損壞的案例
- REDO檔案丟失或者損壞的恢復
- inactive狀態日誌組檔案損壞的恢復
- 備份＆恢復之十一：損壞當前聯機日誌
- MySQL重做日誌（redo log）MySql
- redo log日誌挖掘（二）
- redo log日誌挖掘（一）
- 當前日誌損壞的案例(轉)
- 解決物理standby 歸檔日誌損壞ORA-00334
- Oracle資料庫恢復：歸檔日誌損壞案例一則Oracle資料庫
- 備份＆恢復之十：損壞非當前聯機日誌
- SQL Server 2005日誌檔案損壞的處理方法SQLServer
- 聯機日誌檔案損壞後的恢復方法[轉帖]
- Oracle重做日誌檔案損壞或丟失後的恢復Oracle
- redo損壞修復啟動資料庫辦法資料庫
- Online Redo Log損壞處理實驗（上）
- Online Redo Log損壞處理實驗（中）
- Online Redo Log損壞處理實驗（下）
- MySQL redo與undo日誌解析MySql
- MySQL之事務和redo日誌MySql
- redo日誌組資訊查詢