[20250506]不使用bbed扫描确定数据段号.txt

[20250506]不使用bbed扫描确定数据段号.txt

--//前段时间恢复truncate的数据，通过bbed确定数据段号，许多情况下并没有安装bbed，测试是否可以通过别的命令行工具完成相似的
--//工作。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试环境建立：
SCOTT@book01p> create table t1 as select * from all_objects;
Table created.

SCOTT@book01p> create table t1_bak as select * from all_objects;
Table created.
--//t1_bak注意目的为了检验drop table的情况。应该使用create table t1_bak as select * from t1;建立。

SCOTT@book01p> @ o2 t1
SCOTT@book01p> @ pr
==============================
O_OWNER                       : SCOTT
O_OBJECT_NAME                 : T1
O_OBJECT_TYPE                 : TABLE
SEG_PART_NAME                 :
O_STATUS                      : VALID
OID                           : 126494
D_OID                         : 126494
CREATED                       : 2025-05-06 14:48:10
LAST_DDL_TIME                 : 2025-05-06 14:48:10
PL/SQL procedure successfully completed.

SCOTT@book01p> @ seg2 t1
SCOTT@book01p> @ pr
==============================
SEG_MB                        : 12
SEG_OWNER                     : SCOTT
SEG_SEGMENT_NAME              : T1
SEG_PARTITION_NAME            :
SEG_SEGMENT_TYPE              : TABLE
SEG_TABLESPACE_NAME           : USERS
BLOCKS                        : 1536
HDRFIL                        : 12
HDRBLK                        : 178
PL/SQL procedure successfully completed.

3.恢复测试：
SCOTT@book01p> drop table t1 purge ;
Table dropped.

SCOTT@book01p> create table emp_xxx as select * from emp ;
Table created.

SCOTT@book01p> @ seg2 emp_xxx
SCOTT@book01p> @ pr
==============================
SEG_MB                        : 0
SEG_OWNER                     : SCOTT
SEG_SEGMENT_NAME              : EMP_XXX
SEG_PARTITION_NAME            :
SEG_SEGMENT_TYPE              : TABLE
SEG_TABLESPACE_NAME           : USERS
BLOCKS                        : 8
HDRFIL                        : 12
HDRBLK                        : 178
PL/SQL procedure successfully completed.
--//原来的段头覆盖另外有1个数据块也被破坏,基本破坏了1块记录block=179。

4.其他方式确定数据段号：
BBED> p /d dba 12,180 ktbbhsid.ktbbhsg1
ub4 ktbbhsg1                                @24       126494

BBED> p /x dba 12,180 ktbbhsid.ktbbhsg1
ub4 ktbbhsg1                                @24       0x0001ee1e
--//实际上如果是数据块，数据段号位于块内的偏移24.占用4个字节。

--//180*8192+24 = 1474584,通过xxd,od 实现如下：
$ xxd -c16 -g 8 -s 1474584 -l 4 /u01/oradata/BOOK/book01p/users01.dbf
0168018: 1eee0100                           ....

$ od -j 1474584 -N 4 -t x4 -v /u01/oradata/BOOK/book01p/users01.dbf
5500030 0001ee1e
5500034

$ od -A d -j 1474584 -N 4 -t x4 -v /u01/oradata/BOOK/book01p/users01.dbf
1474584 0001ee1e
1474588

--//确定最大块号
SYS@book01p> select * from dba_DATA_FILES where file_id=12
  2  @ pr
==============================
FILE_NAME                     : /u01/oradata/BOOK/book01p/users01.dbf
FILE_ID                       : 12
TABLESPACE_NAME               : USERS
BYTES                         : 267386880
BLOCKS                        : 32640
STATUS                        : AVAILABLE
RELATIVE_FNO                  : 12
AUTOEXTENSIBLE                : YES
MAXBYTES                      : 34359721984
MAXBLOCKS                     : 4194302
INCREMENT_BY                  : 160
USER_BYTES                    : 266338304
USER_BLOCKS                   : 32512
ONLINE_STATUS                 : ONLINE
LOST_WRITE_PROTECT            : OFF
PL/SQL procedure successfully completed.

$ seq -f "%7.0f" 128 1 32640 | xargs -IQ expr Q \* 8192 + 24 | xargs -IQ od -A d -j Q -N 4 -t x4 -v /u01/oradata/BOOK/book01p/users01.dbf | grep "0001ee1e$"
$ seq -f "%7.0f" $[128*8192+24] 8192 $[32640*8192+24] | xargs -IQ od -A d -j Q -N 4 -t x4 -v /u01/oradata/BOOK/book01p/users01.dbf | grep "0001ee1e$"

$ seq -f "%7.0f" $[128*8192+24] 8192 $[32640*8192+24] | xargs -IQ od -A d -j Q -N 4 -t x4 -v /u01/oradata/BOOK/book01p/users01.dbf | awk '/ 0001ee1e$/{print "12,"($1-24)/8192}'
$ seq -f "%7.0f" $[128*8192+24] 8192 $[32640*8192+24] | xargs -IQ od -A d -j Q -N 4 -t x4 -v /u01/oradata/BOOK/book01p/users01.dbf | awk '/ 0001ee1e$/{print "12,"($1-24)/8192}' > scan2.txt

5.对比bbed的情况：
$ seq 128 1 32640 | xargs -IQ echo p /d dba 12,Q ktbbhsid.ktbbhsg1 | rlbbed  > /dev/null
$ grep -B1  "  126494$" log.bbd | grep ktbbhsid.ktbbhsg1 | head -7
BBED> p /d dba 12,180 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,181 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,182 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,183 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,184 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,185 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,186 ktbbhsid.ktbbhsg1

--//将需要扫描的数据块保存在文本scan.txt文件中。
$ grep -B1 126494$ log.bbd | grep ktbbhsid.ktbbhsg1 >| scan.txt
$ awk '{print $5}' scan.txt >scan3.txt

$ diff scan2.txt ~/bbed/scan3.txt
--//结果集合完全一样。