在一些情况下,因为某种原因数据库中的某个数据文件丢失,然后重建控制文件(该文件不存在,重建控制文件肯定不包含该数据文件),这个时候丢失的数据文件所有的归档都存在,尝试alter database create datafile命令创建该数据文件并且利用所有的归档来恢复该数据文件,可是因为控制文件已经重建,无法完成数据文件的重新创建（报ORA-01178错误)
出现这个错误的原因是因为数据文件的创建时间不能早于你的控制文件的创建时间,现在你重建了控制文件,那么很明显你的数据文件的创建时间要早于你的控制文件创建时间,数据库从恢复的原理上判断,你这样做法是违法,终止该操作.解决该问题的主要思路有三个:
1.修改控制文件,让数据库认为你的控制文件创建时间在该数据文件创建之前,通过该欺骗的方法来实现create datafile
2.人工创建好异常数据文件,然后让数据库开始应用归档日志,使得归档的内容能够重现到人工创建的数据文件中,从而找回归档中内容
3.使用logminer挖归档,对应成sql,然后在其他库中重现，此方法只能对于归档比较少的库
因为3比较简单,不做任何操作,因为控制文件结果不是非常了解,修改了N次还是报异常,最后放弃,使用方法2,人工重构文件完成该项工作
模拟环境

SQL> create tablespace ORA01178 DATAFILE 'E:\ORACLE\ORADATA\XIFENFEI\ORA01178.01.DBF'
   2 SIZE 10M AUTOEXTEND ON MAXSIZE 30G;
Tablespace created.
SQL> DROP TABLE T_XIFENFEI;
Table dropped.
SQL> CREATE TABLE T_XIFENFEI TABLESPACE ORA01178
  2  AS
  3  SELECT * FROM DBA_OBJECTS;
Table created.
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     123
Next log sequence to archive   125
Current log sequence           125
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     127
Next log sequence to archive   129
Current log sequence           129
SQL> INSERT INTO T_XIFENFEI
  2  SELECT * FROM DBA_OBJECTS;
68078 rows created.
SQL> /
68078 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI' AND OWNER='SYS';
BYTES/1024/1024
---------------
             24
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
    204234
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1385052 bytes
Variable Size             331353508 bytes
Database Buffers           79691776 bytes
Redo Buffers                6053888 bytes
SQL>  CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
  2        MAXLOGFILES 16
  3        MAXLOGMEMBERS 3
  4        MAXDATAFILES 100
  5        MAXINSTANCES 8
  6        MAXLOGHISTORY 292
  7    LOGFILE
  8      GROUP 1 'E:\ORACLE\ORADATA\XIFENFEI\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9      GROUP 2 'E:\ORACLE\ORADATA\XIFENFEI\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10      GROUP 3 'E:\ORACLE\ORADATA\XIFENFEI\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11    DATAFILE
 12      'E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF',
 13      'E:\ORACLE\ORADATA\XIFENFEI\SYSAUX01.DBF',
 14      'E:\ORACLE\ORADATA\XIFENFEI\UNDOTBS01.DBF',
 15      'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF',
 16      'E:\ORACLE\ORADATA\XIFENFEI\UNDO01.DBF',
 17      'E:\ORACLE\ORADATA\XIFENFEI\CZUM01.DBF',
 18      'E:\ORACLE\ORADATA\XIFENFEI\DUL01.DBF',
 19      'E:\ORACLE\ORADATA\XIFENFEI\DD_DUL.DBF'
 20    CHARACTER SET ZHS16GBK
 21    ;
Control file created.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF'
SQL> RECOVER DATABASE;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.

尝试创建数据文件,报ORA-01178

