[20250505]drop table的恢复.txt
--//尝试drop table的恢复，前提没有备份的情况不得已为之的方法，前面测试提到drop table时清除段头里面的Extent Map以及
--//Auxillary Map的信息。这样通过建立新表的方式也只能像truncate 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 t1 as select * from all_objects;
Table created.
SCOTT@book01p> create table t1_bak as select * from all_objects;
Table created.
--//t1_bak注意目的为了检验drop table的情况。
SCOTT@book01p> @ o2 t1
SCOTT@book01p> @ pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : T1
O_OBJECT_TYPE : TABLE
SEG_PART_NAME :
O_STATUS : VALID
OID : 125494
D_OID : 125494
CREATED : 2025-05-05 09:05:53
LAST_DDL_TIME : 2025-05-05 09:05:53
PL/SQL procedure successfully completed.
SCOTT@book01p> @ seg2 t1
SCOTT@book01p> @ pr
==============================
SEG_MB : 12
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : T1
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 1536
HDRFIL : 12
HDRBLK : 170
PL/SQL procedure successfully completed.
3.恢复测试：
SCOTT@book01p> drop table t1 purge ;
Table dropped.
SCOTT@book01p> create table emp_bak as select * from emp ;
Table created.
SCOTT@book01p> @ seg2 emp_bak
SCOTT@book01p> @ pr
==============================
SEG_MB : 0
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : EMP_BAK
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 8
HDRFIL : 12
HDRBLK : 170
PL/SQL procedure successfully completed.
--//原来的段头覆盖另外有1个数据块也被破坏。
4.确定drop table的段头。
SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' versions_operation='D'
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V OBJ# DATAOBJ# NAME
-------------------- -------------------- ----------------- --------------- ---------------- - ---------- ---------- ------
2025-05-05 09:01:32. 36801365 07001F00621E0000 D 125490 125492 BAK_T
2025-05-05 09:04:47. 36803040 01000A00341E0000 D 125491 125491 T_BAK
2025-05-05 09:09:44. 36803972 08000100C01E0000 D 125494 125494 T1
--//前面2个我测试前删除的表，最后1个OBJ#=125494就是需要恢复的表，实际上你可以通过logminer确定drop table原来的数据段号。
SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' OBJ#=125494
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V OBJ# DATAOBJ# NAME
-------------------- -------------------- ----------------- --------------- ---------------- - ---------- ---------- ------
2025-05-05 09:05:53. 2025-05-05 09:09:44. 36803312 36803972 0A000400701E0000 I 125494 125494 T1
2025-05-05 09:09:44. 36803972 08000100C01E0000 D 125494 125494 T1
SYS@book01p> @ as_of tab$ OBJ#,DATAOBJ#,ts#,file#,block# s 36803312 obj#=125494
ROWID OBJ# DATAOBJ# TS# FILE# BLOCK#
------------------ ---------- ---------- ---------- ---------- ----------
AAAAACAABAAAH0yAAA 125494 125494 5 12 170
SYS@book01p> @as_of seg$ * s 36803312 HWMINCR=125494
SYS@book01p> @ pr
==============================
ROWID : AAAAAIAABAAAIygAAZ
FILE# : 12
BLOCK# : 170
TYPE# : 5
TS# : 5
BLOCKS : 1536
EXTENTS : 27
INIEXTS : 8
MINEXTS : 1
MAXEXTS : 2147483645
EXTSIZE : 128
EXTPCT : 0
USER# : 109
LISTS : 0
GROUPS : 0
BITMAPRANGES : 2147483645
CACHEHINT : 0
SCANHINT : 0
HWMINCR : 125494
SPARE1 : 4325633
SPARE2 :
PL/SQL procedure successfully completed.
--//获取drop 前的信息。
--//建立与原来数据结构一样的表，修改段号，在扫描数据块应该可以恢复相关信息。
SCOTT@book01p> create table t1_drop SEGMENT CREATION IMMEDIATE as select * from all_objects where 0=1;
Table created.
--//建立表空间TSP_AUDIT略。
--//ALTER USER SCOTT QUOTA UNLIMITED ON TSP_AUDIT;
SCOTT@book01p> create table bak_t1 tablespace TSP_AUDIT as select * from scott.t1_drop where 0=1;
Table created.
--//建立新表在原来表空间,只要没有记录插入,不会覆盖(错误!!看后面的测试),另外建立的表要加入SEGMENT CREATION IMMEDIATE,不然
--//oracle认为是空表,无法通过rowid方式读取。扫描获得信息插入的表bak_t1放在另外的表空间,避免覆盖原来的数据文件信息。
SCOTT@book01p> @ o2 t1_drop
SCOTT@book01p> @pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : T1_DROP
O_OBJECT_TYPE : TABLE
SEG_PART_NAME :
O_STATUS : VALID
OID : 125500
D_OID : 125500
CREATED : 2025-05-05 09:19:58
LAST_DDL_TIME : 2025-05-05 09:19:58
PL/SQL procedure successfully completed.
--//修改t1_drop的数据段号等于原来t1表的数据段号。
SYS@book01p> update (select OBJ#, DATAOBJ# , OWNER#, NAME from obj$ where obj#=125500) set DATAOBJ#=125494;
1 row updated.
SYS@book01p> commit ;
Commit complete.
SYS@book01p> alter system flush shared_pool;
System altered.
SYS@book01p> alter system flush shared_pool;
System altered.
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,125494,12,175,0);
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS I_JIJOIN$
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,125494,12,176,0);
select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,125494,12,176,0)
*
ERROR at line 1:
ORA-01410: invalid ROWID
--//嗯，1个OK，另外1个报错。
SCOTT@book01p> @ seg2 t1_drop
SCOTT@book01p> @pr
==============================
SEG_MB : 0
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : T1_DROP
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 8
HDRFIL : 12
HDRBLK : 178
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from dba_extents where segment_name='T1_DROP';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T1_DROP TABLE USERS 0 12 176 65536 8 12
--//噢，我忽略问题，就是新建立的表要分配新段，建立新的段头，等于覆盖其中3个数据块。先不管这些，继续恢复.
SYS@book01p> set timing on
SYS@book01p> @ txt/truncT.txt SCOTT T1_DROP SCOTT BAK_T1
PL/SQL procedure successfully completed.
Elapsed: 00:01:03.00
SYS@book01p> set timing off
SCOTT@book01p> select count(*) from bak_t1;
COUNT(*)
----------
69389
COTT@book01p> select count(*) from t1_bak;
COUNT(*)
----------
69880
SCOTT@book01p> select avg(A) from (select count(*) a ,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) from t1_bak group by DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid));
AVG(A)
----------
47.412483
--//69880-69389=491,丢失的了491行。平均每个数据块有47条记录(最小33，最大63),491/47=10.44,大约10-11个数据块没有恢复。
--//建立的表emp_bak占了64K，前面段头3块不算，占用1个数据块，4块含有原来的数据。
--//而建立的新表T1_DROP虽然没有数据，破坏了3个原来的数据块,。剩下5块含有原来的数据。
--//按照以上推断，原始脚本少扫描这16块，9个数据块,其中4个数据块已经破坏。3个其他数据块没有数据。
--//因为里面的扫描范围是：
(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)
--//最后1个条件extent_id > 0的情况少考虑drop table建立新表的情况。
SCOTT@book01p> select * from bak_t1 minus select * from t1_bak ;
no rows selected
--//说明恢复的数据问题。
--//还原：
SYS@book01p> update (select OBJ#, DATAOBJ# , OWNER#, NAME from obj$ where obj#=125500) set DATAOBJ#=125500;
1 row updated.
SYS@book01p> commit ;
Commit complete.
--//如何解决在建表覆盖的问题.可以在原表空间增加一个数据文件：
ALTER TABLESPACE USERS
ADD DATAFILE '/u01/oradata/BOOK/book01p/users02.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 4M
MAXSIZE UNLIMITED;
create table t1_drop as select * from all_objects where 0=1;
alter table t1_drop allocate extent ( datafile '/u01/oradata/BOOK/book01p/users02.dbf' size 64K);
--//后面的测试发现这种方法行不通。
5.补充测试看看：
SCOTT@book01p> drop table t1_drop purge ;
Table dropped.
ALTER TABLESPACE USERS
ADD DATAFILE '/u01/oradata/BOOK/book01p/users02.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 4M
MAXSIZE UNLIMITED;
SCOTT@book01p> create table t1_drop as select * from all_objects where 0=1;
Table created.
SCOTT@book01p> alter table t1_drop allocate extent ( datafile '/u01/oradata/BOOK/book01p/users02.dbf' size 8K);
Table altered.
SCOTT@book01p> @ o2 t1_drop
SCOTT@book01p> @ pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : T1_DROP
O_OBJECT_TYPE : TABLE
SEG_PART_NAME :
O_STATUS : VALID
OID : 125503
D_OID : 125503
CREATED : 2025-05-05 09:57:24
LAST_DDL_TIME : 2025-05-05 09:57:39
PL/SQL procedure successfully completed.
COTT@book01p> select * from dba_extents where segment_name='T1_DROP';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T1_DROP TABLE USERS 0 12 176 65536 8 12
SCOTT T1_DROP TABLE USERS 1 14 128 65536 8 14
--//不行。如何解决这个问题,另外写一篇blog说明。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3083499/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
