[20150529]使用bbed解决丢失的归档.txt
-- 以前跟别人探讨过这个问题,我个人的观点通过bbed等手段来跳过丢失的归档来恢复存在许多问题.
-- 我以前个人的主张是通过别的手段抽取数据文件的数据,结合logminer来重新整合数据.但是如果抽取的logminer很多,会非常麻烦.
--实际上无论那种方式,都存在许多问题,这里通过例子来说明bbed如何解决跳过丢失的归档.
1.测试环境建立:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16256K AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table tx(id number,name varchar2(20),pad varchar2(100)) tablespace mssm ;
insert into tx values (1,'aaaa','xxxx');
insert into tx values (2,'bbbb','yyyy');
insert into tx values (3,'cccc','zzzz');
commit;
create unique index pk_tx on scott.tx(id) tablespace mssm ;
alter table tx add constraint pk_tx primary key (id);
--关闭数据库,做一个冷备份.
$ cp /mnt/ramdisk/test/mssm01.dbf /home/oracle/mssm/mssm01.dbf_1
--启动数据库.
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
2.建立测试数据:
--当前log sequence=32
update tx set name='AAAA' where id=1;
commit ;
alter system archive log current ;
--当前log sequence=33
update tx set pad='XXXX' where id=1;
commit ;
alter system archive log current ;
--当前log sequence=34
update tx set name='BBBB' where id=2;
commit ;
alter system archive log current ;
--当前log sequence=35
insert into tx values (4,'dddd','vvvv');
commit ;
alter system archive log current ;
SCOTT@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -----
AAAPpDAAGAAAAASAAA 1 AAAA XXXX
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
SCOTT@test> @ &r/lookup_rowid AAAPpDAAGAAAAASAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
64067 6 18 0 6,18 alter system dump datafile 6 block 18 ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 37
Next log sequence to archive 39
Current log sequence 39
-- log sequence=32,33,34,35已经覆盖,关闭数据库.
3.再做一个备份(包括arivelog):
$ cp /mnt/ramdisk/test/mssm01.dbf /home/oracle/mssm/mssm01.dbf_2
$ cp -a /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29 /home/oracle/mssm/
4.移除1个archivelog开始测试:
-- 删除log seq=33.
$ mv o1_mf_1_33_bphmvodr_.arc o1_mf_1_33_bphmvodr_.arc_xxx
$ cp /home/oracle/mssm/mssm01.dbf_1 /mnt/ramdisk/test/mssm01.dbf
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> recover datafile 6 ;
ORA-00279: change 12691855449 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_%u_.arc
ORA-00280: change 12691855449 for thread 1 is in sequence #32
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 12691855551 generated at 05/29/2015 10:31:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_%u_.arc
ORA-00280: change 12691855551 for thread 1 is in sequence #33
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_bphmv8sm_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_bphmvodr_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--由于相应的archive log已经不存在.
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xf47e58bf
ub2 kscnwrp @488 0x0002
ub4 kcvcptim @492 0x348291e8
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000021
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
--注意看~部分,现在seq=0x21=33,跳过1个看看!
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000021
BBED> modify /x 22
..
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000022
BBED> sum apply
Check value for File 6, Block 1:
current = 0x57ac, required = 0x57ac
--不需要做sum apply,why?还是数据库打开的原因.
SYS@test> recover datafile 6;
ORA-00279: change 12691855551 generated at 05/29/2015 10:31:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_%u_.arc
ORA-00280: change 12691855551 for thread 1 is in sequence #34
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00326: log begins at change 12691855559, need earlier change 12691855551
ORA-00334: archived log: '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_bphmw074_.arc'
--还有修改scn的信息.
12691855559-2^33=4101920967
4101920967 = 0xf47e58c7
12691855551-2^33=4101920959
4101920959 = 0xf47e58bf
--说明我的数据库ub2 kscnwrp @488 0x0002,要减去2^32*2,也就是2^33.
--这样修改为0x58bf=>0x58c7
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e58bf
BBED> modify /x c758
..
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e58c7
BBED> sum apply
Check value for File 6, Block 1:
current = 0x577a, required = 0x577a
SYS@test> recover datafile 6;
ORA-00279: change 12691855559 generated at 05/29/2015 10:31:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_%u_.arc
ORA-00280: change 12691855559 for thread 1 is in sequence #34
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12691855566 generated at 05/29/2015 10:31:28 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_%u_.arc
ORA-00280: change 12691855566 for thread 1 is in sequence #35
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_bphmw074_.arc' no longer needed for this recovery
ORA-00279: change 12691855572 generated at 05/29/2015 10:31:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_36_%u_.arc
ORA-00280: change 12691855572 for thread 1 is in sequence #36
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_bphmw7oj_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
--OK,恢复完成.
SYS@test> alter database open read only;
Database altered.
SYS@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -------
AAAPpDAAGAAAAASAAA 1 AAAA xxxx
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--对比前面的情况:
SCOTT@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -----
AAAPpDAAGAAAAASAAA 1 AAAA XXXX
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--可以发现ID=1的,PAD='XXXX'的修改丢失了.依旧是小写的'xxxx'.所以讲这样恢复导致数据"混乱"
5.重复测试,这次删除log seq=32看看会出现什么情况呢?
--关闭数据库,还原现场:
$ cp /home/oracle/mssm/mssm01.dbf_1 /mnt/ramdisk/test/mssm01.dbf
$ cd /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29
$ mv o1_mf_1_33_bphmvodr_.arc_xxx o1_mf_1_33_bphmvodr_.arc
$ mv o1_mf_1_32_bphmv8sm_.arc o1_mf_1_32_bphmv8sm_.arc_XXX
--这次移除log seq=32,看看情况如何呢?
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> recover datafile 6;
ORA-00279: change 12691855449 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_%u_.arc
ORA-00280: change 12691855449 for thread 1 is in sequence #32
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_bphmv8sm_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_bphmv8sm_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@test> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--重复bbed的修改过程.
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000020
BBED> modify /x 21
...
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000021
BBED> sum apply
Check value for File 6, Block 1:
current = 0x1a0b, required = 0x1a0b
--也没有变化,难道这部分信息不参与检查和运算吗?
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> recover datafile 6;
ORA-00279: change 12691855449 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_%u_.arc
ORA-00280: change 12691855449 for thread 1 is in sequence #33
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00326: log begins at change 12691855551, need earlier change 12691855449
ORA-00334: archived log: '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_bphmvodr_.arc'
--还有修改scn的信息.
12691855551-2^33=4101920959
4101920959 = 0xf47e58bf
12691855449-2^33=4101920857
4101920857 = 0xf47e5859
--修改0x5859 => 0x58bf
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e5859
BBED> modify /x bf58
..
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e58bf
BBED> sum apply
Check value for File 6, Block 1:
current = 0x1918, required = 0x1918
SYS@test> recover datafile 6;
ORA-00279: change 12691855551 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_%u_.arc
ORA-00280: change 12691855551 for thread 1 is in sequence #33
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12691855559 generated at 05/29/2015 10:31:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_%u_.arc
ORA-00280: change 12691855559 for thread 1 is in sequence #34
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_bphmvodr_.arc' no longer needed for this recovery
ORA-00279: change 12691855566 generated at 05/29/2015 10:31:28 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_%u_.arc
ORA-00280: change 12691855566 for thread 1 is in sequence #35
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_bphmw074_.arc' no longer needed for this recovery
ORA-00279: change 12691855572 generated at 05/29/2015 10:31:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_36_%u_.arc
ORA-00280: change 12691855572 for thread 1 is in sequence #36
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_bphmw7oj_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
--再看看结果如何:
SYS@test> alter database open read only;
Database altered.
SYS@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- --------
AAAPpDAAGAAAAASAAA 1 aaaa xxxx
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--对比前面的情况:
SCOTT@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -----
AAAPpDAAGAAAAASAAA 1 AAAA XXXX
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--可以发现ID=1的,name='AAAA',PAD='XXXX'的修改丢失了.依旧是原来的信息.
--如果讲前面的测试仅仅导致数据"混乱",第2次的情况导致两次修改都丢失了.
--实际上的情况比这些还要复杂.这种修复的意义我个人认为意义不大.但是还是给权衡利弊.
--也就是那段时间的记录要仔细检查.
--另外我做了多次,有几次出现.修复方式也是一样的,修改scn信息.
ORA-00600: internal error code, arguments: [2608], [1], [2], [4101877000], [2], [4101877009], [], []
