ORA-01207 old control file完全解決方案

這個錯誤是Oracle db最常見的錯誤了，引起的原因很多，但是最主要的一個原因是資料庫伺服器突然掉電，然後重啟啟動資料庫報錯。
究其這個問題產生的原因，其實就是控制檔案中記錄的db資訊太老，導致資料庫在啟動檢測時出現不一致。
控制檔案中記錄了整個資料庫的全部資訊，具體包括資料檔案的，記錄檔等等。
那麼為什麼會出現控制檔案記錄的資料庫資訊太老呢，原因很簡單：根據oracle db運行原理，資料庫在運行期間，由於檢查點發生等原因會不斷的更新控制檔案，同時資料庫在關閉和重啟過程中都會更新控制檔案的內容，但是資料庫伺服器突然的掉電，會導致當前的db資訊無法適時更新到控制檔案中，再次啟動資料庫後，當oracle檢測控制檔案和其它檔案資訊是否一致時，就出現了這個錯誤。

那麼解決這個問題的方法有兩個：

方法1：
u        主導思想：建立控制檔案，然後open資料庫。
u        具體步驟：
1.startup mount
2.alter database backup controlfile to trace
3.create a control file creation script from the trace file,
and use the noresetlogs option
4.shutdown
5.startup nomount
Use the script generated by the 'backup controlfile to trace' command above
creaet the control file
6.recover database
7.alter database open
u        步驟示範：
[oracle@localhost orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 19 15:26:07 2006
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> create table gaojf as select * from all_objects;
Table created.
SQL> insert into gaojf select * from gaojf;
49390 rows created.
SQL> /
98780 rows created.
…………………………………
1580480 rows created.
SQL> commit;
Commit complete.
SQL> shutdown abort   
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -Production
With the Partitioning, OLAP and Data Mining options

類比ORA-01207錯誤很簡單，這裡不再說明，然後接著如下：
[oracle@localhost orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 19 15:26:47 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                 1218316 bytes
Variable Size             71305460 bytes
Database Buffers           92274688bytes
Redo Buffers               2973696 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file
出現了ORA-01207錯誤：
SQL> shutdown abort
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -Production
With the Partitioning, OLAP and Data Mining options
首先刪除old control file，其實也可以不刪除，個人習慣，然後重建控制檔案。
重建控制檔案可以在資料庫到mount狀態下執行alter database backup controlfile to trace產生sql檔案，具體操作很簡單，不再描述。
IXDBA.NET社區論壇
[oracle@localhost orcl]$ rm -rf control0*
[oracle@localhost orcl]$ vi create.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/free/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/free/oracle/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/free/oracle/oradata/orcl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/free/oracle/oradata/orcl/system01.dbf',
  '/free/oracle/oradata/orcl/undotbs01.dbf',
  '/free/oracle/oradata/orcl/sysaux01.dbf',
  '/free/oracle/oradata/orcl/users01.dbf',
  '/free/oracle/oradata/orcl/gaojfdb.dbf'
CHARACTER SET AL32UTF8
;                            
[oracle@localhost orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 19 15:28:00 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> @create
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                 1218316 bytes
Variable Size             71305460 bytes
Database Buffers           92274688bytes
Redo Buffers               2973696 bytes

Control file created.
SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'
不能直接open，看來有需要恢複的，執行recover database；
SQL> recover database;
Media recovery complete.
看來是在讀取redo file進行前滾，出現這個現象是由於在上面的操作中，我在添加測試資料完畢後，執行了commit命令，然後直接執行了shutdown abort的緣故，所以在重啟以後資料庫要前滾，從redo file中恢複資料。
一般資料庫伺服器在突然掉電後，也就是相當於執行了shutdown abort的操作。所以必須在建立控制檔案後，執行recoverdatabase。
如果是正常關閉的資料庫，可以直接open，無需recover。
此時後台進程執行前滾日誌資訊如下：
ALTER DATABASE RECOVER  database  
Media Recovery Start
Sun Nov 19 15:28:23 2006
Recovery of Online Redo Log: Thread 1 Group 1 Seq 22 Reading mem 0
  Mem# 0 errs 0: /free/oracle/oradata/orcl/redo01.log
Sun Nov 19 15:28:29 2006
Recovery of Online Redo Log: Thread 1 Group 2 Seq 23 Reading mem 0
  Mem# 0 errs 0: /free/oracle/oradata/orcl/redo02.log
Sun Nov 19 15:28:38 2006
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
  Mem# 0 errs 0: /free/oracle/oradata/orcl/redo03.log
Sun Nov 19 15:28:40 2006
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER  database  
此時可以開啟資料庫
SQL> alter database open;
SQL> select count(*) from gaojf;
  COUNT(*)
----------
   3160960

SQL>

方法2：
類比ORA-01207錯誤很簡單，不再描述：

u        思路：用舊的控制檔案恢複，最後用resetlogs開啟資料庫。
u        具體步驟：
1：startup mount;
2：recover database using backup controlfile untilcancel;
然後根據情況，指定archive log和redo file。
3：alter database open resetlogs;
u        操作示範：
oracle@linux:/free/oracle/oradata/orcl> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 19 13:57:512006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                 
1218316 bytes
Variable Size             88082676 bytes
Database Buffers           75497472bytes
Redo Buffers               2973696 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file
執行恢複：

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 891145 generated at 11/18/2006 06:02:11 needed for thread 1
ORA-00289: suggestion :
/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_47_%u_.arc

ORA-00280: change 891145 for thread 1 is in sequence #47

Specify log: {=suggested | filename | AUTO | CANCEL}
auto （由於o1_mf_1_47_%u_.arc這個歸檔日誌，我這裡有這個檔案，所以指定auto，此時應用了一些歸檔日誌）
ORA-00279: change 911145 generated at 11/19/2006 13:49:24 needed for thread 1
ORA-00289: suggestion :
/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_48_%u_.arc
ORA-00280: change 911145 for thread 1 is in sequence #48
ORA-00278: log file
'/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_47_2ozw355r
_.arc' no longer needed for this recovery

ORA-00279: change 911687 generated at 11/19/2006 13:54:36 needed for thread 1
ORA-00289: suggestion :
/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_49_%u_.arc
ORA-00280: change 911687 for thread 1 is in sequence #49
ORA-00278: log file
'/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_48_2ozwf289
_.arc' no longer needed for this recovery


ORA-00308: cannot open archived log
'/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_49_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'


這種恢複方式，不會自動去找聯機redo檔案，所以必須要手工指定！


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 911687 generated at 11/19/2006 13:54:36 needed for thread 1
ORA-00289: suggestion :
/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_49_%u_.arc
ORA-00280: change 911687 for thread 1 is in sequence #49


Specify log: {=suggested | filename | AUTO | CANCEL}
/free/oracle/oradata/orcl/redo01.log
ORA-00310: archived log contains sequence 47; sequence 49 required
ORA-00334: archived log: '/free/oracle/oradata/orcl/redo01.log'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'

需要的不是這個redo檔案，繼續指定！

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 911687 generated at 11/19/2006 13:54:36 needed for thread 1
ORA-00289: suggestion :
/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_49_%u_.arc
ORA-00280: change 911687 for thread 1 is in sequence #49

Specify log: {=suggested | filename | AUTO | CANCEL}
/free/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 48; sequence 49 required

ORA-00334: archived log: '/free/oracle/oradata/orcl/redo02.log'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'

仍然不是，繼續指定!

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 911687 generated at 11/19/2006 13:54:36 needed for thread 1
ORA-00289: suggestion :
/free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_49_%u_.arc
ORA-00280: change 911687 for thread 1 is in sequence #49


Specify log: {=suggested | filename | AUTO | CANCEL}
/free/oracle/oradata/orcl/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from gaojf;
  COUNT(*)
----------
    791392
SQL>
完成恢複，資料沒有丟失，但是redo file被重設，建議馬上備份資料庫一次。

通過以上兩個方法，都可以完成恢複。
但是方法1中重建控制檔案這個方法可能會簡單一些,並且最後可以直接open,這樣以前的備份也可以使用。建議使用這個方法。
方法2中，由於在recover中，還要一個一個的指定redo檔案去試,最後resetlogs資料庫，重設了redo檔案,這樣恢複完成後，以前如果有的備份可能就無法使用了.可能我認為是稍微複雜了，但是恢複資料庫的目的都達到了

更多Oracle相關資訊見Oracle 專題頁面 http://www.bkjia.com/topicnews.aspx?tid=12