Being able to plug in an older version of a file
into the current instance could be a huge save
in terms of recovery. At one point in time, a
file has certain values related to its state,
and that state is consistent with other files in
the database. When the state is off, Oracle
informs the DBA about it right away via one or
more error messages, especially the one about a
file needing more media recovery. In a normal
recovery scenario where media loss has occurred,
the recovery consists of restoring a backed up
copy of one or more datafiles and then applying
archived redo to bring the file to a consistent
state.
In the bbed recovery scenario, one will not be
applying redo, but rather will be jumping the
state of the file from a point in the past to a
point in time consistent with the rest of the
database. The specific part of the file to be
edited is the file header. Information will be
needed from three structs: kcvfhckp, kcvfhcpc,
and kcvfhccc. Published elsewhere are decode
tables for many of these items, and these are
generally easy to interpret. Kernel-related
codes begin with a k, fh looks like file header,
and the remainder are related to checkpoints.
Specifically, good values from the following are
needed:
-
kscnbas – last change SCN
-
kcvcptim – time of the last change
-
kcvfhcpc – checkpoint count
-
kcvfhccc – a checkpoint checker value, which
is one less than kcvfhcpc
For file headers, one is interested in the
output of the kcvfh struct. The output of “p
kcvfh” is long, but it is interesting to browse
through the output. It is also interesting how
the name of the database (ORCL2 here) appears
spelled out.
BBED> p kcvfh
struct kcvfh, 676 bytes
@0
struct kcvfhbfh, 20 bytes
@0
ub1 type_kcbh
@0
0x0b
ub1 frmt_kcbh
@1
0xa2
ub1 spare1_kcbh
@2
0x00
ub1 spare2_kcbh
@3
0x00
ub4 rdba_kcbh
@4
0x01800001
ub4 bas_kcbh
@8
0x00000000
ub2 wrp_kcbh
@12
0x0000
ub1 seq_kcbh
@14
0x01
ub1 flg_kcbh
@15
0x04 (KCBHFCKV)
ub2 chkval_kcbh
@16
0x8f50
ub2 spare3_kcbh
@18
0x0000
struct kcvfhhdr, 76 bytes
@20
ub4 kccfhswv
@20
0x00000000
ub4 kccfhcvn
@24
0x0a200100
ub4 kccfhdbi
@28
0x266ecc46
text kccfhdbn[0]
@32
O
text kccfhdbn[1]
@33
R
text kccfhdbn[2]
@34
C
text kccfhdbn[3]
@35
L
text kccfhdbn[4]
@36
2
Here is the test case. A new tablespace (BB),
user (BB) and table (EMP) where the table has
three rows and three columns from Scott’s EMP
table is created. The datafile is bb01.dbf,
created with a size of 100K. A backup exists
where the three rows are in place. The current
instance has had a deletion of all three rows
and the object is to restore the data by
recovering the older datafile. Therefore, get a
new list of files for use within the parameter
file, and the copy of bb01.dbf will be the older
version. Only two files are really needed – one
with a good SCN state, and the older file, but
all were listed anyway.
1 /opt/app/oracle/oradata/ORCL2/system01.dbf
513802240
2 /opt/app/oracle/oradata/ORCL2/undotbs01.dbf
36700160
3 /opt/app/oracle/oradata/ORCL2/sysaux01.dbf
272629760
4 /opt/app/oracle/oradata/ORCL2/users01.dbf
5242880
5 /opt/app/oracle/oradata/ORCL2/example01.dbf
104857600
6 /opt/app/oracle/oradata/ORCL2/bb01.dbf
106496
Replace the file and issue a startup command.
One should see the error related to the “bad”
file.
SQL> conn / as sysdba
Connected to an idle
instance.
SQL> startup
ORACLE instance started.
Total System Global Area
922746880 bytes
Fixed Size
1222624 bytes
Variable Size
281020448 bytes
Database Buffers
633339904 bytes
Redo Buffers
7163904 bytes
Database mounted.
ORA-01113: file 6 needs
media recovery
ORA-01110: data file 6:
'/opt/app/oracle/oradata/ORCL2/bb01.dbf'
The current checkpoint number is not necessarily
needed, but since it can be pulled directly from
the database, see what it is. It will also show
up in the file itself courtesy of bbed. The
current SCN is at 689110 and the bad file is at
685758.
SQL> select distinct
checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
689110
SQL> select change# from
v$recover_file;
CHANGE#
----------
685758
Start a bbed session and print the kcvfhckp
struct. The first few lines are shown and the
values of interest are the SCN and last time.
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes
@484
struct kcvcpscn, 8 bytes
@484
ub4 kscnbas
@484
0x000a83d6
ub2 kscnwrp
@488
0x0000
ub4 kcvcptim
@492
0x2799048e
The hexadecimal value 0x000a83d6 should convert
to 689110 in base 10 (decimal). Use the
scientific calculator on Windows, something on
the Internet, or get the decimal value manually.
The fun way is by hand. The significant part of
the hex value is a83d6. Convert hex to binary
where each hex character is xxxx in binary. Now
there is:
|
a
|
8
|
3
|
d
|
6
|
|
1010
|
1000
|
0011
|
1101
|
0110
|
Binary 1010100001111010110 is 689110, so that is
good. Now print kcvfhcpc and kcvfhccc. The
SYSTEM datafile is used by setting dba 1,1.
BBED> p kcvfhcpc
ub4 kcvfhcpc
@140
0x0000004a
BBED> p kcvfhccc
ub4 kcvfhccc
@148
0x00000049
One other, albeit final complication in all of
this is taking into account the byte ordering of
data on the platform the server is running on.
It is big endian versus little endian. The
database is running Oracle Enterprise Linux on a
PC, so that makes it little endian. The order of
the first two values has to be reversed (pair by
pair). To summarize the changes to be made to
the older file, see the table below.
|
Attribute
|
kscnbas
|
kcvcptim
|
kcvfhcpc
|
kcvfhccc
|
|
Value
|
000a83d6
(d6830a00)
|
2799048e
(8e049927)
|
4a
|
49
|
|
Offset
|
484
|
492
|
140
|
148
|
Use the modify command with
/x for hexadecimal editing and a
dba of file 6, block 1.
When finished with the four modify statements,
perform a “sum dba x,1 apply” where “x” is your
file number. If after applying the changes one
receives an error upon startup, check the SCN
values output from:
SELECT FILE#, CHANGE# FROM
V$RECOVER_FILE; and
SELECT V1.GROUP#, MEMBER,
SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE
V2
WHERE V1.GROUP# =
V2.GROUP#;
If the SCN for the recovered file is wildly
different from the others, the byte ordering of
the hex value during the modify command might
have been reversed.
SQL> startup
ORACLE instance started.
Total System Global Area
922746880 bytes
Fixed Size
1222624 bytes
Variable Size
281020448 bytes
Database Buffers
633339904 bytes
Redo Buffers
7163904 bytes
Database mounted.
ORA-01122: database file 6
failed verification check
ORA-01110: data file 6:
'/opt/app/oracle/oradata/ORCL2/bb01.dbf'
ORA-01207: file is more
recent than control file - old control file
