丟失活動或當前日誌檔案的恢復
oracle透過日誌檔案保證提交成功的資料不丟失,可是在故障中,使用者可能損失了當前的
(current)日誌檔案.這又分為兩種情況:此時資料是正常關閉的和此時資料庫是異常關閉.
1.在損失當前日誌時,資料庫是正常關閉的.
由於關閉資料庫前,oracle會執行全面檢查點,當前日誌在例項恢復中可以不再需要.
下面進行測試(資料庫執行在非歸檔模式下).在oracle9i及以後版本中,是無法對當前
日誌進行clear,需要透過until cancel恢復後再以resetlogs方式開啟
[oracle@jingyong ~]$ sqlplus / as sysdba

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

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

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不進行一致性檢查,
可能會導致資料庫損壞,資料庫應當重建.

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

相關文章