[20250515]drop table相关数据段的确定与恢复.txt

来源：这里教程网时间：2026-03-03 21:56:55 作者：

[20250515]drop table相关数据段的确定与恢复.txt --//这几天一直在做truncare table，drop table的非常规恢复，通过修改obj$表的DATAOBJ#指向原来的数据段号，然后通过rowid扫描 --//的方式收集数据。该方式最大的缺点就是恢复很慢，原始的脚本定义行号是999，而且如果扫描数据块范围很大的情况下，真是很慢. --//我第一次尝试没有修改脚本使用行号999，后面修改150才快了不少。 --//如果通过redo分析，确定扫描范围，这样通过bbed进一步确定每块的最大行号，这样就可以大大加快恢复进度。 --//本文尝试如何通过分析redo的转储，确定drop table的相关数据段的占用数据块的范围。 --//我看了以前的笔记，[20181210]truncate的另外恢复4.txt，通过修改段头，恢复原来的信息，这样难度太大，应该很容易分析redo --//的转储获得相关信息。 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. ALTER TABLESPACE USERS ADD DATAFILE '/u01/oradata/BOOK/book01p/users02.dbf' SIZE 100M AUTOEXTEND ON NEXT 4M MAXSIZE UNLIMITED; --//表空间users增加1个数据文件，比较符合许多生产系统的情况。 2.测试环境建立： SCOTT@book01p> create table t8 as select * from all_objects; Table created. --//分析略。 SCOTT@book01p> select count(*) from t8; COUNT(*) ---------- 69896 SCOTT@book01p> create unique index pk_t8 on t8 (object_id); Index created. SCOTT@book01p> alter table t8 add constraint pk_t8 primary key (object_id); Table altered. SCOTT@book01p> create index i_t8_OBJECT_NAME on t8(OBJECT_NAME); Index created. --//建立一些索引比较符合实际的情况。 3.先转储该表的数据段头： SCOTT@book01p> @ seg2z T8 '' ============================== SEG_MB : 12 SEG_OWNER : SCOTT SEG_SEGMENT_NAME : T8 SEG_PARTITION_NAME : SEG_SEGMENT_TYPE : TABLE SEG_TABLESPACE_NAME : USERS BLOCKS : 1536 HDRFIL : 12 HDRBLK : 170 PL/SQL procedure successfully completed. SCOTT@book01p> column PARTITION_NAME noprint SCOTT@book01p> select * from dba_extents where segment_name='T8' order by EXTENT_ID; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ----- ------------ ------------ --------------- --------- ------- -------- -------- ------ ------------ SCOTT T8 TABLE USERS 0 12 168 65536 8 12 SCOTT T8 TABLE USERS 1 12 176 65536 8 12 SCOTT T8 TABLE USERS 2 12 184 65536 8 12 SCOTT T8 TABLE USERS 3 12 192 65536 8 12 SCOTT T8 TABLE USERS 4 12 200 65536 8 12 SCOTT T8 TABLE USERS 5 12 208 65536 8 12 SCOTT T8 TABLE USERS 6 12 216 65536 8 12 SCOTT T8 TABLE USERS 7 12 224 65536 8 12 SCOTT T8 TABLE USERS 8 12 232 65536 8 12 SCOTT T8 TABLE USERS 9 12 240 65536 8 12 SCOTT T8 TABLE USERS 10 12 248 65536 8 12 SCOTT T8 TABLE USERS 11 12 24576 65536 8 12 SCOTT T8 TABLE USERS 12 12 24584 65536 8 12 SCOTT T8 TABLE USERS 13 12 24592 65536 8 12 SCOTT T8 TABLE USERS 14 12 24600 65536 8 12 SCOTT T8 TABLE USERS 15 12 24608 65536 8 12 SCOTT T8 TABLE USERS 16 18 128 1048576 128 18 SCOTT T8 TABLE USERS 17 12 256 1048576 128 12 SCOTT T8 TABLE USERS 18 18 256 1048576 128 18 SCOTT T8 TABLE USERS 19 12 384 1048576 128 12 SCOTT T8 TABLE USERS 20 18 384 1048576 128 18 SCOTT T8 TABLE USERS 21 12 512 1048576 128 12 SCOTT T8 TABLE USERS 22 18 512 1048576 128 18 SCOTT T8 TABLE USERS 23 12 640 1048576 128 12 SCOTT T8 TABLE USERS 24 18 640 1048576 128 18 SCOTT T8 TABLE USERS 25 12 768 1048576 128 12 SCOTT T8 TABLE USERS 26 18 768 1048576 128 18 27 rows selected. --//EXTENT_ID 0-15使用的数据文件12，EXTENT_ID=16开始使用数据文件18，然后相互交叉使用。 --//实际上Extent Map的信息与查询dba_extents视图结果一致。 --//理论讲扫描范围是 Data dba 到对应Extent Map看到的dba + length -1. --//注：表太小，没有看到L2 dba的情况。 4.看看drop table的情况： $ cat dr.txt column member new_value v_member column member noprint set numw 12 pause run alter system archive log current or alter system switch logfile; --//12c不允许在pluggable database执行这条命令 --//alter system archive log current; set termout off SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1; set termout on column curr1 new_value v_curr1 select current_scn curr1 from v$database; --//以下操作内容: drop table t8 purge; --//以上操作内容: column curr2 new_value v_curr2 select current_scn curr2 from v$database; prompt exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => &&v_curr1 ,ENDSCN => &&v_curr2 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2; @ti alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2; SCOTT@book01p> @ dr.txt run alter system archive log current or alter system switch logfile CURR1 ------------ 41722606 Table dropped. CURR2 ------------ 41722643 exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => 41722606 ,ENDSCN => 41722643 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE) alter system dump logfile '/u01/oradata/BOOK/redo02.log' scn min 41722606 scn max 41722643 New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3627_0001.trc System altered. 5.尝试恢复： --//分析转储： $ egrep "ADD: dba|ADDAXT:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3627_0001.trc --//居然不能像truncate那样看到相关信息。 --//仔细查看redo转储，可以发现如下信息： REDO RECORD - Thread:1 RBA: 0x0000d1.00000103.01a8 LEN: 0x0054 VLD: 0x01 CON_UID: 1073777561 SCN: 0x00000000027ca30c SUBSCN: 98 05/15/2025 08:40:22 CHANGE #1 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:18.3 ENC:0 FLG:0x0000 Reuse redo entry Range reuse: pdb = 3 tsn=5 base=0x030000a8 nblks=8 --//dba 12,170 = file#,block# dba(10): 50331818 = file#,block# dba(16): 0x30000aa,段头 --//0x030000a8 = set dba 12,168 = alter system dump datafile 12 block 168 = 50331816 --//回收使用空间，但是保留1个extent。 $ grep 30000a /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3627_0001.trc CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027c9cb3 SEQ:1 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:1 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:2 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:3 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:4 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:5 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:6 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:7 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:8 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:9 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:10 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:11 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:12 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:13 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:14 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:15 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:16 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:17 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:18 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:19 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:20 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:21 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:22 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:23 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:24 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:25 OP:14.4 ENC:0 RBL:0 FLG:0x0000 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027ca30c SEQ:26 OP:14.2 ENC:0 RBL:0 FLG:0x0000 Range reuse: pdb = 3 tsn=5 base=0x030000a8 nblks=8 --//0x030000a8 = set dba 12,168 = alter system dump datafile 12 block 168 = 50331816 --//节选其中第1个： REDO RECORD - Thread:1 RBA: 0x0000d1.000000ee.0170 LEN: 0x00a0 VLD: 0x01 CON_UID: 1073777561 SCN: 0x00000000027ca30c SUBSCN: 2 05/15/2025 08:40:22 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:12 DBA:0x030000aa OBJ:129569 SCN:0x00000000027c9cb3 SEQ:1 OP:14.4 ENC:0 RBL:0 FLG:0x0000 kteop redo - redo operation on extent map DELETE: entry:26 shift back: dba:0x0 len:0 SETSTAT: exts:26 blks:1408 lastmap:0x0 mapcnt:0 CHANGE #2 CON_ID:3 TYP:0 CLS:13 AFN:18 DBA:0x04800002 OBJ:4294967295 SCN:0x00000000027ca309 SEQ:1 OP:22.2 ENC:0 RBL:0 FLG:0x0000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ktfbhredo - File Space Header Redo: Header Opcode: Save: Free Extent: Begin: 768, Length: 128, Instance: 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//对比truncate的情况，两者完全不同。 REDO RECORD - Thread:1 RBA: 0x0000d1.000000ef.0020 LEN: 0x0048 VLD: 0x01 CON_UID: 1073777561 SCN: 0x00000000027ca30c SUBSCN: 3 05/15/2025 08:40:22 CHANGE #1 CON_ID:3 TYP:0 CLS:12 AFN:18 DBA:0x04800003 OBJ:4294967295 SCN:0x00000000027ca309 SEQ:1 OP:22.5 ENC:0 RBL:0 FLG:0x0000 ktfbbredo - File BitMap Block Redo: Free Bits: Begin: 80, Length: 16 REDO RECORD - Thread:1 RBA: 0x0000d1.000000ef.0068 LEN: 0x0040 VLD: 0x01 CON_UID: 1073777561 SCN: 0x00000000027ca30c SUBSCN: 4 05/15/2025 08:40:22 CHANGE #1 CON_ID:3 TYP:0 CLS:13 AFN:18 DBA:0x04800002 OBJ:4294967295 SCN:0x00000000027ca30c SEQ:1 OP:22.2 ENC:0 RBL:0 FLG:0x0000 ktfbhredo - File Space Header Redo: Header Opcode: Save: No Pending Op REDO RECORD - Thread:1 RBA: 0x0000d1.000000ef.00a8 LEN: 0x0054 VLD: 0x01 CON_UID: 1073777561 SCN: 0x00000000027ca30c SUBSCN: 5 05/15/2025 08:40:22 CHANGE #1 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:18.3 ENC:0 FLG:0x0000 Reuse redo entry Local range reuse: pdb = 3 tsn=5 base=0x03000300 nblks=2 --//只有输出下划线的信息才知道原来这些extents的相关信息。涉及文件号，开始块号，长度等信息。 $ awk '/^CHANGE .*DBA:0x030000aa/,/^ $/{print $0}' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3627_0001.trc >| zzz1.txt --//每个段落分割出现1个空行，实际上包含1个空格的空行,在vim下执行:set list可以发现，使用/^ $/作为结束。 $ awk '/^CHANGE #2 /{print substr($6,5)};/^Begin: /{print $2 $4}' zzz1.txt | paste -d',' - - | awk -F"," '{print $1,$2,$2+$3-1}' | tac > zzz2.txt $ head -4 zzz2.txt 12 176 183 12 184 191 12 192 199 12 200 207 --//注意追加1行12 168 175 SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name,mtime s '' '' "versions_operation='D' and name='T8'" VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OBJ# DATAOBJ# NAME MTIME -------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ---------- ---------- ---- ------------------- 2025-05-15 08:40:21. 41722615 03001A00DC1F0000 D AAAAASAABAAAI6CAAC 129569 129569 T8 2025-05-15 08:33:16 --//实际上看转储就知道doid号 129569。 $ cat zzz2.txt | xargs -IQ echo ./finddoid.sh Q 129569| bash scan range file# = 12 begin_block =168 end_block = 175 , doid = 129569 max_row = 66 scan result in 129569_scan.txt , scan max rows num result in 129569_max_rowsnum.txt scan range file# = 12 begin_block =176 end_block = 183 , doid = 129569 max_row = 63 scan result in 129569_scan.txt , scan max rows num result in 129569_max_rowsnum.txt ... scan range file# = 18 begin_block =768 end_block = 895 , doid = 129569 max_row = 60 scan result in 129569_scan.txt , scan max rows num result in 129569_max_rowsnum.txt --//使用t8_bak保存rowid扫描的数据记录。 SCOTT@book01p> create table t8_bak tablespace tsp_audit as select * from all_objects where 0=1; Table created. $ paste -d"," 129569_scan.txt 129569_max_rowsnum.txt | xargs -IQ echo " insert into scanblock values ( Q ); " >| zzz3.txt SCOTT@book01p> create table scanblock ( file_id number,block_id number,rowsnum number ) tablespace TSP_AUDIT; Table created. --//执行zzz3.txt脚本，不要忘记提交。 SCOTT@book01p> create table t8 SEGMENT CREATION IMMEDIATE as select * from all_objects where 0=1; Table created. SCOTT@book01p> @ o2z t8 ============================== O_OWNER : SCOTT O_OBJECT_NAME : T8 O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 130057 D_OID : 130057 CREATED : 2025-05-15 10:02:55 LAST_DDL_TIME : 2025-05-15 10:02:55 PL/SQL procedure successfully completed. SCOTT@book01p> @ seg2z t8 '' ============================== SEG_MB : 0 SEG_OWNER : SCOTT SEG_SEGMENT_NAME : T8 SEG_PARTITION_NAME : SEG_SEGMENT_TYPE : TABLE SEG_TABLESPACE_NAME : USERS BLOCKS : 8 HDRFIL : 12 HDRBLK : 170 PL/SQL procedure successfully completed. --//理论讲应该建立在另外的数据文件,避免覆盖，测试环境相对可控，问题不大，还是使用原来的段头,数据段没有被破坏。 SYS@book01p> update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=130057) set DATAOBJ#=129569; 1 row updated. SYS@book01p> commit ; Commit complete. SYS@book01p> alter system flush shared_pool; System altered. SYS@book01p> set timing on SYS@book01p> @ txt/truncTz.txt SCOTT T8 SCOTT T8_BAK '' PL/SQL procedure successfully completed. Elapsed: 00:00:04.02 SYS@book01p> set timing off SCOTT@book01p> select count(*) from t8_bak; COUNT(*) ------------ 69896 --//恢复数量完成正确。

编辑推荐：

返回列表

[20250515]drop table相关数据段的确定与恢复.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推