[20250506]drop table的恢复2.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 : 126494
D_OID : 126494
CREATED : 2025-05-06 14:48:10
LAST_DDL_TIME : 2025-05-06 14:48:10
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 : 178
PL/SQL procedure successfully completed.
3.恢复测试：
SCOTT@book01p> drop table t1 purge ;
Table dropped.
SCOTT@book01p> create table emp_xxx as select * from emp ;
Table created.
SCOTT@book01p> @ seg2 emp_xxx
SCOTT@book01p> @ pr
==============================
SEG_MB : 0
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : EMP_XXX
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 8
HDRFIL : 12
HDRBLK : 178
PL/SQL procedure successfully completed.
–//原来的段头覆盖另外有1个数据块也被破坏,基本破坏了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-06 14:49:09. 37973932 07000D00BF1E0000 D 126494 126494 T1
–//OBJ#=126494就是需要恢复的表，实际上你可以通过logminer确定drop table原来的数据段号。
–//昏，第1次测试OBJ#=125494，非常容易混淆。
SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s ” ” versions_operation=’I’
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V OBJ# DATAOBJ# NAME
——————– ——————– —————– ————— —————- – ———- ———- ——-
2025-05-06 14:48:11. 2025-05-06 14:49:09. 37973692 37973932 07000B00B81E0000 I 126494 126494 T1
2025-05-06 14:48:17. 37973818 02002000C01E0000 I 126495 126495 T1_BAK
2025-05-06 14:49:18. 37973982 060017006F1E0000 I 126496 126496 EMP_XXX
SYS@book01p> @ as_of tab$ OBJ#,DATAOBJ#,ts#,file#,block# s 37973692 obj#=126494
ROWID OBJ# DATAOBJ# TS# FILE# BLOCK#
—————— ———- ———- ———- ———- ———-
AAAAACAABAAAH1AAAA 126494 126494 5 12 178
SYS@book01p> @as_of seg$ * s 37973692 HWMINCR=126494
SYS@book01p> @ pr
==============================
ROWID : AAAAAIAABAAAJClAAC
FILE# : 12
BLOCK# : 178
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 : 126494
SPARE1 : 4325633
SPARE2 :
PL/SQL procedure successfully completed.
–//获取drop 前的信息。
–//建立与原来数据结构一样的表，修改段号，在扫描数据块应该可以恢复相关信息。
5.注意覆盖问题：
–//如何解决在建表覆盖的问题.可以在原表空间增加一个数据文件：
ALTER TABLESPACE USERS
ADD DATAFILE ‘/u01/oradata/BOOK/book01p/users02.dbf’
SIZE 100M
AUTOEXTEND ON
NEXT 4M
MAXSIZE UNLIMITED;
SCOTT@book01p> alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ offline ;
alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SYS@book01p> alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ offline drop;
Database altered.
–//注：因为我的测试环境没有打开归档，导致直接offline报错，加入drop没有问题，注意并非真正意思删除。
SYS@book01p> recover datafile 12;
Media recovery complete.
–//顺手先recover datafile 12;以后可以直接online。
SCOTT@book01p> create table t1_drop SEGMENT CREATION IMMEDIATE as select * from all_objects where 0=1;
Table created.
SCOTT@book01p> select * from dba_extents where segment_name=’T1_DROP’;
SCOTT@book01p> @ pr
==============================
OWNER : SCOTT
SEGMENT_NAME : T1_DROP
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : USERS
EXTENT_ID : 0
FILE_ID : 16
BLOCK_ID : 128
BYTES : 65536
BLOCKS : 8
RELATIVE_FNO : 16
PL/SQL procedure successfully completed.
–//没有使用数据文件/u01/oradata/BOOK/book01p/users01.dbf。
–//建立表空间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 : 126498
D_OID : 126498
CREATED : 2025-05-06 15:02:48
LAST_DDL_TIME : 2025-05-06 15:02:48
PL/SQL procedure successfully completed.
–//修改t1_drop的数据段号等于原来t1表的数据段号。
SYS@book01p> update (select OBJ#, DATAOBJ# , OWNER#, NAME from obj$ where obj#=126498) set DATAOBJ#=126494;
1 row updated.
SYS@book01p> commit ;
Commit complete.
SYS@book01p> alter system flush shared_pool;
System altered.
SYS@book01p> alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ online ;
Database altered.
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,180,0);
OWNER OBJECT_NAME
—————————— ——————————
SYS HIST_HEAD$
SYS@book01p> set timing on
SYS@book01p> @ txt/truncT.txt SCOTT T1_DROP SCOTT BAK_T1
PL/SQL procedure successfully completed.
Elapsed: 00:01:07.88
SYS@book01p> set timing off
SCOTT@book01p> select count(*) from bak_t1;
COUNT(*)
———-
69571
SCOTT@book01p> select count(*) from t1_bak;
COUNT(*)
———-
69886
–//69886-69571 = 315，丢失的了315行。
SCOTT@book01p> select * from bak_t1 minus select * from t1_bak ;
no rows selected
–//说明恢复的数据问题。
6.疑问：
SCOTT@book01p> select rowid from t1_bak where rownum=1;
ROWID
——————
AAAe4fAAMAAAGA7AAA
SCOTT@book01p> select count(*) from t1_bak where rowid between ‘AAAe4fAAMAAAGA7AAA’ and ‘AAAe4fAAMAAAGA7BBB’;
COUNT(*)
———-
66
–//按照道理仅仅破坏1个数据块，为什么丢失了316/66 = 4.78,将近5块呢，实际上建立表exp_xxx时，建立1个extents占用8块。
SCOTT@book01p> select * from dba_extents where segment_name=’EMP_XXX’;
SCOTT@book01p> @ pr
==============================
OWNER : SCOTT
SEGMENT_NAME : EMP_XXX
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : USERS
EXTENT_ID : 0
FILE_ID : 12
BLOCK_ID : 176
BYTES : 65536
BLOCKS : 8
RELATIVE_FNO : 12
PL/SQL procedure successfully completed.
–//176,177,178没有数据。179已经被emp_xxx占用,剩下180,181,182,183块还是有数据，看看能否恢复。
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,180,0);
OWNER OBJECT_NAME
—– ———–
SYS HIST_HEAD$
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,181,0);
OWNER OBJECT_NAME
—– ———–
SYS MIGRATE$
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,182,0);
OWNER OBJECT_NAME
—– ———–
SYS CDB_TS$
SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,183,0);
OWNER OBJECT_NAME
—– ———–
SYS I_JIJOIN$
–//而使用truncT.sql脚本无法扫描这些数据块。通过我前面的bbed扫描确定数据段号的方式就没有问题。
7.改用bbed扫描数据文件看看：
–//$ cd bbed ;
–//$ rm log.bbd
–//确定最大块号
SYS@book01p> select * from dba_DATA_FILES where file_id=12
2 @ 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.
–//确定那些数据块的段号等于126494。
$ seq 128 1 32640 | xargs -IQ echo p /d dba 12,Q ktbbhsid.ktbbhsg1 | rlbbed > /dev/null
$ grep -B1 ” 126494$” log.bbd | grep ktbbhsid.ktbbhsg1 | head -7
BBED> p /d dba 12,180 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,181 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,182 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,183 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,184 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,185 ktbbhsid.ktbbhsg1
BBED> p /d dba 12,186 ktbbhsid.ktbbhsg1
–//将需要扫描的数据块保存在文本scan.txt文件中。
$ grep -B1 126494$ log.bbd | grep ktbbhsid.ktbbhsg1 >| scan.txt
SYS@book01p> create table scott.scanblock ( file_id number,block_id number ) tablespace TSP_AUDIT;
Table created.
–//注意建立的新表一定不能使用原来的表空间，避免覆盖。
$ awk ‘{print $5}’ scan.txt | sed ‘s/^/insert into scanblock values (/;s/$/);/’ >| scan1.txt
$ head -2 scan1.txt ; tail -2 scan1.txt
insert into scanblock values (12,180);
insert into scanblock values (12,181);
insert into scanblock values (12,24630);
insert into scanblock values (12,24631);
–//执行@scan1.txt.注意提交。
–//SCOTT@book01p> truncate table BAK_T1;
–//Table truncated.
SYS@book01p> set timing on
SYS@book01p> @ truncT.txt SCOTT T1_DROP SCOTT BAK_T1
PL/SQL procedure successfully completed.
Elapsed: 00:00:56.70
SYS@book01p> set timing off
SCOTT@book01p> select count(*) from bak_T1;
COUNT(*)
———-
69819
SCOTT@book01p> select count(*) from T1_BAK;
COUNT(*)
———-
69886
–//69886-69819 = 67，这样恢复丢失67条。(注：实际丢失66条)。
–//还是存在小小疑问。
SCOTT@book01p> select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) b from t1_bak where rownum<=1;
ROWID B
—————— ———-
AAAe4fAAMAAAGA7AAA 24635
SCOTT@book01p> select count(*) from t1_bak where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=24635;
COUNT(*)
———-
66
SCOTT@book01p> select * from ( select * from (select count(*) a ,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) b from t1_bak group by DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) )) where a=67 ;
no rows selected
–//保存的备份表也没有67条记录的数据块。噢回看才明白问题在那里，t1_bak建立如下：
SCOTT@book01p> create table t1_bak as select * from all_objects;
Table created.
–//这样多1条记录。应该写成：
create table t1_bak as select * from t1;
–//测试疏忽了。
SCOTT@book01p> select count(*) from t1_bak where object_name=’T1′;
COUNT(*)
———-
1
SCOTT@book01p> select count(*) from bak_t1 where object_name=’T1′;
COUNT(*)
———-
0
SCOTT@book01p> select * from bak_t1 minus select * from t1_bak;
no rows selected
–//说明恢复的数据没有任何问题。
–//理论讲确定扫描那些数据块的方法以及原始方法执行实际差别不大，但是前者丢失数据要少一些。
8.收尾还原：
SYS@book01p> update (select OBJ#, DATAOBJ# , OWNER#, NAME from obj$ where obj#=126498) set DATAOBJ#=126498;
1 row updated.
SYS@book01p> commit ;
Commit complete.
SYS@book01p> alter system flush shared_pool;
System altered.
9.执行脚本另外写1个文章贴出。
来源链接：https://www.cnblogs.com/lfree/p/18865071
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容