[20150529]使用bbed解决丢失的归档.txt

2021-12-02 00:26:00

[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], [], []

上一篇：大量使用临时表带来的问题，替代方案，以及如何擦屁股


下一篇：UML学习笔记