[20250515]drop table相关数据段的确定与恢复.txt
[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
--//恢复数量完成正确。
--//这几天一直在做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
--//恢复数量完成正确。
浙公网安备 33010602011771号