标签归档：bbed

bbed 找回被删除数据

发表于 2012 年 5 月 3 日由惜分飞

创建模拟表数据

SQL> create table t_xifenfei(id number,name varchar2(10));

Table created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'XIFENFEI');

1 row created.

SQL> commit;

Commit complete.

dump数据块

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> select   rowid,id,name,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from chf.t_xifenfei;

ROWID                      ID NAME          REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ---------- ---------- ----------
AAASdmAAEAAAACvAAA          1 xifenfei            4        175          0
AAASdmAAEAAAACvAAB          2 XIFENFEI            4        175          1

SQL> alter system dump datafile 4 block 175;

System altered.

dump文件内容

block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
end_of_block_dump
2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

删除表数据

SQL> delete from t_xifenfei;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.

dump文件内容

block_row_dump:
tab 0, row 0, @0x1f89
tl: 2 fb: --HDFL-- lb: 0x2 
tab 0, row 1, @0x1f7a
tl: 2 fb: --HDFL-- lb: 0x2 
end_of_block_dump
2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

通过对比这两次的dump文件发现

1.数据内容被删除,并不是真正删除,而是给其增加了一个标识位(fd:---D----)
2.fb:--H-FL--(head of row piece+first data piece+last data piece )
  其有8个选项每个选项的值分别对应bitmask即32+8+4=44 or 0x2c
3.如果一个row被delete了,那么row flag就会更新,bitmask里的deleted被设置为16.
  此时row flag为：32+16+8+4 = 60 or 0x3c.
4.如果我们要找回来被删除的数据,只需要把3c改为2c即可

关闭数据库

SQL> select * from chf.t_xifenfei;

no rows selected

SQL> select name from v$datafile where file#=4;

NAME
------------------------------------------------
/tmp/user01.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

bbed修改数据

BBED> set filename '/tmp/user01.dbf'
        FILENAME        /tmp/user01.dbf

BBED> set block 175
        BLOCK#          175

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set mode edit
        MODE            Edit

BBED> map 
 File: /tmp/user01.dbf (0)
 Block: 175                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8036]                        @122     

 ub1 rowdata[30]                            @8158    

 ub4 tailchk                                @8188    

BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15]                             @8173     0x3c

BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8158     0x3c

BBED> m /x 2c offset 8158
 File: /tmp/user01.dbf (0)
 Block: 175              Offsets: 8158 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106 
 b47e 

 <32 bytes per line>

BBED>  m /x 2c offset 8173
 File: /tmp/user01.dbf (0)
 Block: 175              Offsets: 8173 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c630202 c1020878 6966656e 66656901 06b47e 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13

启动数据库验证

SQL> startup 
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xifenfei;

        ID NAME
---------- ----------
         1 xifenfei
         2 XIFENFEI

发表在非常规恢复 | 标签为 bbed | 5 条评论

bbed修改ASM中数据

发表于 2012 年 4 月 30 日由惜分飞

本篇文章演示了如何从ASM中通过dd拷贝出某种表的记录,然后通过bbed修改相关记录,再拷贝到库中.说的简单点就是通过dd拷贝出最少的需要对象数据块,然后通过bbed绕过数据库级别对相关记录进行修改
模拟测试数据

--ORACLE数据库中执行
SQL> create tablespace xifenfei datafile '+xifenfei' size 30M autoextend on maxsize 10G;

Tablespace created.

SQL> create table t_xifenfei (id number,name varchar2(10)) tablespace xifenfei;

Table created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'XFF');

1 row created.

SQL> SELECT * FROM T_XIFENFEI;

        ID NAME
---------- ----------
         1 xifenfei
         2 XFF

SQL> commit;

Commit complete.

SQL> select EXTENT_ID, BLOCK_ID, BLOCKS, FILE_ID from dba_extents 
  2  where SEGMENT_NAME='T_XIFENFEI' and OWNER='SYS';

 EXTENT_ID   BLOCK_ID     BLOCKS    FILE_ID
---------- ---------- ---------- ----------
         0        128          8          6

SQL> select name from v$datafile where file#=6;

