Oracle SYSTEM文件头损坏及修复方法
一:通过10046跟踪open过程，需要读取file#=1 block#=1，即SYSTEM文件头;
二:通过dump查看SYSTEM文件头信息;
三:模拟SYSTEM文件头损坏;
四:SYSTEM文件头损坏影响；
五:SYSTEM文件头损坏详细修复过程；
包括如下13步骤:
1. rdba_kcbh(offset 4) 文件头block的rdba地址
2. kccfhfsz (offset 44) 文件大小
3. kccfhfno (offset 52) datafile文件号
4. kcvfhrdb (offset 96) root dba
5. kscnbas (offset 100) v$datafile.creation_change#
6. kcvfhcrt (offset 108) v$datafile.creation_time
7. kcvfhsta (offset 138) 文件状态
8. kcvfhtsn (offset 332) 表空间号v$datafile.ts#
9. kcvfhtln (offset 336) 表空间名称字符长度
10.kcvfhtnm (offset 338) 表空间名称v$tablespace.name
11.kcvfhrfn (offset 368) 相对文件号v$datafile.rfile#
12.kscnbas (offset 484) checkpoint scn
13.kcvcptim (offset 492) last checkpoint time
---14.kcvfhcpc (offset 144) Datafile checkpoint count(不需要)
一:通过10046跟踪open过程，需要读取file#=1 block#=1，即SYSTEM文件头。
[oracle@cjc-db-01 ~]$ sqlplus / as sysdba
startup mount;
alter session set events '10046 trace name context forever,level 8';
alter database open;
alter session set events '10046 trace name context off';
select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5163.trc
cp /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5163.trc /home/oracle/tmp/
查看文件
[oracle@cjc-db-01 tmp]$ vi cjc_ora_5163.trc
WAIT #140737299719984: nam='db file sequential read' ela= 7 file#=1 block#=1 blocks=1 obj#=-1 tim=1728793861760782
WAIT #140737299719984: nam='db file sequential read' ela= 4 file#=2 block#=1 blocks=1 obj#=-1 tim=1728793861760827
WAIT #140737299719984: nam='db file sequential read' ela= 3 file#=3 block#=1 blocks=1 obj#=-1 tim=1728793861760858
WAIT #140737299719984: nam='db file sequential read' ela= 4 file#=4 block#=1 blocks=1 obj#=-1 tim=1728793861760875
WAIT #140737299719984: nam='db file sequential read' ela= 4 file#=5 block#=1 blocks=1 obj#=-1 tim=1728793861760891
数据库open时会扫描所有数据文件头，当然也包括system01.dbf文件的block 1。
SQL> set line 100
SQL> col name for a50
SQL> select * from v$dbfile order by file#;
FILE# NAME
---------- --------------------------------------------------
1 /oracle/app/oracle/oradata/cjc/system01.dbf
2 /oracle/app/oracle/oradata/cjc/sysaux01.dbf
3 /oracle/app/oracle/oradata/cjc/undotbs01.dbf
4 /oracle/app/oracle/oradata/cjc/users01.dbf
5 /oracle/app/oracle/oradata/cjc/cjc01.dbf
二:通过dump查看SYSTEM文件头信息
select spid from v$process where addr in (select paddr from v$session where sid = (select sid from v$mystat where rownum=1));
SPID
------------------------
6264
alter system set events 'immediate trace name file_hdrs level 3';
[oracle@cjc-db-01 trace]$ cp cjc_ora_6264.trc /home/oracle/tmp/
[oracle@cjc-db-01 tmp]$ vi cjc_ora_6264.trc
DATA FILE #1:
name #7: /oracle/app/oracle/oradata/cjc/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:122 scn: 0x0000.0010e5b2 10/13/2024 12:31:02
Stop scn: 0xffff.ffffffff 10/13/2024 12:30:15
Creation Checkpointed at scn: 0x0000.00000007 08/24/2013 11:37:33
thread:0 rba:(0x0.0.0)
......
Offline scn: 0x0000.000e2005 prev_range: 0
Online Checkpointed at scn: 0x0000.000e2006 08/17/2024 12:43:52
thread:1 rba:(0x1.2.0)
......
Online move state: 0
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=3819394245=0xe3a754c5, Db Name='CJC'
Activation ID=0=0x0
Control Seq=1015=0x3f7, File size=96000=0x17700
File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000007 08/24/2013 11:37:33
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x462b5888 scn: 0x0000.000e2006
prev reset logs count:0x3121c97a scn: 0x0000.00000001
recovered at 09/06/2024 21:15:45
status:0x2004 root dba:0x00400208 chkpt cnt: 122 ctl cnt:121
begin-hot-backup file size: 0
三:模拟system文件头损坏
测试之前，先停库冷备。
登陆BBED工具:
已在环境变量配置了bbed别名
[oracle@cjc-db-01 ~]$ cat .bash_profile |grep bbed
alias bbed='bbed parfile=/home/oracle/bbed/bbed.par password=blockedit'
直接登陆
[oracle@cjc-db-01 ~]$ bbed
BBED: Release 2.0.0.0.0 - Limited Production on Sun Oct 13 12:52:16 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /oracle/app/oracle/oradata/cjc/system01.dbf 96000
2 /oracle/app/oracle/oradata/cjc/sysaux01.dbf 66560
3 /oracle/app/oracle/oradata/cjc/undotbs01.dbf 8960
4 /oracle/app/oracle/oradata/cjc/users01.dbf 640
5 /oracle/app/oracle/oradata/cjc/cjc01.dbf 1280
查看 file 1 block 1 信息
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map /v
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
sword kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
0ba20000 01004000 00000000 00000104 b4610000 00000000 0004200b c554a7e3
434a4300 00000000 f7030000 00770100 00200000 01000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
08024000 07000000 00000000 7dc92131 88582b46 06200e00 00000000 00000000
00000000 00000000 00000420 7a000000 812e4646 79000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 06005359 5354454d 00000000 00000000
00000000 00000000 00000000 00000000 01000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000a00
7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 b2e51000 00000000 86cc7746 01000000 0d000000 994f0100 10000000
<32 bytes per line>
模拟file 1 block 1损坏，用file 5 block 10覆盖file 1 block 10
BBED> copy file 5 block 10 to file 1 block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
1ea20000 0a004001 61bb0e00 00000104 e9010000 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
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 1, Block 1:
current = 0x01e9, required = 0x01e9
四:SYSTEM文件头损坏影响
数据库无法正常关闭，报错如下:
SQL> shutdown immediate
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oracle/app/oracle/oradata/cjc/system01.dbf'
ORA-01210: data file header is media corrupt
告警日志如下:
Sun Oct 13 12:57:27 2024
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Read of datafile '/oracle/app/oracle/oradata/cjc/system01.dbf' (fno 1) header failed with ORA-01210
Hex dump of (file 1, block 1) in trace file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5163.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.000ebb61 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xbb611e01
check value in block header: 0x1e9
computed block checksum: 0x0
Rereading datafile 1 header failed with ORA-01210
Sun Oct 13 12:57:28 2024
Checker run found 1 new persistent data failures
查看对应trace日志
vi /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5163.trc
......
*** 2024-10-13 12:57:27.534
Stopping background process SMCO
Read of datafile '/oracle/app/oracle/oradata/cjc/system01.dbf' (fno 1) header failed with ORA-01210
Hex dump of (file 1, block 1)
Dump of memory from 0x00007FFFF4AC0C00 to 0x00007FFFF4AC2C00
7FFFF4AC0C00 0000A21E 0140000A 000EBB61 04010000 [......@.a.......]
7FFFF4AC0C10 000001E9 00000005 00364080 00000000 [.........@6.....]
7FFFF4AC0C20 00000000 0000F800 00000000 00000000 [................]
7FFFF4AC0C30 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
7FFFF4AC2BF0 00000000 00000000 00000000 BB611E01 [..............a.]
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.000ebb61 seq: 0x1 flg: 0x04
......
强制关闭数据库
SQL> shutdown abort
启动数据库，报错
SQL> startup
ORACLE instance started.
Total System Global Area 563691520 bytes
Fixed Size 2255232 bytes
Variable Size 222299776 bytes
Database Buffers 335544320 bytes
Redo Buffers 3592192 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oracle/app/oracle/oradata/cjc/system01.dbf'
ORA-01210: data file header is media corrupt
ORA-01210 错误说明:
[oracle@cjc-db-01 ~]$ oerr ora 01210
01210, 00000, "data file header is media corrupt"
// *Cause: The file header block is internally inconsistent. The beginning
// of the block has a header with a checksum and other data for
// insuring the consistancy of the block. It is possible that
// the last disk write did not operate correctly. The most likely
// problem is that this is not a datafile for any database.
// *Action: Have operating system make correct file available to database.
// If the trace file dump indicates that only the checksum is wrong,
// restore from a backup and do media recovery.
通过 dbv 工具进行查看:
oracle@cjc-db-01 ~]$ dbv file=/oracle/app/oracle/oradata/cjc/system01.dbf start=1 end=2
DBVERIFY: Release 11.2.0.4.0 - Production on Sun Oct 13 13:02:49 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/app/oracle/oradata/cjc/system01.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad header found during dbv:
Data in bad block:
type: 30 format: 2 rdba: 0x0140000a
last change scn: 0x0000.000ebb61 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xbb611e01
check value in block header: 0x1e9
computed block checksum: 0x0
Page 2 is marked corrupt
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv:
Data in bad block:
type: 29 format: 2 rdba: 0x00400002
last change scn: 0x0000.0010a41d seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xa41d1d02
check value in block header: 0xac8c
computed block checksum: 0x0
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): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
文件头：无效的块类型
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map /v
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
BBED-00400: invalid blocktype (30)
用file 2 block 1 构造file 1 block 1 结构
BBED> set file 2 block 1
FILE# 2
BLOCK# 1
BBED> map /v
File: /oracle/app/oracle/oradata/cjc/sysaux01.dbf (2)
Block: 1 Dba:0x00800001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
sword kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
BBED> copy file 2 block 1 to file 1 block 1
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
0ba20000 01008000 00000000 00000104 73570000 00000000 0004200b c554a7e3
434a4300 00000000 f7030000 00040100 00200000 02000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 2a070000 00000000 81c92131 88582b46 06200e00 00000000 00000000
00000000 00000000 00000400 7a000000 812e4646 79000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 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 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 b2e51000 00000000 86cc7746 01000000 0d000000 994f0100 10000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x5773, required = 0x5773
查看文件头的结构
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map /v
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
sword kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
五:SYSTEM文件头损坏详细修复过程
1. rdba_kcbh(offset 4) 文件头block的rdba地址
2. kccfhfsz (offset 44) 文件大小
3. kccfhfno (offset 52) datafile文件号
4. kcvfhrdb (offset 96) root dba
5. kscnbas (offset 100) v$datafile.creation_change#
6. kcvfhcrt (offset 108) v$datafile.creation_time
7. kcvfhsta (offset 138) 文件状态
8. kcvfhtsn (offset 332) 表空间号v$datafile.ts#
9. kcvfhtln (offset 336) 表空间名称字符长度
10.kcvfhtnm (offset 338) 表空间名称v$tablespace.name
11.kcvfhrfn (offset 368) 相对文件号v$datafile.rfile#
12.kscnbas (offset 484) checkpoint scn
13.kcvcptim (offset 492) last checkpoint time
---14.kcvfhcpc (offset 144) Datafile checkpoint count(不需要)
1.BBED修复文件头block的rdba地址
BBED> p kcvfhbfh.rdba_kcbh
ub4 rdba_kcbh @4 0x00800001
rdba地址转换
select
dbms_utility.data_block_address_file(TO_NUMBER('800001','XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER('800001','XXXXXXXX')) block_id
from dual;
注意:
此查询需要在open状态下执行，可以找一个相同版本数据库下执行，结果如下:
FILE_ID BLOCK_ID
---------- ----------
2 1
在mount下执行，报如下错误:
ERROR at line 3:
ORA-00904: "DBMS_UTILITY"."DATA_BLOCK_ADDRESS_BLOCK": invalid identifier
最终需要将上面查询的FILE_ID=2 改成 FILE_ID=1；
32个字节，前10个bit文件号，后22个bit块号
0000 0000 1000 >>0000 0000 10 得出是2号文件，现在要修改为1号文件0000 0000 0100>>0x00400001 转换成十六进制
BBED修复文件头block的rdba地址 ( Cont … )
BBED> set file 1 block 1 offset 4 count 32
FILE# 1
BLOCK# 1
OFFSET 4
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 4 to 35 Dba:0x00400001
------------------------------------------------------------------------
01008000 00000000 00000104 73570000 00000000 0004200b c554a7e3 434a4300
顺序是反的，所以要修改的值 00400001 应该是 01004000
BBED> modify /x 01004000 offset 4
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 4 to 35 Dba:0x00400001
------------------------------------------------------------------------
01004000 00000000 00000104 73570000 00000000 0004200b c554a7e3 434a4300
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x57b3, required = 0x57b3
BBED> p kcvfhbfh.rdba_kcbh
ub4 rdba_kcbh @4 0x00400001
验证:
select
dbms_utility.data_block_address_file(TO_NUMBER('400001','XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER('400001','XXXXXXXX')) block_id
from dual;
FILE_ID BLOCK_ID
---------- ----------
1 1
2.BBED修复文件头的文件大小
BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz @44 0x00010400
[oracle@cjc-db-01 cjc]$ ls -l system01.dbf
-rw-r----- 1 oracle oinstall 786440192 Oct 13 13:46 system01.dbf
##减去os层面的0号块8192
select (786440192-8192)/8192 from dual;
(786440192-8192)/8192
---------------------
96000
select to_char(96000,'xxxxxxxxxxxxxxx') from dual;
TO_CHAR(96000,'X
----------------
17700
BBED> set file 1 block 1 offset 44 count 32
FILE# 1
BLOCK# 1
OFFSET 44
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 44 to 75 Dba:0x00400001
------------------------------------------------------------------------
00040100 00200000 02000300 00000000 00000000 00000000 00000000 00000000
其中p kcvfhhdr.kccfhfsz查询 00010400，在dump查询的是反序的00040100，所以需要将17700反序，先补全 00017700，在反序:00770100
BBED> modify /x 00770100
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 44 to 75 Dba:0x00400001
------------------------------------------------------------------------
00770100 00200000 02000300 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x24b3, required = 0x24b3
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 44 to 75 Dba:0x00400001
------------------------------------------------------------------------
00770100 00200000 02000300 00000000 00000000 00000000 00000000 00000000
3.BBED修复文件头的文件号
BBED> p kcvfhhdr.kccfhfno
ub2 kccfhfno @52 0x0002
BBED> set file 1 block 1 offset 52 count 32
FILE# 1
BLOCK# 1
OFFSET 52
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 52 to 83 Dba:0x00400001
------------------------------------------------------------------------
02000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
2号文件，改成1号文件
BBED> modify /x 01 offset 52
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 52 to 83 Dba:0x00400001
------------------------------------------------------------------------
01000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x24b0, required = 0x24b0
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 52 to 83 Dba:0x00400001
------------------------------------------------------------------------
01000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
4.BBED修复文件头的root数据块号
SQL> select fhrdb,FHFNO from x$kcvfh order by 2;
FHRDB FHFNO
---------- ----------
4194824 1
0 2
0 3
0 4
0 5
注意:查询 x$kcvfh 需要在open状态，如果是mount状态，FHRDB 结果都是0。
SQL> select to_char(4194824,'xxxxxxxxxx') from dual;
TO_CHAR(419
-----------
400208
SQL> select
dbms_utility.data_block_address_file(TO_NUMBER('400208', 'XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER('400208', 'XXXXXXXX')) block_id
from dual;
FILE_ID BLOCK_ID
---------- ----------
1 520
SQL> select to_char(520,'xxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(520,'XXXXXXX
--------------------
208
SQL> select to_char(520,'xxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(520,'XXXXXXX
--------------------
208 =>00400208
BBED> p kcvfhrdb.kcvfhrdb
ub4 kcvfhrdb @96 0x00000000
BBED> set file 1 block 1 offset 96 count 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 96 to 127 Dba:0x00400001
------------------------------------------------------------------------
00000000 2a070000 00000000 81c92131 88582b46 06200e00 00000000 00000000
将 00400208 取反 08024000
BBED> modify /x 08024000
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 96 to 127 Dba:0x00400001
------------------------------------------------------------------------
08024000 2a070000 00000000 81c92131 88582b46 06200e00 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x26f8, required = 0x26f8
5.BBED修复文件头的文件创建SCN
BBED> p kcvfhcrs
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x0000072a
ub2 kscnwrp @104 0x0000
SQL> select file#,creation_change# from v$datafile;
FILE# CREATION_CHANGE#
---------- ----------------
1 7
2 1834
3 923328
4 16143
5 965449
SQL> select to_char(1834,'xxxxxxxxxx') from dual;
TO_CHAR(183
-----------
72a
1号文件的CREATION_CHANGE#由1834改成7:
BBED> set file 1 block 1 offset 100 count 32
FILE# 1
BLOCK# 1
OFFSET 100
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 100 to 131 Dba:0x00400001
------------------------------------------------------------------------
2a070000 00000000 81c92131 88582b46 06200e00 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 07000000 offset 100
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 100 to 131 Dba:0x00400001
------------------------------------------------------------------------
07000000 00000000 81c92131 88582b46 06200e00 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x21d5, required = 0x21d5
6.BBED修复文件头的文件创建时间
BBED> p kcvfhcrt
ub4 kcvfhcrt @108 0x3121c981
BBED> set file 1 block 1 offset 108 count 32
FILE# 1
BLOCK# 1
OFFSET 108
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 108 to 139 Dba:0x00400001
------------------------------------------------------------------------
81c92131 88582b46 06200e00 00000000 00000000 00000000 00000000 00000400
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 2013-08-24 11:37:33 824297853
2 2013-08-24 11:37:37 824297857
3 2013-08-24 12:07:19 824299639
4 2013-08-24 11:37:49 824297869
5 2024-08-17 13:10:02 1177247402
SQL> select to_char(824297853,'xxxxxxxxxxxx') from dual;
TO_CHAR(82429
-------------
3121c97d
将 3121c97d 取反:7dc92131
BBED> modify /x 7dc92131 offset 108
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 108 to 139 Dba:0x00400001
------------------------------------------------------------------------
7dc92131 88582b46 06200e00 00000000 00000000 00000000 00000000 00000400
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x2129, required = 0x2129
如果这里报错无效的number，则分开修改 108,109,110,111
例如 如果想改成d042be39
modify /x d042be39 offset 108
分开修改:
modify /x d0 offset 108
modify /x 42 offset 109
modify /x be offset 110
modify /x 39 offset 111
7.BBED修复文件头的文件状态
BBED> p offset 138
kcvfh.kcvfhsta
--------------
ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)
BBED> set file 1 block 1 offset 138 count 32
FILE# 1
BLOCK# 1
OFFSET 138
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 138 to 169 Dba:0x00400001
------------------------------------------------------------------------
04007a00 0000812e 46467900 00000000 00000000 00000000 00000000 00000000
状态说明:
Status Definition (from kcv3.h)
#define KCVFHHBP 0x01 /*hotbackup-in-process on file(fuzzy file)*/
#define KCVFHOFZ 0x04 /*Online FuZzy because it was online and db open*/
#define KCVFHMFZ 0x10 /*Media recovery FuZzy - file in media recovery */
#define KCVFHAFZ 0x40 /*Absolutely FuZzy - fuzzyness from file scan*/
当一个datafile处于fuzzy状态的时候，其kcvfhsta为0x04,这里是abort关闭，状态时04，不修改，如果是正常关闭，则是0x2000。
8.BBED修复文件头的表空间号
BBED> p kcvfhtsn
sword kcvfhtsn @332 1
BBED> set file 1 block 1 offset 332 count 32
FILE# 1
BLOCK# 1
OFFSET 332
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 332 to 363 Dba:0x00400001
------------------------------------------------------------------------
01000000 06005359 53415558 00000000 00000000 00000000 00000000 00000000
SQL> select file#,ts# from v$datafile;
FILE# TS#
---------- ----------
1 0
2 1
3 2
4 4
5 6
system01.dbf对应的TS#=0,这块需要改成0。
BBED> modify /x 00 offset 332
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 332 to 363 Dba:0x00400001
------------------------------------------------------------------------
00000000 06005359 53415558 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x2128, required = 0x2128
9.BBED修复文件头的表空间长度
BBED> p kcvfhtln
ub2 kcvfhtln @336 0x0006
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
......
SYSAUX 和 SYSTEM 表空间长度相同，不需要修改。
10.BBED修复文件头的表空间名称
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
......
BBED> set file 1 block 1 offset 338 count 32
FILE# 1
BLOCK# 1
OFFSET 338
COUNT 32
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 338 to 369 Dba:0x00400001
------------------------------------------------------------------------
53595341 55580000 00000000 00000000 00000000 00000000 00000000 00000200
SQL> select dump('SYSAUX',16) from dual;
DUMP('SYSAUX',16)
-------------------------------
Typ=96 Len=6: 53,59,53,41,55,58
SQL> select dump('SYSTEM',16) from dual;
DUMP('SYSTEM',16)
-------------------------------
Typ=96 Len=6: 53,59,53,54,45,4d
从341偏移量开始修改，前面SYS相同
BBED> set file 1 block 1 offset 341
FILE# 1
BLOCK# 1
OFFSET 341
BBED> modify /x 54454d
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 341 to 372 Dba:0x00400001
------------------------------------------------------------------------
54454d00 00000000 00000000 00000000 00000000 00000000 00000002 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x2138, required = 0x2138
查看:
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
......
11.BBED修复文件头的相对文件号
BBED> p kcvfhrfn
ub4 kcvfhrfn @368 0x00000002
BBED> set file 1 block 1 offset 368
FILE# 1
BLOCK# 1
OFFSET 368
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 368 to 399 Dba:0x00400001
------------------------------------------------------------------------
02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
SQL> select file#,rfile# from v$datafile;
FILE# RFILE#
---------- ----------
1 1
2 2
3 3
4 4
5 5
BBED> modify /x 01 offset 368
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 368 to 399 Dba:0x00400001
------------------------------------------------------------------------
01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> sum apply
Check value for File 1, Block 1:
current = 0x213b, required = 0x213b
12.BBED修复文件头的检查点SCN
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0010e5b2
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x4677cc86
ub2 kcvcpthr @496 0x0001
......
set line 300
select file#,CREATION_CHANGE#,CHECKPOINT_CHANGE# ,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE# from v$datafile order by 1;
FILE# CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ---------------- ------------------ --------------------- ------------ ---------------
1 7 1107378 0 925701
2 1834 1107378 0 925701
3 923328 1107378 0 925701
4 16143 1107378 0 925701
5 965449 1107378 0 0
SQL> select to_char(1107378,'xxxxxxxxxxxxxxxx') from dual;
TO_CHAR(1107378,'
-----------------
10e5b2
和 ub4 kscnbas @484 0x0010e5b2 值相等，不需要修改。
13.BBED修复文件头的检查点时间
BBED> set file 1 block 1 offset 492
FILE# 1
BLOCK# 1
OFFSET 492
BBED> dump
File: /oracle/app/oracle/oradata/cjc/system01.dbf (1)
Block: 1 Offsets: 492 to 523 Dba:0x00400001
------------------------------------------------------------------------
86cc7746 01000000 0d000000 994f0100 10000000 02000000 00000000 00000000
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-10-13 12:31:02 1182256262
2 2024-10-13 12:31:02 1182256262
3 2024-10-13 12:31:02 1182256262
4 2024-10-13 12:31:02 1182256262
5 2024-10-13 12:31:02 1182256262
select to_char(1182256262,'xxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(1182256262,'XXXX
------------------------
4677cc86
和 kcvcptim 0x4677cc86 值相同，不需要修改。
通过dbv检查下文件头修改是否都正确
[oracle@cjc-db-01 cjc]$ dbv file=/oracle/app/oracle/oradata/cjc/system01.dbf start=1 end=2
DBVERIFY: Release 11.2.0.4.0 - Production on Sun Oct 13 15:51:32 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/app/oracle/oradata/cjc/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 : 1090589 (0.1090589)
启动数据库:
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
cjc MOUNTED
成功!
SQL> alter database open;
Database altered.
参考文章:《04 bbed修复system文件头损坏》，链接如下:
https://www.cnblogs.com/yhq1314/p/10870998.html
###chenjuchao 20241013###
欢迎关注我的公众号《IT小Chen》
扫一扫
1368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
