无归档情况下使用BBED处理ORA-01113错误

在丢失归档情况下,恢复时常会遇到ORA-01113错误，以下实验模拟表空间offline，然后在丢失归档文件的情况下使用BBED修改文件头信息，最后恢复数据文件；
数据库版本:SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production数据文件信息:SQL> select file#,name, CHECKPOINT_CHANGE#,to_char（CHECKPOINT_TIME,"yyyy-mm-dd hh24:mi:ss"）checkpoint_time,status,BLOCK_SIZE from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS BLOCK_SIZE
---------- ----------------------------------- ------------------ ------------------- ------- ----------
1 /u01/app/oradata/sydb/system01.dbf 3161898 2015-04-13 20:46:37 SYSTEM 8192
2 /u01/app/oradata/sydb/sysaux01.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192
3 /u01/app/oradata/sydb/undotbs01.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192
4 /u01/app/oradata/sydb/users01.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192
5 /disk2/oradata/sydb/tbs01.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192
6 /disk2/oradata/sydb/tbs02.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192
7 /disk2/oradata/sydb/tbs03.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192
8 /tmp/tbs_tmp.dbf 3161898 2015-04-13 20:46:37 ONLINE 8192 offline数据文件7并删除归档模拟ora-01113: SQL> alter database datafile 7 offline;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: "/disk2/oradata/sydb/tbs03.dbf"SQL> select file#,change#,time from v$recover_file;
FILE# CHANGE# TIME
---------- ---------- ---------
7 3161898 13-APR-15 使用BBED需要修改的内容有:
ub4 kscnbas @484 #最后检查的SCN
ub4 kcvcptim @492 #检查时间
ub4 kcvfhcpc @140 #检查点发生次数
ub4 kcvfhccc @148 #未知,但值一直小于kcvfhcpc 1 BBED> info
File# Name Size（blks）
----- ---- ----------
1 /u01/app/oradata/sydb/system01.dbf 92160
2 /u01/app/oradata/sydb/sysaux01.dbf 71680
3 /u01/app/oradata/sydb/undotbs01.dbf 52480
4 /u01/app/oradata/sydb/users01.dbf 1600
5 /disk2/oradata/sydb/tbs01.dbf 221696
6 /disk2/oradata/sydb/tbs02.dbf 14592
7 /disk2/oradata/sydb/tbs03.dbf 2560
8 /tmp/tbs_tmp.dbf 1280 先确认好system表空间文件信息: BBED> set dba 1,1 block 1
DBA 0x00400001 （4194305 1,1）
BLOCK# 1
BBED> print kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x003043a9
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x34453174BBED> dump/v dba 1,1 offset 484 count 30
File: /u01/app/oradata/sydb/system01.dbf （1）
Block: 1 Offsets: 484 to 513 Dba:0x00400001
-------------------------------------------------------
a9433000 00000000 74314534 01000000 l ....t1E4....
ab000000 2e080000 1000866b 0200 l .......k..BBED> dump/v dba 1,1 offset 492 count 30
File: /u01/app/oradata/sydb/system01.dbf （1）
Block: 1 Offsets: 492 to 521 Dba:0x00400001
-------------------------------------------------------
74314534 01000000 ab000000 2e080000 l t1E4........
1000866b 02000000 00000000 0000 l ...k.......…BBED> print kcvfhcpc
ub4 kcvfhcpc @140 0x00000237BBED> dump/v dba 1,1 offset 140 count 30
File: /u01/app/oradata/sydb/system01.dbf （1）
Block: 1 Offsets: 140 to 169 Dba:0x00400001
-------------------------------------------------------
37020000 fe522034 36020000 00000000 l 7...6.......
00000000 00000000 00000000 0000 l ...........…BBED> print kcvfhccc
ub4 kcvfhccc @148 0x00000236BBED> dump/v dba 1,1 offset 148 count 30
File: /u01/app/oradata/sydb/system01.dbf （1）
Block: 1 Offsets: 148 to 177 Dba:0x00400001
-------------------------------------------------------
36020000 00000000 00000000 00000000 l 6...............
00000000 00000000 00000000 0000 l .............. 再确认数据文件7信息: BBED> print kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00303f2a
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x34452bad
ub2 kcvcpthr @496 0x0001
BBED> dump/v dba 7,1 offset 484 count 30
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 484 to 513 Dba:0x01c00001
-------------------------------------------------------
2a3f3000 00000000 ad2b4534 01000000 l *？0.........
a8000000 65000000 10000000 0200 l e.........
<16 bytes per line>BBED> dump/v dba 7,1 offset 492 count 30
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 492 to 521 Dba:0x01c00001
-------------------------------------------------------
ad2b4534 01000000 a8000000 65000000 l ....e...
10000000 02000000 00000000 0000 l ...........…BBED> print kcvfhcpc
ub4 kcvfhcpc @140 0x00000164
BBED> dump/v dba 7,1 offset 140
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 140 to 169 Dba:0x01c00001
-------------------------------------------------------
64010000 fe522034 63010000 00000000 l d...c.......
00000000 00000000 00000000 0000 l ...........…BBED> print kcvfhccc
ub4 kcvfhccc @148 0x00000163BBED> dump/v dba 7,1 offset 148
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 148 to 177 Dba:0x01c00001
-------------------------------------------------------
63010000 00000000 00000000 00000000 l c...............
00000000 00000000 00000000 0000 l .............. 00303f2a 刚好是v$recover_file 中查询到的CHANGE#值SQL> select to_number（"00303f2a","xxxxxxxxx"） from dual;
TO_NUMBER（"00303F2A","XXXXXXXXX"）
---------------------------------
3161898修改值: BBED> modify /x a943 dba 7,1 offset 484
BBED> set offset +2
OFFSET 486
BBED> modify /x 3000
BBED> dump /v dba 7,1 offset 484
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 484 to 513 Dba:0x01c00001
-------------------------------------------------------
a9433000 00000000 ad2b4534 01000000 l ........
a8000000 65000000 10000000 0200 l e......…BBED> modify /x 74314534 dba 7,1 offset 492
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 492 to 521 Dba:0x01c00001
------------------------------------------------------------------------
74314534 01000000 a8000000 65000000 10000000 02000000 00000000 0000BBED> modify /x 37020000 dba 7,1 offset 140
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 140 to 169 Dba:0x01c00001
------------------------------------------------------------------------
37020000 fe522034 63010000 00000000 00000000 00000000 00000000 0000BBED> modify /x 36020000 dba 7,1 offset 148
File: /disk2/oradata/sydb/tbs03.dbf （7）
Block: 1 Offsets: 148 to 177 Dba:0x01c00001
------------------------------------------------------------------------
36020000 00000000 00000000 00000000 00000000 00000000 00000000 0000BBED> sum apply
Check value for File 7, Block 1:
current = 0x7b4d, required = 0x7b4d 确认修改: SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 OFFLINE OFFLINE UNKNOWN ERROR 3163049 13-APR-15
SQL> column name format a35
SQL> select file#,name, CHECKPOINT_CHANGE#,to_char（CHECKPOINT_TIME,"yyyy-mm-dd hh24:mi:ss"）checkpoint_time,status,BLOCK_SIZE from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS BLOCK_SIZE
---------- ----------------------------------- ------------------ ------------------- ------- ----------
1 /u01/app/oradata/sydb/system01.dbf 3163049 2015-04-13 21:11:16 SYSTEM 8192
2 /u01/app/oradata/sydb/sysaux01.dbf 3163049 2015-04-13 21:11:16 ONLINE 8192
3 /u01/app/oradata/sydb/undotbs01.dbf 3163049 2015-04-13 21:11:16 ONLINE 8192
4 /u01/app/oradata/sydb/users01.dbf 3163049 2015-04-13 21:11:16 ONLINE 8192
5 /disk2/oradata/sydb/tbs01.dbf 3163049 2015-04-13 21:11:16 ONLINE 8192
6 /disk2/oradata/sydb/tbs02.dbf 3163049 2015-04-13 21:11:16 ONLINE 8192
7 /disk2/oradata/sydb/tbs03.dbf 3161898 2015-04-13 20:46:37 RECOVER 8192
8 /tmp/tbs_tmp.dbf 3163049 2015-04-13 21:11:16 ONLINE 8192 控制文件的信息没有被修改也无法通过BBED修改,所以此时无法恢复data file 7; SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: "/disk2/oradata/sydb/tbs03.dbf"SQL> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: "/disk2/oradata/sydb/tbs03.dbf"
ORA-01207: file is more recent than control file - old control file 通过重建控制文件恢复数据文件7: SQL> alter database backup controlfile to trace;
SQL> shutdown immediate
SQL>STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SYDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 "/u01/app/oradata/sydb/redo01.log" SIZE 50M BLOCKSIZE 512,
GROUP 2 "/u01/app/oradata/sydb/redo02.log" SIZE 50M BLOCKSIZE 512,
GROUP 3 "/u01/app/oradata/sydb/redo03.log" SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
"/u01/app/oradata/sydb/system01.dbf",
"/u01/app/oradata/sydb/sysaux01.dbf",
"/u01/app/oradata/sydb/undotbs01.dbf",
"/u01/app/oradata/sydb/users01.dbf",
"/disk2/oradata/sydb/tbs01.dbf",
"/disk2/oradata/sydb/tbs02.dbf",
"/disk2/oradata/sydb/tbs03.dbf",
"/tmp/tbs_tmp.dbf"
CHARACTER SET UTF8
;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: "/disk2/oradata/sydb/tbs03.dbf""
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.SQL> column name format a35
SQL> select file#,name, CHECKPOINT_CHANGE#,to_char（CHECKPOINT_TIME,"yyyy-mm-dd hh24:mi:ss"）checkpoint_time,status,BLOCK_SIZE from v$datafile; FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS BLOCK_SIZE
---------- ----------------------------------- ------------------ ------------------- ------- ----------
1 /u01/app/oradata/sydb/system01.dbf 3183058 2015-04-13 22:11:12 SYSTEM 8192
2 /u01/app/oradata/sydb/sysaux01.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192
3 /u01/app/oradata/sydb/undotbs01.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192
4 /u01/app/oradata/sydb/users01.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192
5 /disk2/oradata/sydb/tbs01.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192
6 /disk2/oradata/sydb/tbs02.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192
7 /disk2/oradata/sydb/tbs03.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192
8 /tmp/tbs_tmp.dbf 3183058 2015-04-13 22:11:12 ONLINE 8192 注:不同情况，有可能还是无法打开数据库，比如报（ORA-01113: file 1 needs media recovery）,尝试使用NORESETLOGS方式重建控制文件,然后在执行Media Recovery 。--Then end更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址