NAME
----------------------------------------------------
+XIFENFEI/xff/datafile/xifenfei.268.781905429

SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME like '%XIFENFEI%';

GROUP_NUMBER
------------
           2

在ASM用户中查询相关数据

--ASM中执行
SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp
  2  FROM x$kffxp                      
  3   WHERE GROUP_KFFXP=2 
  4  AND NUMBER_KFFXP=268; 

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        681          0
         1       1092          1
         1       1093          2
         0        682          3
         1       1094          4
         1       1095          5
         0        683          6
         1       1096          7
         0        684          8
         1       1097          9
         1       1098         10

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        685         11
         1       1099         12
         0        686         13
         1       1100         14
         1       1101         15
         0        687         16
         1       1102         17
         1       1103         18
         0        688         19
         1       1104         20
         0        689         21

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         1       1105         22
         1       1106         23
         0        690         24
         1       1107         25
         0        691         26
         1       1108         27
         1       1109         28
         0        692         29
         1       1110         30

31 rows selected.
--数据文件6的AU分配情况

SQL> select 128*8/1024 from dual;

128*8/1024
----------
         1


SQL> select 8*8/1024 from dual;

  8*8/1024
----------
     .0625
--可以得出该表T_XIFENFEI的数据分布在第二块AU中(DISK_KFFXP=1/AU_KFFXP=1092/XNUM_KFFXP=1)

SQL> select name, path from v$asm_disk where group_number=2     
  2  and disk_number=1;

NAME                           PATH
------------------------------ --------------------------
XIFENFEI_0001                  /dev/oracleasm/disks/VOL4

找出对应磁盘或者分区

