草庐IT

[20220909]bbed关于删除记录恢复的问题.txt

lfree 2023-03-28 原文
[20220909]bbed关于删除记录恢复的问题.txt

--//快下班被别人问的关于删除记录使用bbed恢复的问题,我开始以为很快讲解完,删除记录oracle仅仅打上一个标识,实际的记录还存在.
--//实际上地方问的是多次DML(删除记录的情况),实际上只要dump还能看到,bbed还是可以恢复的,做一个例子说明:

1.环境:
SCOTT@test01p> @ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SCOTT@test01p> create table deptx as select * from dept ;
Table created.

SCOTT@test01p> select rowid from deptx where rownum=1;
ROWID
------------------
AAAHGVAALAAAACjAAA

SCOTT@test01p> @ rowid AAAHGVAALAAAACjAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA   DBA    TEXT
---------- ---------- ---------- ---------- ----------- ------ ----------------------------------------
     29077         11        163          0  0x2C000A3  11,163 alter system dump datafile 11 block 163

SCOTT@test01p> alter system checkpoint ;
System altered.

SCOTT@test01p> alter system checkpoint ;
System altered.

2.测试:
BBED> set dba 11,164
        DBA             0x02c000a4 (46137508 11,164)
--//注:bbed for windows 访问的block要加1,主要问题在于无法识别数据文件的第0块(OS块头)

BBED> p *kdbr
rowdata[66]
-----------
ub1 rowdata[66]                             @8162     0x2c

BBED> p kdbr
sb2 kdbr[0]                                 @142      8038
sb2 kdbr[1]                                 @144      8016
sb2 kdbr[2]                                 @146      7996
sb2 kdbr[3]                                 @148      7972

SCOTT@test01p> delete from deptx where deptno=20;
1 row deleted.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> alter system checkpoint ;
System altered.

--//通过bbed观察如下:
BBED> p kdbr dba 11,164
sb2 kdbr[0]                                 @142      8038
sb2 kdbr[1]                                 @144      8016
sb2 kdbr[2]                                 @146      7996
sb2 kdbr[3]                                 @148      7972

BBED> x /rncc *kdbr[0]
rowdata[66]                                 @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164:    3
col    0[2] @8165: 10
col   1[10] @8168: ACCOUNTING
col    2[8] @8179: NEW YORK

BBED> x /rncc *kdbr[1]
rowdata[44]                                 @8140
-----------
flag@8140: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8141: 0x02
cols@8142:    0
--//你可以发现flag 从0x2c变成 0x3c，打上了KDRHFD标识表示删除，恢复实际上就是修改flag=0x2c(也就是取消KDRHFD标识).
--//继续:

SCOTT@test01p> delete from deptx where deptno=30;
1 row deleted.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> alter system checkpoint ;
System altered.

BBED> p kdbr dba 11,164
sb2 kdbr[0]                                 @142      8038
sb2 kdbr[1]                                 @144      8016
sb2 kdbr[2]                                 @146      7996
sb2 kdbr[3]                                 @148      7972
--//偏移量还是不变,也就是这样的情况下以上两条记录都可以恢复.

SCOTT@test01p> update deptx set dname=upper(dname) where deptno=10;
1 row updated.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> alter system checkpoint ;
System altered.

BBED> p kdbr dba 11,164
sb2 kdbr[0]                                 @142      8038
sb2 kdbr[1]                                 @144      8016
sb2 kdbr[2]                                 @146      7996
sb2 kdbr[3]                                 @148      7972
--//我执行update操作，但是行目录并没有修改，主要原因在于DML修改的记录长度没有变化，oracle并不会改变行目录的偏移。
--//而是就地修改相关记录信息。

SCOTT@test01p> update deptx set dname=upper(dname)||'0' where deptno=10;
1 row updated.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> alter system checkpoint ;
System altered.

BBED> p kdbr dba 11,164
sb2 kdbr[0]                                 @142      7945
sb2 kdbr[1]                                 @144      2
sb2 kdbr[2]                                 @146     -1
sb2 kdbr[3]                                 @148      7972
--//在DML后修改记录长度发生变化后,oracle在修改kdbr[0]的偏移时,同时也修改kdbr[1],kdbr[2]的信息,
--//这个应该是oracle的一个设计理念，顺手把以前没做的事情做完...
--//你可以从修改的信息可以推断，如果下次操作是插入，使用那个行目录时应该从kdbr[1]，kdbr[2]选择。
--//如果你仔细观察可以发现删除记录的行目录记录的偏移记录的信息形成1个链表结构,sb2 kdbr[2] = -1 表示链表结构的尾部.
--//并且可以通过行目录偏移 kdbr[N] 是否 >= kdbh.kdbhnrow (当前为4),来确定是否指向正确的记录信息。

