1.环境说明
OS:Centos7.9
DB:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
2.模拟数据库SYSTEM文件头损坏
[oracle@oracledb bbed]$ bbed password=blockedit listfile=/home/oracle/bbed/filelist.txt blocksize=8192 mode=edit
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/EVA/system01.dbf 112640
2 /u01/app/oracle/oradata/EVA/sysaux01.dbf 87040
3 /u01/app/oracle/oradata/EVA/undotbs01.dbf 131200
4 /u01/app/oracle/oradata/EVA/users01.dbf 640
5 /u01/app/oracle/oradata/EVA/tbs1.dbf 1280
--用5号文件10号块覆盖1号文件1号块
BBED> copy file 5 block 10 to file 1 block 1
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
1ea20000 0a004001 683f0b00 00000104 ec010000 05000000 80403600 00000000
00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
--模拟业务数据
SYS@EVA>conn scott/tiger
Connected.
SCOTT@EVA>create table t01(id number,name varchar2(10));
Table created.
SCOTT@EVA>insert into t01 values(1,'AAAAA');
1 row created.
SCOTT@EVA>insert into t01 values(2,'BBBBB');
1 row created.
SCOTT@EVA>insert into t01 values(3,'CCCCC');
1 row created.
SCOTT@EVA>COMMIT;
Commit complete.
SCOTT@EVA>alter system switch logfile;
System altered.
SCOTT@EVA>alter system switch logfile;
System altered.
--日志第三次切换时直接报错,数据库crash
SCOTT@EVA>alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28295
Session ID: 5 Serial number: 59586
--重新启动数据库,提示system01.dbf损坏
idle>startup
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 822085928 bytes
Database Buffers 1677721600 bytes
Redo Buffers 8151040 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
ORA-01210: data file header is media corrupt
3.通过alert日志查看报错
从报错中可以看出system01.dbf文件头损坏
--日志切换时报错
2024-07-05 14:57:58.200000 +08:00
Thread 1 advanced to log sequence 34 (LGWR switch)
Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/EVA/redo01.log
2024-07-05 14:58:00.819000 +08:00
Thread 1 advanced to log sequence 35 (LGWR switch)
Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/EVA/redo02.log
Thread 1 cannot allocate new log, sequence 36
Checkpoint not complete
Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/EVA/redo02.log
2024-07-05 14:58:05.167000 +08:00
Read of datafile '/u01/app/oracle/oradata/EVA/system01.dbf' (fno 1) header failed with ORA-01210
Hex dump of (file 1, block 1) in trace file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_ckpt_19801.trc
Corrupt block relative dba: 0x00400001 (file 1, block 1)
Bad header found during datafile header read
Data in bad block:
type: 30 format: 2 rdba: 0x0140000a
last change scn: 0x0000.0000.000b3f68 seq: 0x1 flg: 0x04
spare3: 0x0
consistency value in tail: 0x3f681e01
check value in block header: 0x1ec
computed block checksum: 0x0
Rereading datafile 1 header failed with ORA-01210
Errors in file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_ckpt_19801.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
ORA-01210: data file header is media corrupt
Errors in file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_ckpt_19801.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
ORA-01210: data file header is media corrupt
Errors in file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_ckpt_19801.trc (incident=56490):
ORA-1242 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/eva/EVA/incident/incdir_56490/EVA_ckpt_19801_i56490.trc
Errors in file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_m000_28766.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
ORA-01210: data file header is media corrupt
USER (ospid: 19801): terminating the instance due to error 1242
--数据库启动时报错
ALTER DATABASE OPEN
Ping without log force is disabled:
instance mounted in exclusive mode.
Read of datafile '/u01/app/oracle/oradata/EVA/system01.dbf' (fno 1) header failed with ORA-01210
Hex dump of (file 1, block 1) in trace file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_ora_28957.trc
Corrupt block relative dba: 0x00400001 (file 1, block 1)
Bad header found during datafile header read
Data in bad block:
type: 30 format: 2 rdba: 0x0140000a
last change scn: 0x0000.0000.000b3f68 seq: 0x1 flg: 0x04
spare3: 0x0
consistency value in tail: 0x3f681e01
check value in block header: 0x1ec
computed block checksum: 0x0
Rereading datafile 1 header failed with ORA-01210
Errors in file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_ora_28957.trc:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
ORA-01210: data file header is media corrupt
ORA-1122 signalled during: ALTER DATABASE OPEN...
Errors in file /u01/app/oracle/diag/rdbms/eva/EVA/trace/EVA_m000_28959.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
ORA-01210: data file header is media corrupt
--使用BBED查看该数据块已变成无效的类型
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
BBED-00400: invalid blocktype (30)
4.使用BBED修复文件头
由于是SYSTEM文件头损坏，我们可以使用跟他类似的SYSAUX文件头来覆盖，再通过BBED对相关参数进行修改。
4.1需要修改的相关参数
1.rdba_kcbh (offset4) 文件头block的rdba地址
2.kccfhfsz (offset44) 文件大小
3.kccfhfno (offset52) datafile文件号
4.kcvfhrdb (offset96) root dba
5.kscnbas (offset100) v$datafile.creation_change#
6.kcvfhcrt (offset108) v$datafile.creation_time
7.kcvfhsta (offset138) 文件状态
8.kcvfhtsn (offset332) 表空间号v$datafile.ts#
9.kcvfhtln (offset336) 表空间名称字符长度
10.kcvfhtnm (offset338) 表空间名称v$tablespace.name
11.kcvfhrfn (offset368) 相对文件号v$datafile.rfile#
12.kscnbas (offset484) checkpoint scn
13.kcvcptim (offset492) last checkpoint time
14.kcvfhcpc (offset140) Datafile checkpoint count
4.2使用SYSAUX文件构造SYSTEM文件头
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/EVA/system01.dbf 112640
2 /u01/app/oracle/oradata/EVA/sysaux01.dbf 87040
3 /u01/app/oracle/oradata/EVA/undotbs01.dbf 131200
4 /u01/app/oracle/oradata/EVA/users01.dbf 640
5 /u01/app/oracle/oradata/EVA/tbs1.dbf 1280
BBED> copy file 2 block 1 to file 1 block 1
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
0ba20000 01008000 00000000 00000104 073e0000 00000000 0000200c 6865e2de
45564100 00000000 ea0d0000 00810100 00200000 02000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 640a0000 00800000 b5e2ef45 a8e2ef45 01000000 00000000 00000000
00000000 00000000 00000400 39000000 3b90f145 38000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 01000000 06005359 53415558 00000000 00000000
00000000 00000000 00000000 00000000 02000000 00000000 00000000 1d90f145
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 362d1400 00800000 92c0f245 01000000 21000000 97ae0000 10000000
BBED> sum apply
--此时已可以正常查看数据块结构
BBED> map
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
struct kcvfh, 1248 bytes @0
ub4 tailchk @8188
4.3修改rdba_kcbh(offset4)
--原值
BBED> p kcvfh
ub4 rdba_kcbh @4 0x00800001
该值转换二进制表示
0x00800001 => 0000 0000 1000 0000 0000 0000 0000 0001
前10位表示文件号0000 0000 10 => 2
后22位表示块号00 0000 0000 0000 0000 0001 => 1
转换后表示2号文件1号块,需要将该值修改为1号文件1号块
前10位0000 0000 01 => 1
后22位00 0000 0000 0000 0000 0001 => 1
组合在一起后转换为16进制
0000 0000 0100 0000 0000 0000 0000 0001 => 0x00400001
--将该值修改为0x00400001(linux系统中是大小端存取,因此需要两位两位反过来存取)
BBED> m /x 01004000 offset 4
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 4 to 515 Dba:0x00400001
------------------------------------------------------------------------
01004000 00000000 00000104 073e0000 00000000 0000200c 6865e2de 45564100
00000000 ea0d0000 00810100 00200000 02000300 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
640a0000 00800000 b5e2ef45 a8e2ef45 01000000 00000000 00000000 00000000
--新值
BBED> p kcvfh
ub4 rdba_kcbh @4 0x00400001
4.4修改kccfhfsz(offset44)
--原值
BBED> p kcvfh
ub4 kccfhfsz @44 0x00018100
该值表示数据块的个数,使用函数转换后正好等于sysaux01.dbf的块个数
idle>select to_number('18100','xxxxxxxx') from dual;
F_SIZE
----------
98560
[oracle@oracledb EVA]$ dbfsize sysaux01.dbf
Database file: sysaux01.dbf
Database file type: file system
Database file size: 98560 8192 byte blocks
--将该值修改为system01.dbf的块个数
[oracle@oracledb EVA]$ dbfsize system01.dbf
Database file: system01.dbf
Database file type: file system
Database file size: 113920 8192 byte blocks
idle>select to_char(113920,'xxxxxxxx') f_size from dual; --转换成16进制
F_SIZE
---------
1bd00
BBED> m /x 00bd01 offset 44
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 44 to 555 Dba:0x00400001
------------------------------------------------------------------------
00bd0100 00200000 02000300 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 640a0000 00800000
b5e2ef45 a8e2ef45 01000000 00000000 00000000 00000000 00000000 00000400
39000000 3b90f145 38000000 00000000 00000000 00000000 00000000 00000000
--新值
BBED> p
kcvfh.kcvfhhdr.kccfhfsz
-----------------------
ub4 kccfhfsz @44 0x0001bd00
4.5修改kccfhfno(offset52)
--原值
BBED> p
kcvfh.kcvfhhdr.kccfhfno
-----------------------
ub2 kccfhfno @52 0x0002
--直接将该值修改为0x0001即可
BBED> m /x 01 offset 52
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 52 to 563 Dba:0x00400001
------------------------------------------------------------------------
01000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 640a0000 00800000 b5e2ef45 a8e2ef45
01000000 00000000 00000000 00000000 00000000 00000400 39000000 3b90f145
38000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
--新值
BBED> p
kcvfh.kcvfhhdr.kccfhfno
-----------------------
ub2 kccfhfno @52 0x0001
4.6修改kcvfhrdb(offset96)
--原值
因为只有system才有root值,因此sysaux该位置为0
BBED> p
kcvfh.kcvfhrdb
--------------
ub4 kcvfhrdb @96 0x00000000
--修改
每个db版本的该值都位于固定的数据块中,在其他正常的同版本数据库中查询该值即可
SQL>select fhrdb,fhfno from x$kcvfh order by 2;
FHRDB FHFNO
---------- ----------
4194824 1
idle>select to_char(4194824,'xxxxxxxx') X from dual;
X
---------
400208
通过下面的查询我们可以得知该值存在于1号文件的520号块,实际上该块保存了BOOTSTRAP$相关信息
SQL> select dbms_utility.data_block_address_file(to_number('400208','xxxxxxxxxxxx')) file_id,dbms_utility.data_block_address_block(to_number('400208','xxxxxxxxxxxx')) block_id from dual;
FILE_ID BLOCK_ID
-------- ---------
1 520
BBED> m /x 08024000 offset 96
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 96 to 607 Dba:0x00400001
------------------------------------------------------------------------
08024000 640a0000 00800000 b5e2ef45 a8e2ef45 01000000 00000000 00000000
00000000 00000000 00000400 39000000 3b90f145 38000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> sum apply
--新值
BBED> p
kcvfh.kcvfhrdb
--------------
ub4 kcvfhrdb @96 0x00400208
4.7修改kscnbas(offset100)
--原值
BBED> p
kcvfh.kcvfhcrs.kscnbas
----------------------
ub4 kscnbas @100 0x00000a64
idle>select file#,creation_change# from v$datafile;
FILE# CREATION_CHANGE#
---------- ----------------
1 7
2 2660
3 3439
4 16399
5 737123
idle>select to_char(2660,'xxxxxxxx') X from dual; --2号文件与原值对应
X
---------
a64
--修改
idle>select to_char(7,'xxxxxxxx') X from dual;
X
---------
7
BBED> m /x 07000000 offset 100
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 100 to 611 Dba:0x00400001
------------------------------------------------------------------------
07000000 00800000 b5e2ef45 a8e2ef45 01000000 00000000 00000000 00000000
00000000 00000400 39000000 3b90f145 38000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
--新值
BBED> p
kcvfh.kcvfhcrs.kscnbas
----------------------
ub4 kscnbas @100 0x00000007
4.8修改kcvfhcrt(offset108)
--原值
BBED> p
kcvfh.kcvfhcrt
--------------
ub4 kcvfhcrt @108 0x45efe2b5
使用以下语句查询文件创建时间及SCN
select file#,to_char(creation_time,'yyyy-mm-dd hh24:mi:ss') creation_time_file,
(to_char(creation_time,'yyyy')-1988)*12*31*24*3600+
(to_char(creation_time,'mm')-1)*31*24*3600+
(to_char(creation_time,'dd')-1)*24*3600+
to_char(creation_time,'hh24')*3600+
to_char(creation_time,'mi')*60+
to_char(creation_time,'ss') creation_name_scn
from v$datafile order by 1;
FILE# CREATION_TIME_FILE CREATION_NAME_SCN
---------- ------------------- -----------------
1 2024-07-03 10:17:21 1173349041
2 2024-07-03 10:17:25 1173349045
3 2024-07-03 10:17:28 1173349048
4 2024-07-03 10:17:43 1173349063
5 2024-07-03 13:21:17 1173360077
idle>select to_char(1173349045,'xxxxxxxx') X from dual; --与2号文件原值对应
X
---------
45efe2b5
--修改
idle>select to_char(1173349041,'xxxxxxxx') X from dual;
X
---------
45efe2b1
BBED> m /x b1 offset 108
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 108 to 619 Dba:0x00400001
------------------------------------------------------------------------
b1e2ef45 a8e2ef45 01000000 00000000 00000000 00000000 00000000 00000400
39000000 3b90f145 38000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> p
kcvfh.kcvfhcrt
--------------
ub4 kcvfhcrt @108 0x45efe2b1
4.9修改kcvfhsta(offset138)
--原值
当一个datafile处于fuzzy状态时,该值为0x04,因为是crash,该值无需修改
BBED> p
kcvfh.kcvfhsta
--------------
ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)
4.10修改kcvfhtsn(offset332)
--原值
BBED> p kcvfhtsn
sword kcvfhtsn @332 1
idle>select file#,ts#,name from v$datafile;
FILE# TS# NAME
---------- ---------- ------------------------------
1 0 /u01/app/oracle/oradata/EVA/system01.dbf
2 1 /u01/app/oracle/oradata/EVA/sysaux01.dbf
--修改
该值需要将1修改为0号表空间
BBED> m /x 00 offset 332
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 332 to 843 Dba:0x00400001
------------------------------------------------------------------------
00000000 06005359 53415558 00000000 00000000 00000000 00000000 00000000
00000000 02000000 00000000 00000000 1d90f145 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> sum apply
--新值
BBED> p kcvfhtsn
sword kcvfhtsn @332 0
4.11修改kcvfhtln(offset336)
--原值
SYSAUX => SYSTEM 字符长度一致,无需修改
BBED> p kcvfhtln
ub2 kcvfhtln @336 0x0006
4.12修改kcvfhtnm(offset338)
--原值
BBED> d offset 338
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 338 to 849 Dba:0x00400001
------------------------------------------------------------------------
53595341 55580000 00000000 00000000 00000000 00000000 00000000 00000200 --前六个字节位表空间名
00000000 00000000 00001d90 f1450000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> p kcvfhtnm
text kcvfhtnm[0] @338 S
text kcvfhtnm[1] @339 Y
text kcvfhtnm[2] @340 S
text kcvfhtnm[3] @341 A
text kcvfhtnm[4] @342 U
text kcvfhtnm[5] @343 X
idle>select dump('SYSAUX',16) from dual; --转换后,字符存储无需改变顺序
DUMP('SYSAUX',16)
-------------------------------
Typ=96 Len=6: 53,59,53,41,55,58
--修改
idle>select dump('SYSTEM',16) from dual;
DUMP('SYSTEM',16)
-------------------------------
Typ=96 Len=6: 53,59,53,54,45,4d
BBED> m /x 54454d offset 341
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 341 to 852 Dba:0x00400001
------------------------------------------------------------------------
54454d00 00000000 00000000 00000000 00000000 00000000 00000002 00000000
00000000 0000001d 90f14500 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
--新值
BBED> d offset 338
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 338 to 849 Dba:0x00400001
------------------------------------------------------------------------
53595354 454d0000 00000000 00000000 00000000 00000000 00000000 00000200
00000000 00000000 00001d90 f1450000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> p kcvfhtnm
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
4.13修改kcvfhrfn(offset368)
--原值
BBED> p kcvfhrfn
ub4 kcvfhrfn @368 0x00000002
idle>select file#,rfile#,ts#,name from v$datafile; --查询相对文件号
FILE# RFILE# TS# NAME
---------- ---------- ---------- ------------------------------
1 1 0 /u01/app/oracle/oradata/EVA/system01.dbf
2 2 1 /u01/app/oracle/oradata/EVA/sysaux01.dbf
--修改
BBED> m /x 01 offset 368
File: /u01/app/oracle/oradata/EVA/system01.dbf (1)
Block: 1 Offsets: 368 to 879 Dba:0x00400001
------------------------------------------------------------------------
01000000 00000000 00000000 1d90f145 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 362d1400 00800000 92c0f245
--新值
BBED> p kcvfhrfn
ub4 kcvfhrfn @368 0x00000001
4.14修改kscnbas(offset484)
--原值
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00142d36 --原值
ub2 kscnwrp @488 0x8000
ub2 kscnwrp2 @490 0x0000
idle>select FILE#,CREATION_CHANGE#,CHECKPOINT_CHANGE#,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE# from v$datafile;
FILE# CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ---------------- ------------------ --------------------- ------------ ---------------
1 7 1322294 0 0
2 2660 1322294 0 0
3 3439 1322294 0 0
4 16399 1322294 0 0
5 737123 1322294 0 0
--两个文件该值相同,无需修改
idle>select to_char(1322294,'xxxxxxxx') X from dual;
X
---------
142d36
4.15修改kcvcptim(offset492)
--原值
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00142d36
ub2 kscnwrp @488 0x8000
ub2 kscnwrp2 @490 0x0000
ub4 kcvcptim @492 0x45f2c092 --原值
ub2 kcvcpthr @496 0x0001
使用以下语句查询检查点时间及SCN
select file#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time_file,
(to_char(checkpoint_time,'yyyy')-1988)*12*31*24*3600+
(to_char(checkpoint_time,'mm')-1)*31*24*3600+
(to_char(checkpoint_time,'dd')-1)*24*3600+
to_char(checkpoint_time,'hh24')*3600+
to_char(checkpoint_time,'mi')*60+
to_char(checkpoint_time,'ss') checkpoint_time_scn
from v$datafile order by 1;
FILE# CHECKPOINT_TIME_FIL CHECKPOINT_TIME_SCN
---------- ------------------- -------------------
1 2024-07-05 14:28:34 1173536914
2 2024-07-05 14:28:34 1173536914
3 2024-07-05 14:28:34 1173536914
4 2024-07-05 14:28:34 1173536914
5 2024-07-05 14:28:34 1173536914
--两个文件该值相同,无需修改
idle>select to_char(1173536914,'xxxxxxxx') X from dual;
X
---------
45f2c092
4.16修改kcvfhcpc(offset140)
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000039 --数据文件检查点的计数器
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000038 --控制文件检查点的计数器
正常情况kcvfhcpc=kcvfhccc+1,此处无需修改
4.17使用dbv校验文件头
--校验成功,没有坏块
[oracle@oracledb ~]$ dbv file=/u01/app/oracle/oradata/EVA/system01.dbf start=1 end=2
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/EVA/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 2
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1314860 (0.1314860)
5.打开数据库,检查数据
idle>alter database open;
Database altered.
idle>conn scott/tiger
Connected.
SCOTT@EVA>select * from t01;
ID NAME
---------- ------------------------------
1 AAAAA
2 BBBBB
3 CCCCC
至此,数据库完成恢复,数据无丢失
