Oracle数据库SYSTEM文件头损坏详细修复过程

小周的数据库进阶之路 2024-11-04

193

一:通过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》

文章转载自小周的数据库进阶之路，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle数据库SYSTEM文件头损坏详细修复过程

评论