跳过归档日志的非常规恢复
来源:互联网 发布:淘宝店铺搜索网址 编辑:程序博客网 时间:2024/06/07 21:04
跳过归档日志的非常规恢复
首先我们先介绍一个小工具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,导致丢失。
整理之网络
- 跳过归档日志的非常规恢复
- 跳过归档日志的非常规恢复
- 跳过归档日志的非常规恢复(一)
- 跳过归档日志的非常规恢复(二)
- Oracle非常规恢复(使用BBED跳过归档)
- Oracle非常规恢复(使用BBED跳过归档)
- 非常规数据恢复的几种场景(跳过坏块导出数据)
- oracle跳过丢失的归档恢复datafile
- BBED跳过归档恢复
- RMAN之非归档日志模式下的数据文件恢复
- Oracle 之利用BBED跳过归档日志实现恢复
- 跳过丢失归档进行恢复
- 使用脚本,自动跳过丢失归档的恢复--------MODIFY SEQ
- RMAN之归档日志模式下的非关键文件恢复
- Oracle 之利用BBED跳过归档日志实现恢复(二)
- 非归档数据文件offline的恢复
- RAMN恢复数据库的过程+数据库非归档恢复+数据库非归档恢复案例+数据库归档恢复案例
- 用归档日志恢复丢失的数据文件
- 求n!的4种方法
- Java读写Excel之POI超入门
- Eclipse中将web项目自动发布到Tomcat webapps下
- GeoServer + Openscales 的跨域问题
- oracle查看存储过程代吗
- 跳过归档日志的非常规恢复
- Unity3D脚本中文系列教程(一)
- 回溯法解最优装载问题
- 使用命令wsimport构建WebService客户端
- RS232串口通信详解
- ubuntu下安装putty
- ORACLE忘记密码之后的操作
- Unity3D脚本中文系列教程(二)
- 重写RadioButtonList服务器控件的RenderItem方法,隐藏radio元素