跳过归档日志的非常规恢复
首先我们先介绍一个小工具bbed,有一点需要提示:BBED的缺省口令为blockedit,请谨慎使用,内部工具。Oracle不做技术支持。
这个工具默认是不安装的,我们这里要编译一下
[oracle@BAK-8-201 ~]$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed [oracle@BAK-8-201 ~]$ cd $ORACLE_HOME/rdbms/lib/ [oracle@BAK-8-201 lib]$ ./bbed Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Nov 27 05:07:39 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> help HELP [ <bbed command> | ALL ]
BBED> help ALL SET DBA [ dba | file#, block# ] SET FILENAME 'filename' SET FILE file# SET BLOCK [+/-]block# SET OFFSET [ [+/-]byte offset | symbol | *symbol ] SET BLOCKSIZE bytes SET LIST[FILE] 'filename' SET WIDTH character_count SET COUNT bytes_to_display SET IBASE [ HEX | OCT | DEC ] SET OBASE [ HEX | OCT | DEC ] SET MODE [ BROWSE | EDIT ] SET SPOOL [ Y | N ] SHOW [ <SET parameter> | ALL ] INFO MAP[/v] [ DBA | FILENAME | FILE | BLOCK ] DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ] PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] </Nuf>: N - a number which specifies a repeat count. u - a letter which specifies a unit size: b - b1, ub1 (byte) h - b2, ub2 (half-word) w - b4, ub4(word) r - Oracle table/index row f - a letter which specifies a display format: x - hexadecimal d - decimal u - unsigned decimal o - octal c - character (native) n - Oracle number t - Oracle date i - Oracle rowid FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ] COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ] MODIFY[/x|d|u|o|c] numeric/character string [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] ASSIGN[/x|d|u|o] <target spec>=<source spec> <target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] <source spec> : [ value | <target spec options> ] SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ] PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ] POP [ALL] REVERT [ DBA | FILE | FILENAME | BLOCK ] UNDO HELP [ <bbed command> | ALL ] VERIFY [ DBA | FILE | FILENAME | BLOCK ] CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
|
Oracle Database 11g中缺省的未提供BBED库文件,但是可以用10g的文件编译出来,参考如下步骤:
1.复制Oracle 10g文件
Copy $ORA10g_HOME/rdbms/lib/ssbbded.o to $ORA11g_HOME/rdbms/lib Copy $ORA10g_HOME/rdbms/lib/sbbdpt.o to $ORA11g_HOME/rdbms/lib
Copy $ORA10g_HOME/rdbms/mesg/bbedus.msb to $ORA11g_HOME/rdbms/mesg Copy $ORA10g_HOME/rdbms/mesg/bbedus.msg to $ORA11g_HOME/rdbms/mesg Copy $ORA10g_HOME/rdbms/mesg/bbedar.msb to $ORA11g_HOME/rdbms/mesg
|
2.编译
make -f $ORA11g_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed |
步骤一、首先创建测试环境:
SQL> select name from v$dbfile;
NAME -------------------------------------------------------------------------------- /oracle/ora10/oradata/jscn/system01.dbf /oracle/ora10/oradata/jscn/undotbs01.dbf /oracle/ora10/oradata/jscn/sysaux01.dbf /oracle/ora10/oradata/jscn/users01.dbf
SQL> create tablespace jscntest datafile '/oradata/jscntest01.dbf' size 10m;
Tablespace created.
SQL> create user jscntest identified by jscntest;
User created.
SQL> alter user jscntest default tablespace jscntest;
User altered.
SQL> grant dba to jscntest;
Grant succeeded.
SQL> alter system switch logfile;
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oraarch Oldest online log sequence 48 Next log sequence to archive 50 Current log sequence 50
|
上面创建了一个用户jscntest 及其相应的表空间jscntest,当前的日志序列号是50。
步骤二、对数据库做一个全备份
[oracle@BAK-8-201 oradata]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Nov 27 04:44:28 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JSCN (DBID=2469648928)
RMAN> backup database;
Starting backup at 27-NOV-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=144 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00002 name=/oracle/ora10/oradata/jscn/undotbs01.dbf input datafile fno=00001 name=/oracle/ora10/oradata/jscn/system01.dbf input datafile fno=00003 name=/oracle/ora10/oradata/jscn/sysaux01.dbf input datafile fno=00005 name=/oradata/jscntest01.dbf input datafile fno=00004 name=/oracle/ora10/oradata/jscn/users01.dbf channel ORA_DISK_1: starting piece 1 at 27-NOV-12 channel ORA_DISK_1: finished piece 1 at 27-NOV-12 piece handle=/oracle/ora10/product/dbs/01nrb2lt_1_1 tag=TAG20121127T044445 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 27-NOV-12 channel ORA_DISK_1: finished piece 1 at 27-NOV-12 piece handle=/oracle/ora10/product/dbs/02nrb2nb_1_1 tag=TAG20121127T044445 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 27-NOV-12
|
步骤三、创建测试数据
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oraarch Oldest online log sequence 48 Next log sequence to archive 50 Current log sequence 50 SQL> conn jscntest/jscntest Connected. SQL> create table test(seq varchar2(20));
Table created.
SQL> insert into test values('sequence 50');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
|
当前的日志文件序列号是50,:因此上面的记录将会存放到日志序列号为50的归档日志中,类似我们插入51、52、53、54
SQL> insert into test values('sequence 51');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into test values('sequence 52');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into test values('sequence 53');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into test values('sequence 54');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select * from jscntest.test;
SEQ ------------------------------------------------------------ sequence 50 sequence 51 sequence 52 sequence 53 sequence 54
|
每次插入一条记录切换一次日志,上面的记录每条记录存放到一个日志文件中。
步骤四、模拟错误
[oracle@BAK-8-201 lib]$ ll /oraarch/ total 468 -rw-r----- 1 oracle oinstall 143872 Nov 27 04:12 1_46_800414496.dbf -rw-r----- 1 oracle oinstall 1024 Nov 27 04:12 1_47_800414496.dbf -rw-r----- 1 oracle oinstall 2048 Nov 27 04:12 1_48_800414496.dbf -rw-r----- 1 oracle oinstall 138752 Nov 27 04:42 1_49_800414496.dbf -rw-r----- 1 oracle oinstall 61440 Nov 27 04:47 1_50_800414496.dbf -rw-r----- 1 oracle oinstall 1536 Nov 27 04:49 1_51_800414496.dbf -rw-r----- 1 oracle oinstall 3584 Nov 27 04:50 1_52_800414496.dbf -rw-r----- 1 oracle oinstall 2048 Nov 27 04:50 1_53_800414496.dbf -rw-r----- 1 oracle oinstall 2048 Nov 27 04:50 1_54_800414496.dbf -rw-r----- 1 oracle oinstall 6144 Nov 27 06:20 1_55_800414496.dbf -rw-r----- 1 oracle oinstall 81920 Nov 27 07:38 1_56_800414496.dbf |
我们删掉日志文件1_51_800414496.dbf 及其表空间jscntest的数据文件。
[oracle@BAK-8-201 oraarch]$ mv 1_53_800414496.dbf.bak 1_53_800414496.dbf.bak [oracle@BAK-8-201 oraarch]$ mv /oradata/jscntest01.dbf /oradata/jscntest01.dbf.bak [oracle@BAK-8-201 lib]$ ll /oraarch/ total 468 -rw-r----- 1 oracle oinstall 143872 Nov 27 04:12 1_46_800414496.dbf -rw-r----- 1 oracle oinstall 1024 Nov 27 04:12 1_47_800414496.dbf -rw-r----- 1 oracle oinstall 2048 Nov 27 04:12 1_48_800414496.dbf -rw-r----- 1 oracle oinstall 138752 Nov 27 04:42 1_49_800414496.dbf -rw-r----- 1 oracle oinstall 61440 Nov 27 04:47 1_50_800414496.dbf -rw-r----- 1 oracle oinstall 1536 Nov 27 04:49 1_51_800414496.dbf -rw-r----- 1 oracle oinstall 3584 Nov 27 04:50 1_52_800414496.dbf -rw-r----- 1 oracle oinstall 2048 Nov 27 04:50 1_53_800414496.dbf.bak -rw-r----- 1 oracle oinstall 2048 Nov 27 04:50 1_54_800414496.dbf -rw-r----- 1 oracle oinstall 6144 Nov 27 06:20 1_55_800414496.dbf -rw-r----- 1 oracle oinstall 81920 Nov 27 07:38 1_56_800414496.dbf
|
此时数据库不能正常关闭
SQL> shutdown immediate ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/oradata/jscntest01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort ORACLE instance shut down.
|
需要采用shutdown abort的方式关闭数据库。
再次启动数据库将会报错。
SQL> startup ORACLE instance started.
Total System Global Area 440401920 bytes Fixed Size 2096888 bytes Variable Size 167772424 bytes Database Buffers 264241152 bytes Redo Buffers 6291456 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/oradata/jscntest01.dbf'
|
步骤五、探讨RECOVER需要修改的文件头信息
还原数据文件
RMAN> restore datafile 5;
Starting restore at 27-NOV-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00005 to /oradata/jscntest01.dbf channel ORA_DISK_1: reading from backup piece /oracle/ora10/product/dbs/01nrb2lt_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/ora10/product/dbs/01nrb2lt_1_1 tag=TAG20121127T044445 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 27-NOV-12
[oracle@BAK-8-201 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Nov 27 07:48:53 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover datafile 5; ORA-00279: change 648021 generated at 11/27/2012 04:44:45 needed for thread 1 ORA-00289: suggestion : /oraarch/1_50_800414496.dbf ORA-00280: change 648021 for thread 1 is in sequence #50
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oraarch/1_50_800414496.dbf ORA-00279: change 648133 generated at 11/27/2012 04:47:08 needed for thread 1 ORA-00289: suggestion : /oraarch/1_51_800414496.dbf ORA-00280: change 648133 for thread 1 is in sequence #51 ORA-00278: log file '/oraarch/1_50_800414496.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oraarch/1_51_800414496.dbf ORA-00279: change 648185 generated at 11/27/2012 04:49:39 needed for thread 1 ORA-00289: suggestion : /oraarch/1_52_800414496.dbf ORA-00280: change 648185 for thread 1 is in sequence #52 ORA-00278: log file '/oraarch/1_51_800414496.dbf' no longer needed for this recovery
|
注意此时我们暂时先不应用归档日志序列号为52的文件。(注意我们这里删除的是53)
我们采用BBED先记录此时的数据文件头的信息。
[oracle@BAK-8-201 lib]$ ./bbed filename=/oradata/jscntest01.dbf Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Nov 27 05:47:09 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
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 0x01400001 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 0x20f0 ub2 spare3_kcbh @18 0x0000 struct kcvfhhdr, 76 bytes @20 ub4 kccfhswv @20 0x00000000 ub4 kccfhcvn @24 0x0a200300 ub4 kccfhdbi @28 0x9333da20 text kccfhdbn[0] @32 J text kccfhdbn[1] @33 S text kccfhdbn[2] @34 C text kccfhdbn[3] @35 N text kccfhdbn[4] @36 text kccfhdbn[5] @37 text kccfhdbn[6] @38 text kccfhdbn[7] @39 ub4 kccfhcsq @40 0x00000222 ub4 kccfhfsz @44 0x00000500 s_blkz kccfhbsz @48 0x00 ub2 kccfhfno @52 0x0005 ub2 kccfhtyp @54 0x0003 ub4 kccfhacid @56 0x00000000 ub4 kccfhcks @60 0x00000000 text kccfhtag[0] @64 text kccfhtag[1] @65 text kccfhtag[2] @66 text kccfhtag[3] @67 text kccfhtag[4] @68 text kccfhtag[5] @69 text kccfhtag[6] @70 text kccfhtag[7] @71 text kccfhtag[8] @72 text kccfhtag[9] @73 text kccfhtag[10] @74 text kccfhtag[11] @75 text kccfhtag[12] @76 text kccfhtag[13] @77 text kccfhtag[14] @78 text kccfhtag[15] @79 text kccfhtag[16] @80 text kccfhtag[17] @81 text kccfhtag[18] @82 text kccfhtag[19] @83 text kccfhtag[20] @84 text kccfhtag[21] @85 text kccfhtag[22] @86 text kccfhtag[23] @87 text kccfhtag[24] @88 text kccfhtag[25] @89 text kccfhtag[26] @90 text kccfhtag[27] @91 text kccfhtag[28] @92 text kccfhtag[29] @93 text kccfhtag[30] @94 text kccfhtag[31] @95 ub4 kcvfhrdb @96 0x00000000 struct kcvfhcrs, 8 bytes @100 ub4 kscnbas @100 0x0009deac ub2 kscnwrp @104 0x0000 ub4 kcvfhcrt @108 0x2fb580ca ub4 kcvfhrlc @112 0x2fb55b20 struct kcvfhrls, 8 bytes @116 ub4 kscnbas @116 0x00000001 ub2 kscnwrp @120 0x0000 ub4 kcvfhbti @124 0x00000000 struct kcvfhbsc, 8 bytes @128 ub4 kscnbas @128 0x00000000 ub2 kscnwrp @132 0x0000 ub2 kcvfhbth @136 0x0000 ub2 kcvfhsta @138 0x0000 (NONE) struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x0009e3f9 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2fb58be3 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000034 ub4 kcrbabno @504 0x00000002 ub2 kcrbabof @508 0x0000 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 ub4 kcvfhcpc @140 0x00000009 ub4 kcvfhrts @144 0x2fb5b606 ub4 kcvfhccc @148 0x00000008 struct kcvfhbcp, 36 bytes @152 struct kcvcpscn, 8 bytes @152 ub4 kscnbas @152 0x00000000 ub2 kscnwrp @156 0x0000 ub4 kcvcptim @160 0x00000000 ub2 kcvcpthr @164 0x0000 union u, 12 bytes @168 struct kcvcprba, 12 bytes @168 ub4 kcrbaseq @168 0x00000000 ub4 kcrbabno @172 0x00000000 ub2 kcrbabof @176 0x0000 ub1 kcvcpetb[0] @180 0x00 ub1 kcvcpetb[1] @181 0x00 ub1 kcvcpetb[2] @182 0x00 ub1 kcvcpetb[3] @183 0x00 ub1 kcvcpetb[4] @184 0x00 ub1 kcvcpetb[5] @185 0x00 ub1 kcvcpetb[6] @186 0x00 ub1 kcvcpetb[7] @187 0x00 ub4 kcvfhbhz @312 0x00000000 struct kcvfhxcd, 16 bytes @316 ub4 space_kcvmxcd[0] @316 0x00000000 ub4 space_kcvmxcd[1] @320 0x00000000 ub4 space_kcvmxcd[2] @324 0x00000000 ub4 space_kcvmxcd[3] @328 0x00000000 word kcvfhtsn @332 5 ub2 kcvfhtln @336 0x0008 text kcvfhtnm[0] @338 J text kcvfhtnm[1] @339 S text kcvfhtnm[2] @340 C text kcvfhtnm[3] @341 N text kcvfhtnm[4] @342 T text kcvfhtnm[5] @343 E text kcvfhtnm[6] @344 S text kcvfhtnm[7] @345 T text kcvfhtnm[8] @346 text kcvfhtnm[9] @347 text kcvfhtnm[10] @348 text kcvfhtnm[11] @349 text kcvfhtnm[12] @350 text kcvfhtnm[13] @351 text kcvfhtnm[14] @352 text kcvfhtnm[15] @353 text kcvfhtnm[16] @354 text kcvfhtnm[17] @355 text kcvfhtnm[18] @356 text kcvfhtnm[19] @357 text kcvfhtnm[20] @358 text kcvfhtnm[21] @359 text kcvfhtnm[22] @360 text kcvfhtnm[23] @361 text kcvfhtnm[24] @362 text kcvfhtnm[25] @363 text kcvfhtnm[26] @364 text kcvfhtnm[27] @365 text kcvfhtnm[28] @366 text kcvfhtnm[29] @367 ub4 kcvfhrfn @368 0x00000005 struct kcvfhrfs, 8 bytes @372 ub4 kscnbas @372 0x00000000 ub2 kscnwrp @376 0x0000 ub4 kcvfhrft @380 0x00000000 struct kcvfhafs, 8 bytes @384 ub4 kscnbas @384 0x00000000 ub2 kscnwrp @388 0x0000 ub4 kcvfhbbc @392 0x00000000 ub4 kcvfhncb @396 0x00000000 ub4 kcvfhmcb @400 0x00000000 ub4 kcvfhlcb @404 0x00000000 ub4 kcvfhbcs @408 0x00000000 ub2 kcvfhofb @412 0x0000 ub2 kcvfhnfb @414 0x0000 ub4 kcvfhprc @416 0x00000000 struct kcvfhprs, 8 bytes @420 ub4 kscnbas @420 0x00000000 ub2 kscnwrp @424 0x0000 struct kcvfhprfs, 8 bytes @428 ub4 kscnbas @428 0x00000000 ub2 kscnwrp @432 0x0000 ub4 kcvfhtrt @444 0x00000000
|
将上面打印出来的信息保存为recover_sequence51.txt
再次回到原来的recovery界面,应用归档日志序列号为52的文件。
如下:
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oraarch/1_52_800414496.dbf ORA-00279: change 648195 generated at 11/27/2012 04:50:01 needed for thread 1 ORA-00289: suggestion : /oraarch/1_53_800414496.dbf ORA-00280: change 648195 for thread 1 is in sequence #53 ORA-00278: log file '/oraarch/1_52_800414496.dbf' no longer needed for this recovery
|
这里要从新进入bbed
[oracle@BAK-8-201 lib]$ ./bbed filename=/oradata/jscntest01.dbf Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Nov 27 05:20:50 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
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 0x01400001 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 0x273b ub2 spare3_kcbh @18 0x0000 struct kcvfhhdr, 76 bytes @20 ub4 kccfhswv @20 0x00000000 ub4 kccfhcvn @24 0x0a200300 ub4 kccfhdbi @28 0x9333da20 text kccfhdbn[0] @32 J text kccfhdbn[1] @33 S text kccfhdbn[2] @34 C text kccfhdbn[3] @35 N text kccfhdbn[4] @36 text kccfhdbn[5] @37 text kccfhdbn[6] @38 text kccfhdbn[7] @39 ub4 kccfhcsq @40 0x00000223 ub4 kccfhfsz @44 0x00000500 s_blkz kccfhbsz @48 0x00 ub2 kccfhfno @52 0x0005 ub2 kccfhtyp @54 0x0003 ub4 kccfhacid @56 0x00000000 ub4 kccfhcks @60 0x00000000 text kccfhtag[0] @64 text kccfhtag[1] @65 text kccfhtag[2] @66 text kccfhtag[3] @67 text kccfhtag[4] @68 text kccfhtag[5] @69 text kccfhtag[6] @70 text kccfhtag[7] @71 text kccfhtag[8] @72 text kccfhtag[9] @73 text kccfhtag[10] @74 text kccfhtag[11] @75 text kccfhtag[12] @76 text kccfhtag[13] @77 text kccfhtag[14] @78 text kccfhtag[15] @79 text kccfhtag[16] @80 text kccfhtag[17] @81 text kccfhtag[18] @82 text kccfhtag[19] @83 text kccfhtag[20] @84 text kccfhtag[21] @85 text kccfhtag[22] @86 text kccfhtag[23] @87 text kccfhtag[24] @88 text kccfhtag[25] @89 text kccfhtag[26] @90 text kccfhtag[27] @91 text kccfhtag[28] @92 text kccfhtag[29] @93 text kccfhtag[30] @94 text kccfhtag[31] @95 ub4 kcvfhrdb @96 0x00000000 struct kcvfhcrs, 8 bytes @100 ub4 kscnbas @100 0x0009deac ub2 kscnwrp @104 0x0000 ub4 kcvfhcrt @108 0x2fb580ca ub4 kcvfhrlc @112 0x2fb55b20 struct kcvfhrls, 8 bytes @116 ub4 kscnbas @116 0x00000001 ub2 kscnwrp @120 0x0000 ub4 kcvfhbti @124 0x00000000 struct kcvfhbsc, 8 bytes @128 ub4 kscnbas @128 0x00000000 ub2 kscnwrp @132 0x0000 ub2 kcvfhbth @136 0x0000 ub2 kcvfhsta @138 0x0000 (NONE) struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x0009e403 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2fb58bf9 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000035 ub4 kcrbabno @504 0x00000002 ub2 kcrbabof @508 0x0000 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 ub4 kcvfhcpc @140 0x00000009 ub4 kcvfhrts @144 0x2fb5b62d ub4 kcvfhccc @148 0x00000008 struct kcvfhbcp, 36 bytes @152 struct kcvcpscn, 8 bytes @152 ub4 kscnbas @152 0x00000000 ub2 kscnwrp @156 0x0000 ub4 kcvcptim @160 0x00000000 ub2 kcvcpthr @164 0x0000 union u, 12 bytes @168 struct kcvcprba, 12 bytes @168 ub4 kcrbaseq @168 0x00000000 ub4 kcrbabno @172 0x00000000 ub2 kcrbabof @176 0x0000 ub1 kcvcpetb[0] @180 0x00 ub1 kcvcpetb[1] @181 0x00 ub1 kcvcpetb[2] @182 0x00 ub1 kcvcpetb[3] @183 0x00 ub1 kcvcpetb[4] @184 0x00 ub1 kcvcpetb[5] @185 0x00 ub1 kcvcpetb[6] @186 0x00 ub1 kcvcpetb[7] @187 0x00 ub4 kcvfhbhz @312 0x00000000 struct kcvfhxcd, 16 bytes @316 ub4 space_kcvmxcd[0] @316 0x00000000 ub4 space_kcvmxcd[1] @320 0x00000000 ub4 space_kcvmxcd[2] @324 0x00000000 ub4 space_kcvmxcd[3] @328 0x00000000 word kcvfhtsn @332 5 ub2 kcvfhtln @336 0x0008 text kcvfhtnm[0] @338 J text kcvfhtnm[1] @339 S text kcvfhtnm[2] @340 C text kcvfhtnm[3] @341 N text kcvfhtnm[4] @342 T text kcvfhtnm[5] @343 E text kcvfhtnm[6] @344 S text kcvfhtnm[7] @345 T text kcvfhtnm[8] @346 text kcvfhtnm[9] @347 text kcvfhtnm[10] @348 text kcvfhtnm[11] @349 text kcvfhtnm[12] @350 text kcvfhtnm[13] @351 text kcvfhtnm[14] @352 text kcvfhtnm[15] @353 text kcvfhtnm[16] @354 text kcvfhtnm[17] @355 text kcvfhtnm[18] @356 text kcvfhtnm[19] @357 text kcvfhtnm[20] @358 text kcvfhtnm[21] @359 text kcvfhtnm[22] @360 text kcvfhtnm[23] @361 text kcvfhtnm[24] @362 text kcvfhtnm[25] @363 text kcvfhtnm[26] @364 text kcvfhtnm[27] @365 text kcvfhtnm[28] @366 text kcvfhtnm[29] @367 ub4 kcvfhrfn @368 0x00000005 struct kcvfhrfs, 8 bytes @372 ub4 kscnbas @372 0x00000000 ub2 kscnwrp @376 0x0000 ub4 kcvfhrft @380 0x00000000 struct kcvfhafs, 8 bytes @384 ub4 kscnbas @384 0x00000000 ub2 kscnwrp @388 0x0000 ub4 kcvfhbbc @392 0x00000000 ub4 kcvfhncb @396 0x00000000 ub4 kcvfhmcb @400 0x00000000 ub4 kcvfhlcb @404 0x00000000 ub4 kcvfhbcs @408 0x00000000 ub2 kcvfhofb @412 0x0000 ub2 kcvfhnfb @414 0x0000 ub4 kcvfhprc @416 0x00000000 struct kcvfhprs, 8 bytes @420 ub4 kscnbas @420 0x00000000 ub2 kscnwrp @424 0x0000 struct kcvfhprfs, 8 bytes @428 ub4 kscnbas @428 0x00000000 ub2 kscnwrp @432 0x0000 ub4 kcvfhtrt @444 0x00000000
|
将打印出来的kcvfh结构另存为recover_sequence52.txt
我们采用diff命令来查看2个文件的不同之处:
[oracle@BAK-8-201 ~]$ diff recover_sequence51.txt recover_sequence52.txt 1c1 < BBED> p kcvfh --- > BBED> p kcvfh 13c13 < ub2 chkval_kcbh @16 0x20f0 --- > ub2 chkval_kcbh @16 0x273b 27c27 < ub4 kccfhcsq @40 0x00000222 --- > ub4 kccfhcsq @40 0x00000223 83c83 < ub4 kscnbas @484 0x0009e3f9 --- > ub4 kscnbas @484 0x0009e403 85c85 < ub4 kcvcptim @492 0x2fb58be3 --- > ub4 kcvcptim @492 0x2fb58bf9 89c89 < ub4 kcrbaseq @500 0x00000034 --- > ub4 kcrbaseq @500 0x00000035 101c101 < ub4 kcvfhrts @144 0x2fb5b606 --- > ub4 kcvfhrts @144 0x2fb5b62d
|
[oracle@BAK-8-201 ~]$
我们发现ORACLE一共改了6个地方:
偏移量16为块的校验值ub2 chkval_kcbh 原来为 0x20f0 后来为 0x273b 偏移量40为control sequence ub4 kccfhcsq 原先为 0x00000222 后来为 0x00000223 偏移量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#。
如下:
日志序列号51的NEXT_CHANGE#为:
SQL> select to_number('9e3f9','xxxxxxxx') from dual;
TO_NUMBER('9E3F9','XXXXXXXX') ----------------------------- 648185 日志序列号52的NEXT_CHANGE#为:
SQL> select to_number('9e403','xxxxxxxx') from dual;
TO_NUMBER('9E403','XXXXXXXX') ----------------------------- 648195 |
日志序列号53的NEXT_CHANGE#可以通过如下方式查到,就是648201,转为16进制就是9E409。
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 ---------- ------------- ------------------ ------------ ------------------ 46 646884 27-NOV-12 647118 27-NOV-12 47 647118 27-NOV-12 647131 27-NOV-12 48 647131 27-NOV-12 647237 27-NOV-12 49 647237 27-NOV-12 647974 27-NOV-12 50 647974 27-NOV-12 648133 27-NOV-12 51 648133 27-NOV-12 648185 27-NOV-12 52 648185 27-NOV-12 648195 27-NOV-12 53 648195 27-NOV-12 648201 27-NOV-12 54 648201 27-NOV-12 648208 27-NOV-12 55 648208 27-NOV-12 668288 27-NOV-12
|
偏移量492需要修改为需要归档日志文件的NEXT_TIME#。
但是这里存放的不是时间值,而是从1988年1月1日起到现在所经历的秒数。
看看前面的这2个值
< ub4 kcvcptim @492 0x2fb58be3 --- > ub4 kcvcptim @492 0x2fb58bf9
SQL> select to_number('2fb58be3','xxxxxxxx') from dual;
TO_NUMBER('2FB58BE3','XXXXXXXX') -------------------------------- 800426979
SQL> select to_number('2fb58bf9','xxxxxxxx') from dual;
TO_NUMBER('2FB58BF9','XXXXXXXX') -------------------------------- 800427001
SQL> select 800427001-800426979 from dual;
800427001-800426979 ------------------- 22
|
从下面的查询我们也可以看到51和52的NEXT_TIME就是差了22秒,52和53之间差了14秒。
SQL> select SEQUENCE#,to_char(NEXT_TIME,'yyyy-mm-dd hh24:mi:ss') from v$archived_log order by 1;
SEQUENCE# TO_CHAR(NEXT_TIME,'YYYY-MM-DDHH24:MI:SS') ---------- --------------------------------------------------------- 46 2012-11-27 04:06:18 47 2012-11-27 04:06:52 48 2012-11-27 04:12:06 49 2012-11-27 04:42:51 50 2012-11-27 04:47:08 51 2012-11-27 04:49:39 52 2012-11-27 04:50:01 53 2012-11-27 04:50:15 54 2012-11-27 04:50:28 55 2012-11-27 06:20:30 56 2012-11-27 07:38:21 |
因此这里存放的值修改为0x2fb58bf9+14=800427001+14=800427015= 0x2FB58C07
偏移量500更简单了修改为54就行了。
有了上面的知识我们回到RECOVER窗口:
SQL> recover datafile 5 ORA-00279: change 648195 generated at 11/27/2012 04:50:01 needed for thread 1 ORA-00289: suggestion : /oraarch/1_53_800414496.dbf ORA-00280: change 648195 for thread 1 is in sequence #53
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oraarch/1_53_800414496.dbf ORA-00308: cannot open archived log '/oraarch/1_53_800414496.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
|
我们在应用日志文件/oraarch/1_53_800414496.dbf的时候报错了,这也是意料之中的。
下面我们进行修改上面提到的3个地方,由于我的是LINUX平台属于LITTLE的字节序,因此修改的时候要注意顺序。修改的时候009E409 需要写成09e40900的形式。492类似。
BBED> set offset 484
OFFSET 484
BBED> dump /v count 16 File: /oradata/jscntest01.dbf (0) Block: 1 Offsets: 484 to 499 Dba:0x00000000 ------------------------------------------------------- 03e40900 00000000 f98bb52f 01000000 l .........../....
<16 bytes per line>
BBED> set mode edit
MODE Edit
BBED> modify /x 09 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /oradata/jscntest01.dbf (0) Block: 1 Offsets: 484 to 499 Dba:0x00000000 ------------------------------------------------------------------------ 09e40900 00000000 f98bb52f 01000000
说明:根据红色的可以看明白吗? <32 bytes per line>
BBED> set offset 492
OFFSET 492
BBED> dump /v count 16 File: /oradata/jscntest01.dbf (0) Block: 1 Offsets: 492 to 507 Dba:0x00000000 ------------------------------------------------------- f98bb52f 01000000 35000000 02000000 l .../....5.......
<16 bytes per line>
0x2fb58bf9 +14=800427001+14=800427015= 0x2FB58C07 078cb52f
BBED> set mode edit
MODE Edit
BBED> modify /x 078c File: /oradata/jscntest01.dbf (0) Block: 1 Offsets: 492 to 507 Dba:0x00000000 ------------------------------------------------------------------------ 078cb52f 01000000 35000000 02000000
<32 bytes per line>
BBED> set offset 500 OFFSET 500
BBED> dump /v count 16 File: /oradata/jscntest01.dbf (0) Block: 1 Offsets: 500 to 515 Dba:0x00000000 ------------------------------------------------------- 35000000 02000000 00008320 02000000 l 5.......... ....
<16 bytes per line>
BBED> modify /x 36 (36就是十进制的54) File: /oradata/jscntest01.dbf (0) Block: 1 Offsets: 500 to 515 Dba:0x00000000 ------------------------------------------------------------------------ 36000000 02000000 00008320 02000000
<32 bytes per line>
BBED> sum apply Check value for File 0, Block 1: current = 0x2bfc, required = 0x2bfc
|
重新进行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 5 ORA-00279: change 648201 generated at 11/27/2012 04:50:15 needed for thread 1 ORA-00289: suggestion : /oraarch/1_54_800414496.dbf ORA-00280: change 648201 for thread 1 is in sequence #54
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oraarch/1_54_800414496.dbf Log applied. Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> alter database open;
Database altered.
SQL> conn jscntest/jscntest Connected. SQL> select * from test;
SEQ ------------------------------------------------------------ sequence 50 sequence 51 sequence 52 sequence 54
|
由于记录"sequence 53" 没有应用归档日志文件53,导致丢失。
整理之网络