bbed恢复truncate表

truncate操作实际上就是修改表段的段头信息，回收高水位线的操作。根据这个思路，我们也可以通过改回segment header和基表信息，修改高水位线来恢复数据。表一旦被误truncate，一定要第一时间将表空间read only，防止二次破坏。
下面创建一组测试表
[oracle@renCAP ~]$ sqlplus bbed_user/oracle@renpdb
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 21 07:50:42 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Jun 21 2019 04:18:22 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> create table bbed_trunc_table as select * from dba_objects;
Table created.
SQL> select count(*) from bbed_trunc_table;
COUNT(*)
----------
72629
SQL>
SQL> select object_id, data_object_id, object_name from dba_objects where object_name='BBED_TRUNC_TABLE';
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- --------------------------------------------------------------------------------------------------------------------------------
73254 73254 BBED_TRUNC_TABLE
SQL> select OWNER,SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='BBED_TRUNC_TABLE';
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- -------------------- ----------- ------------
BBED_USER BBED_TRUNC_TABLE 15 162
SQL> alter system dump datafile 15 block 162;
System altered.
SQL> select value from v$diag_info where name ='Default Trace File';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/app/oracle/diag/rdbms/ren/ren/trace/ren_ora_108261.trc
测试表创建完毕，我们对表dump，可以发现dump显示了block size:8192,Level 1 BMB :03c00681,Level 2 BMB :03c000a1,extent:27个，obj#：73254
在segment中存在用来描述每个块使用情况的位图块（BMB Bitmap block），对于数据插入肯定是要找有空间的block。
L1 ，level 1 Bitmap Block（8k），一个位图块可以描述几十个、几百个数据，但是一个L1 BMB不足以描述所有数据，所以会有多个L1 BMB，当大于一个BMB就会生成 L2 level 2 Bitmap Block，同样的还有L3 BMB，基本上很少存在，除非表特别大。其基本架构如图所示。
+++++++++++++++
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 27 #blocks: 1536
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1448
mapblk 0x00000000 offset: 26
Disk Lock:: Locked by xid: 0x0007.001.00000243
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1448
mapblk 0x00000000 offset: 26
Level 1 BMB for High HWM block: 0x03c00680
Level 1 BMB for Low HWM block: 0x03c00680
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x03c000a1
Last Level 1 BMB: 0x03c00681 >>>>>>L1 BMB 15,160
Last Level II BMB: 0x03c000a1 >>>>>>L2 BMB 15,161
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 27 obj#: 73254 flag: 0x10000000
Inc # 0
Extent Map
++++++++++++++
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /data/area
db_recovery_file_dest_size big integer 8016M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> ! ls -l /data/area/REN/archivelog/2019_06_21
总用量 15244
-rw-r----- 1 oracle oinstall 14902784 6月 20 11:04 o1_mf_1_30_gjoxxs8w_.arc
-rw-r----- 1 oracle oinstall 702976 6月 20 11:12 o1_mf_1_31_gjoyf19t_.arc
SQL>
SQL> truncate table BBED_TRUNC_TABLE;
Table truncated.
SQL> select count(*) from BBED_TRUNC_TABLE;
COUNT(*)
----------
0
SQL> alter system dump datafile 15 block 162;
System altered.
SQL> select value from v$diag_info where name ='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/opt/app/oracle/diag/rdbms/ren/ren/trace/ren_ora_109512.trc
一、恢复数据字典
bbed数据恢复所有信息来自redo 或归档中获取
SQL> alter system dump logfile '/data/area/REN/archivelog/2019_06_21/o1_mf_1_34_gjr789v0_.arc';
System altered.
SQL> select value from v$diag_info where name ='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/opt/app/oracle/diag/rdbms/ren/ren/trace/ren_ora_77919.trc
归档重要信息解读
++++++++++
Both the HWMs
Low HWM
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128 <<<<<<<truncate 之前的LOW HWM ，在26号段上
#blocks in seg. hdr's freelists: 0
#blocks below: 1448 <<<<<<<<<管理1448个block
mapblk 0x00000000 offset: 26
lfdba: 0x03c00680
High HWM
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128 <<<<<<<truncate 之前的High HWM ，在26号段上
#blocks in seg. hdr's freelists: 0
#blocks below: 1448 <<<<<<<<<管理1448个block
mapblk 0x00000000 offset: 26
lfdba: 0x03c00680 hint dba: 0x03c000a1
Lasts in Header
LF: 62916225 LS: 62914721 LT: 0 FT: 0
CHANGE #2 CON_ID:3 TYP:0 CLS:4 AFN:15 DBA:0x03c000a2 OBJ:73254 SCN:0x00000000001a3803 SEQ:2 OP:13.28 ENC:0 RBL:0 FLG:0x0000
Both the HWMs
Low HWM
Highwater:: 0x03c000a3 ext#: 0 blk#: 3 ext size: 8 <<<<<<<truncate 之后的LOW HWM
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x03c000a0
High HWM
Highwater:: 0x03c000a3 ext#: 0 blk#: 3 ext size: 8 <<<<<<<truncate 之后的High HWM
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x03c000a0 hint dba: 0x03c000a1
Lasts in Header
LF: 62914720 LS: 62914721 LT: 0 FT: 0
CHANGE #3 CON_ID:3 TYP:0 CLS:8 AFN:15 DBA:0x03c000a0 OBJ:73254 SCN:0x00000000001a3803 SEQ:2 OP:13.22 ENC:0 RBL:0 FLG:0x0000
Redo on Level1 Bitmap Block
Redo to set hwm
Opcode: 32 Highwater:: 0x03c000a3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
REDO RECORD - Thread:1 RBA: 0x000022.0000805d.0184 LEN: 0x0060 VLD: 0x01 CON_UID: 1250055670
SCN: 0x00000000001a3803 SUBSCN: 6 06/21/2019 07:54:58
CHANGE #1 CON_ID:3 TYP:0 CLS:8 AFN:15 DBA:0x03c00680 OBJ:73254 SCN:0x00000000001a3803 SEQ:1 OP:13.22 ENC:0 RBL:0 FLG:0x0000
Redo on Level1 Bitmap Block
Redo to set hwm
Opcode: 32 Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
REDO RECORD - Thread:1 RBA: 0x000022.0000805d.01e4 LEN: 0x0054 VLD: 0x01 CON_UID: 1250055670
SCN: 0x00000000001a3803 SUBSCN: 6 06/21/2019 07:54:58
CHANGE #1 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:18.3 ENC:0 FLG:0x0000
Reuse redo entry
Object reuse: pdb=3, tsn=8 objd=73254
REDO RECORD - Thread:1 RBA: 0x000022.00008060.0010 LEN: 0x0078 VLD: 0x05 CON_UID: 1250055670
SCN: 0x00000000001a3805 SUBSCN: 1 06/21/2019 07:54:58
(LWN RBA: 0x000022.00008060.0010 LEN: 0x00000001 NST: 0x0001 SCN: 0x00000000001a3805)
CHANGE #1 CON_ID:3 TYP:0 CLS:8 AFN:15 DBA:0x03c000a0 OBJ:73254 SCN:0x00000000001a3803 SEQ:3 OP:13.22 ENC:0 RBL:0 FLG:0x0000
Redo on Level1 Bitmap Block
Redo to Change Opcode
Opcode: 9locking transaction:
REDO RECORD - Thread:1 RBA: 0x000022.00008060.0088 LEN: 0x00cc VLD: 0x01 CON_UID: 1250055670
SCN: 0x00000000001a3805 SUBSCN: 2 06/21/2019 07:54:58
CHANGE #1 CON_ID:3 TYP:0 CLS:22 AFN:11 DBA:0x0240240a OBJ:4294967295 SCN:0x00000000001a3803 SEQ:4 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 72 spc: 7240 flg: 0x0022 seq: 0x0136 rec: 0x08
xid: 0x0003.000.000002db
ktubu redo: slt: 0 rci: 7 opc: 13.23 objn: 73254 objd: 73254 tsn: 8
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
Undo for Lev1 Bitmap Block
L1 DBA: 0x03c000a0 L2 DBA: 0x00000000 fcls: 8 scls: 0 offset: 0
Redo on Level1 Bitmap Block
Change objd
Newobjd: 73254
CHANGE #2 CON_ID:3 TYP:0 CLS:8 AFN:15 DBA:0x03c000a0 OBJ:73254 SCN:0x00000000001a3805 SEQ:1 OP:13.22 ENC:0 RBL:0 FLG:0x0000
Redo on Level1 Bitmap Block
Change objd
Newobjd: 73259 >>>>>>>>>dataobj# 从73254变成了73259
....省略.............
REDO RECORD - Thread:1 RBA: 0x000022.0000806e.009c LEN: 0x0044 VLD: 0x01 CON_UID: 1250055670
SCN: 0x00000000001a3811 SUBSCN: 28 06/21/2019 07:54:58
CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:15 DBA:0x03c000a2 OBJ:73259 SCN:0x00000000001a3811 SEQ:1 OP:14.2 ENC:0 RBL:0 FLG:0x0000
ktelk redo: xid: 0x0001.017.00000359
REDO RECORD - Thread:1 RBA: 0x000022.0000806e.00e0 LEN: 0x0150 VLD: 0x01 CON_UID: 1250055670
SCN: 0x00000000001a3811 SUBSCN: 29 06/21/2019 07:54:58
CHANGE #1 CON_ID:3 TYP:0 CLS:17 AFN:11 DBA:0x02400080 OBJ:4294967295 SCN:0x00000000001a3811 SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000
ktudh redo: slt: 0x0017 sqn: 0x00000000 flg: 0x0002 siz: 112 fbi: 184
uba: 0x02401191.017b.17 pxid: 0x0000.000.00000000
CHANGE #2 CON_ID:3 TYP:0 CLS:18 AFN:11 DBA:0x02401191 OBJ:4294967295 SCN:0x00000000001a3811 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 112 spc: 5142 flg: 0x0022 seq: 0x017b rec: 0x17
xid: 0x0001.017.00000359
ktubu redo: slt: 23 rci: 0 opc: 14.5 objn: 1 objd: 73259 tsn: 8
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: Yes
0x00000000
kteopu undo - undo operation on extent map
segdba: 0x3c000a2 class: 4 mapdba:0x3c000a2 offset: 3
rbr extent - dba: 0x0 nbk: 0x0
kteop redo - redo operation on extent map
ADD: dba:0x3c00680 len:128 at offset:26 >>>>>>>>>extent map 在26号段上
ADDAXT: offset:26 fdba:x03c00680 bdba:0x03c00682 >>>>>>>sux map 在26号段上
SETSTAT: exts:27 blks:1536 lastmap:0x0 mapcnt:0
CHANGE #3 CON_ID:3 TYP:0 CLS:4 AFN:15 DBA:0x03c000a2 OBJ:73259 SCN:0x00000000001a3811 SEQ:2 OP:14.4 ENC:0 RBL:0 FLG:0x0000
kteop redo - redo operation on extent map
DELETE: entry:26
shift back: dba:0x0 len:0
SETSTAT: exts:26 blks:1408 lastmap:0x0 mapcnt:0
+++++++++
检查truncate之后dataobj#变成了什么，从归档dump中获取，dataobj#从73254变成了73259，这里我们只需改回73254即可
[root@renCAP 2019_06_20]# grep -i "CDOBJ" /opt/app/oracle/diag/rdbms/ren/ren/trace/ren_ora_77919.trc
CDOBJ: new object number:73254
CDOBJ: new object number:73259
SQL> select obj#,dataobj# from sys.obj$ where obj#=73254;
OBJ# DATAOBJ#
---------- ----------
73254 73259
SQL> select obj#,dataobj# from sys.tab$ where obj#=73254;
OBJ# DATAOBJ#
---------- ----------
73254 73259
SQL> update sys.obj$ set dataobj#=73254 where obj#=73254;
1 row updated.
SQL> update sys.tab$ set dataobj#=73254 where obj#=73254;
1 row updated.
SQL> commit;
Commit complete.
SQL>
二、修改段头，L1、L2块头的dataobj#
改完数据字典之后接下来就需要改L1 BMB、L2BMB、segment header的dataobj#了。
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x03c000a1
Last Level 1 BMB: 0x03c000a0 <<<file 15 block 160
Last Level II BMB: 0x03c000a1 <<<file 15 block 161
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 27 obj#: 73259 flag: 0x10000000
通过truncate后的段头块dump确定
L2块 --offset 104
select dbms_utility.data_block_address_file(to_number(‘03c000a1’,‘xxxxxxxx’)) as file_id,dbms_utility.data_block_address_block(to_number(‘03c000a1’,‘xxxxxxxx’)) as block_id from dual;
FILE_ID BLOCK_ID
15 161
L1块 --offset 192
select dbms_utility.data_block_address_file(to_number(‘03c000a0’,‘xxxxxxxx’)) as file_id,dbms_utility.data_block_address_block(to_number(‘03c000a0’,‘xxxxxxxx’)) as block_id from dual;
FILE_ID BLOCK_ID
15 160
段头是 15,162 --offset 272
SQL> select to_char('73254','xxxxxxxx') old_dataobj,to_char('73259','xxxxxxxx') new_dataobj from dual;
OLD_DATAO NEW_DATAO
--------- ---------
11e26 11e2b
[oracle@renCAP bbed]$ bbed parfile=par.bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Jun 21 08:15:48 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info all;
File# Name Size(blks)
----- ---- ----------
1 /data/REN/datafile/o1_mf_system_gjlsnfcr_.dbf 104960
9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 32000
10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 47360
11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 12800
12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 640
14 /data/cap_test.dbf 131072
15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 12800
16 /data/bbed_test_tbs.dbf 12800
BBED> set dba 15,162 <<<修改段头
DBA 0x03c000a2 (62914722 15,162)
BBED> d offset 272
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 162 Offsets: 272 to 783 Dba:0x03c000a2
------------------------------------------------------------------------
2b1e0100 00000010 a000c003 08000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 26 offset 272
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 162 Offsets: 272 to 783 Dba:0x03c000a2
------------------------------------------------------------------------
261e0100 00000010 a000c003 08000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set dba 15,161 <<<修改L2 BMB
DBA 0x03c000a1 (62914721 15,161)
BBED> d offset 104
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 161 Offsets: 104 to 615 Dba:0x03c000a1
------------------------------------------------------------------------
2b1e0100 03000000 00000000 a000c003 07000100 b000c003 01000100 c000c003
01000100 d000c003 01000100 e000c003 01000100 f000c003 01000100 0001c003
01000100 1001c003 01000100 8001c003 01000100 8101c003 01000100 0002c003
01000100 0102c003 01000100 8002c003 01000100 8102c003 01000100 0003c003
01000100 0103c003 01000100 8003c003 01000100 8103c003 01000100 0004c003
01000100 0104c003 01000100 8004c003 01000100 8104c003 01000100 0005c003
01000100 0105c003 01000100 8005c003 01000100 8105c003 01000100 0006c003
01000100 0106c003 01000100 8006c003 07000100 8106c003 07000100 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 26 offset 104
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 161 Offsets: 104 to 615 Dba:0x03c000a1
------------------------------------------------------------------------
261e0100 03000000 00000000 a000c003 07000100 b000c003 01000100 c000c003
01000100 d000c003 01000100 e000c003 01000100 f000c003 01000100 0001c003
01000100 1001c003 01000100 8001c003 01000100 8101c003 01000100 0002c003
01000100 0102c003 01000100 8002c003 01000100 8102c003 01000100 0003c003
01000100 0103c003 01000100 8003c003 01000100 8103c003 01000100 0004c003
01000100 0104c003 01000100 8004c003 01000100 8104c003 01000100 0005c003
01000100 0105c003 01000100 8005c003 01000100 8105c003 01000100 0006c003
01000100 0106c003 01000100 8006c003 07000100 8106c003 07000100 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set dba 15,160 <<<修改L1 BMB
DBA 0x03c000a0 (62914720 15,160)
BBED> d offset 192
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 160 Offsets: 192 to 703 Dba:0x03c000a0
------------------------------------------------------------------------
2b1e0100 05381a00 00000000 a000c003 08000000 00000000 a800c003 08000000
08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 11100000 11111111 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 26 offset 192
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 160 Offsets: 192 to 703 Dba:0x03c000a0
------------------------------------------------------------------------
261e0100 05381a00 00000000 a000c003 08000000 00000000 a800c003 08000000
08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 11100000 11111111 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 15, Block 160:
current = 0x80ed, required = 0x80ed
BBED> verify
DBVERIFY - Verification starting
FILE = /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf
BLOCK = 162
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED>
SQL> alter system flush buffer_cache;
System altered.
--此处已经可以查询了，由于HWM依然没有改，这里还查不到数据
SQL> select count(*) from BBED_USER.BBED_TRUNC_TABLE;
COUNT(*)
----------
0
SQL>
三、修改高水位线
Both the HWMs
Low HWM
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1448
mapblk 0x00000000 offset: 26
lfdba: 0x03c00680
High HWM
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1448
mapblk 0x00000000 offset: 26
lfdba: 0x03c00680 hint dba: 0x03c000a1
Lasts in Header
LF: 62916225 LS: 62914721 LT: 0 FT: 0
CHANGE #2 CON_ID:3 TYP:0 CLS:4 AFN:15 DBA:0x03c000a2 OBJ:73254 SCN:0x00000000001a3803 SEQ:2 OP:13.28 ENC:0 RBL:0 FLG:0x0000
Both the HWMs
Low HWM
Highwater:: 0x03c000a3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x03c000a0
High HWM
Highwater:: 0x03c000a3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x03c000a0 hint dba: 0x03c000a1
Lasts in Header
这里信息依然是从归档dump中获取，可以看到HWM从“Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128 ”变成了“Highwater:: 0x03c000a3 ext#: 0 blk#: 3 ext size: 8”，我们只需吧HWM改回即可。
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128
SQL> select to_char(26,'xxxxx') ext,to_char(40,'xxxxx') blk,to_char(128,'xxxxx') extsize from dual;
EXT BLK EXTSIZ
------ ------ ------
1a 28 80
m /x 1a offset 48
m /x 28 offset 52
m /x 80 offset 56
m /x 2c06 offset 60
m /x 1a offset 92
m /x 28 offset 96
m /x 80 offset 100
m /x 2c06 offset 104
四、修改段头的extent map和Auxillary Map
修改完高水位线还要修改段头的extent map和 Auxillary Map
恢复extent map
grep -i ‘ADD:’ /opt/app/oracle/diag/rdbms/ren/ren/trace/ren_ora_77919.trc
ADD: dba:0x3c00680 len:128 at offset:26
ADD: dba:0x3c00600 len:128 at offset:25
ADD: dba:0x3c00580 len:128 at offset:24
ADD: dba:0x3c00500 len:128 at offset:23
ADD: dba:0x3c00480 len:128 at offset:22
ADD: dba:0x3c00400 len:128 at offset:21
ADD: dba:0x3c00380 len:128 at offset:20
ADD: dba:0x3c00300 len:128 at offset:19
ADD: dba:0x3c00280 len:128 at offset:18
ADD: dba:0x3c00200 len:128 at offset:17
ADD: dba:0x3c00180 len:128 at offset:16
ADD: dba:0x3c00118 len:8 at offset:15
ADD: dba:0x3c00110 len:8 at offset:14
ADD: dba:0x3c00108 len:8 at offset:13
ADD: dba:0x3c00100 len:8 at offset:12
ADD: dba:0x3c000f8 len:8 at offset:11
ADD: dba:0x3c000f0 len:8 at offset:10
ADD: dba:0x3c000e8 len:8 at offset:9
ADD: dba:0x3c000e0 len:8 at offset:8
ADD: dba:0x3c000d8 len:8 at offset:7
ADD: dba:0x3c000d0 len:8 at offset:6
ADD: dba:0x3c000c8 len:8 at offset:5
ADD: dba:0x3c000c0 len:8 at offset:4
ADD: dba:0x3c000b8 len:8 at offset:3
ADD: dba:0x3c000b0 len:8 at offset:2
ADD: dba:0x3c000a8 len:8 at offset:1
–生成bbed修改语句
grep -i ‘ADD:’ ext | sort -k 5 -t ‘:’ -n | awk ‘{print $2,$5,$3}’ | awk -F ‘len:’ ‘{printf("%s %08x\n",$1,$2)}’ | sed ‘s/dba://g’ | sed ‘s/offset://g’ | sed ‘s/0x//g’ | sed ‘s/^/0/g’ | awk ‘{print substr($1,7,2)substr($1,5,2),substr($1,3,2)substr($1,1,2) ,$2,substr($3,7,2)substr($3,5,2),substr($3,3,2)substr($3,3,2)}’ |awk ‘{print "m /x "$1 " offset " 280+8*$3 "\nm /x " $2 " offset "280+8*$3+2 "\nm /x " $4 " offset " 280+8*$3+4 "\nm /x " $5 " offset " 280+8*$3+6}’
恢复Auxillary Map
grep -i ‘ADDAXT:’ /opt/app/oracle/diag/rdbms/ren/ren/trace/ren_ora_77919.trc
ADDAXT: offset:26 fdba:x03c00680 bdba:0x03c00682
ADDAXT: offset:25 fdba:x03c00600 bdba:0x03c00602
ADDAXT: offset:24 fdba:x03c00580 bdba:0x03c00582
ADDAXT: offset:23 fdba:x03c00500 bdba:0x03c00502
ADDAXT: offset:22 fdba:x03c00480 bdba:0x03c00482
ADDAXT: offset:21 fdba:x03c00400 bdba:0x03c00402
ADDAXT: offset:20 fdba:x03c00380 bdba:0x03c00382
ADDAXT: offset:19 fdba:x03c00300 bdba:0x03c00302
ADDAXT: offset:18 fdba:x03c00280 bdba:0x03c00282
ADDAXT: offset:17 fdba:x03c00200 bdba:0x03c00202
ADDAXT: offset:16 fdba:x03c00180 bdba:0x03c00182
ADDAXT: offset:15 fdba:x03c00110 bdba:0x03c00118
ADDAXT: offset:14 fdba:x03c00110 bdba:0x03c00111
ADDAXT: offset:13 fdba:x03c00100 bdba:0x03c00108
ADDAXT: offset:12 fdba:x03c00100 bdba:0x03c00101
ADDAXT: offset:11 fdba:x03c000f0 bdba:0x03c000f8
ADDAXT: offset:10 fdba:x03c000f0 bdba:0x03c000f1
ADDAXT: offset:9 fdba:x03c000e0 bdba:0x03c000e8
ADDAXT: offset:8 fdba:x03c000e0 bdba:0x03c000e1
ADDAXT: offset:7 fdba:x03c000d0 bdba:0x03c000d8
ADDAXT: offset:6 fdba:x03c000d0 bdba:0x03c000d1
ADDAXT: offset:5 fdba:x03c000c0 bdba:0x03c000c8
ADDAXT: offset:4 fdba:x03c000c0 bdba:0x03c000c1
ADDAXT: offset:3 fdba:x03c000b0 bdba:0x03c000b8
ADDAXT: offset:2 fdba:x03c000b0 bdba:0x03c000b1
ADDAXT: offset:1 fdba:x03c000a0 bdba:0x03c000a8
–生成bbed修改语句
grep -i ‘ADDAXT:’ aux |awk ‘{print$3 " " $4 " " $2}’|sort -k 4 -t ‘:’ -n|uniq|sed ‘s/fdba:x//g’|sed ‘s/bdba:0x//g’|sed ‘s/offset://g’ |awk ‘{print substr($1,7,2)substr($1,5,2),substr($1,3,2)substr($1,1,2),$3,substr($2,7,2)substr($2,5,2),substr($2,3,2)substr($2,1,2)}’|awk ‘{print "m /x "$1 " offset " 2736+8*$3 "\nm /x “$2 " offset " 2736+8*$3+2”\nm /x " $4 " offset " 2736+8*$3+4 “\nm /x " $5” offset " 2736+8*$3+6}’
到此在查询是可以查询到数据的，但是数据不全，因为只有一个extent，需要修改extent个数
五、恢复extent 个数
Highwater:: 0x03c006a8 ext#: 26 blk#: 40 ext size: 128
extent从0 开始 第27个
BBED> set dba 15,162 <<段头
DBA 0x03c000a2 (62914722 15,162)
BBED> d offset 264
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 162 Offsets: 264 to 775 Dba:0x03c000a2
------------------------------------------------------------------------
01000000 00000000 261e0100 00000010 a000c003 08000000 a800c003 08000000
b000c003 08000000 b800c003 08000000 c000c003 08000000 c800c003 08000000
d000c003 08000000 d800c003 08000000 e000c003 08000000 e800c003 08000000
f000c003 08000000 f800c003 08000000 0001c003 08000000 0801c003 08000000
1001c003 08000000 1801c003 08000000 8001c003 80000000 0002c003 80000000
8002c003 80000000 0003c003 80000000 8003c003 80000000 0004c003 80000000
8004c003 80000000 0005c003 80000000 8005c003 80000000 0006c003 80000000
8006c003 80000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 1b offset 264
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 162 Offsets: 264 to 775 Dba:0x03c000a2
------------------------------------------------------------------------
1b000000 00000000 261e0100 00000010 a000c003 08000000 a800c003 08000000
b000c003 08000000 b800c003 08000000 c000c003 08000000 c800c003 08000000
d000c003 08000000 d800c003 08000000 e000c003 08000000 e800c003 08000000
f000c003 08000000 f800c003 08000000 0001c003 08000000 0801c003 08000000
1001c003 08000000 1801c003 08000000 8001c003 80000000 0002c003 80000000
8002c003 80000000 0003c003 80000000 8003c003 80000000 0004c003 80000000
8004c003 80000000 0005c003 80000000 8005c003 80000000 0006c003 80000000
8006c003 80000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 15, Block 162:
current = 0xb6ca, required = 0xb6ca
BBED>
BBED> d offset 36
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 162 Offsets: 36 to 547 Dba:0x03c000a2
------------------------------------------------------------------------
01000000 08000000 9c0a0000 1a000000 28000000 80000000 a806c003 00000000
00000000 00000000 00000000 07000d00 46020000 01000000 1a000000 28000000
80000000 a806c003 00000000 00000000 00000000 00000000 a000c003 a000c003
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 01000000 00200000 00000000 34140000 00000000
a100c003 01000000 a000c003 a100c003 00000000 00000000 00000000 00000000
00000000 1b000000 00000000 261e0100 00000010 a000c003 08000000 a800c003
08000000 b000c003 08000000 b800c003 08000000 c000c003 08000000 c800c003
08000000 d000c003 08000000 d800c003 08000000 e000c003 08000000 e800c003
08000000 f000c003 08000000 f800c003 08000000 0001c003 08000000 0801c003
08000000 1001c003 08000000 1801c003 08000000 8001c003 80000000 0002c003
80000000 8002c003 80000000 0003c003 80000000 8003c003 80000000 0004c003
80000000 8004c003 80000000 0005c003 80000000 8005c003 80000000 0006c003
80000000 8006c003 80000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 1b offset 36;
File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
Block: 162 Offsets: 36 to 547 Dba:0x03c000a2
------------------------------------------------------------------------
1b000000 08000000 9c0a0000 1a000000 28000000 80000000 a806c003 00000000
00000000 00000000 00000000 07000d00 46020000 01000000 1a000000 28000000
80000000 a806c003 00000000 00000000 00000000 00000000 a000c003 a000c003
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 01000000 00200000 00000000 34140000 00000000
a100c003 01000000 a000c003 a100c003 00000000 00000000 00000000 00000000
00000000 1b000000 00000000 261e0100 00000010 a000c003 08000000 a800c003
08000000 b000c003 08000000 b800c003 08000000 c000c003 08000000 c800c003
08000000 d000c003 08000000 d800c003 08000000 e000c003 08000000 e800c003
08000000 f000c003 08000000 f800c003 08000000 0001c003 08000000 0801c003
08000000 1001c003 08000000 1801c003 08000000 8001c003 80000000 0002c003
80000000 8002c003 80000000 0003c003 80000000 8003c003 80000000 0004c003
80000000 8004c003 80000000 0005c003 80000000 8005c003 80000000 0006c003
80000000 8006c003 80000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 15, Block 162:
current = 0xb6d0, required = 0xb6d0
刷新内存后数据恢复。
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from BBED_USER.BBED_TRUNC_TABLE;
COUNT(*)
----------
72629
SQL>
SQL>
恢复后立刻CTAS创建新表，由于未修改L1和L2块，该表无法drop insert等操作
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
