Oracle数据库宕机，启动数据库报错ORA-01122 ORA-01110 ORA-01207问题处理方法

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维

一个测试库异常宕机，启动数据时，报错如下：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/u01/oradata/dbbbed/tbst01.dbf'
ORA-01207: file is more recent than control file - old control file

一、关于ORA-01207错误解释

$ oerr ora 01207
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.

从上面的解释看出，造成ora-01207错误的是由于数据文件头部记录控制seq号比控制文件中记录的值大。

二、问题排查

–查看数据文件头部记录的控制文件seq号
说明：数据文件头部记录的控制文件seq号位于 offset 40，如下:

BBED> set file 5 block 1
	FILE#          	5
	BLOCK#         	1

BBED> p kcvfh
....................................
      ub4 kccfhcsq                          @40       0x00000f9b  转换为10进制为3995

–bbed查看文件头部信息和通过表x$kcvfh查询到的值一样(fhcsq为控制文件seq号）

SQL> select hxfil,fhcsq,fhscn,fhrba_seq,fhcpc from x$kcvfh;  

     HXFIL	FHCSQ FHSCN		FHRBA_SEQ      FHCPC
---------- ---------- ---------------- ---------- ----------
	 1	 3739 1978449		       42	 306
	 2	 3739 1978449		       42	 308
	 3	 3739 1978449		       42	 227
	 4	 3739 1978449		       42	 305
	 5	 3995 1978449		       42	 295              <<<<<<<<<<<<fhcsq为3995
	 6	 3739 1978449		       42	 181
	 7	 3739 1978449		       42	 170

–查看控制文件记录的seq号

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CURRENT 		 3747		  1978393	     1978449

控制文件记录的seq号为3747，数据文件头部记录的控制文件seq号（3995）大于控制文件记录的seq号（3747）

三：问题处理

由上面排查，可以知道是由于数据文件头部的记录的控制文件seq号比控制文件的seq号大，那么针对这个错误提供如下两种处理方法：
方法一：重建控制文件，因为在重建控制文件的过程中，控制文件中记录的seq号的值会取自于数据文件头部，从而达到一致性。
方法二：利用bbed直接修改数据文件头部记录的控制文件seq号，将其改成比控制文件记录的seq号小。

方法一处理过程：

–生成控制文件

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name 
/u01/app/oracle/diag/rdbms/dbbbed/dbbbed/trace/dbbbed_ora_2086.trc
SQL> alter database backup controlfile to trace;

—从trace文件找到重建控制文件语句

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBBBED" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/dbbbed/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/dbbbed/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/dbbbed/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/dbbbed/system01.dbf',
  '/u01/oradata/dbbbed/sysaux01.dbf',
  '/u01/oradata/dbbbed/undotbs01.dbf',
  '/u01/oradata/dbbbed/users01.dbf',
  '/u01/oradata/dbbbed/tbst01.dbf',
  '/u01/oradata/dbbbed/szr01.dbf',
  '/u01/oradata/dbbbed/mssm01.dbf'
CHARACTER SET AL32UTF8
;

RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/dbbbed/temp01.dbf' REUSE;

方法二处理过程：

BBED> set file 5 block 1
	FILE#          	5
	BLOCK#         	1

BBED> dump /v offset 40 count 32
 File: /u01/oradata/dbbbed/tbst01.dbf (5)
 Block: 1       Offsets:   40 to   71  Dba:0x01400001
-------------------------------------------------------
 9b0f0000 00050000 00200000 05000300 l ......... ......
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

--将其改成3739（16进制是e9b，反序后就是9b0e）
BBED> modify /x 9b0e offset 40
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oradata/dbbbed/tbst01.dbf (5)
 Block: 1                Offsets:   40 to   71           Dba:0x01400001
------------------------------------------------------------------------
 9b0e0000 00050000 00200000 05000300 00000000 00000000 00000000 00000000 

 <32 bytes per line>
BBED> p offset 40
kcvfh.kcvfhhdr.kccfhcsq
-----------------------
ub4 kccfhcsq                                @40       0x00000e9b

BBED> sum apply
Check value for File 5, Block 1:
current = 0x22b0, required = 0x22b0

–再次查询

SQL> select hxfil,fhcsq,fhscn,fhrba_seq,fhcpc from x$kcvfh;

     HXFIL	FHCSQ FHSCN		FHRBA_SEQ      FHCPC
---------- ---------- ---------------- ---------- ----------
	 1	 3739 1978449		       42	 306
	 2	 3739 1978449		       42	 308
	 3	 3739 1978449		       42	 227
	 4	 3739 1978449		       42	 305
	 5	 3739 1978449		       42	 295                     <<<<<<已更改为 3739
	 6	 3739 1978449		       42	 181
	 7	 3739 1978449		       42	 170

7 rows selected.

–打开数据库

SQL> alter database open;

Database altered.

最后数据库正常打开。