oracle丟失active或current日誌檔案的恢復操作過程

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 00:39:59 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
NO

         2         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

         1         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log
NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !mv /u01/app/oracle/product/10.2.0/oradata/jingyong/redo* /u01/app/oracle

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 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/product/10.2.0/oradata/jingyong/redo01.log'

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1          0   52428800          1 NO  UNUSED
            0 06-JAN-13

         3          1          0   52428800          1 NO  CLEARING_CURRENT
       914164 06-JAN-13

         2          1          0   52428800          1 NO  UNUSED
       914157 06-JAN-13

SQL> recover database until cancel;
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

2.在損失當前日誌,資料庫是異常關閉的
如果在損失當前日誌時,資料庫是異常關閉的,那麼oracle在進行例項恢復時必須要求當前日誌,
否則oracle將無法保證提交成功的資料不丟失(也就是意味著oracle會丟失資料),在這種情況下,
oracle資料庫將無法啟動.

對於這種情況,通常需要從備份中恢復資料檔案,透過應用歸檔日誌檔案向前推演,直到最後一個
完好的日誌檔案,然後可以透過resetlogs啟動資料庫完成恢復.丟失的資料就是損壞的日誌檔案
中的資料.

如果沒有備份,oracle有一類具有特殊作用的隱含引數,其中一個引數是_allow_resetlogs_corruption,來看一下
這個引數的說明:
SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%_allow_resetlogs_%';

NAME                           VALUE          DESCRIB
------------------------------ -------------- --------------------------------------------------
_allow_resetlogs_corruption    FALSE          allow resetlogs even if it will cause corruption

該引數的含義是,允許在破壞一致性的情況下強制重置日誌,開啟資料庫._allow_resetlogs_corruption將
使用所有資料檔案中最舊的scn開啟資料庫,所以通常需要保證system表空間擁有最舊的scn.

在強制開啟資料庫之後,可能因為各種原因伴隨出現ora-00600錯誤,有些可以依據常規途徑解決,看一下下面
的例子:
[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 02:04:02 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

查詢當前的日誌組
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1          2   52428800          1 NO  CURRENT
       936058 07-JAN-13

         2          1          0   52428800          1 YES UNUSED
            0

         3          1          1   52428800          1 NO  INACTIVE
       914918 07-JAN-13

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3 STALE   ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
NO

         2         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

         1         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log
NO

刪除當前日誌組的日誌檔案
SQL> !rm -rf /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 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/product/10.2.0/oradata/jingyong/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

啟動失敗,日誌檔案損壞,在mount狀態,可以查詢v$log檢視,發現此處損壞的是current的日誌檔案
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1          2   52428800          1 NO  CURRENT
       936058 07-JAN-13

         3          1          1   52428800          1 NO  INACTIVE
       914918 07-JAN-13

         2          1          0   52428800          1 YES UNUSED
            0

SQL>

由於active和current日誌沒有完成檢查點，在恢復中需要用到,丟失active和current日誌情況類似.
如果沒有備份,只好使用隱含引數_allow_resetlogs_corruption強制啟動資料庫,設定此引數之後,
在資料庫open過程中,oracle會跳過某些一致性檢查,從而使用權資料庫可能跳過不一致狀態,直接開啟.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 936059 generated at 01/07/2013 02:01:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/flash_recovery_area/JINGYONG/archivelog/2013_01_0
7/o1_mf_1_2_%u_.arc
ORA-00280: change 936059 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
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/product/10.2.0/oradata/jingyong/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

Database altered.

SQL>

如果運氣好的話,資料庫就可以成功開啟了,如果不幸,則可能會遇到一些ora-00600的錯誤
那麼就需要使用其它方法來進行恢復
透過這種方法恢復可以在alert日誌中看見類似以下的日誌資訊:
Mon Jan  7 02:11:19 2013
alter database open resetlogs
Mon Jan  7 02:11:19 2013
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 936059
Resetting resetlogs activation ID 3141718551 (0xbb42d217)
Mon Jan  7 02:11:19 2013

不一致恢復最後恢復到的change號是936059,資訊中說明了強制resetlogs不進行一致性檢查,
可能會導致資料庫損壞,資料庫應當重建.