1 資料檔案頭部和恢複相關的幾個偏移量說明：
ub4 kcvfhcpc @140 0x00000308------檢查點計數
ub4 kcvfhccc @148 0x00000307------總是比檢查點計算少1
ub4 kcvcptim @492 0x2f9af923-----檢查點時間
ub4 kscnbas @484 0x8013ea80-------- scn的低位
ub2 kscnwrp @488 0x0000--------- scn的高位
ub4 kcvfhrlc @112 0x2f9af2a9------resetlogs count
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x8013e6b3-------- resetlogs scn
ub2 kscnwrp @120 0x0000
如果是resetlogs 方式打開資料庫對于recover失敗的資料檔案其資料檔案中偏移量 @140、@148、@492、@484、@112、@116應該和其它不需要恢複的資料檔案保持一緻
其它情況資料檔案中偏移量 可能僅需要@140、@148、@492、@484和其它不需要恢複的資料檔案保持一緻
2重建控制檔案的語句：
[oracle@oracle ~]$ cat /oracle/control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oracle/app/db1/dbs/log1CRM.dbf',
'/oracle/CRM2/CRM/redo01b.log'
) SIZE 200M,
GROUP 2 (
'/oracle/app/db1/dbs/log2CRM.dbf',
'/oracle/CRM2/CRM/redo02b.log'
) SIZE 50M,
GROUP 3 (
'/oracle/CRM2/CRM/redo03.log',
'/oracle/CRM2/CRM/redo03b.log'
GROUP 4 (
'/oracle/CRM2/CRM/redo04.log',
'/oracle/CRM2/CRM/redo04b.log'
GROUP 5 (
'/oracle/CRM2/CRM/redo05.log',
'/oracle/CRM2/CRM/redo05b.log'
GROUP 6 (
'/oracle/CRM2/CRM/redo06.log',
'/oracle/CRM2/CRM/redo06b.log'
) SIZE 200M
DATAFILE
'/oracle/CRM2/system1.dbf',
'/oracle/CRM2/CRM/sysaux01.dbf',
'/oracle/CRM2/CRM/users01.dbf',
'/oracle/CRM2/zxa.dbf',
'/oracle/CRM2/CRM/undotbs2.dbf',
'/oracle/CRM2/zxc.dbf',
'/oracle/CRM2/CRM/zxbig1.dbf'
CHARACTER SET ZHS16GBK
;
[oracle@oracle ~]$ exit
exit
3 開始重建控制檔案産生missingsnnnn 檔案
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 2020480 bytes
Variable Size 92277632 bytes
Database Buffers 222298112 bytes
Redo Buffers 6365184 bytes
Control file created.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2148806610 generated at 11/07/2012 12:15:58 needed for thread
1
ORA-00289: suggestion : /oracle/archive/1_3_798683817.dbf
ORA-00280: change 2148806610 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /oracle/CRM2/system1.dbf SYSTEM
2 /oracle/app/db1/dbs/MISSING00002 RECOVER
3 /oracle/CRM2/CRM/sysaux01.dbf ONLINE
4 /oracle/CRM2/CRM/users01.dbf ONLINE
5 /oracle/CRM2/zxa.dbf ONLINE
6 /oracle/CRM2/CRM/undotbs2.dbf ONLINE
7 /oracle/CRM2/zxc.dbf ONLINE
8 /oracle/CRM2/CRM/zxbig1.dbf ONLINE
8 rows selected.
4 重命名missingnnnn資料檔案
SQL> alter tablespace zx rename datafile '/oracle/app/db1/dbs/MISSING00002' to '/oracle/CRM2/zxb.dbf';
Tablespace altered.
2 /oracle/CRM2/zxb.dbf RECOVER
5 執行恢複報錯如下：（前面幾個步驟就是為了構造這個錯誤）
SQL> recover datafile 2;
ORA-00600: internal error code, arguments: [krhpfh_03-1202], [fno =], [2],
[fhcrt =], [797369834], [cptim =], [0], []
ORA-01110: data file 2: '/oracle/CRM2/zxb.dbf'
6 bbed參數和檔案清單如下：
[oracle@oracle ~]$ cat bbed.para
blocksize=8192
mode=edit
listfile=/oracle/filelist
[oracle@oracle ~]$ cat /oracle/filelist
1 /oracle/CRM2/system1.dbf 503316480
2 /oracle/CRM2/zxb.dbf 10485760
3 /oracle/CRM2/CRM/sysaux01.dbf 304087040
4 /oracle/CRM2/CRM/users01.dbf 5242880
5 /oracle/CRM2/zxa.dbf 104857600
6 /oracle/CRM2/CRM/undotbs2.dbf 209715200
7 /oracle/CRM2/zxc.dbf 10485760
8 /oracle/CRM2/CRM/zxbig1.dbf 2147483648
7 關閉資料庫啟動bbed對資料檔案2進行調整
[oracle@oracle ~]$ bbed parfile=bbed.para
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Nov 7 12:25:14 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /oracle/CRM2/system1.dbf 61440
2 /oracle/CRM2/zxb.dbf 1280
3 /oracle/CRM2/CRM/sysaux01.dbf 37120
4 /oracle/CRM2/CRM/users01.dbf 640
5 /oracle/CRM2/zxa.dbf 12800
6 /oracle/CRM2/CRM/undotbs2.dbf 25600
7 /oracle/CRM2/zxc.dbf 1280
8 /oracle/CRM2/CRM/zxbig1.dbf 262144
BBED> show
FILE# 1
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /oracle/CRM2/system1.dbf
BIFILE bifile.bbd
LISTFILE /oracle/filelist
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> dump /v dba 1,1 offset 140 count 20
File: /oracle/CRM2/system1.dbf (1)
Block: 1 Offsets: 140 to 159 Dba:0x00400001
-------------------------------------------------------
1d030000 e7969b2f 1c030000 00000000 l ....?./........
00000000 l ....
<16 bytes per line>
BBED> dump /v dba 2,1 offset 140 count 20
File: /oracle/CRM2/zxb.dbf (2)
Block: 1 Offsets: 140 to 159 Dba:0x00800001
1d030000 36969b2f 1c030000 00000000 l ....6../........
BBED> dump /v dba 1,1 offset 148 count 20;
Block: 1 Offsets: 148 to 167 Dba:0x00400001
1c030000 00000000 00000000 00000000 l ................
BBED> dump /v dba 2,1 offset 148 count 20
Block: 1 Offsets: 148 to 167 Dba:0x00800001
BBED> dump /v dba 1,1 offset 492 count 20
Block: 1 Offsets: 492 to 511 Dba:0x00400001
f2979b2f 01000000 01000000 02000000 l ?./............
10006960 l ..i`
BBED> dump /v dba 2,1 offset 492 count 20
Block: 1 Offsets: 492 to 511 Dba:0x00800001
7e969b2f 01000000 03000000 41010000 l ~../........A...
1000403d l ..@=
BBED> modify /x f2979b2f
BBED-00209: invalid number (f2979b2f)
BBED> modify /x f297
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block: 1 Offsets: 492 to 511 Dba:0x00800001
------------------------------------------------------------------------
f2979b2f 01000000 03000000 41010000 1000403d
<32 bytes per line>
BBED> set offset +2
OFFSET 494
BBED> modify /x 9b2f
Block: 1 Offsets: 494 to 513 Dba:0x00800001
9b2f0100 00000300 00004101 00001000 403d0200
f2979b2f 01000000 03000000 41010000 l ?./........A...
BBED> dump /v dba 1,1 offset 484 count 20
Block: 1 Offsets: 484 to 503 Dba:0x00400001
d42f1480 00000000 f2979b2f 01000000 l ?......?./....
01000000 l ....
BBED> dump /v dba 2,1 offset 484 count 20
Block: 1 Offsets: 484 to 503 Dba:0x00800001
d22f1480 00000000 f2979b2f 01000000 l ?......?./....
03000000 l ....
BBED> modify /x d42f1480
BBED-00209: invalid number (d42f1480)
BBED> modify /x d42f
Block: 1 Offsets: 484 to 503 Dba:0x00800001
d42f1480 00000000 f2979b2f 01000000 03000000
OFFSET 486
BBED> modify /x 1480
Block: 1 Offsets: 486 to 505 Dba:0x00800001
14800000 0000f297 9b2f0100 00000300 00004101
BBED> dump /v offset 484 count 20
BBED> dump /v dba 1,1 offset 112 count 20
Block: 1 Offsets: 112 to 131 Dba:0x00400001
f8969b2f d32f1480 00000000 00000000 l ?./?..........
BBED> dump /v dba 2,1 offset 112 count 20
Block: 1 Offsets: 112 to 131 Dba:0x00800001
a9f29a2f b3e61380 00000000 00000000 l ?./蟲..........
BBED> modify /x f8969b2f
BBED-00209: invalid number (f8969b2f)
BBED> modify /x f896
Block: 1 Offsets: 112 to 131 Dba:0x00800001
f8969a2f b3e61380 00000000 00000000 00000000
OFFSET 114
Block: 1 Offsets: 114 to 133 Dba:0x00800001
9b2fb3e6 13800000 00000000 00000000 00000000
BBED> dump /v offset 112
f8969b2f b3e61380 00000000 00000000 l ?./蟲..........
BBED> dump /v dba 1,1 offset 116 count 20
Block: 1 Offsets: 116 to 135 Dba:0x00400001
d32f1480 00000000 00000000 00000000 l ?..............
BBED> dump /v dba 2,1 offset 116 count 20
Block: 1 Offsets: 116 to 135 Dba:0x00800001
b3e61380 00000000 00000000 00000000 l 蟲..............
BBED> modify /x d32f1480
BBED-00209: invalid number (d32f1480)
BBED> modify /x d32f
Block: 1 Offsets: 116 to 135 Dba:0x00800001
d32f1380 00000000 00000000 00000000 00000000
OFFSET 118
Block: 1 Offsets: 118 to 137 Dba:0x00800001
14800000 00000000 00000000 00000000 00000000
BBED> dump /v offset 116
BBED> sum apply
Check value for File 2, Block 1:
current = 0x6d37, required = 0x6d37
BBED> exit
SQL> recover datafile 2
Media recovery complete.
2 /oracle/CRM2/zxb.dbf OFFLINE
SQL> alter database datafile 2 online;
本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1052934，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)