将打印出来的kcvfh结构另存为recover_sequence8.txt
我们采用diff命令来查看2个文件的不同之处:
[oracle@db2server ~]$ diff recover_sequence7.txt recover_sequence8.txt
13c13
< ub2 chkval_kcbh @16 0xc785
---
> ub2 chkval_kcbh @16 0xc404
27c27
< ub4 kccfhcsq @40 0x00000325
---
> ub4 kccfhcsq @40 0x00000327
83c83
< ub4 kscnbas @484 0x000cf775
---
> ub4 kscnbas @484 0x000cf786
85c85
< ub4 kcvcptim @492 0x2f171759
---
> ub4 kcvcptim @492 0x2f171773
89c89
< ub4 kcrbaseq @500 0x00000008
---
> ub4 kcrbaseq @500 0x00000009
101c101
< ub4 kcvfhrts @144 0x2f171995
---
> ub4 kcvfhrts @144 0x2f171ace
[oracle@db2server ~]$
我们发现ORACLE一共改了6个地方:
偏移量16为块的校验值ub2 chkval_kcbh原来为0xc785 后来为0xc404
偏移量40为control sequence ub4 kccfhcsq 原先为0x00000325后来为0x00000327
偏移量484为 ub4 kscnbas --SCN of last change to the datafile.
偏移量492为 ub4 kcvcptim --Time of the last change to the datafile
偏移量500为 ub4 kcrbaseq --RECOVER需要的下一个日志序列号
偏移量144,我也不知道是干啥的
重点修改的内容为偏移量484,偏移量492,偏移量500,chkval_kcbh最后通过bbed sum apply来得到,其他2个地方不修改问题也不大。
那么这3个地方要修改为啥呢?
偏移量484需要修改为需要归档日志文件的NEXT_CHANGE#。
如下:
日志序列号7的NEXT_CHANGE#为:
SQL> select to_number('cf775','xxxxxxxx') from dual;
TO_NUMBER('CF775','XXXXXXXX')
-----------------------------
849781
日志序列号8的NEXT_CHANGE#为:
SQL> select to_number('cf786','xxxxxxxx') from dual;
TO_NUMBER('CF786','XXXXXXXX')
-----------------------------
849798
日志序列号9的NEXT_CHANGE#可以通过如下方式查到,就是849810,转为16进制就是0000cf792。
SQL> select SEQUENCE#,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME from v$archived_log order by 1;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------------- ------------ -------------------
6 848360 2012-07-31 00:03:06 849604 2012-07-31 00:08:36
7 849604 2012-07-31 00:08:36 849781 2012-07-31 00:14:17
8 849781 2012-07-31 00:14:17 849798 2012-07-31 00:14:43
9 849798 2012-07-31 00:14:43 849810 2012-07-31 00:14:58
10 849810 2012-07-31 00:14:58 849819 2012-07-31 00:15:10
11 849819 2012-07-31 00:15:10 849834 2012-07-31 00:15:37
6 rows selected.
偏移量492需要修改为需要归档日志文件的NEXT_TIME#。
但是这里存放的不是时间值,而是从1988年1月1日起到现在所经历的秒数。
看看前面的这2个值
< ub4 kcvcptim @492 0x2f171759
---
> ub4 kcvcptim @492 0x2f171773
SQL> select to_number('2f171759','xxxxxxxx') from dual;
TO_NUMBER('2F171759','XXXXXXXX')
--------------------------------
790042457
SQL> select to_number('2f171773','xxxxxxxx') from dual;
TO_NUMBER('2F171773','XXXXXXXX')
--------------------------------
790042483
SQL> select 790042483-790042457 from dual;
790042483-790042457
-------------------
26
从下面的查询我们也可以看到7和8的NEXT_TIME就是差了26秒,8和9之间差了15秒。
SQL> select SEQUENCE#,NEXT_TIME from v$archived_log order by 1;
SEQUENCE# NEXT_TIME
---------- -------------------
6 2012-07-31 00:08:36
7 2012-07-31 00:14:17
8 2012-07-31 00:14:43
9 2012-07-31 00:14:58
10 2012-07-31 00:15:10
11 2012-07-31 00:15:37
6 rows selected.
因此这里存放的值修改为0x2f171773+15=790042483+15=790042498=0x2f171782
偏移量500更简单了修改为10就行了。
有了上面的知识我们回到RECOVER窗口:
SQL> recover datafile 6;
ORA-00279: change 849630 generated at 07/31/2012 00:09:16 needed for thread 1
ORA-00289: suggestion : /archivelog/1_7_789791289.dbf
ORA-00280: change 849630 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/archivelog/1_7_789791289.dbf
ORA-00279: change 849781 generated at 07/31/2012 00:14:17 needed for thread 1
ORA-00289: suggestion : /archivelog/1_8_789791289.dbf
ORA-00280: change 849781 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/archivelog/1_8_789791289.dbf
ORA-00279: change 849798 generated at 07/31/2012 00:14:43 needed for thread 1
ORA-00289: suggestion : /archivelog/1_9_789791289.dbf
ORA-00280: change 849798 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/archivelog/1_9_789791289.dbf
ORA-00308: cannot open archived log '/archivelog/1_9_789791289.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
我们在应用日志文件/archivelog/1_9_789791289.dbf的时候报错了,这也是意料之中的。
下面我们进行修改上面提到的3个地方,由于我的是LINUX平台属于LITTLE的字节序,因此修改的时候要注意顺序。
修改的时候 0000cf792 需要写成92f70c00的形式。
BBED> set offset 484
OFFSET 484
BBED> dump /v count 16
File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
Block: 1 Offsets: 484 to 499 Dba:0x00000000
-------------------------------------------------------
86f70c00 00000000 7317172f 01000000 l ........s../....
<16 bytes per line>
BBED> set mode edit
MODE Edit
BBED> modify /x 92
File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
Block: 1 Offsets: 484 to 499 Dba:0x00000000
------------------------------------------------------------------------
92f70c00 00000000 7317172f 01000000
<32 bytes per line>
BBED> set offset 492
OFFSET 492
BBED> dump /v count 16
File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
Block: 1 Offsets: 492 to 507 Dba:0x00000000
-------------------------------------------------------
7317172f 01000000 09000000 02000000 l s../............
<16 bytes per line>
BBED> modify /x 82
File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
Block: 1 Offsets: 492 to 507 Dba:0x00000000
------------------------------------------------------------------------
8217172f 01000000 09000000 02000000
<32 bytes per line>
BBED> set offset 500
OFFSET 500
BBED> dump /v count 16
File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
Block: 1 Offsets: 500 to 515 Dba:0x00000000
-------------------------------------------------------
09000000 02000000 00006c10 02000000 l ..........l.....
<16 bytes per line>
BBED> modify /x 0a
File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
Block: 1 Offsets: 500 to 515 Dba:0x00000000
------------------------------------------------------------------------
0a000000 02000000 00006c10 02000000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 1:
current = 0xc4e2, required = 0xc4e2
重新进行RECOVER
[oracle@db2server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 31 01:22:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover datafile 6
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from htyansp.test;
SEQ
--------------------
sequence 7
sequence 8
sequence 10
sequence 11
SQL>
由于记录"sequence 9" 没有应用归档日志文件9,导致丢失。