​[20250513]建立完善finddoid.sh脚本3.txt

[20250513]建立完善finddoid.sh脚本3.txt --//从原来扫描数据文件取数据记录的脚本分离出来，写一个根据数据段号确定文件号以及块号的脚本，并增加确定数据段号的最大行 --//号的情况，前面改写感觉文件命名不合理再做一些修改。 $ cat finddoid.sh #!/bin/bash # argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id file_number=$1 begin_block=$2 end_block=$3 data_object_id=$4 # scan begin_block to end_block,define search scope. /bin/rm scan1.txt 2>/dev/null #seq  -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \ #rlbbed | grep "^BBED" | egrep "$file_number,|ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt /bin/seq  -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo -n $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \ rlbbed | grep -B1 --no-group-separator "ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt /bin/cp scan1.txt ${data_object_id}_scan.txt # scan begin_block to end_block,obtain max row. /bin/rm scan2.txt 2>/dev/null /bin/cat ${data_object_id}_scan.txt | xargs -IQ echo p dba Q kdbt[0].kdbtnrow | rlbbed | grep kdbtnrow | awk '{print $NF}' >| scan2.txt max_row=$(sort -nr scan2.txt | head -1) echo $data_object_id  max_row = $max_row /bin/cp scan2.txt ${data_object_id}_max_rowsnum.txt echo echo scan result in ${data_object_id}_scan.txt, scan max rows num result in ${data_object_id}_max_rowsnum.txt echo --//顺便测试看看： 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> select * from dba_extents where segment_name='EMP'   2  @ pr ============================== OWNER                         : SCOTT SEGMENT_NAME                  : EMP PARTITION_NAME                : SEGMENT_TYPE                  : TABLE TABLESPACE_NAME               : USERS EXTENT_ID                     : 0 FILE_ID                       : 12 BLOCK_ID                      : 144 BYTES                         : 65536 BLOCKS                        : 8 RELATIVE_FNO                  : 12 PL/SQL procedure successfully completed. $ . finddoid.sh 12 144 $((144+8-1)) 76193 76193 max_row = 28 scan result in 76193_scan.txt, scan max rows num result in 76193_max_rowsnum.txt --//最大行号是28，emp表14条记录，该表里面记录删除再重新插入，这样的情况导致行号是28. $ paste 76193_scan.txt 76193_max_rowsnum.txt 12,147  12 12,148  0 12,149  0 12,150  28 12,151  0 SCOTT@book01p> select rowid from emp where rownum=1; ROWID ------------------ AAASmhAAMAAAACWAAN SCOTT@book01p> @ rowid AAASmhAAMAAAACWAAN DATA_OBJECT_ID       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT -------------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------------------------          76193         12        150         13  0x3000096           12,150               alter system dump datafile 12 block 150 ; --//通过bbed观察可以发现： BBED> p dba 12,150 kdbt[0].kdbtnrow sb2 kdbtnrow                                @116      28 BBED> p  dba 12,150 kdbr sb2 kdbr[0]         @118      1 sb2 kdbr[1]         @120      2 sb2 kdbr[2]         @122      3 sb2 kdbr[3]         @124      4 sb2 kdbr[4]         @126      5 sb2 kdbr[5]         @128      6 sb2 kdbr[6]         @130      7 sb2 kdbr[7]         @132      8 sb2 kdbr[8]         @134      9 sb2 kdbr[9]         @136      10 sb2 kdbr[10]        @138      11 sb2 kdbr[11]        @140      12 sb2 kdbr[12]        @142      24 sb2 kdbr[13]        @144      6505 sb2 kdbr[14]        @146      6462 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ sb2 kdbr[15]        @148      6419 sb2 kdbr[16]        @150      6378 sb2 kdbr[17]        @152      6333 sb2 kdbr[18]        @154      6292 sb2 kdbr[19]        @156      6251 sb2 kdbr[20]        @158      6211 sb2 kdbr[21]        @160      6173 sb2 kdbr[22]        @162      6130 sb2 kdbr[23]        @164      6092 sb2 kdbr[24]        @166     -1 sb2 kdbr[25]        @168      6054 sb2 kdbr[26]        @170      6015 sb2 kdbr[27]        @172      5976 --//最后1列是记录是相对偏移,绝对偏移需要加+kdbh的偏移。 --//偏移小于kdbt[0].kdbtnrow的情况记录的是可以重新使用的行目录链表，数值指向下1个行目录，-1表示结尾。 BBED> p dba 12,150 kdbh struct kdbh, 14 bytes                       @100    ub1 kdbhflag                             @100      0x00 (NONE)    sb1 kdbhntab                             @101      1    sb2 kdbhnrow                             @102      28    sb2 kdbhfrre                             @104      0    sb2 kdbhfsbo                             @106      74    sb2 kdbhfseo                             @108      5976    sb2 kdbhavsp                             @110      7447    sb2 kdbhtosp                             @112      7447 --//kdbh的偏移 是100. SCOTT@book01p> @ bbedcol12 scott emp DISPLAY BBED EXAMINE(X) FORMAT C80 -------------------------------------------------------------------------------- nccntnnn BBED> x /rnccntnnn dba 12,150 *kdbr[14] rowdata[486]                                @6562 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ------------ flag@6562: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@6563: 0x00 cols@6564:    8 col    0[3] @6565: 7499 col    1[5] @6569: ALLEN col    2[8] @6575: SALESMAN col    3[3] @6584: 7698 col    4[7] @6588: 1981-02-20 00:00:00 col    5[2] @6596: 1600 col    6[2] @6599: 300 col    7[2] @6602: 30 --//下划线记录的是绝对偏移 6462+100 = 6562。 --//看看dba 12,147的情况： BBED> p dba 12,147 kdbt[0].kdbtnrow sb2 kdbtnrow                                @116      12 BBED> p dba 12,147 kdbr sb2 kdbr[0]                                 @118      8050 sb2 kdbr[1]                                 @120      8007 sb2 kdbr[2]                                 @122      7964 sb2 kdbr[3]                                 @124      7923 sb2 kdbr[4]                                 @126      7878 sb2 kdbr[5]                                 @128      7837 sb2 kdbr[6]                                 @130      7796 sb2 kdbr[7]                                 @132      7758 sb2 kdbr[8]                                 @134      7715 sb2 kdbr[9]                                 @136      7677 sb2 kdbr[10]                                @138      7638 sb2 kdbr[11]                                @140      7599 BBED> x /rnccntnnn dba 12,147 *kdbr[0] rowdata[451]                                @8150 ------------ flag@8150: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) lock@8151: 0x02 cols@8152:    0 --//已经删除了。 $ . /home/oracle/sqllaji/bbed/fffext.sh 12 147 150 76193 nccntnnn 7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20 7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30 7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30 7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20 7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30 7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30 7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10 7788|SCOTT|ANALYST|7566|1987-07-13 00:00:00|3000||20 7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10 7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30 7876|ADAMS|CLERK|7788|1987-07-13 00:00:00|1100||20 7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30 7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20 7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10 --//读取没有问题。 3.继续： SCOTT@book01p> create table t4 (id number ,text clob); Table created. SCOTT@book01p> insert into t4 select rownum,to_char(rownum)||lpad('x',3964,'x') from dual connect by level <=1e4; 10000 rows created. SCOTT@book01p> @ o2 t4 SCOTT@book01p> @ pr ============================== O_OWNER                       : SCOTT O_OBJECT_NAME                 : T4 O_OBJECT_TYPE                 : TABLE SEG_PART_NAME                 : O_STATUS                      : VALID OID                           : 128574 D_OID                         : 128574 CREATED                       : 2025-05-13 08:55:49 LAST_DDL_TIME                 : 2025-05-13 08:55:49 PL/SQL procedure successfully completed. $ . finddoid.sh 12 128 32640 128574 128574 max_row = 153 scan result in 128574_scan.txt , scan max rows num result in 128574_max_rowsnum.txt $ paste 128574_scan.txt 128574_max_rowsnum.txt | head -6 12,171  151 12,172  151 12,173  151 12,174  151 12,175  153 12,216  151 --//这样在使用恢复脚本时最大行号不用采用很大的值，减少不必要的扫描。实际上行号从0开始计数，输入值采用152就可以了。