oracle drop table purge无备份bbed恢复
1. 内容概述
oracle drop table purge后无有效备份集，信息系统面临业务数据丢失的风险，本文使用bbed工具，
针对该场景进行业务数据恢复。
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 .. 1000000 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 13863 13863
set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
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 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
HSQL DROP_1 19 5 640 128
HSQL DROP_1 20 5 768 128
HSQL DROP_1 21 5 896 128
HSQL DROP_1 22 5 1024 128
HSQL DROP_1 23 5 1152 128
HSQL DROP_1 24 5 1280 128
HSQL DROP_1 25 5 1408 128
HSQL DROP_1 26 5 1536 128
HSQL DROP_1 27 5 1664 128
HSQL DROP_1 28 5 1792 128
HSQL DROP_1 29 5 1920 128
HSQL DROP_1 30 5 2048 128
HSQL DROP_1 31 5 2176 128
HSQL DROP_1 32 5 2304 128
HSQL DROP_1 33 5 2432 128
HSQL DROP_1 34 5 2560 128
HSQL DROP_1 35 5 2688 128
HSQL DROP_1 36 5 2816 128
HSQL DROP_1 37 5 2944 128
HSQL DROP_1 38 5 3072 128
HSQL DROP_1 39 5 3200 128
HSQL DROP_1 40 5 3328 128
HSQL DROP_1 41 5 3456 128
HSQL DROP_1 42 5 3584 128
HSQL DROP_1 43 5 3712 128
HSQL DROP_1 44 5 3840 128
45 rows selected.
SQL>
4. 在线日志查询
生产环境根据业务反馈的drop操作时间，查找archive log信息
SQL> alter system archive log current;
System altered.
SQL> set linesize 200 pagesize 9999
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#;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS MEMBER
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
1 1 43 104857600 1 YES INACTIVE /data2/enmo/redo01a.log
2 1 44 104857600 1 NO CURRENT /data2/enmo/redo02a.log
3 1 42 104857600 1 YES INACTIVE /data2/enmo/redo03a.log
SQL>
5. drop操作
drop table hsql.drop_1 purge;
alter system archive log current;
6. logmgr日志分析
Step 1 Determine which redo log file was most recently archived by the database.
set line 200
col name for a80
SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
Step 2 Ensure that you have a complete list of redo log files.
SELECT NAME FROM V$ARCHIVED_LOG
WHERE SEQUENCE# >= 203 AND SEQUENCE# <= 204
ORDER BY SEQUENCE# ASC;
Step 3 Specify the list of the redo log files of interest.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch1/1_35_1042492403.dbf', OPTIONS => DBMS_LOGMNR.NEW);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_11_1056101196.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_12_1056101196.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_13_1056101196.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
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 3000
set pagesize 2000
col USR for a10
col sql_redo for a50
col SQL_UNDO for a50
COL OPERATION FOR A20
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROM
V$LOGMNR_CONTENTS
WHERE TABLE_NAME in ('DROP_1','SEG$','OBJ$','TAB$','COL$'); --and OPERATION in ('DELETE','INSERT');
Step 6 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch1/1_44_1042492403.dbf', OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY); > >
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 3000
set pagesize 2000
col USR for a10
col sql_redo for a50
col SQL_UNDO for a50
COL OPERATION FOR A20
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROM
V$LOGMNR_CONTENTS
WHERE TABLE_NAME in ('DROP_1','SEG$','OBJ$','TAB$','COL$');
T_TIME USR SQL_REDO SQL_UNDO OPERATION
-------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------
2020-06-21 07:33:08 UNKNOWN drop table hsql.drop_1 purge; DDL
2020-06-21 07:33:08 UNKNOWN Unsupported Unsupported UNSUPPORTED
2020-06-21 07:33:08 UNKNOWN Unsupported Unsupported UNSUPPORTED
2020-06-21 07:33:08 UNKNOWN Unsupported Unsupported UNSUPPORTED
2020-06-21 07:33:08 UNKNOWN delete from "SYS"."OBJ$" where "OBJ#" = '13863' an insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER# DELETE
d "DATAOBJ#" = '13863' and "OWNER#" = '32' and "NA ","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MT
ME" = 'DROP_1' and "NAMESPACE" = '1' and "SUBNAME" IME","STIME","STATUS","REMOTEOWNER","LINKNAME","FL
IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE(' AGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","S
21-JUN-20', 'DD-MON-RR') and "MTIME" = TO_DATE('21 PARE5","SPARE6") values ('13863','13863','32','DRO
-JUN-20', 'DD-MON-RR') and "STIME" = TO_DATE('21-J P_1','1',NULL,'2',TO_DATE('21-JUN-20', 'DD-MON-RR'
UN-20', 'DD-MON-RR') and "STATUS" = '1' and "REMOT ),TO_DATE('21-JUN-20', 'DD-MON-RR'),TO_DATE('21-JU
EOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" N-20', 'DD-MON-RR'),'1',NULL,NULL,'',NULL,'6','1'
= '0' and "OID$" IS NULL and "SPARE1" = '6' and " ,'32',NULL,NULL,NULL);
SPARE2" = '1' and "SPARE3" = '32' and "SPARE4" IS
NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and
ROWID = 'AAAAASAABAAAFodAAB';
2020-06-21 07:33:08 UNKNOWN Unsupported Unsupported UNSUPPORTED
2020-06-21 07:33:08 UNKNOWN Unsupported Unsupported UNSUPPORTED
2020-06-21 07:33:08 UNKNOWN Unsupported Unsupported UNSUPPORTED
2020-06-21 07:33:09 UNKNOWN Unsupported Unsupported UNSUPPORTED
11 rows selected.
SQL>
delete obj#: 13863,DATAOBJ: 13863
timestamp: 2020-06-21 07:33:08
7. 检查表已被删除
select count(1) from hsql.drop_1;
SQL> select count(1) from hsql.drop_1;
select count(1) from hsql.drop_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
8. 闪回查询基表
select * from seg$ where HWMINCR=13863;
select * from obj$ where DATAOBJ#=13863;
select * from tab$ where DATAOBJ#=13863;
select * from col$ where OBJ#=13863;
select * from seg$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13863;
select * from obj$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
select * from tab$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
select * from col$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where OBJ#=13863;
9. 闪回恢复基表
insert into seg$ select * from seg$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13863;
insert into obj$ select * from obj$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
insert into tab$ select * from tab$ AS OF timestamp to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
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-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where OBJ#=13863;
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
TYPE# NOT NULL NUMBER
TS# NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
select count(1) from seg$ where HWMINCR=13863;
select count(1) from obj$ where DATAOBJ#=13863;
select count(1) from tab$ where DATAOBJ#=13863;
select count(1) from col$ where OBJ#=13863;
10. 检查恢复情况
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 a;
*
ERROR at line 1:
ORA-08103: object no longer exists
SQL>
11. 更新mhflag_ech --ub4 mhflag_ech @276 --0x10000000
set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name ='DROP_1' order by extent_id;
查询结果为空，
BBED> set dba 5,130
DBA x01400082 (20971650 5,130)
BBED> d offset 276 count 12
File: /data2/enmo/hsql01.dbf (5)
Block: 130 Offsets: 276 to 287 Dba:0x01400082
------------------------------------------------------------------------
00000012 80004001 08000000
<32 bytes per line>
BBED> m /x 00000010 offset 276
File: /data2/enmo/hsql01.dbf (5)
Block: 130 Offsets: 276 to 287 Dba:0x01400082
------------------------------------------------------------------------
00000010 80004001 08000000
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 130:
current = 0x951b, required = 0x951b
BBED>
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 a;
COUNT(1)
----------
1345
set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
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 5 128 8
12. bbed恢复extent map
SQL> alter system dump datafile 5 block 129;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
Default Trace File
/u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_36383.trc
grep -w 'Inst' /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_36383.trc|awk '{print $1}'|xargs -n 1 ora_rdba|grep command|sed 's/dump command://g'
select value from v$diag_info where name='Default Trace File';
--outfile: /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_36462.trc
grep -i 'Length:' /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_36462.trc|grep -in 'Offset:'|sed 's/://g'|sed 's/0x//g'|awk '{print "select "$1 " as n_num,\47" $2 "\47 as n_dba," $4 " as n_len from dual union all"}'
#### extent map
set linesize 200 pagesize 9999
col dba_em_1 for a20
col dba_em_2 for a20
col n_len for a30
with new_ext as (
select 1 as n_num,'01400080' as n_dba,8 as n_len from dual union all
select 2 as n_num,'01400088' as n_dba,8 as n_len from dual union all
select 3 as n_num,'01400090' as n_dba,8 as n_len from dual union all
select 4 as n_num,'01400098' as n_dba,8 as n_len from dual union all
select 5 as n_num,'014000a0' as n_dba,8 as n_len from dual union all
select 6 as n_num,'014000a8' as n_dba,8 as n_len from dual union all
select 7 as n_num,'014000b0' as n_dba,8 as n_len from dual union all
select 8 as n_num,'014000b8' as n_dba,8 as n_len from dual union all
select 9 as n_num,'014000c0' as n_dba,8 as n_len from dual union all
select 10 as n_num,'014000c8' as n_dba,8 as n_len from dual union all
select 11 as n_num,'014000d0' as n_dba,8 as n_len from dual union all
select 12 as n_num,'014000d8' as n_dba,8 as n_len from dual union all
select 13 as n_num,'014000e0' as n_dba,8 as n_len from dual union all
select 14 as n_num,'014000e8' as n_dba,8 as n_len from dual union all
select 15 as n_num,'014000f0' as n_dba,8 as n_len from dual union all
select 16 as n_num,'014000f8' as n_dba,8 as n_len from dual union all
select 17 as n_num,'01400100' as n_dba,64 as n_len from dual union all
select 18 as n_num,'01400140' as n_dba,64 as n_len from dual union all
select 19 as n_num,'01400180' as n_dba,64 as n_len from dual union all
select 20 as n_num,'014001c0' as n_dba,64 as n_len from dual union all
select 21 as n_num,'01400200' as n_dba,64 as n_len from dual union all
select 22 as n_num,'01400240' as n_dba,64 as n_len from dual union all
select 23 as n_num,'01400280' as n_dba,64 as n_len from dual union all
select 24 as n_num,'014002c0' as n_dba,64 as n_len from dual union all
select 25 as n_num,'01400300' as n_dba,64 as n_len from dual union all
select 26 as n_num,'01400340' as n_dba,64 as n_len from dual union all
select 27 as n_num,'01400380' as n_dba,64 as n_len from dual union all
select 28 as n_num,'014003c0' as n_dba,64 as n_len from dual union all
select 29 as n_num,'01400400' as n_dba,64 as n_len from dual union all
select 30 as n_num,'01400440' as n_dba,64 as n_len from dual union all
select 31 as n_num,'01400480' as n_dba,64 as n_len from dual union all
select 32 as n_num,'014004c0' as n_dba,64 as n_len from dual union all
select 33 as n_num,'01400500' as n_dba,64 as n_len from dual union all
select 34 as n_num,'01400540' as n_dba,64 as n_len from dual union all
select 35 as n_num,'01400580' as n_dba,64 as n_len from dual union all
select 36 as n_num,'014005c0' as n_dba,64 as n_len from dual union all
select 37 as n_num,'01400600' as n_dba,64 as n_len from dual union all
select 38 as n_num,'01400640' as n_dba,64 as n_len from dual union all
select 39 as n_num,'01400680' as n_dba,64 as n_len from dual union all
select 40 as n_num,'014006c0' as n_dba,64 as n_len from dual union all
select 41 as n_num,'01400700' as n_dba,64 as n_len from dual union all
select 42 as n_num,'01400740' as n_dba,64 as n_len from dual union all
select 43 as n_num,'01400780' as n_dba,64 as n_len from dual union all
select 44 as n_num,'014007c0' as n_dba,64 as n_len from dual union all
select 45 as n_num,'01400800' as n_dba,64 as n_len from dual union all
select 46 as n_num,'01400840' as n_dba,64 as n_len from dual union all
select 47 as n_num,'01400880' as n_dba,64 as n_len from dual union all
select 48 as n_num,'014008c0' as n_dba,64 as n_len from dual union all
select 49 as n_num,'01400900' as n_dba,64 as n_len from dual union all
select 50 as n_num,'01400940' as n_dba,64 as n_len from dual union all
select 51 as n_num,'01400980' as n_dba,64 as n_len from dual union all
select 52 as n_num,'014009c0' as n_dba,64 as n_len from dual union all
select 53 as n_num,'01400a00' as n_dba,64 as n_len from dual union all
select 54 as n_num,'01400a40' as n_dba,64 as n_len from dual union all
select 55 as n_num,'01400a80' as n_dba,64 as n_len from dual union all
select 56 as n_num,'01400ac0' as n_dba,64 as n_len from dual union all
select 57 as n_num,'01400b00' as n_dba,64 as n_len from dual union all
select 58 as n_num,'01400b40' as n_dba,64 as n_len from dual union all
select 59 as n_num,'01400b80' as n_dba,64 as n_len from dual union all
select 60 as n_num,'01400bc0' as n_dba,64 as n_len from dual union all
select 61 as n_num,'01400c00' as n_dba,64 as n_len from dual union all
select 62 as n_num,'01400c40' as n_dba,64 as n_len from dual union all
select 63 as n_num,'01400c80' as n_dba,64 as n_len from dual union all
select 64 as n_num,'01400cc0' as n_dba,64 as n_len from dual union all
select 65 as n_num,'01400d00' as n_dba,64 as n_len from dual union all
select 66 as n_num,'01400d40' as n_dba,64 as n_len from dual union all
select 67 as n_num,'01400d80' as n_dba,64 as n_len from dual union all
select 68 as n_num,'01400dc0' as n_dba,64 as n_len from dual union all
select 69 as n_num,'01400e00' as n_dba,64 as n_len from dual union all
select 70 as n_num,'01400e40' as n_dba,64 as n_len from dual union all
select 71 as n_num,'01400e80' as n_dba,64 as n_len from dual union all
select 72 as n_num,'01400ec0' as n_dba,64 as n_len from dual union all
select 73 as n_num,'01400f00' as n_dba,64 as n_len from dual union all
select 74 as n_num,'01400f40' as n_dba,64 as n_len from dual
),
new_ext_temp as(
select
n_num,
dbms_utility.data_block_address_file(TO_NUMBER(n_dba, 'XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER(n_dba,'XXXXXXXX')) block_id,
'm /x '||substr(n_dba,7,2)||substr(n_dba,5,2) ||' offset '||to_char(280+(n_num-1)*8) dba_em_1,
'm /x '||substr(n_dba,3,2)||substr(n_dba,1,2) ||' offset '||to_char(280+(n_num-1)*8+2) dba_em_2,
case when n_num<=16 then 'm /x '||'0800'||' offset '||to_char(280+(n_num-1)*8+4)
when n_num>16 and n_num<=16+63 then 'm /x '||'8000'||' offset '||to_char(280+(n_num-1)*8+4)
when n_num>16+63 and n_num<=16+63+120 then 'm /x '||'0004'||' offset '||to_char(280+(n_num-1)*8+4)
when n_num>16+63+120 then 'm /x '||'0020'||' offset '||to_char(280+(n_num-1)*8+4)
else '0'
end as n_len
from (select rownum n_num,n_dba from new_ext where n_num<=16 or mod(n_num,2)=1)
)
select dba_em_1 from new_ext_temp where n_num>1
union all
select dba_em_2 from new_ext_temp where n_num>1
union all
select n_len from new_ext_temp where n_num>1
;
#### aux extmap
set linesize 200 pagesize 9999
col dba_em_1 for a20
col dba_em_2 for a20
col n_len for a30
with all_ext as (
select 1 as n_num,'01400080' as n_dba,8 as n_len from dual union all
select 2 as n_num,'01400088' as n_dba,8 as n_len from dual union all
select 3 as n_num,'01400090' as n_dba,8 as n_len from dual union all
select 4 as n_num,'01400098' as n_dba,8 as n_len from dual union all
select 5 as n_num,'014000a0' as n_dba,8 as n_len from dual union all
select 6 as n_num,'014000a8' as n_dba,8 as n_len from dual union all
select 7 as n_num,'014000b0' as n_dba,8 as n_len from dual union all
select 8 as n_num,'014000b8' as n_dba,8 as n_len from dual union all
select 9 as n_num,'014000c0' as n_dba,8 as n_len from dual union all
select 10 as n_num,'014000c8' as n_dba,8 as n_len from dual union all
select 11 as n_num,'014000d0' as n_dba,8 as n_len from dual union all
select 12 as n_num,'014000d8' as n_dba,8 as n_len from dual union all
select 13 as n_num,'014000e0' as n_dba,8 as n_len from dual union all
select 14 as n_num,'014000e8' as n_dba,8 as n_len from dual union all
select 15 as n_num,'014000f0' as n_dba,8 as n_len from dual union all
select 16 as n_num,'014000f8' as n_dba,8 as n_len from dual union all
select 17 as n_num,'01400100' as n_dba,64 as n_len from dual union all
select 18 as n_num,'01400140' as n_dba,64 as n_len from dual union all
select 19 as n_num,'01400180' as n_dba,64 as n_len from dual union all
select 20 as n_num,'014001c0' as n_dba,64 as n_len from dual union all
select 21 as n_num,'01400200' as n_dba,64 as n_len from dual union all
select 22 as n_num,'01400240' as n_dba,64 as n_len from dual union all
select 23 as n_num,'01400280' as n_dba,64 as n_len from dual union all
select 24 as n_num,'014002c0' as n_dba,64 as n_len from dual union all
select 25 as n_num,'01400300' as n_dba,64 as n_len from dual union all
select 26 as n_num,'01400340' as n_dba,64 as n_len from dual union all
select 27 as n_num,'01400380' as n_dba,64 as n_len from dual union all
select 28 as n_num,'014003c0' as n_dba,64 as n_len from dual union all
select 29 as n_num,'01400400' as n_dba,64 as n_len from dual union all
select 30 as n_num,'01400440' as n_dba,64 as n_len from dual union all
select 31 as n_num,'01400480' as n_dba,64 as n_len from dual union all
select 32 as n_num,'014004c0' as n_dba,64 as n_len from dual union all
select 33 as n_num,'01400500' as n_dba,64 as n_len from dual union all
select 34 as n_num,'01400540' as n_dba,64 as n_len from dual union all
select 35 as n_num,'01400580' as n_dba,64 as n_len from dual union all
select 36 as n_num,'014005c0' as n_dba,64 as n_len from dual union all
select 37 as n_num,'01400600' as n_dba,64 as n_len from dual union all
select 38 as n_num,'01400640' as n_dba,64 as n_len from dual union all
select 39 as n_num,'01400680' as n_dba,64 as n_len from dual union all
select 40 as n_num,'014006c0' as n_dba,64 as n_len from dual union all
select 41 as n_num,'01400700' as n_dba,64 as n_len from dual union all
select 42 as n_num,'01400740' as n_dba,64 as n_len from dual union all
select 43 as n_num,'01400780' as n_dba,64 as n_len from dual union all
select 44 as n_num,'014007c0' as n_dba,64 as n_len from dual union all
select 45 as n_num,'01400800' as n_dba,64 as n_len from dual union all
select 46 as n_num,'01400840' as n_dba,64 as n_len from dual union all
select 47 as n_num,'01400880' as n_dba,64 as n_len from dual union all
select 48 as n_num,'014008c0' as n_dba,64 as n_len from dual union all
select 49 as n_num,'01400900' as n_dba,64 as n_len from dual union all
select 50 as n_num,'01400940' as n_dba,64 as n_len from dual union all
select 51 as n_num,'01400980' as n_dba,64 as n_len from dual union all
select 52 as n_num,'014009c0' as n_dba,64 as n_len from dual union all
select 53 as n_num,'01400a00' as n_dba,64 as n_len from dual union all
select 54 as n_num,'01400a40' as n_dba,64 as n_len from dual union all
select 55 as n_num,'01400a80' as n_dba,64 as n_len from dual union all
select 56 as n_num,'01400ac0' as n_dba,64 as n_len from dual union all
select 57 as n_num,'01400b00' as n_dba,64 as n_len from dual union all
select 58 as n_num,'01400b40' as n_dba,64 as n_len from dual union all
select 59 as n_num,'01400b80' as n_dba,64 as n_len from dual union all
select 60 as n_num,'01400bc0' as n_dba,64 as n_len from dual union all
select 61 as n_num,'01400c00' as n_dba,64 as n_len from dual union all
select 62 as n_num,'01400c40' as n_dba,64 as n_len from dual union all
select 63 as n_num,'01400c80' as n_dba,64 as n_len from dual union all
select 64 as n_num,'01400cc0' as n_dba,64 as n_len from dual union all
select 65 as n_num,'01400d00' as n_dba,64 as n_len from dual union all
select 66 as n_num,'01400d40' as n_dba,64 as n_len from dual union all
select 67 as n_num,'01400d80' as n_dba,64 as n_len from dual union all
select 68 as n_num,'01400dc0' as n_dba,64 as n_len from dual union all
select 69 as n_num,'01400e00' as n_dba,64 as n_len from dual union all
select 70 as n_num,'01400e40' as n_dba,64 as n_len from dual union all
select 71 as n_num,'01400e80' as n_dba,64 as n_len from dual union all
select 72 as n_num,'01400ec0' as n_dba,64 as n_len from dual union all
select 73 as n_num,'01400f00' as n_dba,64 as n_len from dual union all
select 74 as n_num,'01400f40' as n_dba,64 as n_len from dual
),
aux_temp as(
select rownum n_num,n_dba L1_dba,n_dba data_dba from all_ext where n_num<=16 or mod(n_num,2)=1
),
aux_temp2 as(
select n_num,L1_dba,
lpad(replace(lower(to_char(to_number(data_dba,'XXXXXXXX')+1,'XXXXXXXX')),' ',''),8,'0') d_dba
from aux_temp where n_num<=16 and mod(n_num,2)=1
union all
select n_num,(select L1_dba from aux_temp t where t.n_num=a.n_num-1) L1_dba,L1_dba d_dba
from aux_temp a where n_num<=16 and mod(n_num,2)=
union all
select n_num,L1_dba,lpad(replace(lower(to_char(to_number(data_dba,'XXXXXXXX')+2,'XXXXXXXX')),' ',''),8,'0')
from aux_temp a where n_num>16
order by 1
)
select 'm /x '||substr(L1_dba,7,2)||substr(L1_dba,5,2) ||' offset '||to_char(2736+(n_num-1)*8) from aux_temp2 where n_num>1
union all
select 'm /x '||substr(L1_dba,3,2)||substr(L1_dba,1,2) ||' offset '||to_char(2736+(n_num-1)*8+2) from aux_temp2 where n_num>1
union all
select 'm /x '||substr(d_dba,7,2)||substr(d_dba,5,2) ||' offset '||to_char(2736+(n_num-1)*8+4) from aux_temp2 where n_num>1
union all
select 'm /x '||substr(d_dba,3,2)||substr(d_dba,1,2) ||' offset '||to_char(2736+(n_num-1)*8+6) from aux_temp2 where n_num>1;
13. 检查恢复情况
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 a;
COUNT(1)
----------
1345
set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
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 5 128 8
14. 检查段头块extent map恢复情况
XDUL>bmb
input fno: 5
input blk: 130
struct kcbh, 20 bytes @
ub1 type_kcbh @ 0x23
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01400082
ub4 bas_kcbh @8 0x060a180c
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04
ub2 chkval_kcbh @16 0x9a10
ub2 spare3_kcbh @18 0x0000
struct ech(Extent Control Header) @36
ub4 extents_ech @36 0x00000001
ub4 blocks_ech @40 0x00000008
ub4 offset_ech @44 0x00000a9c
ub4 ext_ech @48 0x00000001
ub4 blk_ech @52 0x00000080
ub4 extsize_ech @56 0x00000080
ub4 hw_ech @60 0x01400f80
ub4 hwmapblk_ech @64 0x00000000
ub4 hwoffset_ech @68 0x00000001
ub4 hwblocksshf_ech @72 0x00000000
ub4 hwblksbelow_ech @76 0x00000ebc
ub4 lhwmext_ech @92 0x0000002b
ub4 lhwmblk_ech @96 0x00000080
ub4 lhwmextsize_ech @100 0x00000080
ub4 lhwm_ech @104 0x01400f00
ub4 lhwmapblk_ech @108 0x00000000
ub4 lhwoffset_ech @112 0x0000002b
ub4 lhwblocksshf_ech @116 0x00000000
ub4 lhwblksbelow_ech @120 0x00000e80
ub4 segtype_ech @208 0x00000001
ub4 blksz_ech @212 0x00002000
ub4 fbsz_echo @216 0x00000000
ub4 l2asoffset_ech @220 0x00001434
ub4 firstbmb3_echo @224 0x00000000
ub4 l2hfinsert_ech @228 0x01400081
ub4 nl2_echo @232 0x00000001
ub4 lastBMB1_echo @236 0x01400f01
ub4 lastBMB2_echo @240 0x01400081
ub4 lastBMB3_echo @244 0x00000000
ub4 mhnext_echo @260 0x00000000
ub4 mhext_echo @264 0x00000001
ub4 mhobj_ech @272 0x00003627
ub4 mhflag_ech @276 0x10000000
struct em(Extent Map) @280
ub4 dba_em[] @280 0x01400080
ub4 len_em[] @284 0x00000008
ub4 dba_em[1] @288 0x01400088
ub4 len_em[1] @292 0x00000008
ub4 dba_em[2] @296 0x01400090
ub4 len_em[2] @300 0x00000008
ub4 dba_em[3] @304 0x01400098
ub4 len_em[3] @308 0x00000008
ub4 dba_em[4] @312 0x014000a0
ub4 len_em[4] @316 0x00000008
ub4 dba_em[5] @320 0x014000a8
ub4 len_em[5] @324 0x00000008
ub4 dba_em[6] @328 0x014000b0
ub4 len_em[6] @332 0x00000008
ub4 dba_em[7] @336 0x014000b8
ub4 len_em[7] @340 0x00000008
ub4 dba_em[8] @344 0x014000c0
ub4 len_em[8] @348 0x00000008
ub4 dba_em[9] @352 0x014000c8
ub4 len_em[9] @356 0x00000008
ub4 dba_em[10] @360 0x014000d0
ub4 len_em[10] @364 0x00000008
ub4 dba_em[11] @368 0x014000d8
ub4 len_em[11] @372 0x00000008
ub4 dba_em[12] @376 0x014000e0
ub4 len_em[12] @380 0x00000008
ub4 dba_em[13] @384 0x014000e8
ub4 len_em[13] @388 0x00000008
ub4 dba_em[14] @392 0x014000f0
ub4 len_em[14] @396 0x00000008
ub4 dba_em[15] @400 0x014000f8
ub4 len_em[15] @404 0x00000008
ub4 dba_em[16] @408 0x01400100
ub4 len_em[16] @412 0x00000080
ub4 dba_em[17] @416 0x01400180
ub4 len_em[17] @420 0x00000080
ub4 dba_em[18] @424 0x01400200
ub4 len_em[18] @428 0x00000080
ub4 dba_em[19] @432 0x01400280
ub4 len_em[19] @436 0x00000080
ub4 dba_em[20] @440 0x01400300
ub4 len_em[20] @444 0x00000080
ub4 dba_em[21] @448 0x01400380
ub4 len_em[21] @452 0x00000080
ub4 dba_em[22] @456 0x01400400
ub4 len_em[22] @460 0x00000080
ub4 dba_em[23] @464 0x01400480
ub4 len_em[23] @468 0x00000080
ub4 dba_em[24] @472 0x01400500
ub4 len_em[24] @476 0x00000080
ub4 dba_em[25] @480 0x01400580
ub4 len_em[25] @484 0x00000080
ub4 dba_em[26] @488 0x01400600
ub4 len_em[26] @492 0x00000080
ub4 dba_em[27] @496 0x01400680
ub4 len_em[27] @500 0x00000080
ub4 dba_em[28] @504 0x01400700
ub4 len_em[28] @508 0x00000080
ub4 dba_em[29] @512 0x01400780
ub4 len_em[29] @516 0x00000080
ub4 dba_em[30] @520 0x01400800
ub4 len_em[30] @524 0x00000080
ub4 dba_em[31] @528 0x01400880
ub4 len_em[31] @532 0x00000080
ub4 dba_em[32] @536 0x01400900
ub4 len_em[32] @540 0x00000080
ub4 dba_em[33] @544 0x01400980
ub4 len_em[33] @548 0x00000080
ub4 dba_em[34] @552 0x01400a00
ub4 len_em[34] @556 0x00000080
ub4 dba_em[35] @560 0x01400a80
ub4 len_em[35] @564 0x00000080
ub4 dba_em[36] @568 0x01400b00
ub4 len_em[36] @572 0x00000080
ub4 dba_em[37] @576 0x01400b80
ub4 len_em[37] @580 0x00000080
ub4 dba_em[38] @584 0x01400c00
ub4 len_em[38] @588 0x00000080
ub4 dba_em[39] @592 0x01400c80
ub4 len_em[39] @596 0x00000080
ub4 dba_em[40] @600 0x01400d00
ub4 len_em[40] @604 0x00000080
ub4 dba_em[41] @608 0x01400d80
ub4 len_em[41] @612 0x00000080
ub4 dba_em[42] @616 0x01400e00
ub4 len_em[42] @620 0x00000080
ub4 dba_em[43] @624 0x01400e80
ub4 len_em[43] @628 0x00000080
ub4 dba_em[44] @632 0x01400f00
ub4 len_em[44] @636 0x00000080
struct am(Auxillary Map) @2736
ub4 fdba_am[] @2736 0x01400080
ub4 datadba_am[] @2740 0x01400083
ub4 fdba_am[1] @2744 0x01400080
ub4 datadba_am[1] @2748 0x01400088
ub4 fdba_am[2] @2752 0x01400090
ub4 datadba_am[2] @2756 0x01400091
ub4 fdba_am[3] @2760 0x01400090
ub4 datadba_am[3] @2764 0x01400098
ub4 fdba_am[4] @2768 0x014000a0
ub4 datadba_am[4] @2772 0x014000a1
ub4 fdba_am[5] @2776 0x014000a0
ub4 datadba_am[5] @2780 0x014000a8
ub4 fdba_am[6] @2784 0x014000b0
ub4 datadba_am[6] @2788 0x014000b1
ub4 fdba_am[7] @2792 0x014000b0
ub4 datadba_am[7] @2796 0x014000b8
ub4 fdba_am[8] @2800 0x014000c0
ub4 datadba_am[8] @2804 0x014000c1
ub4 fdba_am[9] @2808 0x014000c0
ub4 datadba_am[9] @2812 0x014000c8
ub4 fdba_am[10] @2816 0x014000d0
ub4 datadba_am[10] @2820 0x014000d1
ub4 fdba_am[11] @2824 0x014000d0
ub4 datadba_am[11] @2828 0x014000d8
ub4 fdba_am[12] @2832 0x014000e0
ub4 datadba_am[12] @2836 0x014000e1
ub4 fdba_am[13] @2840 0x014000e0
ub4 datadba_am[13] @2844 0x014000e8
ub4 fdba_am[14] @2848 0x014000f0
ub4 datadba_am[14] @2852 0x014000f1
ub4 fdba_am[15] @2856 0x014000f0
ub4 datadba_am[15] @2860 0x014000f8
ub4 fdba_am[16] @2864 0x01400100
ub4 datadba_am[16] @2868 0x01400102
ub4 fdba_am[17] @2872 0x01400180
ub4 datadba_am[17] @2876 0x01400182
ub4 fdba_am[18] @2880 0x01400200
ub4 datadba_am[18] @2884 0x01400202
ub4 fdba_am[19] @2888 0x01400280
ub4 datadba_am[19] @2892 0x01400282
ub4 fdba_am[20] @2896 0x01400300
ub4 datadba_am[20] @2900 0x01400302
ub4 fdba_am[21] @2904 0x01400380
ub4 datadba_am[21] @2908 0x01400382
ub4 fdba_am[22] @2912 0x01400400
ub4 datadba_am[22] @2916 0x01400402
ub4 fdba_am[23] @2920 0x01400480
ub4 datadba_am[23] @2924 0x01400482
ub4 fdba_am[24] @2928 0x01400500
ub4 datadba_am[24] @2932 0x01400502
ub4 fdba_am[25] @2936 0x01400580
ub4 datadba_am[25] @2940 0x01400582
ub4 fdba_am[26] @2944 0x01400600
ub4 datadba_am[26] @2948 0x01400602
ub4 fdba_am[27] @2952 0x01400680
ub4 datadba_am[27] @2956 0x01400682
ub4 fdba_am[28] @2960 0x01400700
ub4 datadba_am[28] @2964 0x01400702
ub4 fdba_am[29] @2968 0x01400780
ub4 datadba_am[29] @2972 0x01400782
ub4 fdba_am[30] @2976 0x01400800
ub4 datadba_am[30] @2980 0x01400802
ub4 fdba_am[31] @2984 0x01400880
ub4 datadba_am[31] @2988 0x01400882
ub4 fdba_am[32] @2992 0x01400900
ub4 datadba_am[32] @2996 0x01400902
ub4 fdba_am[33] @3000 0x01400980
ub4 datadba_am[33] @3004 0x01400982
ub4 fdba_am[34] @3008 0x01400a00
ub4 datadba_am[34] @3012 0x01400a02
ub4 fdba_am[35] @3016 0x01400a80
ub4 datadba_am[35] @3020 0x01400a82
ub4 fdba_am[36] @3024 0x01400b00
ub4 datadba_am[36] @3028 0x01400b02
ub4 fdba_am[37] @3032 0x01400b80
ub4 datadba_am[37] @3036 0x01400b82
ub4 fdba_am[38] @3040 0x01400c00
ub4 datadba_am[38] @3044 0x01400c02
ub4 fdba_am[39] @3048 0x01400c80
ub4 datadba_am[39] @3052 0x01400c82
ub4 fdba_am[40] @3056 0x01400d00
ub4 datadba_am[40] @3060 0x01400d02
ub4 fdba_am[41] @3064 0x01400d80
ub4 datadba_am[41] @3068 0x01400d82
ub4 fdba_am[42] @3072 0x01400e00
ub4 datadba_am[42] @3076 0x01400e02
ub4 fdba_am[43] @3080 0x01400e80
ub4 datadba_am[43] @3084 0x01400e82
ub4 fdba_am[44] @3088 0x01400f00
ub4 datadba_am[44] @3092 0x01400f02
ub4 slbbdba1_am @5192 0x01400081
ub4 tail_kcbh @8188 0x180c2301
XDUL>
15. 根据exts计算blocks脚本
set serveroutput on
DECLARE
exts number;
blocks number;
BEGIN
exts:=45;
blocks:=;
for i in 1..exts loop
if(i<=16) then blocks := blocks + 8;
elsif(i<=63+16) then blocks := blocks + 128;
elsif(i<=120+63+16) then blocks := blocks + 1024;
else
blocks := blocks + 8192;
end if;
end loop;
dbms_output.put_line('Total blocks: '||to_char(blocks));
END;
/
Total blocks: 3840 --> F00
PL/SQL procedure successfully completed.
SQL>
16. 恢复段头块ext信息
#### 参考值
<p class="mume-header " id="参考值"></p>
ub4 extents_ech @36 --num exts 45 --> 2d
ub4 blocks_ech @40 --num blks F00
ub4 ext_ech @48 --exts - 1
ub4 hwoffset_ech @68 --exts - 1
ub4 mhext_echo @264 --exts
####
<p class="mume-header " id=""></p>
d offset 36 count 12
d offset 40 count 12
d offset 48 count 12
d offset 68 count 12
d offset 264 count 12
m /x 2d offset 36
m /x 00f offset 40
m /x 2c offset 48
m /x 2c offset 48
m /x 2d offset 264
17. 检查恢复情况
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 a;
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
COUNT(1)
----------
1000000
SQL> set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name ='DROP_1' order by extent_id;SQL> SQL> SQL>
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- -------------------- ---------- ---------- ---------- ----------
HSQL DROP_1 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
HSQL DROP_1 19 5 640 128
HSQL DROP_1 20 5 768 128
HSQL DROP_1 21 5 896 128
HSQL DROP_1 22 5 1024 128
HSQL DROP_1 23 5 1152 128
HSQL DROP_1 24 5 1280 128
HSQL DROP_1 25 5 1408 128
HSQL DROP_1 26 5 1536 128
HSQL DROP_1 27 5 1664 128
HSQL DROP_1 28 5 1792 128
HSQL DROP_1 29 5 1920 128
HSQL DROP_1 30 5 2048 128
HSQL DROP_1 31 5 2176 128
HSQL DROP_1 32 5 2304 128
HSQL DROP_1 33 5 2432 128
HSQL DROP_1 34 5 2560 128
HSQL DROP_1 35 5 2688 128
HSQL DROP_1 36 5 2816 128
HSQL DROP_1 37 5 2944 128
HSQL DROP_1 38 5 3072 128
HSQL DROP_1 39 5 3200 128
HSQL DROP_1 40 5 3328 128
HSQL DROP_1 41 5 3456 128
HSQL DROP_1 42 5 3584 128
HSQL DROP_1 43 5 3712 128
HSQL DROP_1 44 5 3840 128
45 rows selected.
SQL>
相关文章