BBED> p kdbh dba 11,164
struct kdbh, 14 bytes                       @124
   ub1 kdbhflag                             @124      0x00 (NONE)
   b1 kdbhntab                              @125      1
   b2 kdbhnrow                              @126      4
=======================================================
   sb2 kdbhfrre                             @128      1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   
   sb2 kdbhfsbo                             @130      26
   sb2 kdbhfseo                             @132      7945
   b2 kdbhavsp                              @134      7987
   b2 kdbhtosp                              @136      7987

--//kdbhfrre = 1 ,表示链表结构的开头，也就是如果在块DML有插入时,会先使用kdbr[1]行目录.
--//你可以发现这时kdbr[1],kdbr[2]执行的偏移并没有覆盖,要恢复一定ok的.

BBED> assign  kdbr[1] = 8016;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbr[0]                                 @144      8016

BBED> assign  kdbr[2] = 7996;
sb2 kdbr[0]                                 @146      7996


BBED> x /rncc *kdbr[1]
rowdata[71]                                 @8140
-----------
flag@8140: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8141: 0x02
cols@8142:    0

BBED> x /rncc *kdbr[2]
rowdata[51]                                 @8120
-----------
flag@8120: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8121: 0x03
cols@8122:    0

--//修改flag取消KDRHFD标识:
BBED> assign  offset 8140 = 0x2c;
ub1 rowdata[0]                              @8140     0x2c

BBED> assign  offset 8120 = 0x2c;
ub1 rowdata[0]                              @8120     0x2c


BBED> x /2rncc *kdbr[2]
rowdata[51]                                 @8120
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x03
cols@8122:    3

col    0[2] @8123: 30
col    1[5] @8126: SALES
col    2[7] @8132: CHICAGO

rowdata[71]                                 @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x02
cols@8142:    3

col    0[2] @8143: 20
col    1[8] @8146: RESEARCH
col    2[6] @8155: DALLAS
--//现在2条记录都可以显见,剩下就是恢复对应数据块的完整性以及一致性问题.

BBED> sum apply
Check value for File 11, Block 164:
current = 0x9fe2, required = 0x9fe2

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: row locked by non-existent transaction
        table=0   slot=2
        lockid=3   ktbbhitc=3
Block 163 failed with check code 6101

--//lock 偏移8121 =0x0.
BBED> assign offset 8121=0x0;
ub1 rowdata[0]                              @8121     0x00

BBED> sum apply
Check value for File 11, Block 164:
current = 0x9ce2, required = 0x9ce2

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: entries on the free list are not ordered
        next=8016 nrow=4 chas=1
Block 163 failed with check code 6106

BBED> assign kdbh.kdbhfrre=-1
sb2 kdbhfrre                                @128     -1

BBED> sum apply
Check value for File 11, Block 164:
current = 0x631c, required = 0x631c

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: xaction header lock count mismatch
        trans=2 ilk=1 nlo=2
Block 163 failed with check code 6108

--//lock 偏移8141 =0x0.
BBED> assign offset 8141=0x0;
ub1 rowdata[0]                              @8141     0x00

BBED> sum apply
Check value for File 11, Block 164:
current = 0x611c, required = 0x611c

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: the amount of space used is not equal to block size
        used=119 fsc=0 avsp=7987 dtl=8064
Block 163 failed with check code 6110

--//dtl-used-fsc = avsp
--//8064-119-0 = 7945

BBED> assign kdbh.kdbhavsp=7945;
b2 kdbhavsp                                 @134      7945

BBED> sum apply
Check value for File 11, Block 164:
current = 0x6126, required = 0x6126

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: space available on commit is incorrect
        tosp=7987 fsc=0 stb=0 avsp=7945
Block 163 failed with check code 6111

BBED> assign kdbh.kdbhtosp=7945;
b2 kdbhtosp                                 @136      7945

BBED> sum apply
Check value for File 11, Block 164:
current = 0x611c, required = 0x611c

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
--//OK现在恢复完成.

SCOTT@test01p> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test01p> select * from deptx;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING0          NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON
--//删除记录已经恢复.
--//原来deptno= 10 的记录没有覆盖，理论将也可以恢复.继续尝试看看.

