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

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

时间： 2024-08-22 04:53:48

[20150529]使用bbed解决丢失的归档.txt的相关文章

[20150527]bbed解决数据文件大小问题.txt

[20150527]bbed解决数据文件大小问题.txt --模拟一个数据文件大小不一致的问题. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86

Oracle中如何使用BBED跳过丢失的归档

在recover datafile的过程当中如果丢失了需要的归档将使得recover无法进行,使用bbed工具可以跳过丢失的归档进行recover datafile. 实验过程如下: SYS@ORCL>select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.

【BBED】丢失归档文件情况下的恢复

[BBED]丢失归档文件情况下的数据文件的恢复   1.1  BLOG文档结构图     1.2  前言部分   1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 若丢失归档情况下数据文件的恢复,bbed和隐含参数(重点) ② 数据库启动过程中的介质恢复,scn号的关系 ③ BBED如何修改文件头 ④ 归档和非归档模式下数据库的全备     Tips:        ① 若文章代码格式有错乱,推荐使用QQ

[20170412]bbed恢复修改记录(不等长).txt

[20170412]bbed恢复修改记录(不等长).txt --//昨天做了删除记录恢复测试,今天测试update记录如何还原通过bbed. --//实际上类似的测试我自己做了好几次,都是按照别人的帖子依葫芦画瓢,没有很好理解一些内在的东西. http://blog.itpub.net/267265/viewspace-2137000/ http://blog.itpub.net/267265/viewspace-2107060/ http://blog.itpub.net/267265/vie

如何解决win7系统打开txt文件提示“无法找到脚本文件”的方法

  我们使用win7系统的用户大家都会使用很多的办公软件,其中txt是一款非常常用的一款文本格式,使用起来也非常的方便,TXT大家主要是用来保存文本用的,可以直接打开就可以查看内容,也可以从浏览器中打开查看,但是有用户说自己在打开文件的时候出现了错误提示"无法找到脚本文件"的情况,要是大家的电脑出现了这样的情况该怎么办呢,小编马上就给大家分享解决方法. 出现TXT文件"无法找到脚本文件"的解决方法: 1.我们需要对自己的电脑进行深度的修复,跟着小编一起来更改一下软件

[20150522]bbed与数据块检查和.txt

[20150522]bbed与数据块检查和.txt --我现在基本拿bbed学习,基本是拿bbed查看,而使用bvi修改数据.我感觉这种方便1写. --实际上使用bbed的好处就是修改数据块检查和不一致,而使用bbed修改很简单仅仅需要执行sum apply就ok了. --对比dbv与bbed确定检查和位置. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER -------

[20170411]bbed删除记录的恢复.txt

[20170411]bbed删除记录的恢复.txt --//昨天上午做的测试,链接:http://blog.itpub.net/267265/viewspace-2136933/ --//我当时并没有选择恢复记录,仅仅看删除的内容.因为这样恢复是存在许多问题. --//执行 drop function scott.sleep ; 删除sys.source$相关记录仅仅是该命令的一小部分,恢复 --//sys.source$相关记录会存在许多问题,但是如果是应用数据恢复还是可以,实际上以前我的博客

[20160528]bbed观察行目录变化.txt

[20160528]bbed观察行目录变化.txt 如果使用bbed观察kdbr,可以发现记录的是相对偏移量,这个偏移我一直认为从kdbh算起.而对于数据块前面有ITL槽信息,对于有2个ITL的块, 使用assm的表空间,一般我看到都是100.如果一个块上有多个事务,ITL槽会增加,kdbh的地址就会发生变化,这样记录在kdbr的行目录信息 就存在变化,通过例子来说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VE

ORA-16038的解决(日志无法归档)

ORA-16038的解决 数据库装载完毕. ORA-16038: 日志 3 序列号 5035 无法归档 ORA-19809: 超出了恢复文件数的限制 ORA-00312: 联机日志 3 线程 1: ......REDO03.LOG' DB是归档模式, 每个日志组只有一个文件(新太公司的人通常使用的方法,FT), 没办法, 搜寻文档和晚上的资料, 有如下的解决方法: 损坏非当前联机日志: 1.启动数据库,遇到ORA-00312 or ORA-00313错误,如: ORA-00313: open f