Oracle資料庫聯機日誌檔案丟失處理方法

試驗一：用命令清空日誌組方法
1、檢視原來表中資料
SQL>; conn test/test
Connected.
SQL>; select * from test;

       TEL
----------
         1
         2
         3
2、插入新資料
SQL>; insert into test values(4);
1 row created.
SQL>; commit;
Commit complete.
SQL>;
3、正常關閉資料庫
4、利用os command刪除所有redo檔案
5、啟動資料庫
SQL>; startup
ORACLE instance started.
Total System Global Area  353862792 bytes
Fixed Size                   730248 bytes
Variable Size             285212672 bytes
Database Buffers           67108864 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
6、檢視當前日誌狀態
SQL>; select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1          2  104857600          1 YES INACTIVE
       487837 01-9月 -05

         2          1          4  104857600          1 NO  CURRENT
       487955 01-9月 -05

         3          1          3  104857600          1 YES INACTIVE
       487839 01-9月 -05
看來redo01.log不是當前日誌，對於這類非當前日誌可以直接clear,系統會重新自動生成一個redo檔案

7、SQL>; alter database clear logfile group 1;
Database altered.
7、繼續啟動db
SQL>; alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
8、看來redo也得恢復，但是redo02是當前redo,直接clear是不行的
SQL>; alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of thread 1 needs to be archived
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
嘗試clear unarchived logfile group ,報錯：
SQL>; alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
看來他是因為找不到這個檔案，從有效的備份中cp一個過來看看
SQL>; host cp /T3/ORACLE/oradatabak/redo02* /T3/ORACLE/oradata/ORA9

SQL>; alter database clear unarchived logfile group 2;

Database altered.
搞定……….

9、按照oracle的某些做法也是可以的
SQL>; alter database clear unarchived logfile group 1 unrecoverable datafile;

Database altered.

10、但是對於非當前日誌就都可以，下面看看redo03
SQL>;  alter database clear logfile group 3;

Database altered.

結論：
如果資料庫是正常shutdown,非當前日誌都可以直接clear來重新生成，而且不丟失資料，因為正常關閉db,資料已經寫入dbf檔案了。唯獨當前日誌不可以，當前日誌必須首先從有效的備份中複製一個日誌檔案過來，然後用
alter database clear unarchived logfile group n 或 alter database clear unarchived logfile group n,除此之外，還可以用下面的方法來做

方法二：用cancel模式恢復資料庫
前面的出錯提示，步驟都一樣，唯獨恢復的方法不一樣
SQL>; startup
ORACLE instance started.
Total System Global Area  353862792 bytes
Fixed Size                   730248 bytes
Variable Size             285212672 bytes
Database Buffers           67108864 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
看看丟失了哪些redo
SQL>; host ls /T3/ORACLE/oradarta/ORA9/redo*
/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory
看來redo都丟了
直接recover
SQL>; recover database until cancel;
Media recovery complete.
這個時候redo還沒有生成
SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
啟動資料庫
SQL>; alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL>;  alter database open resetlogs;
Database altered.
（注意，這裡必須用resetlogs,否則會錯誤的
SQL>; alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
SQL>;
Resetlogs其實就是根據控制檔案讓系統自動重新生成redo,如果noresetlog的話，就不會重新生成redo,缺少了檔案，db 自然無法啟動）
SQL>;  host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo01.log  /T3/ORACLE/oradata/ORA9/redo02.log  /T3/ORACLE/oradata/ORA9/redo03.log
SQL>;
檢驗
SQL>; select * from test.test;

       TEL
----------
         1
         2
         3
         4
SQL>;
資料一點兒都沒有丟失

結論：
如果資料庫是正常關閉的，用recover database until cancel可以輕鬆恢復或者說重新建立所有的redo，不再區分是否是當前日誌，而且由於正常關閉，不會丟失任何資料，唯一可能丟失的情況就是如果日誌還沒有歸檔
這種恢復方法由於要resetlogs,所以在恢復完成後，日誌清零，以前的備份不再起作用，所以建議立即備份
SQL>; archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /T3/ORACLE/arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL>;

實驗三：透過重新生成控制檔案來恢復redo
前面的都一樣，只是處理方法不一樣
SQL>; startup
ORACLE instance started.

Total System Global Area  353862792 bytes
Fixed Size                   730248 bytes
Variable Size             285212672 bytes
Database Buffers           67108864 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'

SQL>; alter database backup controlfile to trace;

Database altered.

SQL>; shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>;
2、修改一下剛才生成的那個檔案
CREATE CONTROLFILE REUSE DATABASE "ORA9"  RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log'  SIZE 100M,
  GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log'  SIZE 100M,
  GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/T3/ORACLE/oradata/ORA9/system01.dbf',
  '/T3/ORACLE/oradata/ORA9/undotbs01.dbf',
  '/T3/ORACLE/oradata/ORA9/cwmlite01.dbf',
  '/T3/ORACLE/oradata/ORA9/drsys01.dbf',
  '/T3/ORACLE/oradata/ORA9/example01.dbf',
  '/T3/ORACLE/oradata/ORA9/indx01.dbf',
  '/T3/ORACLE/oradata/ORA9/odm01.dbf',
  '/T3/ORACLE/oradata/ORA9/tools01.dbf',
  '/T3/ORACLE/oradata/ORA9/users01.dbf',
  '/T3/ORACLE/oradata/ORA9/xdb01.dbf',
  '/T3/ORACLE/oradata/ORA9/test01.dbf'
CHARACTER SET ZHS16GBK
;
另存為一個指令碼，執行他
SQL>; @clone.sql
Control file created.
SQL>; alter database open resetlogs;
Database altered.
SQL>;
搞定……………

