This document outlines the procedure for recovering a corrupted SYSTEM datafile header using the Oracle BBED utility.
1. Initial Diagnostics and Trace Analysis
Begin by starting the database in MOUNT mode and enabling SQL trace to capture diagnostic information during the OPEN attempt. This will help identify which datafiles and blocks are being accessed and potentially failing.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 511708728 bytes
Database Buffers 264241152 bytes
Redo Buffers 6791168 bytes
Database mounted.
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> alter database open;
Database altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11247.trc
Examine the trace file to pinpoint reads of block 1 from each datafile during startup. A db file sequential read on file# 1 indicates a potential issue with the SYSTEM datafile's header.
[oracle@DSI ~]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11247.trc
WAIT #139760412634104: nam='control file sequential read' ela= 2 file#=0 block#=23 blocks=1 obj#=-1 tim=1557884751568850
WAIT #139760412634104: nam='db file sequential read' ela= 3 file#=1 block#=1 blocks=1 obj#=-1 tim=1557884751568874 ##数据文件1的1号块
WAIT #139760412634104: nam='db file sequential read' ela= 1 file#=2 block#=1 blocks=1 obj#=-1 tim=1557884751568894
WAIT #139760412634104: nam='db file sequential read' ela= 2 file#=3 block#=1 blocks=1 obj#=-1 tim=1557884751568911
WAIT #139760412634104: nam='db file sequential read' ela= 1 file#=4 block#=1 blocks=1 obj#=-1 tim=1557884751568922
WAIT #139760412634104: nam='db file sequential read' ela= 1 file#=5 block#=1 blocks=1 obj#=-1 tim=1557884751568929
2. BBED for SYSTEM Datafile Header Corruption Recovery
Perform a cold backup before attempting any recovery operations.
[oracle@DSI oradata]$ cp -rf orcl/ /home/oracle/.
Use BBED to inspect block 1 of datafile 1.
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/orcl/system01.dbf 94720
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 76800
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 144640
4 /u01/app/oracle/oradata/orcl/users01.dbf 640
5 /u01/app/oracle/oradata/orcl/test01.dbf 6400
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map /v
File: /u01/app/oracle/oradata/orcl/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: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
0ba20000 01004000 00000000 00000104 9bec0000 00000000 0004200b cf766f5b
4f52434c 00000000 c0170000 00770100 00200000 01000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
08024000 07000000 00000000 512bfa3b 4f2bfa3b 01000000 00000000 00000000
00000000 00000000 00000420 8c000000 b993173c 8b000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 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 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 21fd1700 00000000 3e171a3c 01000000 75000000 1e7b0000 10000000
<32 bytes per line>
Simulate corruption by overwriting block 1 of file 1 with block 10 of file 4.
BBED> copy file 4 block 10 to file 1 block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------
1ea20000 0a000101 553e0000 00000104 a6010000 04000000 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 = 0xa61e, required = 0xa61e
Attempting a normal shutdown will fail.
SQL> shutdown immediate
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-01210: data file header is media corrupt
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
... (startup messages) ...
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-01210: data file header is media corrupt
The database can only be mounted, indicating a need for media recovery.
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map /v
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
BBED-00400: invalid blocktype (30) ## The block is identified as invalid.
Constructing a valid header:
Overwrite block 1 of file 1 with block 1 of file 2.
BBED> set file 2 block 1
FILE# 2
BLOCK# 1
BBED> map /v
File: /u01/app/oracle/oradata/orcl/sysaux01.dbf (2)
Block: 1 Dba:0x00800001
------------------------------------------------------------
Data File Header
BBED> copy file 2 block 1 to file 1 block 1
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------
0ba20000 01008000 00000000 00000104 c4d80000 00000000 0004200b cf766f5b
4f52434c 00000000 c0170000 003b0100 00200000 02000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 07070000 00000000 522bfa3b 4f2bfa3b 01000000 00000000 00000000
00000000 00000000 00000400 8c000000 b993173c 8b000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 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
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 21fd1700 00000000 3e171a3c 01000000 75000000 1e7b0000 10000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0xc4d8, required = 0xc4d8
Check the structure of the file header:
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> map /v
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
3. BBED File Header Repair
The following fields in the datafile header are critical for recovery:
rdba_kcbh(offset 4): RDBA address of the header block.kccfhfsz(offset 44): File size.kccfhfno(offset 52): Datafile number.kcvfhrdb(offset 96): Root DBA.kscnbas(offset 100):v$datafile.creation_change#.kcvfhcrt(offset 108):v$datafile.creation_time.kcvfhsta(offset 138): File status.kcvfhtsn(offset 332): Tablespace number (v$datafile.ts#).kcvfhtln(offset 336): Length of the tablespace name.kcvfhtnm(offset 338): Tablespace name (v$tablespace.name).kcvfhrfn(offset 368): Relative file number (v$datafile.rfile#).kscnbas(offset 484): Checkpoint SCN.kcvcptim(offset 492): Last checkpoint time.kcvfhcpc(offset 144): Datafile checkpoint count.
3.1 Repairing the RDBA Address
BBED> p kcvfhbfh
struct kcvfhbfh, 20 bytes @0
ub4 rdba_kcbh @4 0x00800001
Convert the RDBA address to file and block numbers.
SQL> 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;
The RDBA is typically represented as 32 bits: the first 10 bits for the file number and the remaining 22 for the block number. In this case, 0x00800001 indicates file 2. We need to change this to file 1, resulting in 0x00400001.
BBED> set file 1 block 1 offset 4 count 32
FILE# 1
BLOCK# 1
OFFSET 4
COUNT 32
BBED> dump ## View the current value
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 4 to 35 Dba:0x00400001
------------------------------------------------------------
01008000 00000000 00000104 c4d80000 00000000 0004200b cf766f5b 4f52434c
<32 bytes per line>
BBED> modify /x 01004000 offset 4 ## Modify the RDBA
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 4 to 35 Dba:0x00400001
------------------------------------------------------------
01004000 00000000 00000104 c4d80000 00000000 0004200b cf766f5b 4f52434c
<32 bytes per line>
BBED> sum apply ## Apply changes
Check value for File 1, Block 1:
current = 0xd804, required = 0xd804
BBED> dump ## Verify the modification
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 4 to 35 Dba:0x00400001
------------------------------------------------------------
01004000 00000000 00000104 04d80000 00000000 0004200b cf766f5b 4f52434c
<32 bytes per line>
3.2 Repairing the File Size
BBED> p kcvfhhdr
ub4 kccfhfsz @44 0x00013b00
Check the file size from the operating system and calculate the size in blocks.
[oracle@DSI orcl]$ ll -l |grep system*
-rw-r----- 1 oracle oinstall 786440192 May 15 15:32 system01.dbf
SQL> select (786440192-8192)/8192 from dual; ## Subtract 8192 for the OS block 0
(786440192-8192)/8192
---------------------
96000
SQL> select to_char(96000,'xxxxxxxxxxxxxxx') from dual;
TO_CHAR(96000,'X
----------------
17700
Modify the file size field. Note that the hex value needs to be reversed (endianness).
BBED> set file 1 block 1 offset 44 count 32
FILE# 1
BLOCK# 1
OFFSET 44
COUNT 32
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 44 to 75 Dba:0x00400001
------------------------------------------------------------
003b0100 00200000 02000300 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 00770100 ## Reversed value for 0x17700
File: /u01/app/oracle/oradata/orcl/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 = 0x9404, required = 0x9404
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 44 to 75 Dba:0x00400001
------------------------------------------------------------
00770100 00200000 02000300 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
3.3 Repairing the File Number
BBED> p kcvfhhdr
ub2 kccfhfno @52 0x0002
Correct the file number to 1.
BBED> set file 1 block 1 offset 52 count 32
FILE# 1
BLOCK# 1
OFFSET 52
COUNT 32
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 52 to 83 Dba:0x00400001
------------------------------------------------------------
02000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 01 offset 52
File: /u01/app/oracle/oradata/orcl/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 = 0x9407, required = 0x9407
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 52 to 83 Dba:0x00400001
------------------------------------------------------------
01000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
3.4 Repairing the Root DBA
Query x$kcvfh to get the correct root DBA.
SQL> select fhrdb,FHFNO from x$kcvfh order by 2;
FHRDB FHFNO
---------- ----------
4194824 1
Convert the FHRDB to hexadecimal.
SQL> select to_char(4194824,'xxxxxxxxxx') from dual;
TO_CHAR(419
-----------
400208
Convert this to file and block numbers.
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
This indicates file 1, block 520. Adjust the value in the header accordingly. The correct value for kcvfhrdb should correspond to the RDBA of block 1 in file 1, which is 0x00400001. The value 0x08024000 seems to be derived from another file's header and needs correction. This requires reversing the hex value to match the endianness.
BBED> p kcvfhrdb
ub4 kcvfhrdb @96 0x00000000
BBED> set file 1 block 1 offset 96 count 32
FILE# 1
BLOCK# 1
OFFSET 96
COUNT 32
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 96 to 127 Dba:0x00400001
------------------------------------------------------------
00000000 07070000 00000000 522bfa3b 4f2bfa3b 01000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 08024000 ## Modify to the correct RDBA, reversed for endianness
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 96 to 127 Dba:0x00400001
------------------------------------------------------------
08024000 07070000 00000000 522bfa3b 4f2bfa3b 01000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x964f, required = 0x964f
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 96 to 127 Dba:0x00400001
------------------------------------------------------------
08024000 07070000 00000000 522bfa3b 4f2bfa3b 01000000 00000000 00000000
<32 bytes per line>
3.5 Repairing the Creation SCN
Query v$datafile for creation_change#.
SQL> select file#,creation_change# from v$datafile;
FILE# creation_change#
---------- ----------------
1 7
2 1799
3 2821
4 15940
5 929531
Convert the creation_change# for file 1 to hexadecimal.
SQL> select to_char(7,'XXXXXXXX') from dual;
TO_CHAR(7,
----------
7
The trace file indicates the current value is 0x00000707, which corresponds to file 2's ceration SCN. This needs to be corrected to the SCN for file 1.
BBED> p kcvfhcrs
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x00000707
ub2 kscnwrp @104 0x0000
BBED> set file 1 block 1 offset 100 count 32
FILE# 1
BLOCK# 1
OFFSET 100
COUNT 32
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 100 to 131 Dba:0x00400001
------------------------------------------------------------
07070000 00000000 522bfa3b 4f2bfa3b 01000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 07000000 offset 100 ## Correct to file 1's creation SCN
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 100 to 131 Dba:0x00400001
------------------------------------------------------------
07000000 00000000 522bfa3b 4f2bfa3b 01000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x914f, required = 0x914f
3.6 Repairing the Craetion Time
Query v$datafile for creation_time and convert it to an SCN.
SQL> 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 2019-04-22 10:07:13 1006250833
2 2019-04-22 10:07:14 1006250834
3 2019-04-22 10:07:15 1006250835
4 2019-04-22 10:07:20 1006250840
5 2019-04-22 17:12:12 1006276332
Convert the SCN for file 1 to hexadecimal.
SQL> select to_char(1006250833,'xxxxxxxxxxxx') from dual;
TO_CHAR(10062
-------------
3bfa2b51
Apply the corrected SCN to the header, reversing for endianness.
BBED> set file 1 block 1 offset 108 count 32
FILE# 1
BLOCK# 1
OFFSET 108
COUNT 32
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 108 to 139 Dba:0x00400001
------------------------------------------------------------
522bfa3b 4f2bfa3b 01000000 00000000 00000000 00000000 00000000 00000400
<32 bytes per line>
BBED> modify /x 512bfa3b offset 108 ## Correct the SCN, reversed for endianness
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 108 to 139 Dba:0x00400001
------------------------------------------------------------
512bfa3b 4f2bfa3b 01000000 00000000 00000000 00000000 00000000 00000400
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x914c, required = 0x914c
3.7 Repairing the File Status
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: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 138 to 169 Dba:0x00400001
------------------------------------------------------------
04008c00 0000b993 173c8b00 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
- The status
0x04(KCVFHOFZ) indicates a fuzzy file, which is expected after anABORTshutdown. If the database was shut down cleanly, the status would be0x2000.
3.8 Repairing the Tablespace Number
Query v$datafile for ts#.
SQL> select file#,ts# from v$datafile;
FILE# TS#
---------- ----------
1 0
2 1
3 2
4 4
5 5
The SYSTEM tablespace has ts# 0. Correct the kcvfhtsn field.
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: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 332 to 363 Dba:0x00400001
------------------------------------------------------------
01000000 06005359 53415558 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 00 offset 332 ## Set to 0 for SYSTEM tablespace
File: /u01/app/oracle/oradata/orcl/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 = 0x914d, required = 0x914d
3.9 Repairing the Tablespace Name Length
This field (kcvfhtln) indicates the length of the tablespace name. Ensure it matches the actual name length.
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
The length 0x06 is correct for SYSAUX, but the SYSTEM tablespace name should be SYSTEM.
3.10 Repairing the Tablespace Name
Modify the kcvfhtnm field to SYSTEM.
BBED> set file 1 block 1 offset 338 count 32
FILE# 1
BLOCK# 1
OFFSET 338
COUNT 32
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 338 to 369 Dba:0x00400001
------------------------------------------------------------
53595341 55580000 00000000 00000000 00000000 00000000 00000000 00000200
<32 bytes per line>
Correct the bytes corresponding to the name SYSTEM.
SQL> select dump('SYSTEM',16) from dual;
DUMP('SYSTEM',16)
-------------------------------
Typ=96 Len=6: 53,59,53,54,45,4d
Adjust the offset starting from 0x341 (D).
BBED> set file 1 block 1 offset 341 ## Start modification from offset 341
FILE# 1
BLOCK# 1
OFFSET 341
BBED> modify /x 54454d ## Modify bytes for 'TEM'
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 341 to 372 Dba:0x00400001
------------------------------------------------------------
54454d00 00000000 00000000 00000000 00000000 00000000 00000200 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x915d, required = 0x915d
3.11 Repairing the Relative File Number
Query v$datafile for rfile#.
SQL> select file#,rfile# from v$datafile;
FILE# RFILE#
---------- ----------
1 1
2 2
3 3
4 4
5 5
Correct the kcvfhrfn field to match the rfile#.
BBED> p kcvfhrfn
ub4 kcvfhrfn @368 0x00000002
BBED> set file 1 block 1 offset 368
FILE# 1
BLOCK# 1
OFFSET 368
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 368 to 399 Dba:0x00400001
------------------------------------------------------------
02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 01 offset 368 # Correct the RFILE number
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 368 to 399 Dba:0x00400001
------------------------------------------------------------
01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x915e, required = 0x915e
3.12 Repairing the Checkpoint SCN
Check the kscnbas field against v$datafile's checkpoint_change#.
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0017fd21
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3c1a173e
SQL> 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 1572129 0 0
2 1799 1572129 0 0
3 2821 1572129 0 0
4 15940 1572129 0 0
5 929531 1572129 0 0
Convert the checkpoint_change# to hexadecimal.
SQL> select to_char(1572129,'xxxxxxxxxxxxxxxx') from dual;
TO_CHAR(1572129,
-----------------
17fd21
The value 0x0017fd21 matches the expected checkpoint SCN, so no modification is needed.
3.13 Repairing the Checkpoint Time
Compare the kcvcptim value with the CHECKPOINT_TIME from v$datafile.
BBED> set file 1 block 1 offset 492
FILE# 1
BLOCK# 1
OFFSET 492
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 1 Offsets: 492 to 523 Dba:0x00400001
------------------------------------------------------------
3e171a3c 01000000 75000000 1e7b0000 10000000 02000000 00000000 00000000
<32 bytes per line>
SQL> 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 2019-05-15 15:14:06 1008342846
2 2019-05-15 15:14:06 1008342846
3 2019-05-15 15:14:06 1008342846
4 2019-05-15 15:14:06 1008342846
5 2019-05-15 15:14:06 1008342846
Convert the SCN to hexadecimal.
SQL> select to_char(1008342846,'xxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(1008342846,'XXXX
------------------------
3c1a173e
The value 0x3c1a173e matches the kcvcptim field, so no modification is needed.
4. Verification and Database Open
Run dbv to verify the integrity of the modified header.
[oracle@DSI ~]$ dbv file=/u01/app/oracle/oradata/orcl/system01.dbf start=1 end=2
DBVERIFY: Release 11.2.0.4.0 - Production on Wed May 15 16:48:53 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/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 : 1352932 (0.1352932)
Attempt to open the database.
SQL> alter database open;
Database altered.
The database should now open successfully. If errors persist, re-examine the modified header fields based on the error messages.
- Control File Too Old Error: If the control file is too old, you might need to:
- Modify the checkpoint counts:
kcvfhcpc(Datafile checkpoint count) andkcvfhccc(Controlfile Checkpoint Count). Typically,kcvfhcpcshould be greater thankcvfhccc + 1. - Manually create a new control file.
- Modify the checkpoint counts:
5. Exercises
1. Recovering a Corrupted Header for Datafile 2
Simulate and recover corruption for datafile 2's header, following a similar procedure as outlined for datafile 1.
2. Block Corruption in Datafile 1, Block 520 (No Backup)
Scenario: Datafile 1 (SYSTEM) block 520 is corrupted, and no backup is available.
Can it be recovered?
Directly overwriting with blocks from other files is not advisable for such critical blocks as their structures differ significantly. If possible, obtaining a clean copy of block 520 from another instance (e.g., in a RAC environment) and applying it using BBED is the preferred method. Alternatively, if the block's content is not critical and can be recreated or is part of unused space, it might be possible to skip or rebuild it, but this requires deep knowledge of the block's purpose.
To proceed, we need to examine the type and content of block 520.
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/orcl/system01.dbf 94720
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 76800
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 144640
4 /u01/app/oracle/oradata/orcl/users01.dbf 640
5 /u01/app/oracle/oradata/orcl/test01.dbf 6400
6 /u01/app/oracle/oradata/orcl/SYSTEM.259.1001364555 0
BBED> set file 1 block 520
FILE# 1
BLOCK# 520
BBED> map /v
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 520 Dba:0x00400208
------------------------------------------------------------
Unlimited Data Segment Header
... (structure details) ...
BBED> dump
File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
Block: 520 Offsets: 0 to 511 Dba:0x00400208
------------------------------------------------------------
10a20000 08024000 b9010000 00000204 43e40000 00000000 00000000 00000000
... (hex dump) ...
If a clean block 520 is available from another compatible SYSTEM datafile (e.g., from a RAC cluster, file SYSTEM.259.1001364555 in the example), it can be copied using BBED:
BBED> copy file 6 block 520 to file 1 block 520
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
... (hex dump of copied block) ...
BBED> sum apply
Check value for File 1, Block 520:
current = 0xe443, required = 0xe443
After applying the copied block, attempt to start the database:
SQL> startup
ORACLE instance started.
...
Database mounted.
Database opened.
3. How is '1988' Derived in the SQL Query?
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;
The value 1988 serves as a reference year for calculating the creation_name_scn. It represents the base year 1988-01-01 00:00:00 from which the SCN is calculated.