[20250512]drop table的恢复3(包含lob类型字段).txt
drop table的恢复3(包含lob类型字段).txt--//作为系列测试，增加drop table的恢复(包含lob类型字段)的情况，建立表包含lob字段，每个lob字段包含2个段，1个数据段，1个其
--//索引段。当drop table时，相关数据段全部删除(包括索引段也会一起删除，不过对于恢复，索引可以重建)，这样通过rowid方式恢
--//复需要建立的新表相关数据段指向原来的旧表的数据段，通过测试说明问题。
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 t5 (id number ,text clob);
Table created.
SCOTT@book01p> insert into t5 select rownum,to_char(rownum)||lpad('x',3964,'x') from dual connect by levelcommit ;
Commit complete.
--//记录相关数据段号：
SCOTT@book01p> @ o2 t5
SCOTT@book01p> @pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : T5
O_OBJECT_TYPE : TABLE
SEG_PART_NAME :
O_STATUS : VALID
OID : 128559
D_OID : 128559
CREATED : 2025-05-12 15:27:44
LAST_DDL_TIME : 2025-05-12 15:27:44
PL/SQL procedure successfully completed.
SCOTT@book01p> @lob scott.%128559%
SCOTT@book01p> @pr
==============================
OWNER : SCOTT
TABLE_NAME : T5
COLUMN_NAME : TEXT
SEGMENT_NAME : SYS_LOB0000128559C00002$$
TABLESPACE_NAME : USERS
INDEX_NAME : SYS_IL0000128559C00002$$
CHUNK : 8192
PCTVERSION :
RETENTION :
FREEPOOLS :
CACHE : NO
LOGGING : YES
ENCRYPT : NO
COMPRESSION : NO
DEDUPLICATION : NO
IN_ROW : YES
FORMAT : ENDIAN NEUTRAL
PARTITIONED : NO
SECUREFILE : YES
SEGMENT_CREATED : YES
RETENTION_TYPE : DEFAULT
RETENTION_VALUE :
VALUE_BASED : NO
MAX_INLINE : 4000
PL/SQL procedure successfully completed.
SCOTT@book01p> @ o2 SYS_LOB0000128559C00002$$
SCOTT@book01p> @ pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : SYS_LOB0000128559C00002$$
O_OBJECT_TYPE : LOB
SEG_PART_NAME :
O_STATUS : VALID
OID : 128560
D_OID : 128560
CREATED : 2025-05-12 15:27:44
LAST_DDL_TIME : 2025-05-12 15:27:44
PL/SQL procedure successfully completed.
SCOTT@book01p> @ o2 SYS_IL0000128559C00002$$
SCOTT@book01p> @ pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : SYS_IL0000128559C00002$$
O_OBJECT_TYPE : INDEX
SEG_PART_NAME :
O_STATUS : VALID
OID : 128561
D_OID : 128561
CREATED : 2025-05-12 15:27:44
LAST_DDL_TIME : 2025-05-12 15:27:44
PL/SQL procedure successfully completed.
--//T5表的数据段号128559。字段text的lob段号128560，其索引段号128561、
SCOTT@book01p> drop table t5 purge ;
Table dropped.
SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' "versions_operation='D' and VERSIONS_STARTTIME>='2025-05-12 15:32:10'"
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OBJ# DATAOBJ# NAME
-------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ---------- ---------- ------------------------------
2025-05-12 15:32:12. 41543968 09001100A51F0000 D AAAAASAABAAAI6GAAD 128560 128560 SYS_LOB0000128559C00002$$
2025-05-12 15:32:12. 41543968 09001100A51F0000 D AAAAASAABAAAI6GAAH 128559 128559 T5
2025-05-12 15:32:12. 41543968 09001100A51F0000 D AAAAASAABAAAI6GAAA 128561 128561 SYS_IL0000128559C00002$$
--//通过版本查询也能够确定删除表以及相关数据段号。
3.开始恢复：
--//不能直接建立新表在原来的表空间，为了避免建立新表的覆盖问题.可以在原表空间增加一个数据文件：
ALTER TABLESPACE USERS
ADD DATAFILE '/u01/oradata/BOOK/book01p/users02.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 4M
MAXSIZE UNLIMITED;
SYS@book01p> alter database datafile '/u01/oradata/BOOK/book01p/users01.dbf' offline drop;
Database altered.
--//注：因为我的测试环境没有打开归档，导致直接offline报错，加入drop没有问题，注意并非真正意思删除。
SYS@book01p> recover datafile 12;
Media recovery complete.
--//顺手先recover datafile 12;以后可以直接online。
--//然后再建立新表绕开这个覆盖问题。
--//建立的表要加入SEGMENT CREATION IMMEDIATE,不然oracle认为是空表,无法通过rowid方式读取。
SCOTT@book01p> create table t5_drop (id number ,text clob) SEGMENT CREATION IMMEDIATE ;
Table created.
SCOTT@book01p> column PARTITION_NAME noprint
SCOTT@book01p> select * from dba_extents where segment_name='T5_DROP';
SCOTT@book01p> @ pr
==============================
OWNER : SCOTT
SEGMENT_NAME : T5_DROP
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : USERS
EXTENT_ID : 0
FILE_ID : 17
BLOCK_ID : 128
BYTES : 65536
BLOCKS : 8
RELATIVE_FNO : 17
PL/SQL procedure successfully completed.
--//建立的新表使用的数据段不在数据文件号12上。
--//建立表空间TSP_AUDIT略。
--//ALTER USER SCOTT QUOTA UNLIMITED ON TSP_AUDIT;
SCOTT@book01p> create table bak_t5 tablespace TSP_AUDIT as select * from scott.t5_drop where 0=1;
Table created.
--//扫描获得信息插入的表bak_t5放在另外的表空间,避免覆盖原来的数据文件信息。
--//修改相关段号：
SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' "versions_operation='I' and VERSIONS_STARTTIME>='2025-05-12 15:32:10'"
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OBJ# DATAOBJ# NAME
-------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ---------- ---------- ------------------------------
2025-05-12 15:39:44. 41545595 04000800821F0000 I AAAAASAABAAAI6GAAD 128563 128563 SYS_LOB0000128562C00002$$
2025-05-12 15:39:44. 41545595 04000800821F0000 I AAAAASAABAAAI6GAAG 128562 128562 T5_DROP
2025-05-12 15:39:44. 41545595 04000800821F0000 I AAAAASAABAAAI6GAAA 128564 128564 SYS_IL0000128562C00002$$
2025-05-12 15:42:45. 41548664 06000400471F0000 I AAAAASAABAAAI6GAAL 128566 128566 SYS_LOB0000128565C00002$$
2025-05-12 15:42:45. 41548664 06000400471F0000 I AAAAASAABAAAI6GAAH 128565 128565 BAK_T5
2025-05-12 15:42:45. 41548664 06000400471F0000 I AAAAASAABAAAI6GAAK 128567 128567 SYS_IL0000128565C00002$$
6 rows selected.
--//建立新表以及lob以及索引段号分别是128562，128563，128564
--//修改指向旧表T5.
update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128562) set DATAOBJ#=128559;
update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128563) set DATAOBJ#=128560;
update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128564) set DATAOBJ#=128561;
commit ;
SYS@book01p> alter system flush shared_pool;
System altered.
--//扫描数据文件确定数据段号。
$ . finddoid.sh 12 128 32640 128559
data_object_id 128559 max_row = 153
--//该脚本顺便确定数据块的最大行记录153条。
--//建立2个文件：
$ paste scan_128559.txt scan_row_128559.txt | head -4
12,171 151
12,172 151
12,173 151
12,174 153
$ sed 's/^/insert into scanblock values (/;s/$/);/' scan_128559.txt | head -3
insert into scanblock values (12,171);
insert into scanblock values (12,172);
insert into scanblock values (12,173);
$ sed 's/^/insert into scanblock values (/;s/$/);/' scan_128559.txt >| xy.txt
SYS@book01p> create table scott.scanblock ( file_id number,block_id number ) tablespace TSP_AUDIT;
Table created.
--//注意建立的新表一定不能使用原来的表空间，避免覆盖。
--//执行xy.txt脚本，注意提交。
SYS@book01p> set timing on
SYS@book01p> @ txt/truncT.txt SCOTT T5_DROP SCOTT BAK_T5 152
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.85
SYS@book01p> set timing off
--//参数5=152，行号从0开始计数。
--//补充应该可以再改进减少扫描范围，另外写一篇blog。
SCOTT@book01p> select count(*) from bak_t5;
COUNT(*)
----------
10000
--//完全恢复。
4.收尾还原：
update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128562) set DATAOBJ#=128562;
update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128563) set DATAOBJ#=128563;
update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128564) set DATAOBJ#=128564;
commit ;
5.附上相关执行脚本：
$ 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 scan_${data_object_id}.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}' >| scan_${data_object_id}.txt
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}' >| scan_${data_object_id}.txt
# scan begin_block to end_block,obtain max row.
/bin/rm scan_row_${data_object_id}.txt 2>/dev/null
cat scan_${data_object_id}.txt | xargs -IQ echo p dba Q kdbt.kdbtnrow | rlbbed | grep kdbtnrow | awk '{print $NF}' >| scan_row_${data_object_id}.txt
max_row=$(sort -nr scan_row_${data_object_id}.txt | head -1)
echo data_object_id $data_object_id max_row = $max_row
$ cat txt/truncT.txt
DECLARE
v_fno NUMBER;
v_s_bno NUMBER;
v_e_bno NUMBER;
v_rowid ROWID;
v_owner VARCHAR2 (100) := '&&1';
v_table VARCHAR2 (100) := '&&2';
v_o_owner VARCHAR2 (100) := '&&3';
v_o_table VARCHAR2 (100) := '&&4';
v_dataobj NUMBER;
v_sql VARCHAR2 (4000);
v_tablespace VARCHAR2 (100);
nrows NUMBER;
BEGIN
nrows := 0;
SELECT data_object_id
INTO v_dataobj
FROM dba_objects
WHERE owner = v_owner AND object_name = v_table;
SELECT tablespace_name
INTO v_tablespace
FROM dba_tables
WHERE owner = v_owner AND table_name = v_table;
-- FOR i
-- IN (SELECT relative_fno, block_id, blocks FROM dba_extents WHERE owner = v_owner AND segment_name = v_table AND extent_id = 0
-- UNION ALL
-- SELECT relative_fno, block_id, blocks FROM dba_free_space WHERE tablespace_name = v_tablespace
-- UNION ALL
-- SELECT relative_fno, block_id, blocks
-- FROM (SELECT relative_fno
-- ,block_id
-- ,blocks
-- ,ROW_NUMBER () OVER (PARTITION BY owner, segment_name, partition_name ORDER BY extent_id DESC)
-- rn
-- FROM dba_extents
-- WHERE tablespace_name = v_tablespace AND extent_id > 0)
-- WHERE rn = 1)
for i in (select file_id relative_fno,block_id, 1 blocks from scott.scanblock)
LOOP
v_fno := i.relative_fno;
v_s_bno := i.block_id;
v_e_bno := i.block_id + i.blocks - 1;
--v_e_bno:=i.block_id+1-1; --//using scanblock method
FOR j IN v_s_bno .. v_e_bno
LOOP
BEGIN
FOR x IN 0 .. &&5
LOOP
v_rowid := DBMS_ROWID.rowid_create ( 1 ,v_dataobj ,v_fno ,j ,x);
v_sql :=
'insert into '
|| v_o_owner
|| '.'
|| v_o_table
|| ' select * from '
|| v_owner
|| '.'
|| v_table
|| ' where rowid=:1';
EXECUTE IMMEDIATE v_sql USING v_rowid;
IF SQL%ROWCOUNT = 1
THEN
nrows := nrows + 1;
END IF;
IF (MOD (nrows, 10000) = 0)
THEN
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
COMMIT;
END LOOP;
END LOOP;
END;
/
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]