使用dbms_rowid恢复数据
2017-03-09 本文已影响0人
胖熊猫l
0. summary
1. dbms_rowid
2. 什么是rowid
. 2.1 rowid的概念
. 2.2 rowid的换算
3. 如何恢复数据
. 3.1 模拟data block坏块
. 3.2 使用dbms_rowid来恢复
. 3.3 使用event 10231处理
. 3.4 如果坏的块多的情况怎么处理
1. dbms_rowid
FUNCTION ROWID_BLOCK_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_CREATE RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN
RELATIVE_FNO NUMBER IN
BLOCK_NUMBER NUMBER IN
ROW_NUMBER NUMBER IN
PROCEDURE ROWID_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_RELATIVE_FNO RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_ROW_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_TO_ABSOLUTE_FNO RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
FUNCTION ROWID_TO_EXTENDED RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TO_RESTRICTED RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TYPE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_VERIFY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
2. 什么是rowid
2.1 rowid的概念
在oracle数据库中，rowid可以定位表某一具体的行位置。所以其实使用rowid进行访问是最快的。rowid其实在oracle 8i之前就有了，随着数据量的增大，从oracle 8i开始对rowid进行了扩展，被称为extented rowid. 8i之前的rowid长度是6位，而前面8i之后看到的extented rowid都是10位。由64位的18个ascii字符组成。分别对应了文件号，block号，row位置等。
2.2 rowid的换算
PANDA@bbed>create table t08 as select owner, object_id, object_name from dba_objects where object_id < 500;
Table created.
PANDA@bbed>select substr(rowid, 1, 6) "object",
2 substr(rowid, 7, 3) "file",
3 substr(rowid, 10, 6) "block",
4 substr(rowid, 16, 3) "row"
5 from t08
6 where object_id < 5
7 order by object_id;
object file block row
------------ ------ ------------ ------
AAAWA4 AAG AAAAZD AAA
AAAWA4 AAG AAAAZD AAB
AAAWA4 AAG AAAAZD AAC
64位编码关系：
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
计算object_id号：
第1位：A, 那么则为 0*(64^5) = 0
第2位：A, 那么则为 0*(64^4) = 0
第3位：A, 那么则为 0*(64^3) = 0
第4位：W, 那么则为 22*(64^2) = 90112
第5位：A, 那么则为 0*(64^1) = 0
第6位：4, 那么则为 56*(64^0) = 56
计算相对文件号：
第1位：A, 那么则为 0*(64^2) = 0
第2位：A, 那么则为 0*(64^1) = 0
第3位：G, 那么则为 6*(64^0) = 6
计算block号：
第1位：A, 那么则为 0*(64^5) = 0
第2位：A, 那么则为 0*(64^4) = 0
第3位：A, 那么则为 0*(64^3) = 0
第4位：A, 那么则为 0*(64^2) = 0
第5位：Z, 那么则为 25*(64^1) = 1600
第6位：D, 那么则为 3*(64^0) = 3
计算ROW号（行目录中的编号，有最大值，但一般达不到），这里即为0, 1, 2.
#### 通过包来查询 ####
PANDA@bbed>select dbms_rowid.rowid_object(rowid) obj#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row#
5 from t08
6 where object_id < 5
7 order by object_id;
OBJ# RFILE# BLOCK# ROW#
---------- ---------- ---------- ----------
90168 6 1603 0
90168 6 1603 1
90168 6 1603 2
通过查询可以发现和手工计算的结果一致。
PANDA@bbed>select dump(rowid,16) from t08 where object_id=2;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,1,60,38,1,80,6,43,0,0
rowid一共80个bit长度（10位），其中：
1~32 bit, 共32个bit表示object_id号
33~43 bit, 共10个bit表示file id
43~64 bir, 共22个bit表示block number
65~80 bit, 共16个bit表示row号
PANDA@bbed>select power(2,32) from dual;
POWER(2,32)
-----------
4294967296
PANDA@bbed>select power(2,10) from dual;
POWER(2,10)
-----------
1024
PANDA@bbed>select power(2,22) from dual;
POWER(2,22)
-----------
4194304
从上面可以看出，最大支持4g个对象。每个对象对应的文件号不超过1024-1(bigfile是1024). 每个数据文件的最大容纳的block数目是4194304个。
3. 如何恢复数据
3.1 模拟data block坏块
#### Session 1 ####
PANDA@bbed>select distinct dbms_rowid.rowid_relative_fno(rowid) from t08;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
6
PANDA@bbed>select distinct dbms_rowid.rowid_block_number(rowid) from t08;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
1604
1603
#### Session 2 ####
BBED> set file 6 block 1603
FILE# 6
BLOCK# 1603
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01800643
ub4 bas_kcbh @8 0x00216fa6
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x2e5f
ub2 spare3_kcbh @18 0x0000
BBED> d /v offset 14 count 20
File: /oradata/bbed/panda01.dbf (6)
Block: 1603 Offsets: 14 to 33 Dba:0x01800643
-------------------------------------------------------
01045f2e 00000100 00003860 0100a36f l .._.......8`...o
21000000 l !...
<16 bytes per line>
BBED> m /x ff offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata/bbed/panda01.dbf (6)
Block: 1603 Offsets: 14 to 33 Dba:0x01800643
------------------------------------------------------------------------
ff045f2e 00000100 00003860 0100a36f 21000000
<32 bytes per line>
BBED> sum apply
Check value for File 6, Block 1603:
current = 0x2ea1, required = 0x2ea1
#### Session 1 ####
PANDA@bbed>alter system flush buffer_cache;
System altered.
PANDA@bbed>select count(1) from t08;
select count(1) from t08
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1603)
ORA-01110: data file 6: '/oradata/bbed/panda01.dbf'
3.2 使用dbms_rowid来恢复
PANDA@bbed>select dbms_rowid.rowid_create(1, 90168, 6, 1603, 0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAWA4AAGAAAAZDAAA
PANDA@bbed>select dbms_rowid.rowid_create(1, 90168, 6, 1604, 0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAWA4AAGAAAAZEAAA
坏的块是1603, 随意从1604的第一个row number开始：
PANDA@bbed>create table t08_new as
2 select /* rowid(t08) */ * from t08
3 where rowid >= chartorowid('AAAWA4AAGAAAAZEAAA');
Table created.
PANDA@bbed>select count(1) from t08_new;
COUNT(1)
----------
208
解释下dbms.rowid_create的第一列rowid_type, 这里1代表extented rowid, 所以从8i以后是固定值。
3.3 使用event 10231处理
使用event 10231也可以达到类似的效果，如下：
PANDA@bbed>alter session SET EVENTS '10231 trace name context forever,level 10';
Session altered.
PANDA@bbed>create table t08_1 as select * from t08;
Table created.
PANDA@bbed>select count(1) from t08_1;
COUNT(1)
----------
208
开启了10231后Oracle是如何处理的，这里用10046跟踪下：
SYS@bbed>alter session SET EVENTS '10231 trace name context forever,level 10';
Session altered.
SYS@bbed>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Session altered.
SYS@bbed>oradebug setmypid
Statement processed.
SYS@bbed>select count(1) from panda.t08;
COUNT(1)
----------
208
SYS@bbed>oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/bbed/bbed/trace/bbed_ora_7493.trc
SYS@bbed>oradebug close_trace
Statement processed.
#### trace内容 ####
=====================
PARSING IN CURSOR #47554728191368 len=30 dep=0 uid=0 oct=3 lid=0 tim=1465641659665706 hv=2009533429 ad='a95b8918' sqlid='9fj905pvwf2zp'
select count(1) from panda.t08
END OF STMT
PARSE #47554728191368:c=3000,e=7692,p=0,cr=5,cu=0,mis=1,r=0,dep=0,og=1,plh=2209779862,tim=1465641659665706
EXEC #47554728191368:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2209779862,tim=1465641659665751
WAIT #47554728191368: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1465641659665779
table scan: segment: file# 6 block# 1602
skipping corrupt block file# 6 block# 1603
FETCH #47554728191368:c=0,e=38,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2209779862,tim=1465641659665832
STAT #47554728191368 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4 pr=0 pw=0 time=38 us)'
STAT #47554728191368 id=2 cnt=208 pid=1 pos=1 obj=90168 op='TABLE ACCESS FULL T08 (cr=4 pr=0 pw=0 time=20 us cost=3 size=0 card=208)'
WAIT #47554728191368: nam='SQL*Net message from client' ela= 346 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1465641659666212
FETCH #47554728191368:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2209779862,tim=1465641659666236
WAIT #47554728191368: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1465641659666247
*** 2016-06-11 18:41:06.849
WAIT #47554728191368: nam='SQL*Net message from client' ela= 7183036 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1465641666849404
CLOSE #47554728191368:c=0,e=7,dep=0,type=0,tim=1465641666849516
注意对于逻辑坏块比如ORA-8103, 10231不一定有用，且也不一定能跟踪到问题块。
3.4 如果坏的块多的情况怎么处理
参考：
Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS (文档 ID 422547.1)
怎么找到坏块所属对象就略过了，首先估算下一个块大概有多少行数据，如果有统计信息可以从dba_tables.avg_row_len来查，根据文档建议对结果*2.
select round((select value from v$parameter where name = 'db_block_size')/a.avg_row_len) *2 rowsperblock
from dba_tables a
where a.owner = '&table_owner'
and a.table_name = '&table_name';
如果没有数据可以随便找一行数据大概估下，首先把列名转成行
select replace(wm_concat(a.column_name),',','||') column_name
from dba_tab_columns a
where a.owner = '&table_owner'
and a.table_name = '&table_name'
order by a.column_id;
再代入得到rowsperblock
select lengthb([columns here]) byte,
round((select value from v$parameter where name = 'db_block_size') /
lengthb([columns here])) * 2 rowsperblock
from &table_owner.&table_name
where rownum <= 1;
建立一个同样表结构的表，再使用rowid抽取
set serveroutput on
set concat off
DECLARE
nrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number;
BEGIN
ROWSPERBLOCK:=[VALUE CALCULATED IN STEP 1];
nrows:=0;
select data_object_id into dobj
from dba_objects
where owner = '&&table_owner'
and object_name = '&&table_name'
-- and subobject_name = '[table partition]' Add this condition if table is partitioned
;
for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner = '&&table_owner'
and segment_name = '&&table_name'
-- and partition_name = '[table partition]' Add this condition if table is partitioned
-- and file_id != [OFFLINED DATAFILE] This condition is only used if a datafile needs to be skipped due to ORA-376 (A)
order by extent_id)
loop
for br in i.block_id..i.totblocks loop
for j in 1..ROWSPERBLOCK loop
begin
rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
insert into [OWNER.NEW_TABLE] ([columns here])
select /*+ ROWID(A) */ [columns here]
from &&table_owner.&&table_name A
where rowid = rid;
if sql%rowcount = 1 then nrows:=nrows+1; end if;
if (mod(nrows,10000)=0) then commit; end if;
exception when others then null;
end;
end loop;
end loop;
end loop;
COMMIT;
dbms_output.put_line('Total rows: '||to_char(nrows));
END;
/
注意最好按照文档要求输入全部列，某些逻辑坏块的场景下不指定列名可能有问题。抽取出来后交给业务选择性处理数据。