BBED> assign kdbr[0]=8038
sb2 kdbr[0]                                 @142      8038

BBED> x /rncc *kdbr[0]
rowdata[93]                                 @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164:    3

col    0[2] @8165: 10
col   1[10] @8168: ACCOUNTING
col    2[8] @8179: NEW YORK
--//这样修改行目录偏移指向执行原来的位置.

BBED> sum apply
Check value for File 11, Block 164:
current = 0x6173, required = 0x6173

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: xaction header lock count mismatch
        trans=2 ilk=1 nlo=0
Block 163 failed with check code 6108

BBED> assign offset 8163  = 0x02
ub1 rowdata[0]                              @8163     0x02

BBED> sum apply
Check value for File 11, Block 164:
current = 0x6373, required = 0x6373

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163

Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: the amount of space used is not equal to block size
        used=118 fsc=0 avsp=7945 dtl=8064
Block 163 failed with check code 6110

--//dtl-used-fsc = avsp
--//8064-118-0 = 7946

BBED> assign kdbh.kdbhavsp=7946;
b2 kdbhavsp                                 @134      7946

BBED> assign kdbh.kdbhtosp=7946;
b2 kdbhtosp                                 @136      7946

BBED> sum apply
Check value for File 11, Block 164:
current = 0x6373, required = 0x6373

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
--//ok,现在恢复到原始建立时的状态.

SCOTT@test01p> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test01p> select * from deptx;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

3.总结：
--//很久不使用bbed，有点生疏了。
--//我前面的恢复定位行目录信息时使用原来显示的信息，实际的恢复只能通过find检索0x2c,0x3c字符定位。
--//然后通过执行 x /rncc offset NNNN,确定显示的信息是否正确。获得NNNN偏移是绝对偏移，必须减去kdbh的偏移(这里是124，前面
--//有3个ITL槽),这样才能确定行目录的相对偏移值。
--//总之到对应的数据块操作相对复杂!!!

BBED> p kdbr dba 11,164
sb2 kdbr[0]                                 @142      8038
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kdbr[1]                                 @144      8016
sb2 kdbr[2]                                 @146      7996
sb2 kdbr[3]                                 @148      7972

BBED> p kdbh dba 11,164
struct kdbh, 14 bytes                       @124
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   ub1 kdbhflag                             @124      0x00 (NONE)
   b1 kdbhntab                              @125      1
   b2 kdbhnrow                              @126      4
   sb2 kdbhfrre                             @128      1
   sb2 kdbhfsbo                             @130      26
   sb2 kdbhfseo                             @132      7945
   b2 kdbhavsp                              @134      7987
   b2 kdbhtosp                              @136      7987


BBED> x /rncc *kdbr[0]
rowdata[66]                                 @8162
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164:    3
col    0[2] @8165: 10
col   1[10] @8168: ACCOUNTING
col    2[8] @8179: NEW YORK
--// 8162-124 = 8038
20220909bbednbspbrkdbrOracle

