本文共 8563 字，大约阅读时间需要 28 分钟。
ora-01207错误处理
一： ora-01207错误解释
[oracle@oracle ~]$ oerr ora 0120701207, 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.parPassword: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 12800BBED> 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 NoBBED> 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 = 0xe985BBED> 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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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> startupORACLE instance started.Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytesVariable Size 704645464 bytesDatabase Buffers 436207616 bytesRedo Buffers 9371648 bytesDatabase mounted.ORA-01122: database file 5 failed verification checkORA-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> startupORACLE instance started.Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytesVariable Size 704645464 bytesDatabase Buffers 436207616 bytesRedo Buffers 9371648 bytesDatabase mounted.ORA-01122: database file 5 failed verification checkORA-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
--------------------MOUNTEDSQL> recover database using backup controlfile;
ORA-00279: change 1077238 generated at 07/26/2013 17:02:10 needed for thread 1ORA-00289: suggestion : /oracle/CRM/archlog/1_1_821811716.dbfORA-00280: change 1077238 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log '/oracle/CRM/archlog/1_1_821811716.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3 ORA-00308: cannot open archived log '/oracle/CRM/archlog/1_1_821811716.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3 SQL> select group#,archived,sequence#,status from v$Log;GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ---------------- 2 YES 0 UNUSED 1 NO 1 CURRENTSQL> 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_.logSQL> recover database using backup controlfile;
ORA-00279: change 1077238 generated at 07/26/2013 17:02:10 needed for thread 1ORA-00289: suggestion : /oracle/CRM/archlog/1_1_821811716.dbfORA-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_.logLog 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 1086663SQL> 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> startupORACLE instance started.Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytesVariable Size 704645464 bytesDatabase Buffers 436207616 bytesRedo Buffers 9371648 bytesDatabase mounted.ORA-01122: database file 5 failed verification checkORA-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.sqlORACLE instance started.Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytesVariable Size 704645464 bytesDatabase Buffers 436207616 bytesRedo Buffers 9371648 bytesControl 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 1109137SQL> 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 2SQL> alter database open;
alter database open*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: data file 1: '/oracle/CRM/ZBCRM/system01.dbf' SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.
关于更多利用旧控制文件恢复问题见于：
转载地址：http://niwdo.baihongyu.com/