[20250511]truncate table恢复实战3(包含lob类型字段).txt-牛翰网

[20250511]truncate table恢复实战3(包含lob类型字段).txt

–//测试看看脚本如果表包含lob类型字段，truncate table后使用该脚本恢复数据是否可行。

–//并且记录自己操作的过程以及相关错误。

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 t3 (id number ,text clob);

Table created.

SCOTT@book01p> insert into t3 select rownum,to_char(rownum)||lpad(‘x’,3964,’x’) from dual connect by level <=1e4;

10000 rows created.

–//这样lob数据不在数据表段内，在表数据块内仅仅保留一些lob定位的信息。

BBED> set dba 12,173

DBA 0x030000ad (50331821 12,173)

BBED> x /rnx *kdbr[152]

rowdata[0] @1250

———-

flag@1250: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1251: 0x01

cols@1252: 2

col 0[3] @1253: 153

col 1[38] @1257: 0x00 0x70 0x00 0x01 0x02 0x0c 0x80 0x80 0x00 0x02 0x00 0x00 0x00 0x01 0x00 0x00 0x00 0xfc 0xde 0x31 0x00 0x12 0x40

0x90 0x00 0x0c 0x21 0x00 0x1e 0xfe 0x01 0x00 0x01 0x03 0x00 0x01 0xce 0x01

SCOTT@book01p> create table t3_bak as select * from t3;

Table created.

SCOTT@book01p> create table bak_t3 tablespace tsp_audit as select * from t3 where 1=0;

Table created.

SCOTT@book01p> @ o2 SYS_LOB0000128044C00002$$ ”

owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME

—– —————————— ———– ——— ———- ———- ——————- ——————-

SCOTT SYS_LOB0000128044C00002$$ LOB VALID 128045 128045 2025-05-11 15:14:20 2025-05-11 15:14:20

3.truncate table的恢复：

SCOTT@book01p> truncate table t3 ;

Table truncated.

SCOTT@book01p> @ o2 t3

SCOTT@book01p> @ pr

==============================

O_OWNER                       : SCOTT

O_OBJECT_NAME                 : T3

O_OBJECT_TYPE                 : TABLE

SEG_PART_NAME                 :

O_STATUS                      : VALID

OID                           : 128044

D_OID                         : 128054

CREATED                       : 2025-05-11 15:14:20

LAST_DDL_TIME                 : 2025-05-11 15:19:26

PL/SQL procedure successfully completed.

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s ” ” name=’T3′

VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V       OBJ#   DATAOBJ# NAME

——————– ——————– —————– ————— —————- – ———- ———- —–

2025-05-11 15:14:18. 2025-05-11 15:19:24.          41437896        41440994 07001100711F0000 I     128044     128044 T3

2025-05-11 15:19:24.                               41440994                 04002100631F0000 U     128044     128054 T3

–//可以确定原来数据段号128044。

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s ” ” name=’SYS_LOB0000128044C00002$$’

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V OBJ# DATAOBJ# NAME

——————– ——————– —————– ————— —————- – ———- ———- ——————————

128045 128053 SYS_LOB0000128044C00002$$

–//实际上lob段也需要修改，对应索引段不需要。原来的数据段号是128045。

SYS@book01p> update (select OBJ#, DATAOBJ# , OWNER#, NAME from obj$ where obj#=128044) set DATAOBJ#=128044;

1 row updated.

SYS@book01p> commit ;

Commit complete.

SYS@book01p> update (select OBJ#, DATAOBJ# , OWNER#, NAME from obj$ where obj#=128045) set DATAOBJ#=128045;

1 row updated.

SYS@book01p> commit ;

Commit complete.

SYS@book01p> alter system flush shared_pool;

System altered.

SYS@book01p> select * from dba_data_files where file_id=12;

SYS@book01p> @ 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.

SCOTT@book01p> create table scanblock ( file_id number,block_id number ) tablespace TSP_AUDIT;

Table created.

$ . /home/oracle/sqllaji/bbed/finddoid.sh 12 128 32640 128044

$ awk ‘{print “insert into scanblock values(“,$1,”);”}’ scan1.txt >| xy.txt

–//执行xy.txt脚本,注意提交。

SCOTT@book01p> select id,substr(text,1,3) c10 from t3 where rowid = dbms_rowid.rowid_create(1,128044,12,171,0);

ID C10

———- ———-

456 456

–//OK.

–//补充：如果不修改lob的数据段号报如下错误。

SCOTT@book01p> select * from t3 where rowid = dbms_rowid.rowid_create(1,128044,12,171,0);

ERROR:

ORA-01555: snapshot too old: rollback segment number with name “” too small

ORA-22924: snapshot too old

SYS@book01p> @ txt/truncT.txt SCOTT T3 SCOTT BAK_T3

PL/SQL procedure successfully completed.

SCOTT@book01p> select count(*) from bak_t3;

COUNT(*)

———-

9998

–//丢失2条，奇怪。

SCOTT@book01p> select id ,substr(id,1,5) from bak_t3 minus select id , substr(id,1,5) from t3_bak;

no rows selected

SCOTT@book01p> select id ,substr(id,1,5) from t3_bak minus select id , substr(id,1,5) from bak_t3;

        ID SUBSTR(ID)

———- ———-

       152 152

       153 153

–//为什么?知道了我truncT.txt脚本定义扫描行号到150条记录(从0计数),正常的业务表很少1个数据块容纳很多记录的情况。修改到200

–//后尝试,理论设置越大扫描恢复时间越长，最佳的情况是根据需要修改该值。

–//truncate table BAK_T3 ;

SYS@book01p> @ txt/truncT.txt SCOTT T3 SCOTT BAK_T3

PL/SQL procedure successfully completed.

SCOTT@book01p> select count(*) from bak_t3;

COUNT(*)

———-

10000

SCOTT@book01p> select id ,substr(id,1,5) from bak_t3 minus select id , substr(id,1,5) from t3_bak;

no rows selected

–//ok没有问题。

4.简单小结：

–//就是恢复需要修改表段以及lob段的数据段号为原来的值。只要没有覆盖，通过rowid方式读取任何问题。

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s ” ” “obj#>=128044”

VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V       OBJ#   DATAOBJ# NAME

——————– ——————– —————– ————— —————- – ———- ———- ——————————

                                                                                                   128044     128044 T3

                                                                                                   128049     128049 SYS_IL0000128047C00002$$

                                                                                                   128055     128055 SCANBLOCK

                                                                                                   128052     128052 SYS_IL0000128050C00002$$

                     2025-05-11 16:02:29.                          41453406                        128051     128051 SYS_LOB0000128050C00002$$

                     2025-05-11 15:48:45.                          41448487                        128045     128053 SYS_LOB0000128044C00002$$

                                                                                                   128046     128046 SYS_IL0000128044C00002$$

                                                                                                   128047     128047 T3_BAK

                                                                                                   128048     128048 SYS_LOB0000128047C00002$$

                     2025-05-11 16:02:29.                          41453406                        128050     128050 BAK_T3

2025-05-11 15:40:23.                               41446934                 08001A00E01F0000 I     128056     128056 ACTIVITY_TABLE$

2025-05-11 15:48:45.                               41448487                 06000400261F0000 U     128045     128045 SYS_LOB0000128044C00002$$

2025-05-11 16:02:29.                               41453406                 0A0017007E1F0000 U     128050     128058 BAK_T3

2025-05-11 16:02:29.                               41453406                 0A0017007E1F0000 U     128051     128057 SYS_LOB0000128050C00002$$

14 rows selected.