Oracle Drop Table Purge后plsql恢复方法

1. 内容介绍

Oracle数据库Drop Table Purge操作后,本文使用pl/sql dbms_rowid.ROWID_CREATE 方法进行业务数据恢复。适用场景大于一个ext且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 .. 100000 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 13755 13755 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 HSQL DROP_1 1 5 136 8 HSQL DROP_1 2 5 144 8 HSQL DROP_1 3 5 152 8 HSQL DROP_1 4 5 160 8 HSQL DROP_1 5 5 168 8 HSQL DROP_1 6 5 176 8 HSQL DROP_1 7 5 184 8 HSQL DROP_1 8 5 192 8 HSQL DROP_1 9 5 200 8 HSQL DROP_1 10 5 208 8 HSQL DROP_1 11 5 216 8 HSQL DROP_1 12 5 224 8 HSQL DROP_1 13 5 232 8 HSQL DROP_1 14 5 240 8 HSQL DROP_1 15 5 248 8 HSQL DROP_1 16 5 256 128 HSQL DROP_1 17 5 384 128 HSQL DROP_1 18 5 512 128 19 rows selected. SQL>

4. 检查REDO LOGFILE

alter system archive log current; alter system checkpoint; set line 200 col MEMBER for a50 COL IS_RECOVERY_DEST_FILE FOR A30 col ARCHIVED for a10 col file_name for a60 col STATUS for a10 select g.group#,g.thread#,g.SEQUENCE#,g.bytes,g.members,g.ARCHIVED,g.STATUS,member from v$log g,v$logfile f where g.group#=f.group#; SQL> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS MEMBER ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------------- 1 1 10 104857600 1 YES INACTIVE /data2/enmo/redo01a.log 2 1 11 104857600 1 YES INACTIVE /data2/enmo/redo02a.log 3 1 12 104857600 1 NO CURRENT /data2/enmo/redo03a.log 3 rows selected. SQL>

5. 执行Drop操作

drop table hsql.drop_1 purge; alter system archive log current; alter system checkpoint; 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>

6. 使用logmgr检查变更操作

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch1/1_12_1045694780.dbf', OPTIONS => DBMS_LOGMNR.NEW); Step 4 Start LogMiner. EXECUTE DBMS_LOGMNR.START_LOGMNR(- OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + - DBMS_LOGMNR.COMMITTED_DATA_ONLY); Step 5 Query the V$LOGMNR_CONTENTS view. set linesize 200 pagesize 9999 col USR for a10 col sql_redo for a30 col SQL_UNDO for a30 col SEG_OWNER for a10 col SEG_NAME for a10 SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,SEG_OWNER,SEG_NAME,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME='OBJ$' and OPERATION in ('DELETE','INSERT'); SQL> set linesize 200 pagesize 9999 col USR for a10 col sql_redo for a30 col SQL_UNDO for a30 col SEG_OWNER for a10 col SEG_NAME for a10 SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,SEG_OWNER,SEG_NAME,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROMSQL> SQL> SQL> SQL> SQL> SQL> V$LOGMNR_CONTENTS WHERE TABLE_NAME='OBJ$' and OPERATION in ('DELETE','INSERT'); 2 3 T_TIME SEG_OWNER SEG_NAME USR SQL_REDO SQL_UNDO OPERATION ------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ -------------------------------- 2020-07-13 23:31:31 SYS OBJ$ UNKNOWN delete from "SYS"."OBJ$" where insert into "SYS"."OBJ$"("OBJ# DELETE "OBJ#" = '13755' and "DATAOBJ ","DATAOBJ#","OWNER#","NAME"," #" = '13755' and "OWNER#" = '3 NAMESPACE","SUBNAME","TYPE#"," 2' and "NAME" = 'DROP_1' and " CTIME","MTIME","STIME","STATUS NAMESPACE" = '1' and "SUBNAME" ","REMOTEOWNER","LINKNAME","FL IS NULL and "TYPE#" = '2' and AGS","OID$","SPARE1","SPARE2", "CTIME" = TO_DATE('13-JUL-20' "SPARE3","SPARE4","SPARE5","SP , 'DD-MON-RR') and "MTIME" = T ARE6") values ('13755','13755' O_DATE('13-JUL-20', 'DD-MON-RR ,'32','DROP_1','1',NULL,'2',TO ') and "STIME" = TO_DATE('13-J _DATE('13-JUL-20', 'DD-MON-RR' UL-20', 'DD-MON-RR') and "STAT ),TO_DATE('13-JUL-20', 'DD-MON US" = '1' and "REMOTEOWNER" IS -RR'),TO_DATE('13-JUL-20', 'DD NULL and "LINKNAME" IS NULL a -MON-RR'),'1',NULL,NULL,'0',NU nd "FLAGS" = '0' and "OID$" IS LL,'6','1','32',NULL,NULL,NULL NULL and "SPARE1" = '6' and " ); SPARE2" = '1' and "SPARE3" = ' 32' and "SPARE4" IS NULL and " SPARE5" IS NULL and "SPARE6" I S NULL and ROWID = 'AAAAASAABA AAFocABL'; 1 row selected. SQL> Step 6 End the LogMiner session. EXECUTE DBMS_LOGMNR.END_LOGMNR();