SQL> alter database create datafile  9 as 'E:\ORACLE\ORADATA\XIFENFEI\ORA01178.01.DBF'
;
alter database create datafile  9 as 'E:\ORACLE\ORADATA\XIFENFEI\ORA01178.01.DBF'
*
ERROR at line 1:
ORA-01178: file 9 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9:
'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00009'
[root@vrh2 ~]# oerr ora 1178
01178, 00000, "file %s created before last CREATE CONTROLFILE, cannot recreate"
// *Cause:  Attempted to use ALTER DATABASE CREATE DATAFILE to recreate a
//          datafile that existed at the last CREATE CONTROLFILE command.
//          The information needed to recreate the file was lost with the
//          control file that existed when the file was added to the database.
// *Action: Find a backup of the file, and recover it. Do incomplete recovery
//          to time before file was originally created.

官方提示,因为控制文件的创建时间比你现在要创建的数据文件时间晚,所以不能完成在此数据文件基础之上完成数据文件的创建工作,如果解决该问题,使用备份的数据文件还原或者使用备份的控制文件然后创建数据文件

继续分析该数据文件

SQL> select name from v$datafile where status='RECOVER';
NAME
--------------------------------------------------------------
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00009
SQL> select FILE#,TS# from v$datafile where status='RECOVER';
     FILE#        TS#
---------- ----------
         9          8
SQL> SELECT FILE# from v$datafile where ts#=8;
     FILE#
----------
         9
SQL> select name from v$tablespace where ts#=8;
NAME
------------------------------
ORA01178
--该表空间只有一个数据文件,不好构造数据该数据文件,增加一数据文件,用于构造
--查询数据文件创建信息
SQL> select ts#,file#,RELFILE#,BLOCKS,crscnwrp,crscnbas from file$;
       TS#      FILE#   RELFILE#     BLOCKS   CRSCNWRP   CRSCNBAS
---------- ---------- ---------- ---------- ---------- ----------
         0          1          1      89600          0          9
         1          2          2      76800          0       2130
         2          3          3      25600          0       3333
         4          4          4        640          0      18379
         5          5          5       1280          0     890563
         6          6          6       1280          0     944810
         7          7          7       1280          0    1289586
         7          8          8       1280          0    1848530
         8          9          9       1280          0    1880412
--创建初始化大小相同在相同表空间数据文件
SQL> alter tablespace ora01178 add datafile 'E:\ORACLE\ORADATA\XIFENFEI\DD_1178.02.dbf' size 10m;
Tablespace altered.
SQL> select ts#,file#,RELFILE#,BLOCKS,crscnwrp,crscnbas from file$;
       TS#      FILE#   RELFILE#     BLOCKS   CRSCNWRP   CRSCNBAS
---------- ---------- ---------- ---------- ---------- ----------
         0          1          1      89600          0          9
         1          2          2      76800          0       2130
         2          3          3      25600          0       3333
         4          4          4        640          0      18379
         5          5          5       1280          0     890563
         6          6          6       1280          0     944810
         7          7          7       1280          0    1289586
         7          8          8       1280          0    1848530
         8          9          9       1280          0    1880412
         8         10         10       1280          0    1901583

到目前,我们创建了一个和故障数据文件在同一个表空间,而且同样初始化大小的数据文件

CP新数据文件
E:\ORACLE\ORADATA\XIFENFEI\DD_1178.02.dbf 为E:\oracle\oradata\XIFENFEI\dd_ora1178.02.dbf

bbed修改相关值
因为在win中block 2其实为数据库的block 1,依次类推

m /x 01004002 block 2 offset 4
m /x 09 block 2 offset 52
m /x 5cb11c00 block 2 offset 100
m /x 09 block 2 offset 368
m /x 5cb11c00 offset 484
…………
m /x 02004002 block 3 offset 4
m /x 09 block 3 offset 20
sum apply block 3
…………

online数据文件

SQL> alter database rename file 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00009'
   2 to 'E:\oracle\oradata\XIFENFEI\dd_ora1178.02.dbf';
