BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。
今天翻出來一篇幾年前寫做的 bbed 的文章。
貼在這裡。
透過 bbed , 修改oracle 資料檔案的 scn號, 達到資料庫的不完全恢復。 搶救資料。
------------------正文開始 ···························
透過bbed 修改資料檔案的 scn 來完成資料庫的回覆測試
1. oracle 9i 10g 的 unix 版本提供了 bbed 工具,但是沒有編譯要自己編譯
> Cd $ORACLE_HOME/rdbms/lib
> make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed
就會在$ORACLE_HOME/bin/ 下生成 bbed 工具。
Ip : 192.168.10.208
Os : redhat linux AS 5.4
Oracle: 10.2.0.4 64 位
Sqlplus "/ as syssba"
Sql> create tablespace test datafile '/data/oracle/oradata/testdb/TEST.dbf' size 10m;
Sql> create table test tablespace test as select * from dba_tables;
Sql > select count(*) from test;
COUNT(*)
----------
1536
Sql > Alter database datafile '/data/oracle/oradata/testdb/TEST.dbf' offline ;
Sql > alter system switch logfile ;
Sql > /
System altered.
Sql > /
System altered.
Sql > /
System altered.
Exit ;
[oracle@testdb dbs]$ cd /data/oracle/oradata/testdb/archive
[oracle@testdb archive]$ ls
1_10_700654442.dbf 1_3_700654442.dbf 1_6_700654442.dbf 1_9_700654442.dbf
1_11_700654442.dbf 1_4_700654442.dbf 1_7_700654442.dbf archive1_2_700654442.dbf
1_12_700654442.dbf 1_5_700654442.dbf 1_8_700654442.dbf
[oracle@testdb archive]$
[oracle@testdb archive.b]$ cd ..
[oracle@testdb testdb]$ ll
× Ü ¼ Æ 1826504
drwxr-xr-x 2 oracle oinstall 4096 10-20 11:56 archive
drwxr-xr-x 2 oracle oinstall 4096 10-20 10:01 archivelog
-rw-r----- 1 oracle oinstall 7389184 10-20 11:58 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 10-20 10:34 control01.ctl.b
-rw-r----- 1 oracle oinstall 7389184 10-20 11:58 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 10-20 10:34 control02.ctl.b
-rw-r----- 1 oracle oinstall 7389184 10-20 11:58 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 10-20 10:34 control03.ctl.b
-rw-r----- 1 oracle oinstall 104858112 10-20 11:56 redo01.log
-rw-r----- 1 oracle oinstall 104858112 10-20 11:58 redo02.log
-rw-r----- 1 oracle oinstall 104858112 10-20 11:37 redo03.log
-rw-r----- 1 oracle oinstall 262152192 10-20 11:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 10-20 11:57 system01.dbf
-rw-r----- 1 oracle oinstall 503324672 10-20 10:41 system01.dbf.b
-rw-r----- 1 oracle oinstall 20979712 10-19 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 10-20 11:37 TEST.dbf
-rw-r----- 1 oracle oinstall 104865792 10-20 10:42 TEST.dbf.b
-rw-r----- 1 oracle oinstall 26222592 10-20 11:57 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 10-20 11:37 users01.dbf
把歸檔日誌轉移造成資料問題件無法online
[oracle@testdb testdb]$ mv archive archive.b
[oracle@testdb testdb]$ ls archive.b/
1_10_700654442.dbf 1_3_700654442.dbf 1_6_700654442.dbf 1_9_700654442.dbf
1_11_700654442.dbf 1_4_700654442.dbf 1_7_700654442.dbf archive1_2_700654442.dbf
1_12_700654442.dbf 1_5_700654442.dbf 1_8_700654442.dbf
SQL*Plus: Release 10.2.0.4.0 - Production on ÐÇÆÚ ¶ þ 10ÔÂ 20 10:28:19 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database datafile '/data/oracle/oradata/testdb/TEST.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oracle/oradata/testdb/TEST.dbf'
SQL> ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/data/oracle/oradata/testdb/system01.dbf'
Sql > shutdown immediate ;
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
資料檔案無法online 了,因為資料庫在對資料檔案做 online 的時候是不修改檔案頭的,只修改 controlfile 的 狀態或者 scn 然後再資料檔案 online 的時候執行恢復操作 .
這裡直接改 datafile 的 datafile header 中的 checkpoint scn 和 RBA 是不行的!
因為 oracle 在對某個 datafile 做 offline 的時候實際上是相當於 offline immediate ,此時不會改 datafile header 中的內容,而只是修改 control 檔案,等到再想 online 的時候一定要做 recovery ,對這一點, 403e 也有描述:
Offline normal (tablespace) :
1 、 Checkpoints data blocks of tablespace
2 、 Updates file headers and control file
Offline immediate (tablespace or data file) :
1 、 Only update control file
2 、 Data files require recovery
BBED 無法改 control 檔案,所以上述這條只改 datafile header 中的 checkpoint scn 和 RBA 的路是走不通的。
這裡我採取的方法是先改 datafile header ,再重建控制檔案,即可強制恢復 offline 狀態的 datafile ,在修改上述 offline 的 datafile header 的過程中我是透過比對 system01.dbf 的 datafile header 來修改 offline datafile 的 datafile header ,完整的恢復過程可見附帶的檔案 session.log
其中重要的步驟如下:
1 、 先透過比對 system01.dbf 的 datafile header 的內容來修改 datafile TEST.dbf 的 datafile header 。
2 、 重建控制檔案
3 、 用帶 *._allow_resetlogs_corruption=TRUE 的 pfile 啟庫到 mount 狀態
4 、 用 open resetlogs 強制開啟上述資料庫
5 、 最後 shutdown immediate 再 startup
[oracle@testdb ~]$ more par.bbd
1 /data/oracle/oradata/testdb/system01.dbf 503324672
2 /data/oracle/oradata/testdb/TEST.dbf 104865792
[oracle@testdb ~]$
[oracle@testdb ~]$ (bbed listfile=par.bbd) |tee test1.log
Password:
Bbed 修改資料檔案 TEST.dbf 檔案頭的 scn
Password:
BBED: Release 2.0.0.0.0 - Limited Production on 星期二 10 月 20 12:10:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set FILE# 1
BBED> set BLOCK# 1
BBED> set FILE# 1
BBED> SHOW
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /data/oracle/oradata/testdb/ystem01.dbf
BIFILE bifile.bbd
LISTFILE par.bbd
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> map
File: /data/oracle/oradata/testdb/ystem01.dbf(1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes @0
ub4 tailchk @8188
BBED> p kcvfh
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00400001
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xe92a
ub2 spare3_kcbh @18 0x0000
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0a200300
ub4 kccfhdbi @28 0x940e90e7
text kccfhdbn[0] @32 T
text kccfhdbn[1] @33 E
text kccfhdbn[2] @34 S
text kccfhdbn[3] @35 T
text kccfhdbn[4] @36 D
text kccfhdbn[5] @37 B
text kccfhdbn[6] @38
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0xd201002a
ub4 kccfhfsz @44 0x0000f000
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0001
ub2 kccfhtyp @54 0x0003
ub4 kccfhacid @56 0x00000000
ub4 kccfhcks @60 0x00000000
text kccfhtag[0] @64
text kccfhtag[1] @65
text kccfhtag[2] @66
text kccfhtag[3] @67
text kccfhtag[4] @68
text kccfhtag[5] @69
text kccfhtag[6] @70
text kccfhtag[7] @71
text kccfhtag[8] @72
text kccfhtag[9] @73
text kccfhtag[10] @74
text kccfhtag[11] @75
text kccfhtag[12] @76
text kccfhtag[13] @77
text kccfhtag[14] @78
text kccfhtag[15] @79
text kccfhtag[16] @80
text kccfhtag[17] @81
text kccfhtag[18] @82
text kccfhtag[19] @83
text kccfhtag[20] @84
text kccfhtag[21] @85
text kccfhtag[22] @86
text kccfhtag[23] @87
text kccfhtag[24] @88
text kccfhtag[25] @89
text kccfhtag[26] @90
text kccfhtag[27] @91
text kccfhtag[28] @92
text kccfhtag[29] @93
text kccfhtag[30] @94
text kccfhtag[31] @95
ub4 kcvfhrdb @96 0x00400179
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x00000005
ub2 kscnwrp @104 0x0000
ub4 kcvfhcrt @108 0x26b0deac
ub4 kcvfhrlc @112 0x29c48816
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000a7164
ub2 kscnwrp @120 0x0000
ub4 kcvfhbti @124 0x00000000
struct kcvfhbsc, 8 bytes @128
ub4 kscnbas @128 0x00000000
ub2 kscnwrp @132 0x0000
ub2 kcvfhbth @136 0x0000
ub2 kcvfhsta @138 0x2004 (KCVFHOFZ)
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000a764a
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x29c48cdd
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000002
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub4 kcvfhcpc @140 0x00000049
ub4 kcvfhrts @144 0x29c486bf
ub4 kcvfhccc @148 0x00000048
struct kcvfhbcp, 36 bytes @152
struct kcvcpscn, 8 bytes @152
ub4 kscnbas @152 0x00000000
ub2 kscnwrp @156 0x0000
ub4 kcvcptim @160 0x00000000
ub2 kcvcpthr @164 0x0000
union u, 12 bytes @168
struct kcvcprba, 12 bytes @168
ub4 kcrbaseq @168 0x00000000
ub4 kcrbabno @172 0x00000000
ub2 kcrbabof @176 0x0000
ub1 kcvcpetb[0] @180 0x00
ub1 kcvcpetb[1] @181 0x00
ub1 kcvcpetb[2] @182 0x00
ub1 kcvcpetb[3] @183 0x00
ub1 kcvcpetb[4] @184 0x00
ub1 kcvcpetb[5] @185 0x00
ub1 kcvcpetb[6] @186 0x00
ub1 kcvcpetb[7] @187 0x00
ub4 kcvfhbhz @312 0x00000000
struct kcvfhxcd, 16 bytes @316
ub4 space_kcvmxcd[0] @316 0x00000000
ub4 space_kcvmxcd[1] @320 0x00000000
ub4 space_kcvmxcd[2] @324 0x00000000
ub4 space_kcvmxcd[3] @328 0x00000000
word kcvfhtsn @332 0
ub2 kcvfhtln @336 0x0006
text kcvfhtnm[0] @338 S
text kcvfhtnm[1] @339 Y
text kcvfhtnm[2] @340 S
text kcvfhtnm[3] @341 T
text kcvfhtnm[4] @342 E
text kcvfhtnm[5] @343 M
text kcvfhtnm[6] @344
text kcvfhtnm[7] @345
text kcvfhtnm[8] @346
text kcvfhtnm[9] @347
text kcvfhtnm[10] @348
text kcvfhtnm[11] @349
text kcvfhtnm[12] @350
text kcvfhtnm[13] @351
text kcvfhtnm[14] @352
text kcvfhtnm[15] @353
text kcvfhtnm[16] @354
text kcvfhtnm[17] @355
text kcvfhtnm[18] @356
text kcvfhtnm[19] @357
text kcvfhtnm[20] @358
text kcvfhtnm[21] @359
text kcvfhtnm[22] @360
text kcvfhtnm[23] @361
text kcvfhtnm[24] @362
text kcvfhtnm[25] @363
text kcvfhtnm[26] @364
text kcvfhtnm[27] @365
text kcvfhtnm[28] @366
text kcvfhtnm[29] @367
ub4 kcvfhrfn @368 0x00000001
struct kcvfhrfs, 8 bytes @372
ub4 kscnbas @372 0x00000000
ub2 kscnwrp @376 0x0000
ub4 kcvfhrft @380 0x00000000
struct kcvfhafs, 8 bytes @384
ub4 kscnbas @384 0x00000000
ub2 kscnwrp @388 0x0000
ub4 kcvfhbbc @392 0x00000000
ub4 kcvfhncb @396 0x00000000
ub4 kcvfhmcb @400 0x00000000
ub4 kcvfhlcb @404 0x00000000
ub4 kcvfhbcs @408 0x00000000
ub2 kcvfhofb @412 0x000a
ub2 kcvfhnfb @414 0x000a
ub4 kcvfhprc @416 0x29c3236a
struct kcvfhprs, 8 bytes @420
ub4 kscnbas @420 0x000932c5
ub2 kscnwrp @424 0x0000
struct kcvfhprfs, 8 bytes @428
ub4 kscnbas @428 0x00000000
ub2 kscnwrp @432 0x0000
ub4 kcvfhtrt @444 0x00000000
BBED> set FILE# 2
BBED> set BLOCK# 1
BBED> set FILE# 2
BBED> SHOW
BLOCK# 1
OFFSET 0
DBA 0x00800001 (8388609 2,1)
FILENAME /home/oracle/TEST.dbf
BIFILE bifile.bbd
LISTFILE par.bbd
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> MAP
File: /home/oracle/TEST.dbf (2)
Block: 1 Dba:0x00800001
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes @0
ub4 tailchk @8188
BBED> p kcvfh
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01400001
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xea92
ub2 spare3_kcbh @18 0x0000
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0a200300
ub4 kccfhdbi @28 0x940e90e7
text kccfhdbn[0] @32 T
text kccfhdbn[1] @33 E
text kccfhdbn[2] @34 S
text kccfhdbn[3] @35 T
text kccfhdbn[4] @36 D
text kccfhdbn[5] @37 B
text kccfhdbn[6] @38
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0x000001a1
ub4 kccfhfsz @44 0x00003200
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0005
ub2 kccfhtyp @54 0x0003
ub4 kccfhacid @56 0x00000000
ub4 kccfhcks @60 0x00000000
text kccfhtag[0] @64
text kccfhtag[1] @65
text kccfhtag[2] @66
text kccfhtag[3] @67
text kccfhtag[4] @68
text kccfhtag[5] @69
text kccfhtag[6] @70
text kccfhtag[7] @71
text kccfhtag[8] @72
text kccfhtag[9] @73
text kccfhtag[10] @74
text kccfhtag[11] @75
text kccfhtag[12] @76
text kccfhtag[13] @77
text kccfhtag[14] @78
text kccfhtag[15] @79
text kccfhtag[16] @80
text kccfhtag[17] @81
text kccfhtag[18] @82
text kccfhtag[19] @83
text kccfhtag[20] @84
text kccfhtag[21] @85
text kccfhtag[22] @86
text kccfhtag[23] @87
text kccfhtag[24] @88
text kccfhtag[25] @89
text kccfhtag[26] @90
text kccfhtag[27] @91
text kccfhtag[28] @92
text kccfhtag[29] @93
text kccfhtag[30] @94
text kccfhtag[31] @95
ub4 kcvfhrdb @96 0x00000000
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x0009dd40
ub2 kscnwrp @104 0x0000
ub4 kcvfhcrt @108 0x29c37de2
ub4 kcvfhrlc @112 0x29c3236a
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000932c5
ub2 kscnwrp @120 0x0000
ub4 kcvfhbti @124 0x00000000
struct kcvfhbsc, 8 bytes @128
ub4 kscnbas @128 0x00000000
ub2 kscnwrp @132 0x0000
ub2 kcvfhbth @136 0x0000
ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000a6bd9
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x29c471dc
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000002
ub4 kcrbabno @504 0x00016142
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub4 kcvfhcpc @140 0x00000005
ub4 kcvfhrts @144 0x00000000
ub4 kcvfhccc @148 0x00000004
struct kcvfhbcp, 36 bytes @152
struct kcvcpscn, 8 bytes @152
ub4 kscnbas @152 0x00000000
ub2 kscnwrp @156 0x0000
ub4 kcvcptim @160 0x00000000
ub2 kcvcpthr @164 0x0000
union u, 12 bytes @168
struct kcvcprba, 12 bytes @168
ub4 kcrbaseq @168 0x00000000
ub4 kcrbabno @172 0x00000000
ub2 kcrbabof @176 0x0000
ub1 kcvcpetb[0] @180 0x00
ub1 kcvcpetb[1] @181 0x00
ub1 kcvcpetb[2] @182 0x00
ub1 kcvcpetb[3] @183 0x00
ub1 kcvcpetb[4] @184 0x00
ub1 kcvcpetb[5] @185 0x00
ub1 kcvcpetb[6] @186 0x00
ub1 kcvcpetb[7] @187 0x00
ub4 kcvfhbhz @312 0x00000000
struct kcvfhxcd, 16 bytes @316
ub4 space_kcvmxcd[0] @316 0x00000000
ub4 space_kcvmxcd[1] @320 0x00000000
ub4 space_kcvmxcd[2] @324 0x00000000
ub4 space_kcvmxcd[3] @328 0x00000000
word kcvfhtsn @332 6
ub2 kcvfhtln @336 0x0004
text kcvfhtnm[0] @338 T
text kcvfhtnm[1] @339 E
text kcvfhtnm[2] @340 S
text kcvfhtnm[3] @341 T
text kcvfhtnm[4] @342
text kcvfhtnm[5] @343
text kcvfhtnm[6] @344
text kcvfhtnm[7] @345
text kcvfhtnm[8] @346
text kcvfhtnm[9] @347
text kcvfhtnm[10] @348
text kcvfhtnm[11] @349
text kcvfhtnm[12] @350
text kcvfhtnm[13] @351
text kcvfhtnm[14] @352
text kcvfhtnm[15] @353
text kcvfhtnm[16] @354
text kcvfhtnm[17] @355
text kcvfhtnm[18] @356
text kcvfhtnm[19] @357
text kcvfhtnm[20] @358
text kcvfhtnm[21] @359
text kcvfhtnm[22] @360
text kcvfhtnm[23] @361
text kcvfhtnm[24] @362
text kcvfhtnm[25] @363
text kcvfhtnm[26] @364
text kcvfhtnm[27] @365
text kcvfhtnm[28] @366
text kcvfhtnm[29] @367
ub4 kcvfhrfn @368 0x00000005
struct kcvfhrfs, 8 bytes @372
ub4 kscnbas @372 0x00000000
ub2 kscnwrp @376 0x0000
ub4 kcvfhrft @380 0x00000000
struct kcvfhafs, 8 bytes @384
ub4 kscnbas @384 0x00000000
ub2 kscnwrp @388 0x0000
ub4 kcvfhbbc @392 0x00000000
ub4 kcvfhncb @396 0x00000000
ub4 kcvfhmcb @400 0x00000000
ub4 kcvfhlcb @404 0x00000000
ub4 kcvfhbcs @408 0x00000000
ub2 kcvfhofb @412 0x0000
ub2 kcvfhnfb @414 0x0000
ub4 kcvfhprc @416 0x26b0de91
struct kcvfhprs, 8 bytes @420
ub4 kscnbas @420 0x00000001
ub2 kscnwrp @424 0x0000
struct kcvfhprfs, 8 bytes @428
ub4 kscnbas @428 0x00000000
ub2 kscnwrp @432 0x0000
ub4 kcvfhtrt @444 0x00000000
注: 我們對比 TEST.dbf 和 system01.dbf 檔案頭中 scn 的差異然後修改 TEST.dbf 檔案的檔案的 scn
BBED> set OFFSET 40
BBED> dump
File: /data/oracle/oradta/testdb /TEST.dbf (2)
Block: 1 Offsets: 40 to 551 Dba:0x00800001
------------------------------------------------------------------------
a1010000 00320000 00200000 05000300 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 40dd0900
00000000 e27dc329 6a23c329 c5320900 00000000 00000000 00000000 00000000
00000400 05000000 00000000 04000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 06000000 04005445 53540000 00000000 00000000 00000000 00000000
00000000 00000000 05000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 91deb026 01000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 d96b0a00
00000a00 dc71c429 01000000 02000000 42610100 10000000 02000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set MODE Edit
BBED> modiby /x 000001d2
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
. File: /data/oracle/oradta/testdb /TEST.dbf(2)
Block: 1 Offsets: 40 to 551 Dba:0x00800001
------------------------------------------------------------------------
000001d2 00320000 00200000 05000300 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 40dd0900
00000000 e27dc329 6a23c329 c5320900 00000000 00000000 00000000 00000000
00000400 05000000 00000000 04000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 06000000 04005445 53540000 00000000 00000000 00000000 00000000
00000000 00000000 05000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 91deb026 01000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 d96b0a00
00000a00 dc71c429 01000000 02000000 42610100 10000000 02000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set OFFSET 484
BBED> dump
File: /data/oracle/oradta/testdb /TEST.dbf(2)
Block: 1 Offsets: 484 to 995 Dba:0x00800001
------------------------------------------------------------------------
d96b0a00 00000a00 dc71c429 01000000 02000000 42610100 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modiby /x 000a7163
File: /data/oracle/oradta/testdb /TEST.dbf(2)
Block: 1 Offsets: 484 to 995 Dba:0x00800001
------------------------------------------------------------------------
000a7163 00000a00 dc71c429 01000000 02000000 42610100 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED>
BBED> set OFFSET 492
BBED> dump
/data/oracle/oradta/testdb /TEST.dbf(2)
Block: 1 Offsets: 492 to 1003 Dba:0x00800001
------------------------------------------------------------------------
dc71c429 01000000 02000000 42610100 10000000 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 29c479ad
File: /data/oracle/oradta/testdb /TEST.dbf(2)
Block: 1 Offsets: 492 to 1003 Dba:0x00800001
------------------------------------------------------------------------
29c479ad 01000000 02000000 42610100 10000000 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 2, Block 1:
current = 0x0ad8, required = 0x0ad8
BBED> exit
重建控制檔案 :
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 10 月 20 11:27:55 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
Sql > startup nomount ;
SQL> ORACLE instance started.
Total System Global Area 1.2012E+10 bytes
Fixed Size 2104248 bytes
Variable Size 1677722696 bytes
Database Buffers 1.0318E+10 bytes
Redo Buffers 14671872 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/oracle/oradata/testdb/redo01.log' SIZE 100M,
GROUP 2 '/data/oracle/oradata/testdb/redo02.log' SIZE 100M,
GROUP 3 '/data/oracle/oradata/testdb/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/data/oracle/oradata/testdb/system01.dbf',
'/data/oracle/oradata/testdb/undotbs01.dbf',
'/data/oracle/oradata/testdb/sysaux01.dbf',
'/data/oracle/oradata/testdb/users01.dbf',
'/data/oracle/oradata/testdb/TEST.dbf'
CHARACTER SET ZHS16GBK
;
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Control file created.
SQL> alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlog
*
ERROR at line 1:
ORA-02288: invalid OPEN mode
SQL> alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data/oracle/oradata/testdb/system01.dbf'
SQL> recover database using backup controlfle until cancel ;
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Auto
ORA-00308: cannot open archived log
'/data/oracle/oradata/testdb/archive/1_13_700654442.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/data/oracle/oradata/testdb/archive/1_13_700654442.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 5 needs more recovery to be consistent
ORA-01110: data file 5: '/data/oracle/oradata/testdb/TEST.dbf'
SQL> alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 5 needs more recovery to be consistent
ORA-01110: data file 5: '/data/oracle/oradata/testdb/TEST.dbf'
SQL>
CURR_SEQ
---------
0
0
0
SQL> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
修改pfile 加入 隱含引數
*._allow_resetlogs_corruption=TRUE
Vi inittestdb.ora
testdb.__db_cache_size=10317987840
testdb.__java_pool_size=16777216
testdb.__large_pool_size=16777216
testdb.__shared_pool_size=1644167168
testdb.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/testdb/adump'
*.background_dump_dest='/data/oracle/admin/testdb/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data/oracle/oradata/testdb/control01.ctl','/data/oracle/oradata/testdb/control02.ctl','/data/oracle/oradata/testdb/control03.ctl'
*.core_dump_dest='/data/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/data/oracle/oradata/testdb/archive'
*.open_cursors=300
*.pga_aggregate_target=1671430144
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2205
*.sga_max_size=12000000000
*.sga_target=12000000000
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/data/oracle/admin/testdb/udump'
*._allow_resetlogs_corruption=TRUE
SQL>startup mount pfile ='/data/oracle/product/10.2.0/dbs/inittestdb.ora';
ORACLE instance started.
Total System Global Area 1.2012E+10 bytes
Fixed Size 2104248 bytes
Variable Size 1677722696 bytes
Database Buffers 1.0318E+10 bytes
Redo Buffers 14671872 bytes
Database mounted.
SQL> alter database open resetlogs
Database altered.
SQL> select count(*) from test;
COUNT(*)
----------
1536
SQL> shutdown immediate;
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
Sql > startup open
SQL> ORACLE instance started.
Total System Global Area 1.2012E+10 bytes
Fixed Size 2104248 bytes
Variable Size 1677722696 bytes
Database Buffers 1.0318E+10 bytes
Redo Buffers 14671872 bytes
Database mounted.
Database opened.
SQL> select count(*) from test;
COUNT(*)
----------
1536
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
OK 到此為止,資料檔案已經可以讀出來了,裡面的資料也可以搶救出來了,
演示的比較簡單,在某些特殊環境下,可以搶救某些表空間裡的資料.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/133735/viewspace-2923494/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復Oracle資料恢復
- 【kingsql分享】使用BBED修改Oracle資料檔案頭推進SCNSQLOracle
- 【BBED】使用BBED修改資料檔案SCN,使該檔案從offline轉變為online
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 透過修改控制檔案scn推進資料庫scn資料庫
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 如何進行Oracle資料庫不完全恢復RBOracle資料庫
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】誤truncate table的Oracle資料庫資料恢復方案資料庫資料恢復Oracle
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- 資料庫資料恢復-SQL SERVER資料庫檔案大小變為“0”的資料恢復方案資料庫資料恢復SQLServer
- 伺服器資料恢復-ext3檔案系統下oracle資料庫資料恢復案例伺服器資料恢復Oracle資料庫
- 資料庫資料恢復—MongoDB資料庫檔案丟失,啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 【資料庫資料恢復】oracle資料庫誤truncate table怎麼恢復資料?資料庫資料恢復Oracle
- 【資料庫資料恢復】ASM磁碟組掉線的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 【資料庫資料恢復】LINUX EXT3檔案系統下ORACLE資料庫誤操作導致資料丟失的資料恢復案例資料庫資料恢復LinuxOracle
- 【資料庫資料恢復】Oracle資料庫ASM磁碟組掉線如何恢復資料?資料庫資料恢復OracleASM
- 【資料庫資料恢復】EXT3檔案系統下MYSQL資料庫恢復案例資料庫資料恢復MySql
- 【資料庫資料恢復】ASM例項不能掛載的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- 【資料庫資料恢復】斷電導致Oracle資料庫資料丟失的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 資料庫資料恢復-oracle資料庫報錯無法開啟的如何恢復資料?資料庫資料恢復Oracle
- MongoDB資料庫報錯,資料庫檔案丟失資料恢復案例MongoDB資料庫資料恢復
- 【資料庫資料恢復】HP-UX系統ORACLE資料庫被誤刪除的資料恢復資料庫資料恢復UXOracle
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- 【資料庫資料恢復】ORACLE常見資料災難&資料恢復可能性資料庫資料恢復Oracle
- MSSQL資料庫資料恢復案例:ndf檔案大小變為0KB恢復資料SQL資料庫資料恢復
- Oracle資料庫恢復之resetlogsOracle資料庫
- 【北亞資料恢復】異常斷電導致Oracle資料庫報錯的oracle資料恢復資料恢復Oracle資料庫
- 資料庫資料恢復-ORACLE資料庫的常見故障&各種故障下的資料恢復可能性資料庫資料恢復Oracle
- 【資料庫資料恢復】Oracle ASM例項無法掛載的資料恢復案例資料庫資料恢復OracleASM
- linux下恢復誤刪除oracle的資料檔案LinuxOracle