Oracle重建控制文件的实例教程

前言

控制文件（control file）是一个相当小的文件（最多能增长到64m左右），其中包含oracle需要的其他文件的一个目录。参数文件告知实例控制文件的位置，控制文件则告知示例数据库和在线重做日志文件的位置。控制文件还告知了oracle其他一些事情，如已发生检查点的有关信息、数据库名（必须和db_name参数匹配）、创建数据库的时间戳、归档重做日志的历史（有时这会让控制文件变大）、rman信息等。

环境：oel 5.7 + oracle 10.2.0.5

背景：在oracle的运维过程中，时常会遇到一些场景是需要重建控制文件才可以解决的。本文的场景可以通过复制控制文件到新路径，运行一段时间后，再用老的控制文件启动数据库重现。

1.当前故障现象

在使用旧的控制文件启动数据库时，报错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.分析故障原因

根据报错信息查找mos文档:

ora-1122, ora-1110, ora-1207 while open the database after crash (文档 id 283927.1)

延伸思考一下，为什么会这样？

主要错误是ora-01207，利用oerr工具看到oralce对这个错误的详细描述是：

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.

一般遇到这种情况，当前的控制文件肯定是找不到了。那么就得考虑重建控制文件来解决，mos给出的建议也是重建控制文件。

3.重建控制文件

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

3.1 备份控制文件到trace

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

3.2 启动数据库到nomount状态

shutdown abort
startup nomount;

3.3 确认重建控制文件的语句

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 恢复并打开数据库

sql> recover database;
media recovery complete.
sql> alter database open;
database altered.

附：实际解决过程如下：

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.

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。