7. 创建恢复中间表

SQL> alter system set deferred_segment_creation=false; SQL> select * from dba_free_space where tablespace_name='HSQL'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ HSQL 5 128 9437184 1152 5 1 rows selected. SQL> create table sys.drop_1_new(c_char1 char(10),c_char2 char(10)) tablespace system; create table hsql.drop_1_old(c_char1 char(10),c_char2 char(10)) tablespace hsql; <-- 需与被恢复表同表空间 SQL> alter tablespace hsql read only;

8.修改seg$(dataobj#) 并测试dbms_rowid.ROWID_CREATE 函数

8.1 修改前测试

SQL> select obj#,dataobj# from obj$ where name='DROP_1_OLD'; OBJ# DATAOBJ# ---------- ---------- 13757 13757 1 row selected. SQL> SQL> select count(1) from hsql.DROP_1_OLD where rowid=dbms_rowid.ROWID_CREATE(1,13750,5, 131 ,0); select count(1) from hsql.DROP_1_OLD where rowid=dbms_rowid.ROWID_CREATE(1,13750,5, 131 ,0) * ERROR at line 1: ORA-01410: invalid ROWID SQL>

8.2 修改seg$(dataobj#)

select obj#,dataobj# from obj$ where obj#=13757; update obj$ set DATAOBJ#=13755 where obj#=13757; commit;

8.3 重新查询

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_OLD where rowid=dbms_rowid.ROWID_CREATE(1,13755,5, 131 ,0); SQL> COUNT(1) ---------- 1 SQL>

9. 恢复操作

set serveroutput on DECLARE nrows number; rid rowid; objd number; ROWSPERBLOCK number; BEGIN ROWSPERBLOCK:=1000; nrows:=0; for i in ( select file_id,block_id,blocks from dba_free_space where tablespace_name='HSQL' union all select file_id,block_id,8 blocks from dba_extents where segment_name='DROP_1_OLD' ) loop for fblkno in i.block_id..i.block_id+i.blocks-1 loop for fblkrow in 1..ROWSPERBLOCK loop begin rid := dbms_rowid.ROWID_CREATE(1,13755,i.file_id, fblkno,fblkrow-1); insert into sys.drop_1_new select * from hsql.DROP_1_OLD A where rowid = rid; if sql%rowcount = 1 then nrows:=nrows+1; end if; if (mod(nrows,1000)=0) then commit; end if; exception when others then null; end; end loop; end loop; end loop; COMMIT; dbms_output.put_line('Total rows: '||to_char(nrows)); END; / Total rows: 100000 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL>

10. 检查恢复结果

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

评论