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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
