联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用bbed解决ORA-01178 file N created before last CREATE CONTROLFILE, cannot recreate
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在一些情况下,因为某种原因数据库中的某个数据文件丢失,然后重建控制文件(该文件不存在,重建控制文件肯定不包含该数据文件),这个时候丢失的数据文件所有的归档都存在,尝试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