Truncate异常恢复一之Truncate前后对比
Truncate异常恢复二之DBMS恢复
Truncate异常恢复三之GDUL恢复
Truncate异常恢复四之BBED恢复
Truncate异常恢复四之BBED恢复
构建测试表
SQL> create table devin.test_trun as select * from dba_objects;
Table created.
SQL> select count(*) from devin.test_trun;
COUNT(*)
----------
72833
SQL> col segment_Name for a10
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='TEST_TRUN';
SEGMENT_NA HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
TEST_TRUN 9 130
SQL> select segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='TEST_TRUN';
SEGMENT_NA EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ---------- ----------
TEST_TRUN 0 8 128 8
TEST_TRUN 1 8 136 8
TEST_TRUN 2 8 144 8
TEST_TRUN 3 8 152 8
TEST_TRUN 4 8 160 8
TEST_TRUN 5 8 168 8
TEST_TRUN 6 8 176 8
TEST_TRUN 7 8 184 8
TEST_TRUN 8 8 192 8
TEST_TRUN 9 8 200 8
TEST_TRUN 10 8 208 8
TEST_TRUN 11 8 216 8
TEST_TRUN 12 8 224 8
TEST_TRUN 13 8 232 8
TEST_TRUN 14 8 240 8
TEST_TRUN 15 8 248 8
TEST_TRUN 16 8 256 128
TEST_TRUN 17 8 384 128
TEST_TRUN 18 8 512 128
TEST_TRUN 19 8 640 128
TEST_TRUN 20 8 768 128
TEST_TRUN 21 8 896 128
TEST_TRUN 22 8 1024 128
TEST_TRUN 23 8 1152 128
TEST_TRUN 24 8 1280 128
TEST_TRUN 25 8 1408 128
TEST_TRUN 26 8 1536 128
27 rows selected.
SQL> select obj#,dataobj# from sys.obj$ where name='TEST_TRUN';
OBJ# DATAOBJ#
---------- ----------
78623 78623
复制DUMP SEGMENT HEADER
SQL> @flush
System altered.
System altered.
SQL> @tracefile
VALUE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1381.trc
SQL> alter system dump datafile 8 block 130;
System altered.
[root@devin-enmo ~]# more /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1381.trc
Trace file /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1381.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /u01/app/oracle/product/12.2/dbhome_1
System name: Linux
Node name: devin-enmo
Release: 4.14.35-1818.3.3.el7uek.x86_64
Version: #2 SMP Mon Sep 24 14:45:01 PDT 2018
Machine: x86_64
Instance name: hkora
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 1381, image: oracle@devin-enmo (TNS V1-V3)
*** 2021-10-26T16:29:50.236396+08:00
*** SESSION ID:(266.10999) 2021-10-26T16:29:50.236408+08:00
*** CLIENT ID:() 2021-10-26T16:29:50.236411+08:00
*** SERVICE NAME:(SYS$USERS) 2021-10-26T16:29:50.236414+08:00
*** MODULE NAME:(sqlplus@devin-enmo (TNS V1-V3)) 2021-10-26T16:29:50.236417+08:00
*** ACTION NAME:() 2021-10-26T16:29:50.236420+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-10-26T16:29:50.236422+08:00
Start dump data blocks tsn: 9 file#:8 minblk 130 maxblk 130
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=33554562
BH (0x763e3898) file#: 8 rdba: 0x02000082 (8/130) class: 4 ba: 0x7619a000
set: 19 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78623 objn: 78623 tsn: [0/9] afn: 8 hint: f
hash: [0x767dc5c8,0x6e7b68e0] lru: [0x757f2e38,0x773f8378]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x285a964],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x285a964],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 16
01. sid:01 L965:MK_CRL:ulnk:objq 02. sid:01 L212:zib:bic:FSQ
03. sid:01 L122:zgb:set:st 04. sid:01 L830:olq1:clr:WRT+CKT
05. sid:01 L951:zgb:lnk:objq 06. sid:01 L372:zgb:set:MEXCL
07. sid:01 L123:zgb:no:FEN 08. sid:01 L083:zgb:ent:fn
09. sid:02 L662:kflb:bis:REU 10. sid:05 L338:zibmlt:set:MSHR
11. sid:05 L144:zibmlt:mk:EXCL 12. sid:05 L122:zgb:set:st
13. sid:05 L830:olq1:clr:WRT+CKT 14. sid:05 L951:zgb:lnk:objq
15. sid:05 L372:zgb:set:MEXCL 16. sid:05 L123:zgb:no:FEN
BH (0x767dc518) file#: 8 rdba: 0x02000082 (8/130) class: 4 ba: 0x764f2000
set: 18 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78623 objn: 78623 tsn: [0/9] afn: 8 hint: f
hash: [0x76fe50a8,0x763e3948] lru: [0x773f5358,0x757f0658]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x285a963],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x285a963],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 14
01. sid:01 L965:MK_CRL:ulnk:objq 02. sid:01 L212:zib:bic:FSQ
03. sid:01 L122:zgb:set:st 04. sid:01 L830:olq1:clr:WRT+CKT
05. sid:01 L951:zgb:lnk:objq 06. sid:01 L372:zgb:set:MEXCL
07. sid:01 L123:zgb:no:FEN 08. sid:01 L083:zgb:ent:fn
09. sid:02 L662:kflb:bis:REU 10. sid:11 L145:zib:mk:EXCL
11. sid:11 L212:zib:bic:FSQ 12. sid:11 L122:zgb:set:st
13. sid:11 L830:olq1:clr:WRT+CKT 14. sid:11 L951:zgb:lnk:objq
15. sid:11 L372:zgb:set:MEXCL 16. sid:11 L123:zgb:no:FEN
BH (0x76fe4ff8) file#: 8 rdba: 0x02000082 (8/130) class: 4 ba: 0x76dbc000
set: 22 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78623 objn: 78623 tsn: [0/9] afn: 8 hint: f
hash: [0x6e7b68e0,0x767dc5c8] lru: [0x74fe20b8,0x757dc078]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktswh03: ktscts' fscn: 0x285a4c3 tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 7
01. sid:02 L662:kflb:bis:REU 02. sid:12 L192:kcbbic2:bic:FBD
03. sid:12 L191:kcbbic2:bic:FBW 04. sid:12 L602:bic1_int:bis:FWC
05. sid:12 L822:bic1_int:ent:rtn 06. sid:12 L832:oswmqbg1:clr:WRT
07. sid:12 L930:kubc:sw:mq 08. sid:12 L913:bxsv:sw:objq
09. sid:12 L608:bxsv:bis:FBW 10. sid:12 L607:bxsv:bis:FFW
11. sid:05 L464:chg1_mn:bic:FMS 12. sid:05 L778:chg1_mn:bis:FMS
13. sid:05 L353:gcur:set:MEXCL 14. sid:05 L464:chg1_mn:bic:FMS
15. sid:05 L778:chg1_mn:bis:FMS 16. sid:05 L353:gcur:set:MEXCL
Block dump from disk:
buffer tsn: 9 rdba: 0x02000082 (8/130)
scn: 0x285a5f7 seq: 0x03 flg: 0x04 tail: 0xa5f72303
frmt: 0x02 chkval: 0x971f type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F5BF9D89000 to 0x00007F5BF9D8B000
7F5BF9D89000 0000A223 02000082 0285A5F7 04030000 [#...............]
...
...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 27 #blocks: 1536
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0200062c ext#: 26 blk#: 44 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1452
mapblk 0x00000000 offset: 26
Disk Lock:: Locked by xid: 0x0009.01c.00000b19
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0200062c ext#: 26 blk#: 44 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1452
mapblk 0x00000000 offset: 26
Level 1 BMB for High HWM block: 0x02000600
Level 1 BMB for Low HWM block: 0x02000600
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x02000081
Last Level 1 BMB: 0x02000601
Last Level II BMB: 0x02000081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 27 obj#: 78623 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02000080 length: 8
0x02000088 length: 8
0x02000090 length: 8
0x02000098 length: 8
0x020000a0 length: 8
0x020000a8 length: 8
0x020000b0 length: 8
0x020000b8 length: 8
0x020000c0 length: 8
0x020000c8 length: 8
0x020000d0 length: 8
0x020000d8 length: 8
0x020000e0 length: 8
0x020000e8 length: 8
0x020000f0 length: 8
0x020000f8 length: 8
0x02000100 length: 128
0x02000180 length: 128
0x02000200 length: 128
0x02000280 length: 128
0x02000300 length: 128
0x02000380 length: 128
0x02000400 length: 128
0x02000480 length: 128
0x02000500 length: 128
0x02000580 length: 128
0x02000600 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02000080 Data dba: 0x02000083
Extent 1 : L1 dba: 0x02000080 Data dba: 0x02000088
Extent 2 : L1 dba: 0x02000090 Data dba: 0x02000091
Extent 3 : L1 dba: 0x02000090 Data dba: 0x02000098
Extent 4 : L1 dba: 0x020000a0 Data dba: 0x020000a1
Extent 5 : L1 dba: 0x020000a0 Data dba: 0x020000a8
Extent 6 : L1 dba: 0x020000b0 Data dba: 0x020000b1
Extent 7 : L1 dba: 0x020000b0 Data dba: 0x020000b8
Extent 8 : L1 dba: 0x020000c0 Data dba: 0x020000c1
Extent 9 : L1 dba: 0x020000c0 Data dba: 0x020000c8
Extent 10 : L1 dba: 0x020000d0 Data dba: 0x020000d1
Extent 11 : L1 dba: 0x020000d0 Data dba: 0x020000d8
Extent 12 : L1 dba: 0x020000e0 Data dba: 0x020000e1
Extent 13 : L1 dba: 0x020000e0 Data dba: 0x020000e8
Extent 14 : L1 dba: 0x020000f0 Data dba: 0x020000f1
Extent 15 : L1 dba: 0x020000f0 Data dba: 0x020000f8
Extent 16 : L1 dba: 0x02000100 Data dba: 0x02000102
Extent 17 : L1 dba: 0x02000180 Data dba: 0x02000182
Extent 18 : L1 dba: 0x02000200 Data dba: 0x02000202
Extent 19 : L1 dba: 0x02000280 Data dba: 0x02000282
Extent 20 : L1 dba: 0x02000300 Data dba: 0x02000302
Extent 21 : L1 dba: 0x02000380 Data dba: 0x02000382
Extent 22 : L1 dba: 0x02000400 Data dba: 0x02000402
Extent 23 : L1 dba: 0x02000480 Data dba: 0x02000482
Extent 24 : L1 dba: 0x02000500 Data dba: 0x02000502
Extent 25 : L1 dba: 0x02000580 Data dba: 0x02000582
Extent 26 : L1 dba: 0x02000600 Data dba: 0x02000602
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x02000081
End dump data blocks tsn: 9 file#: 8 minblk 130 maxblk 130
复制执行TRUNCATE
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/HKORA/onlinelog/o1_mf_1_hvyllq9j_.log
3 /u01/app/oracle/oradata/HKORA/onlinelog/o1_mf_3_j7z2qcb9_.log
2 /u01/app/oracle/oradata/HKORA/onlinelog/o1_mf_2_hvyllqbz_.log
# 便于测试，truncate前后记录一下scn
SQL> select timestamp_to_scn(to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')) scn from dual;
SCN
----------
42308854
SQL> truncate table devin.test_trun;
Table truncated.
SQL> select timestamp_to_scn(to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')) scn from dual;
SCN
----------
42308928
SQL> alter tablespace devin read only;
Tablespace altered.
复制恢复
经过前面的分析，需要恢复的元数据块和数据字典基表以及内容为：
- segment header(dataobj#,LHWM,HHWM,extent map,aux map以及extents个数)
- tab$(dataobj#)
- obj$(dataobj#)
恢复数据字典
# 数据字典恢复通过logminer挖掘redo，也可以通过redo dump来恢复，这里采用redo dump
SQL> @tracefile
VALUE
------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1793.trc
SQL> alter system dump logfile '/u01/app/oracle/oradata/HKORA/onlinelog/o1_mf_3_j7z2qcb9_.log' scn min 42308854 scn max 42308928;
System altered.
[root@devin-enmo ~]# grep -i 'CDOBJ' /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1793.trc
CDOBJ: new object number:78623
CDOBJ: new object number:78624
# 可以看到dataobj#从78623变成了78624，需要将段头和基表obj$,tab$的dataobj#恢复成78623
SQL> select obj#,dataobj# from sys.obj$ where obj#=78623;
OBJ# DATAOBJ#
---------- ----------
78623 78624
SQL> select obj#,dataobj# from sys.tab$ where obj#=78623;
OBJ# DATAOBJ#
---------- ----------
78623 78624
SQL> update sys.obj$ set dataobj#=78623 where obj#=78623;
1 row updated.
SQL> update sys.tab$ set dataobj#=78623 where obj#=78623;
1 row updated.
SQL> commit;
Commit complete.
SQL> @flush
System altered.
System altered.
复制恢复相关块的dataobj#
# truncate会修改段头，L2和第一个L1的dataobj#
SQL> select to_char('78623','xxxxxxxx') old_dataobj,to_char('78624','xxxxxxxx') new_dataobj from dual;
OLD_DATAO NEW_DATAO
--------- ---------
1331f 13320
[oracle@devin-enmo bbed]$ bbed
BBED: Release 2.0.0.0.0 - Limited Production on Tue Oct 26 09:02:00 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_system_hvylgg 0
2 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_ccps_par_hvyl 0
3 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_sysaux_hvyljh 0
4 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_undotbs1_hvyl 0
5 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_ccps_dat_hvyl 0
7 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_users_hvylkn9 0
10 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_ccps_inx_hvyl 0
8 /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1by 0
BBED> set file 8 block 130
FILE# 9
BLOCK# 130
BBED> d offset 272 count 8
File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf (8)
Block: 130 Offsets: 272 to 279 Dba:0x02400082
------------------------------------------------------------------------
20330100 00000010
<32 bytes per line>
BBED> m /x 1f offset 272
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf (8)
Block: 130 Offsets: 272 to 279 Dba:0x02400082
------------------------------------------------------------------------
1f330100 00000010
<32 bytes per line>
BBED> sum apply
Check value for File 8, Block 130:
current = 0x929f, required = 0x929f
## 同样方式恢复L2和第一个L1的dataobj#
L2:
m /x 1f offset 104
L1:
m /x 1f offset 192
SQL> @flush
System altered.
System altered.
SQL> select count(*) from devin;
COUNT(*)
----------
0
## 此时对象可以查询，但是因为没有恢复高水位，所以数据条数为0
复制恢复高水位
## 高水位块的辨别，可以设置到最后一个Extnets的最后一个块，这个信息的准确性其实无所谓，全表扫描的时候它一定会扫描这个块下面所有的块。
[root@devin-enmo ~]# grep -E 'Low HWM|Highwater|High HWM' /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1793.trc
Low HWM
Highwater:: 0x0200062c ext#: 26 blk#: 44 ext size: 128
High HWM
Highwater:: 0x0200062c ext#: 26 blk#: 44 ext size: 128
Low HWM
Highwater:: 0x02000083 ext#: 0 blk#: 3 ext size: 8
High HWM
Highwater:: 0x02000083 ext#: 0 blk#: 3 ext size: 8
##恢复
SQL> select to_char(26,'xxxxx') ext,to_char(44,'xxxxx') blk,to_char(128,'xxxxx') extsize from dual;
EXT BLK EXTSIZ
------ ------ ------
1a 2c 80
m /x 1a offset 48
m /x 2c offset 52
m /x 80 offset 56
m /x 2c06 offset 60
m /x 1a offset 92
m /x 2c offset 96
m /x 80 offset 100
m /x 2c06 offset 104
复制恢复extent map
BBED> d offset 280 count 400
File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_jqhgnz7m_.dbf (8)
Block: 130 Offsets: 280 to 679 Dba:0x02000082
------------------------------------------------------------------------
80000002 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
<32 bytes per line>
## 通过redo可以看到extent map已经被清空，ext map可以从redo dump获取，并且是从最后一个ext map开始倒序清空的
[oracle@devin-enmo script]$ grep -i 'ADD:' /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_6495.trc
ADD: dba:0x2400680 len:128 at offset:26
ADD: dba:0x2400600 len:128 at offset:25
ADD: dba:0x2400580 len:128 at offset:24
ADD: dba:0x2400500 len:128 at offset:23
ADD: dba:0x2400480 len:128 at offset:22
ADD: dba:0x2400400 len:128 at offset:21
ADD: dba:0x2400380 len:128 at offset:20
ADD: dba:0x2400300 len:128 at offset:19
ADD: dba:0x2400280 len:128 at offset:18
ADD: dba:0x2400200 len:128 at offset:17
ADD: dba:0x2400180 len:128 at offset:16
ADD: dba:0x2400100 len:8 at offset:15
ADD: dba:0x24000f8 len:8 at offset:14
ADD: dba:0x24000f0 len:8 at offset:13
ADD: dba:0x24000e8 len:8 at offset:12
ADD: dba:0x24000e0 len:8 at offset:11
ADD: dba:0x24000d8 len:8 at offset:10
ADD: dba:0x24000d0 len:8 at offset:9
ADD: dba:0x24000c8 len:8 at offset:8
ADD: dba:0x24000c0 len:8 at offset:7
ADD: dba:0x24000b8 len:8 at offset:6
ADD: dba:0x24000b0 len:8 at offset:5
ADD: dba:0x24000a8 len:8 at offset:4
ADD: dba:0x2400098 len:8 at offset:3
ADD: dba:0x2400090 len:8 at offset:2
ADD: dba:0x2400088 len:8 at offset:1
## 构建bbed命令
[root@devin-enmo ~]# grep -i 'ADD:' /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1793.trc | 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}'
m /x 8800 offset 288
m /x 0002 offset 290
m /x 0800 offset 292
m /x 0000 offset 294
m /x 9000 offset 296
...
...
m /x 0000 offset 494
复制恢复aux map
[root@devin-enmo ~]# grep -i 'ADDAXT:' /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_1793.trc |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}'
m /x 8000 offset 2744
m /x 0002 offset 2746
m /x 8800 offset 2748
m /x 0002 offset 2750
m /x 9000 offset 2752
m /x 0002 offset 2754
...
...
m /x 0002 offset 2950
复制恢复extent个数
Highwater:: 0x0200062c ext#: 26 blk#: 44 ext size: 128
# extent从0开始，即27个
BBED>m /x 1b offset 264
复制验证
SQL> select count(*) from devin.test_trun;
COUNT(*)
----------
72833
## 数据恢复成功，但是没有恢复L1,L2，insert会有问题，但是全表扫描不读L1,L2，所以恢复成功后立即做个ctas
## 注意需要在其它表空间CTAS，原表空间ctas会报错：ORA-00600: internal error code, arguments: [ktsscrsegfmt:objdchk_kcbnew_3], [0], [78627], [4], [4], [8], [], [], [], [], [], []
SQL> create table sys.test_trun_bak as select * from devin.test_trun;
Table created.
复制最后修改时间：2021-11-30 08:40:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
墨天轮福利君
3年前
评论
0您好，您的文章已入选合格奖，10墨值奖励已经到账请查收！
❤️我们还会实时派发您的流量收益。
3年前
点赞 评论
相关阅读
2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
1309次阅读
2025-04-09 15:33:27
【DBA坦白局】第一期：在小城市和一线城市做DBA，是“躺”还是“卷”？
墨天轮编辑部
1142次阅读
2025-04-10 14:17:22
Oracle Concepts（Oracle 19c）：07 SQL
Ryan Bai
957次阅读
2025-04-09 10:57:11
2025年3月国产数据库大事记
墨天轮编辑部
736次阅读
2025-04-03 15:21:16
Oracle数据库常用的78个脚本，速来下载！
陈举超
593次阅读
2025-03-27 12:27:50
2025年3月国产数据库中标情况一览：TDSQL大单622万、GaussDB大单581万……
通讯员
532次阅读
2025-04-10 15:35:48
东方通 TongWeb 中间件入门指南： 轻松掌握从部署到认证
shunwah
496次阅读
2025-03-19 15:09:52
Oracle DBA 高效运维指南：高频实用 SQL 大全
Lucifer三思而后行
486次阅读
2025-03-28 21:52:03
如何利用Deepseek自动优化SQL并推送
潇湘秦
469次阅读
2025-03-28 10:02:33
征文大赛 |「码」上数据库—— KWDB 2025 创作者计划启动
KaiwuDB
458次阅读
2025-04-01 20:42:12
