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

lpwebnet發表於2013-12-10

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           92274688 bytes

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檔案,具體操作很簡單,不再描述。

[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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17172228/viewspace-1062884/,如需轉載,請註明出處,否則將追究法律責任。

相關文章