0

Oracle Drop Table Purge后小表快速恢复

1. 内容介绍

Oracle数据库Drop Table Purge操作后,如果drop table 只有一个extent,且drop时间小于undo_retention,使用本方法可实现快速恢复。

2.环境准备

create user hsql identified by hsql; grant connect,resource,dba to hsql; drop tablespace hsql including contents and datafiles; create tablespace hsql datafile '/data2/enmo/hsql01.dbf' size 10M autoextend off; drop table hsql.drop_1 purge; create table hsql.drop_1(c_char1 char(10),c_char2 char(10)) tablespace hsql; begin for i in 1 .. 1000 loop insert into hsql.drop_1 values(i,'orastar'); end loop; commit; end; / alter system flush shared_pool; alter system flush shared_pool; alter system flush buffer_cache; alter system flush buffer_cache; select count(1) from hsql.drop_1;

3. 检查数据字典信息

set linesize 200 pagesize 200 col owner for a10 col segment_name for a10 select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='DROP_1'; OWNER SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE ---------- ---------- ----------- ------------ ------------------ HSQL DROP_1 5 130 TABLE set linesize 200 pagesize 9999 col owner for a10 col object_name for a20 select owner,object_name,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='DROP_1'; OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID ---------- -------------------- ---------- -------------- HSQL DROP_1 13746 13746 set linesize 200 pagesize 999 col OWNER for a10 col segment_name for a60 select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name ='DROP_1' order by extent_id; OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- --------------- ---------- ---------- ---------- ---------- HSQL DROP_1 0 5 128 8 SQL>

4. 记录变更时间 <-- 生产环境根据业务或logmgr确定该时间

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select sysdate from dual; SQL> SYSDATE ------------------- 2020-07-13 23:00:42 SQL>

5. 执行Drop操作

drop table hsql.drop_1 purge; alter system flush shared_pool; alter system flush shared_pool; alter system flush buffer_cache; alter system flush buffer_cache; select count(1) from hsql.drop_1; SQL> select count(1) from hsql.drop_1 * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> select count(1) from seg$ where HWMINCR=13746; select count(1) from obj$ where DATAOBJ#=13746; select count(1) from tab$ where DATAOBJ#=13746; select count(1) from col$ where OBJ#=13746; COUNT(1) ---------- 0 1 row selected. SQL> COUNT(1) ---------- 0 1 row selected. SQL> COUNT(1) ---------- 0 1 row selected. SQL> COUNT(1) ---------- 0 1 row selected. SQL>

6. 恢复基表

select count(1) from seg$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13746; select count(1) from obj$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746; select count(1) from tab$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746; select count(1) from col$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where OBJ#=13746; --select * from seg$ where HWMINCR=13805; insert into seg$ select * from seg$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13746; insert into obj$ select * from obj$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746; insert into tab$ select * from tab$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746; insert into col$ select OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET, NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#, SCALE, NULL$, DEFLENGTH, '', INTCOL#, PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2, SPARE3, SPARE4, SPARE5, SPARE6 from col$ AS OF timestamp to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where OBJ#=13746; select count(1) from seg$ where HWMINCR=13746; select count(1) from obj$ where DATAOBJ#=13746; select count(1) from tab$ where DATAOBJ#=13746; select count(1) from col$ where OBJ#=13746; SQL> select count(1) from seg$ where HWMINCR=13746; select count(1) from obj$ where DATAOBJ#=13746; select count(1) from tab$ where DATAOBJ#=13746; select count(1) from col$ where OBJ#=13746; COUNT(1) ---------- 1 1 row selected. SQL> COUNT(1) ---------- 1 1 row selected. SQL> COUNT(1) ---------- 1 1 row selected. SQL> COUNT(1) ---------- 2 1 row selected. SQL> --delete from seg$ where HWMINCR=13746; --delete from obj$ where DATAOBJ#=13746; --delete from tab$ where DATAOBJ#=13746; --delete from col$ where OBJ#=13746;

7. 检查恢复结果

SQL> alter system flush shared_pool; alter system flush shared_pool; alter system flush buffer_cache; alter system flush buffer_cache; select count(1) from hsql.drop_1; System altered. SQL> System altered. SQL> System altered. SQL> System altered. SQL> COUNT(1) ---------- 1000 1 row selected. SQL>
「喜欢文章,快来给作者赞赏墨值吧」
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

关注
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部