結論：這種方法的關鍵是重新建立控制檔案，後面的步驟和前面的道理一樣的

前面的三種方法都是假設db是正常關閉的，資料已經寫入資料庫檔案中，所以不會由資料存在redo種，所以clear的話也不會有資料丟失

方法四：修改系統引數方法
1、插入資料
SQL>; select * from test;

       TEL
----------
         1
         2
         3
         4

SQL>; insert into test values(5);

1 row created.

SQL>; commit;

Commit complete.

SQL>;
2、強行關閉
SQL>; shutdown abort
ORACLE instance shut down.
SQL>;
3、手工模擬刪除redo
4、啟動db
SQL>; startup
ORACLE instance started.

Total System Global Area  353862792 bytes
Fixed Size                   730248 bytes
Variable Size             285212672 bytes
Database Buffers           67108864 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/T3/ORACLE/oradata/ORA9/redo03.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
5、嘗試使用前3中方法中最簡單的
SQL>; recover database until cancel;
ORA-00279: change 550174 generated at 09/02/2005 16:00:19 needed for thread 1
ORA-00289: suggestion : /T3/ORACLE/arch/1_1.dbf
ORA-00280: change 550174 for thread 1 is in sequence #1
Specify log: {;=suggested | filename | AUTO | CANCEL}
看來不行
6、修改init.ora，加入一行
_allow_resetlogs_corruption=true
7、啟動with pfile
SQL>; startup
ORACLE instance started.
Total System Global Area  320308312 bytes
Fixed Size                   730200 bytes
Variable Size             285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
SQL>; alter database open resetlogs;
Database altered.

SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo01.log  /T3/ORACLE/oradata/ORA9/redo02.log  /T3/ORACLE/oradata/ORA9/redo03.log
8、檢驗資料
SQL>; select * from test.test;

       TEL
----------
         1
         2
         3
         4

SQL>;
看到了吧，我們前面由於執行了SHUTDOWN ABORT,這時候對資料的修改還沒有儲存到資料檔案中，雖然執行了COMMIT,這個時候還在聯機日誌中，等待CKPT觸發DBWR寫入DATAFILE,但是這個時候執行了SHUTDOWN ABORT,redo被刪除後，裡面的資訊也就丟了，造成資料丟失
9、備份，去掉那個引數

試驗五：丟失當前日誌組的成員
1、SQL>; select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
/T3/ORACLE/oradata/ORA9/redo03.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02.log

         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01.log

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01a.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02a.log

         3         ONLINE
/T3/ORACLE/oradata/ORA9/redo03a.log

SQL>; select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1          2  104857600          2 YES INACTIVE
       554599 02-9月 -05

         2          1          3  104857600          2 YES INACTIVE
       554601 02-9月 -05

         3          1          4  104857600          2 NO  CURRENT
       554603 02-9月 -05
SQL>;
3、模擬插入資料
SQL>; conn test/test
Connected.
SQL>; select * from test;

       TEL
----------
         1
         2
         3
         4

SQL>; insert into test values(5);

1 row created.

SQL>; commit
  2  ;

Commit complete.
4、 shutdown db,模擬刪除一個當前日誌成員
$ cd oradata/ORA9
$ ls redo03*
redo03.log   redo03a.log
$ rm redo03a.log
5、啟動db,表面沒有錯誤
SQL>; startup
ORACLE instance started.

Total System Global Area  353862792 bytes
Fixed Size                   730248 bytes
Variable Size             285212672 bytes
Database Buffers           67108864 bytes
Redo Buffers                 811008 bytes
Database mounted.
Database opened.
SQL>;
6、檢視日至成員
SQL>; select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
/T3/ORACLE/oradata/ORA9/redo03.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02.log

         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01.log

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01a.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02a.log

         3 INVALID ONLINE
/T3/ORACLE/oradata/ORA9/redo03a.log
7、刪除出問題的聯機日誌檔案
SQL>; alter database drop logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log';
alter database drop logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '/T3/ORACLE/oradata/ORA9/redo03.log'
ORA-00312: online log 3 thread 1: '/T3/ORACLE/oradata/ORA9/redo03a.log'

SQL>;
看來當前日誌成員是不允許刪除的
SQL>; alter system switch logfile;

System altered.

SQL>; select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1          5  104857600          2 NO  CURRENT
       557687 02-9月 -05

         2          1          3  104857600          2 YES INACTIVE
       554601 02-9月 -05

         3          1          4  104857600          2 YES ACTIVE
       554603 02-9月 -05

SQL>; alter database drop logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log';

Database altered.

SQL>;  alter database add logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log' to group 3;

Database altered.
SQL>; select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
/T3/ORACLE/oradata/ORA9/redo03.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02.log

         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01.log

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01a.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02a.log

         3 INVALID ONLINE
/T3/ORACLE/oradata/ORA9/redo03a.log
看來還得切換一下日至
SQL>; alter system switch logfile;

System altered.
SQL>; select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
/T3/ORACLE/oradata/ORA9/redo03.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02.log

         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01.log

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         1         ONLINE
/T3/ORACLE/oradata/ORA9/redo01a.log

         2         ONLINE
/T3/ORACLE/oradata/ORA9/redo02a.log

         3         ONLINE
/T3/ORACLE/oradata/ORA9/redo03a.log

至此，大功告成…………….

結論：
只要日誌組的member不是一個，出現前面的4種可能性是非常小的，即使出現了也有相應的恢復方法，所以不必驚慌；
如果memer多於1個，即使壞了其中的幾個，也不會影響資料庫的正常啟動，啟動後，再進行相應的操作即可，所以這個時候每天察看 alert.log就顯得非常重要了。

Remen
2005.9.2

come from：

Oracle資料庫聯機日誌檔案丟失處理方法（總結）！

相關文章