Database altered.
SQL> recover datafile 9;
ORA-00279: change 1880412 generated at 07/05/2013 20:59:44 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000125_0805223583.0001
ORA-00280: change 1880412 for thread 1 is in sequence #125
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000125_0805223583.0001
ORA-00279: change 1880816 generated at 07/05/2013 11:51:27 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000126_0805223583.0001
ORA-00280: change 1880816 for thread 1 is in sequence #126
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000126_0805223583.0001
ORA-00279: change 1880819 generated at 07/05/2013 11:51:29 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000127_0805223583.0001
ORA-00280: change 1880819 for thread 1 is in sequence #127
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000127_0805223583.0001
ORA-00279: change 1880824 generated at 07/05/2013 11:51:33 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000128_0805223583.0001
ORA-00280: change 1880824 for thread 1 is in sequence #128
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000128_0805223583.0001
ORA-00279: change 1880827 generated at 07/05/2013 11:51:34 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000129_0805223583.0001
ORA-00280: change 1880827 for thread 1 is in sequence #129
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000129_0805223583.0001
ORA-00279: change 1901078 generated at 07/05/2013 11:57:44 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000130_0805223583.0001
ORA-00280: change 1901078 for thread 1 is in sequence #130
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000130_0805223583.0001
Log applied.
Media recovery complete.
SQL> alter database datafile 9 online;
Database altered.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
    204234
SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI' AND OWNER='SYS';
BYTES/1024/1024
---------------
             24

从这里可以完整的看到,人工利用datafile 10,构造了datafile 9,然后应用归档,完美的找回了归档中的内容.该操作是一个复杂的过程,如果你需要技术支持请联系:
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

一个朋友的测试库出现ORA-01200错误,正好周末比较空闲,随手帮他使用bbed进行了恢复,给广大朋友提供一种解决该问题的方法
数据库启动报错

C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:09:11 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
SQL> connect/as sysdba
已连接到空闲例程。
SQL> startup force
ORACLE 例程已经启动。
Total System Global Area 1071333376 bytes
Fixed Size                  1334380 bytes
Variable Size             318768020 bytes
Database Buffers          746586112 bytes
Redo Buffers                4644864 bytes
数据库装载完毕。
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01200: 87946 的实际文件大小小于 88320 块的正确大小

这里的错误很明显是因为file 1的数据文件头记录block大小为88320个block,而该数据文件的实际大小只有87946个block,所以出现该问题.

dbv检测文件

D:\app\Administrator\oradata\orcl>dbv file=SYSTEM01.DBF
DBVERIFY: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:30:29 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - 开始验证: FILE = SYSTEM01.DBF
DBVERIFY - 验证完成
检查的页总数: 87040
处理的页总数 (数据): 62870
失败的页总数 (数据): 0
处理的页总数 (索引): 11055
失败的页总数 (索引): 0
处理的页总数 (其它): 2437
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 10678
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 980055 (0.980055)

检查发现该数据文件未发现坏块,减小了该数据文件通过bbed恢复异常的风险,数据库最怕就是system中出现很多坏块

使用bbed修改kccfhfsz
因为win的bbed问题,所以拷贝到我的电脑上进行修改

C:\Users\XIFENFEI\Desktop\temp>bbed filename=system01.dbf blocksize=8192
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun May 12 23:27:26 2013
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 2
        BLOCK#          2
--从一台机器中拷贝到另外的机器，实际中的block可能发生改变，因为含block 0
BBED> map
 File: system01.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
 ub4 tailchk                                @8188
BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a
--通过ORA-01200错误报出来的文件头记录大小88320实际就是0x0001578a
BBED> set mode edit
        MODE            Edit
BBED> set count 32
        COUNT           32
BBED> d
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
00590100 00200000 01000300 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 8A570100
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
 8a570100 00200000 01000300 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--通过ORA-01200错误报出来的数据文件实际大小,来修改该文件头的kcvfhhdr.kccfhfsz值,也可以通过文件实际大小计算出来
BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a
BBED> sum apply
Check value for File 0, Block 2:
current = 0x0f79, required = 0x0f79
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

打开数据库

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
数据库已更改。