有关[20220909]bbed关于删除记录恢复的问题.txt的更多相关文章

  1. ruby - 在 64 位 Snow Leopard 上使用 rvm、postgres 9.0、ruby 1.9.2-p136 安装 pg gem 时出现问题 - 2

    我想为Heroku构建一个Rails3应用程序。他们使用Postgres作为他们的数据库，所以我通过MacPorts安装了postgres9.0。现在我需要一个postgresgem并且共识是出于性能原因你想要pggem。但是我对我得到的错误感到非常困惑当我尝试在rvm下通过geminstall安装pg时。我已经非常明确地指定了所有postgres目录的位置可以找到但仍然无法完成安装:$envARCHFLAGS='-archx86_64'geminstallpg--\--with-pg-config=/opt/local/var/db/postgresql90/defaultdb/po

  2. ruby - 通过 rvm 升级 ruby​​gems 的问题 - 2

    尝试通过RVM将RubyGems升级到版本1.8.10并出现此错误:$rvmrubygemslatestRemovingoldRubygemsfiles...Installingrubygems-1.8.10forruby-1.9.2-p180...ERROR:Errorrunning'GEM_PATH="/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/ruby-1.9.2-p180@global:/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/rub

  3. ruby-on-rails - 如何从 format.xml 中删除 <hash></hash> - 2

    我有一个对象has_many应呈现为xml的子对象。这不是问题。我的问题是我创建了一个Hash包含此数据，就像解析器需要它一样。但是rails自动将整个文件包含在.........我需要摆脱type="array"和我该如何处理？我没有在文档中找到任何内容。 最佳答案 我遇到了同样的问题；这是我的XML:我在用这个:entries.to_xml将散列数据转换为XML，但这会将条目的数据包装到中所以我修改了:entries.to_xml(root:"Contacts")但这仍然将转换后的XML包装在“联系人”中，将我的XML代码修改为

  4. ruby - 我可以使用 Ruby 从 CSV 中删除列吗？ - 2

    查看Ruby的CSV库的文档，我非常确定这是可能且简单的。我只需要使用Ruby删除CSV文件的前三列，但我没有成功运行它。 最佳答案 csv_table=CSV.read(file_path_in,:headers=>true)csv_table.delete("header_name")csv_table.to_csv#=>ThenewCSVinstringformat检查CSV::Table文档:http://ruby-doc.org/stdlib-1.9.2/libdoc/csv/rdoc/CSV/Table.html

  5. ruby - 通过 RVM (OSX Mountain Lion) 安装 Ruby 2.0.0-p247 时遇到问题 - 2

    我的最终目标是安装当前版本的RubyonRails。我在OSXMountainLion上运行。到目前为止，这是我的过程:已安装的RVM$\curl-Lhttps://get.rvm.io|bash-sstable检查已知(我假设已批准)安装$rvmlistknown我看到当前的稳定版本可用[ruby-]2.0.0[-p247]输入命令安装$rvminstall2.0.0-p247注意:我也试过这些安装命令$rvminstallruby-2.0.0-p247$rvminstallruby=2.0.0-p247我很快就无处可去了。结果:$rvminstall2.0.0-p247Search

  6. ruby - 我可以使用 aws-sdk-ruby 在 AWS S3 上使用事务性文件删除/上传吗？ - 2

    我发现ActiveRecord::Base.transaction在复杂方法中非常有效。我想知道是否可以在如下事务中从AWSS3上传/删除文件:S3Object.transactiondo#writeintofiles#raiseanexceptionend引发异常后，每个操作都应在S3上回滚。S3Object这可能吗？？ 最佳答案 虽然S3API具有批量删除功能，但它不支持事务，因为每个删除操作都可以独立于其他操作成功/失败。该API不提供任何批量上传功能(通过PUT或POST)，因此每个上传操作都是通过一个独立的API调用完成的

  7. ruby - Sinatra:运行 rspec 测试时记录噪音 - 2

    Sinatra新手；我正在运行一些rspec测试，但在日志中收到了一堆不需要的噪音。如何消除日志中过多的噪音？我仔细检查了环境是否设置为:test，这意味着记录器级别应设置为WARN而不是DEBUG。spec_helper:require"./app"require"sinatra"require"rspec"require"rack/test"require"database_cleaner"require"factory_girl"set:environment,:testFactoryGirl.definition_file_paths=%w{./factories./test/

  8. ruby - Fast-stemmer 安装问题 - 2

    由于fast-stemmer的问题，我很难安装我想要的任何ruby​​gem。我把我得到的错误放在下面。Buildingnativeextensions.Thiscouldtakeawhile...ERROR:Errorinstallingfast-stemmer:ERROR:Failedtobuildgemnativeextension./System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/bin/rubyextconf.rbcreatingMakefilemake"DESTDIR="cleanmake"DESTDIR=

  9. ruby - 安装 Ruby 时遇到问题(无法下载资源 "readline--patch") - 2

    当我尝试安装Ruby时遇到此错误。我试过查看this和this但无济于事➜~brewinstallrubyWarning:YouareusingOSX10.12.Wedonotprovidesupportforthispre-releaseversion.Youmayencounterbuildfailuresorotherbreakages.Pleasecreatepull-requestsinsteadoffilingissues.==>Installingdependenciesforruby:readline,libyaml,makedepend==>Installingrub

  10. ruby-on-rails - Rails 5 Active Record 记录无效错误 - 2

    我有两个Rails模型，即Invoice和Invoice_details。一个Invoice_details属于Invoice，一个Invoice有多个Invoice_details。我无法使用accepts_nested_attributes_forinInvoice通过Invoice模型保存Invoice_details。我收到以下错误:(0.2ms)BEGIN(0.2ms)ROLLBACKCompleted422UnprocessableEntityin25ms(ActiveRecord:4.0ms)ActiveRecord::RecordInvalid(Validationfa

随机推荐