[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就可以了。
--//从原来扫描数据文件取数据记录的脚本分离出来，写一个根据数据段号确定文件号以及块号的脚本，并增加确定数据段号的最大行
--//号的情况，前面改写感觉文件命名不合理再做一些修改。
$ 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就可以了。
浙公网安备 33010602011771号