[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 --//结果集合完全一样。