[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. --//估计undo设置的问题，还是truncate的恢复事务很大，导致前面的查询看不见OBJ#=128044的变化。 --//可以看出truncate后lob的索引段没有变化。 --//另外写一篇说明truncate table后相关数据段号的变化。