Oracle重建控制文件的實(shí)例教程

更新時(shí)間：2018年05月28日 08:39:50 作者：AlfredZhao

前些天在做Oracle數(shù)據(jù)庫(kù)恢復(fù)測(cè)試時(shí)，因?yàn)橐恍┊惓２僮鲗?dǎo)致控制文件出了問(wèn)題，數(shù)據(jù)庫(kù)無(wú)法正常使用，下面這篇文章就來(lái)給大家介紹了關(guān)于Oracle重建控制文件的相關(guān)資料，需要的朋友可以參考借鑒，下面來(lái)一起看看吧

前言

控制文件（control file）是一個(gè)相當(dāng)小的文件（最多能增長(zhǎng)到64M左右），其中包含Oracle需要的其他文件的一個(gè)目錄。參數(shù)文件告知實(shí)例控制文件的位置，控制文件則告知示例數(shù)據(jù)庫(kù)和在線重做日志文件的位置?？刂莆募€告知了Oracle其他一些事情，如已發(fā)生檢查點(diǎn)的有關(guān)信息、數(shù)據(jù)庫(kù)名（必須和db_name參數(shù)匹配）、創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)間戳、歸檔重做日志的歷史（有時(shí)這會(huì)讓控制文件變大）、RMAN信息等。

環(huán)境：OEL 5.7 + Oracle 10.2.0.5

背景：在Oracle的運(yùn)維過(guò)程中，時(shí)常會(huì)遇到一些場(chǎng)景是需要重建控制文件才可以解決的。本文的場(chǎng)景可以通過(guò)復(fù)制控制文件到新路徑，運(yùn)行一段時(shí)間后，再用老的控制文件啟動(dòng)數(shù)據(jù)庫(kù)重現(xiàn)。

1.當(dāng)前故障現(xiàn)象

在使用舊的控制文件啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，報(bào)錯(cuò)ORA-01122、ORA-01110、ORA-01207:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size   2098112 bytes
Variable Size  218106944 bytes
Database Buffers  373293056 bytes
Redo Buffers  6287360 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradata/cxywdb/system01.dbf'
ORA-01207: file is more recent than control file - old control file

2.分析故障原因

根據(jù)報(bào)錯(cuò)信息查找MOS文檔:

ORA-1122, ORA-1110, ORA-1207 while open the database after crash (文檔 ID 283927.1)

延伸思考一下，為什么會(huì)這樣？

主要錯(cuò)誤是ORA-01207，利用oerr工具看到Oralce對(duì)這個(gè)錯(cuò)誤的詳細(xì)描述是：

01207, 00000, "file is more recent than control file - old control file"
// *Cause: The control file change sequence number in the data file is
//         greater than the number in the control file. This implies that
//         the wrong control file is being used. Note that repeatedly causing
//         this error can make it stop happening without correcting the real
//         problem. Every attempt to open the database will advance the
//         control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to
//         make the control file current. Be sure to follow all restrictions
//         on doing a backup control file recovery.

一般遇到這種情況，當(dāng)前的控制文件肯定是找不到了。那么就得考慮重建控制文件來(lái)解決，MOS給出的建議也是重建控制文件。

3.重建控制文件

重建控制文件的核心步驟：

3.1 備份控制文件到trace

startup mount
alter database backup controlfile to trace;
oradebug setmypid
oradebug tracefile_name

3.2 啟動(dòng)數(shù)據(jù)庫(kù)到nomount狀態(tài)

shutdown abort
startup nomount;

3.3 確認(rèn)重建控制文件的語(yǔ)句

vi control.sql
CREATE CONTROLFILE REUSE DATABASE "CXYWDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
LOGFILE
 GROUP 5 '/oradata2/cxywdb/redo11.log' SIZE 50M,
 GROUP 6 '/oradata2/cxywdb/redo12.log' SIZE 50M,
 GROUP 7 '/oradata2/cxywdb/redo13.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
 '/oradata/cxywdb/system01.dbf',
 '/oradata/cxywdb/undotbs01.dbf',
 '/oradata/cxywdb/sysaux01.dbf',
 '/oradata/cxywdb/users01.dbf',
 '/oradata/cxywdb/alfred01.dbf',
 '/oradata/cxywdb/alfred02.dbf',
 '/oradata/cxywdb/alfred03.dbf',
 '/oradata/cxywdb/alfred04.dbf',
 '/oradata/cxywdb/alfred05.dbf',
 '/oradata/cxywdb/dbs_i_alfred01.dbf'
CHARACTER SET ZHS16GBK
;

3.4 恢復(fù)并打開(kāi)數(shù)據(jù)庫(kù)

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.

附：實(shí)際解決過(guò)程如下：

SQL> shutdown abort  
ORACLE instance shut down.
SQL> startup mount

ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size     2098112 bytes
Variable Size    218106944 bytes
Database Buffers   373293056 bytes
Redo Buffers    6287360 bytes
Database mounted.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/oracle/admin/cxywdb/udump/cxywdb_ora_3983.trc
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size     2098112 bytes
Variable Size    218106944 bytes
Database Buffers   373293056 bytes
Redo Buffers    6287360 bytes
SQL> @control

Control file created.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章: