Oracle 重建控制文件一例

时间：2022-07-11 15:19:55

环境：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.

相关文章