本位参考自:http://www.xifenfei.com/1527.html
目的:将已经offline掉的datafile 5 的scn信息改为与其他datafile一致。
db版本为11.2.0.4
背景知识:
1、datafile 的file header 存储在第一个block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:(不同oracle版本offset可能不同)
(1)kscnbas (at offset 140) – SCN of last change to the datafile.
(2)kcvcptim (at offset 148) -Time of the last change to the datafile.
(3)kcvfhcpc (at offset 176) – Checkpoint count.
(4)kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致,如果都一致,可以正常操作,如果不一致,那么会报ORA-01113错误
bbed password=blockedit blocksize=8192 listfile=/home/oracle/bbed.file mode=edit
/home/oracle/bbed.file的内容如下:
1 /u01/app/oracle/oradata/test/system01.dbf 849346560
2 /u01/app/oracle/oradata/test/sysaux01.dbf 3470786560
3 /u01/app/oracle/oradata/test/undotbs01.dbf 251658240
4 /u01/app/oracle/oradata/test/users01.dbf 382730240
5 /u01/app/oracle/oradata/test/ten01.dbf 52428800
6 /u01/app/oracle/oradata/test/tb_test_01.dbf 5242880
7 /u01/app/oracle/oradata/test/ts1.dbf 524288000
8 /u01/app/oracle/oradata/test/ts2.dbf 524288000
9 /u01/app/oracle/oradata/test/test01.dbf 52428800
10 /u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf 52428800
11 /u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf 209715200
12 /u01/app/oracle/oradata/test/test.dbf 10485760
如上内容,可以用如下语句来生成:
select file#||' '||name||' '||bytes from v$datafile;
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0036b5c8--->
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x344bc95d--->
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000001
ub4 kcrbabno @504 0x000021e7
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000168--->
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000167--->
BBED> set dba 5,1
DBA 0x01400001 (20971521 5,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00369818--->
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x344b98b3--->
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000098
ub4 kcrbabno @504 0x0000b62c
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000104--->
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000103--->
======================================================================================
下面开始修改:
BBED> set dba 5,1
DBA 0x01400001 (20971521 5,1)
BBED> m /x c8b53600 offset 484
BBED-00209: invalid number (c8b53600)
BBED> m /x c8b5
File: /u01/app/oracle/oradata/test/ten01.dbf (5)
Block: 1 Offsets: 484 to 487 Dba:0x01400001
------------------------------------------------------------------------
c8b53600
<32 bytes per line>
BBED> set offset +2
OFFSET 486
BBED> m /x 3600
File: /u01/app/oracle/oradata/test/ten01.dbf (5)
Block: 1 Offsets: 486 to 489 Dba:0x01400001
------------------------------------------------------------------------
36000000
<32 bytes per line>
BBED> m /x 5dc94b34 offset 492
File: /u01/app/oracle/oradata/test/ten01.dbf (5)
Block: 1 Offsets: 492 to 495 Dba:0x01400001
------------------------------------------------------------------------
5dc94b34
<32 bytes per line>
BBED> m /x 68010000 offset 140
File: /u01/app/oracle/oradata/test/ten01.dbf (5)
Block: 1 Offsets: 140 to 143 Dba:0x01400001
------------------------------------------------------------------------
68010000
<32 bytes per line>
BBED> m /x 67010000 offset 148
File: /u01/app/oracle/oradata/test/ten01.dbf (5)
Block: 1 Offsets: 148 to 151 Dba:0x01400001
------------------------------------------------------------------------
67010000
<32 bytes per line>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0036b5c8
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x344bc95d
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000098
ub4 kcrbabno @504 0x0000b62c
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000168
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000167
BBED> sum
Check value for File 5, Block 1:
current = 0xc16c, required = 0xbd5a
BBED> sum apply
Check value for File 5, Block 1:
current = 0xbd5a, required = 0xbd5a
BBED>
SQL> select file#,to_char(checkpoint_change#,'999999999999'),to_char(RESETLOGS_CHANGE#,'999999999999')
from v$datafile_header; 2
FILE# TO_CHAR(CHECK TO_CHAR(RESET
----- ------------- -------------
1 3585483 3580553
2 3585483 3580553
3 3585483 3580553
4 3585483 3580553
5 3585480 995548 --->3585480跟3585483还是不一样。
6 3585483 3580553
7 3585483 3580553
8 3585483 3580553
9 3585483 3580553
10 3585483 3580553
11 3585483 3580553
12 3395372 995548
12 rows selected.
SQL> recover datafile 5;--->recover是不行滴,原因是该datafile是属于在 resetlog之前就已经offline的数据文件
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 5 belongs to an orphan incarnation
ORA-01110: data file 5: '/u01/app/oracle/oradata/test/ten01.dbf'