ora-01207错误处理
一： ora-01207错误解释
[oracle@oracle ~]$ 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号比控制文件中记录的该值大。
二：通过bbed修改数据文件头部的seq号模拟该错误，过程如下：
说明：数据文件头部记录的控制文件seq号位于，如下所示
ub4 kccfhcsq @40 0x000003df 转换为10进制为991
和通过表x$kcvfh查询到的值一样(fhcsq为控制文件seq号）
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 991 1077238 1
2 991 1077238 1
3 991 1077238 1
4 991 1077238 1
5 991 1077238 1
模拟过程如下：
[oracle@oracle ~]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Jul 31 14:38:26 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /oracle/CRM/ZBCRM/system01.dbf 89600
2 /oracle/CRM/ZBCRM/sysaux01.dbf 65280
3 /oracle/CRM/ZBCRM/undotbs01.dbf 8960
4 /oracle/CRM/ZBCRM/users01.dbf 640
5 /oracle/CRM/ZBCRM/sysaux02.dbf 12800
BBED> show all
FILE# 1
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /oracle/CRM/ZBCRM/system01.dbf
BIFILE bifile.bbd
LISTFILE /oracle/file
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> dump /v dba 5,1 offset 40 count 30
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets: 40 to 69 Dba:0x01400001
-------------------------------------------------------
df030000 00320000 00200000 05000300 l ?...2... ......
00000000 00000000 00000000 0000 l ..............
<16 bytes per line>
BBED> modify /x df04
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets: 40 to 69 Dba:0x01400001
------------------------------------------------------------------------
df040000 00320000 00200000 05000300 00000000 00000000 00000000 0000
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1:
current = 0xe985, required = 0xe985
BBED> exit
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 31 14:39:48 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 991 1077238 1
2 991 1077238 1
3 991 1077238 1
4 991 1077238 1
5 1247 1077238 1 SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CURRENT 1074 1086625 1077238
上面模拟了数据文件头部记录的控制文件seq号大于控制文件记录的seq号，下面重启数据库触发报错
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytes
Variable Size 704645464 bytes
Database Buffers 436207616 bytes
Redo Buffers 9371648 bytes
Database mounted.
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oracle/CRM/ZBCRM/sysaux02.dbf'
ORA-01207: file is more recent than control file - old control file
三 对于ora-01207错误的处理
既然已经知道ora-01207的错误是由于数据文件头部的记录的控制文件seq号比控制文件的seq号大，那么针对这个错误提供如下两种处理方法
方法一：
1 利用归档和联机日志推进控制文件中的相关信息，即通过执行命令recover database using backup controlfile来实现。不过这个命令能顺利执行的前提
是controlfile_change#值所在的归档及其之后所需归档能够利用进行恢复。
2 恢复过程如下：
SQL> startup
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytes
Variable Size 704645464 bytes
Database Buffers 436207616 bytes
Redo Buffers 9371648 bytes
Database mounted.
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oracle/CRM/ZBCRM/sysaux02.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> recover database using backup controlfile;
ORA-00279: change 1077238 generated at 07/26/2013 17:02:10 needed for thread 1
ORA-00289: suggestion : /oracle/CRM/archlog/1_1_821811716.dbf
ORA-00280: change 1077238 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/CRM/archlog/1_1_821811716.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/CRM/archlog/1_1_821811716.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select group#,archived,sequence#,status from v$Log;
GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
2 YES 0 UNUSED
1 NO 1 CURRENT
SQL> col member for a60
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------
2 /oracle/CRM/ZBCRM/log1/ZBCRM/onlinelog/o1_mf_2_8z4grccs_.log
2 /oracle/CRM/ZBCRM/log2/ZBCRM/onlinelog/o1_mf_2_8z4grd0x_.log
1 /oracle/CRM/ZBCRM/log1/ZBCRM/onlinelog/o1_mf_1_8z4gr4yz_.log
1 /oracle/CRM/ZBCRM/log2/ZBCRM/onlinelog/o1_mf_1_8z4gr5p0_.log
SQL> recover database using backup controlfile;
ORA-00279: change 1077238 generated at 07/26/2013 17:02:10 needed for thread 1
ORA-00289: suggestion : /oracle/CRM/archlog/1_1_821811716.dbf
ORA-00280: change 1077238 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/CRM/ZBCRM/log1/ZBCRM/onlinelog/o1_mf_1_8z4gr4yz_.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CURRENT 1112 1086859 1086663
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 1102 1086663 1
2 1102 1086663 1
3 1102 1086663 1
4 1102 1086663 1
5 1102 1086663 1
方法二：重建控制文件。因为在重建控制文件的过程中，控制文件中记录的seq号的基值会取自于数据文件头部，从而达到一致性。其次通过重建控制文件还可以规避recover database using backup controlfile 过程中遭遇归档丢失问题。
SQL> startup
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytes
Variable Size 704645464 bytes
Database Buffers 436207616 bytes
Redo Buffers 9371648 bytes
Database mounted.
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oracle/CRM/ZBCRM/sysaux02.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @/oracle/noresetlogs.sql
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytes
Variable Size 704645464 bytes
Database Buffers 436207616 bytes
Redo Buffers 9371648 bytes
Control file created.
SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CREATED 1379 0 1109137
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 1135 1109138 2
2 1135 1109138 2
3 1135 1109138 2
4 1135 1109138 2
5 1375 1109138 2
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/CRM/ZBCRM/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
