标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,679)
- DB2 (22)
- MySQL (73)
- Oracle (1,541)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
标签归档:ORA-01178
使用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