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;

  1. 信息收集
    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>

  1. 在线日志查询
    生产环境根据业务反馈的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>

  1. drop操作
    drop table hsql.drop_1 purge;
    alter system archive log current;

  2. 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

  1. 检查表已被删除
    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>

  1. 闪回查询基表
    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;
  1. 闪回恢复基表
    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;

  1. 检查恢复情况
    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 exist

SQL>

  1. 更新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

  1. 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;

  1. 检查恢复情况
    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

  1. 检查段头块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>

  2. 根据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>

  1. 恢复段头块ext信息

    参考值

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

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>

gladlyDBA

个人简介。