[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ cat /proc/partitions |grep "8       18"
   8       18    3879697 sdb2

因为这里的block_id=128,刚好是下一个AU的起点,所以dd操作的起点是第二个AU(DISK_KFFXP=1/AU_KFFXP=1092),而终点是8*8=64K(第二个AU中offset 64KB)

执行dd导出表数据

of=/dev/sdb2
sb=1K
skip=1092*1024=1118208
count=64

[root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208|strings
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.000656471 seconds, 99.8 MB/s
XFF,
xifenfei
[root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208 of=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.00226337 seconds, 29.0 MB/s
[root@rac1 ~]# chown oracle.oinstall /tmp/t_xifenfe.tab 
[root@rac1 ~]# ll /tmp/t_xifenfe.tab 
-rw-r--r-- 1 oracle oinstall 65536 Apr 29 21:54 /tmp/t_xifenfe.tab

bbed 修改数据内容

[oracle@rac1 ~]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 29 22:43:56 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/tmp/t_xifenfe.tab'
        FILENAME        /tmp/t_xifenfe.tab

BBED> set block 4
        BLOCK#          4

BBED> set mode edit
        MODE            Edit

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> map
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                                     Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8041]                        @122     

 ub1 rowdata[25]                            @8163    

 ub4 tailchk                                @8188    


BBED> p kdbr
sb2 kdbr[0]                                 @118      8073
sb2 kdbr[1]                                 @120      8063

BBED> find /c XFF
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 5846462c 010202c1 02087869 66656e66 65690106 ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8170 to 8191  Dba:0x00000000
-------------------------------------------------------
 5846462c 010202c1 02087869 66656e66 l XFF,......xifenf
 65690106 ba33                       l ei...3

 <16 bytes per line>

BBED> m /c xff
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 7866662c 010202c1 02087869 66656e66 65690106 ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8170 to 8191  Dba:0x00000000
-------------------------------------------------------
 7866662c 010202c1 02087869 66656e66 l xff,......xifenf
 65690106 ba33                       l ei...3

 <16 bytes per line>

BBED> find /c xifenfei
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 78696665 6e666569 0106ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8180 to 8191  Dba:0x00000000
-------------------------------------------------------
 78696665 6e666569 0106ba33          l xifenfei...3

 <16 bytes per line>

BBED> m /c XIFENFEI
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 58494645 4e464549 0106ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8180 to 8191  Dba:0x00000000
-------------------------------------------------------
 58494645 4e464549 0106ba33          l XIFENFEI...3

 <16 bytes per line>

BBED> sum
Check value for File 0, Block 4:
current = 0xd332, required = 0xf332

BBED> sum apply
Check value for File 0, Block 4:
current = 0xf332, required = 0xf332

BBED> set offset 8073
        OFFSET          8073

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8073 to 8191  Dba:0x00000000
-------------------------------------------------------
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00002c01 0202c103 l ..........,.....
 03786666 2c010202 c1020858 4946454e l .xff,......XIFEN
 46454901 06ba33                     l FEI...3

 <16 bytes per line>

BBED> exit

dd导入修改后数据验证

--会话1关闭数据库
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:48:51 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


--会话2导入bbed修改后数据
[root@rac1 ~]# dd of=/dev/sdb2 bs=1k count=64 seek=1118208 if=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.0014908 seconds, 44.0 MB/s

--会话1启动数据库库查询
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:51:00 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> select * from t_xifenfei;

        ID NAME
---------- ----------
         1 XIFENFEI
         2 xff

发表在 Oracle ASM, 非常规恢复 | 标签为 bbed | 3 条评论

bbed解决ORA-01190

发表于 2012 年 3 月 15 日由惜分飞

当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。
一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
         5 OFFLINE               868810

SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'


SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(last_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
         1        868874
         2        868874
         3        868874
         4        868874
         5        868810                     868874

--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;

     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        868874                     868871
         2        868874                     868871
         3        868874                     868871
         4        868874                     868871
         5        868810                     787897

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

二、bbed修改相关项

下面两项与resetlogs相关
kcvfhrlc表示 reset logs count
kcvfhrls表示 resetlogs scn
下面四项与数据库文件scn相关
kscnbas (at offset 140) – SCN of last change to the datafile.
kcvcptim (at offset 148) - Time of the last change to the datafile.
kcvfhcpc (at offset 176) – Checkpoint count.
kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.


BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/system01.dbf

BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5eed37

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x000d4207
   ub2 kscnwrp                              @120      0x0000

       

BBED>  p kcvcpscn
struct kcvcpscn, 8 bytes                 @484 
   ub4 kscnbas                           @484      0x000d4495
   ub2 kscnwrp                           @488      0x0000
   
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000078

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000077

BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/xifenfei01.dbf

BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5bc6e5

BBED> set mode edit
        MODE            Edit

BBED> m /x 37ed5e2e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5eed37

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x000c05b9
   ub2 kscnwrp                              @120      0x0000

BBED> m /x 07420d00

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x000d4207
   ub2 kscnwrp                              @120      0x0000

BBED>  p kcvcpscn
struct kcvcpscn, 8 bytes                 @484 
   ub4 kscnbas                           @484      0x000d41ca
   ub2 kscnwrp                           @488      0x0000
 
BBED> set offset 484
        OFFSET          484

BBED> m /x 95440d00
BBED-00209: invalid number (95440d00)

BBED> m /x 9544

BBED> set offset +2
        OFFSET          486

BBED> m /x 0d00

BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000003

BBED> m /x 78000000

BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000078

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000002

BBED> m /x 77000000

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000077

BBED> sum
Check value for File 0, Block 1:
current = 0xe079, required = 0x5940

BBED> sum apply
Check value for File 0, Block 1:
current = 0x5940, required = 0x5940
1

三、数据文件online

[oracle@xifenfei ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             301992456 bytes
Database Buffers           58720256 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;

     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        869528                     868871
         2        869528                     868871
         3        869528                     868871
         4        869528                     868871
         5        869525                     868871

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.

姊妹篇:_allow_resetlogs_corruption和adjust_scn解决ORA-01190

发表在非常规恢复 | 标签为 bbed, ORA-01190 | 4 条评论

17813235971

QQ 咨询

标签归档：bbed

bbed 找回被删除数据

bbed修改ASM中数据

bbed解决ORA-01190

文章分类

最近发表

标签归档：bbed

bbed 找回被删除数据

bbed修改ASM中数据

bbed解决ORA-01190

标签云

文章分类

最近发表