使用ORWID模拟坏块并修复过程__rowid_the_ROWID_row_data_

当前位置: 数据库>oracle

使用ORWID模拟坏块并修复过程

来源: 互联网发布时间：2017-05-31

本文导语: 使用rowid模拟数据库坏块修复过程：一、Rowid定义（官网）A globally unique address for a row in a database.Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not ...

使用rowid模拟数据库坏块修复过程：

一、Rowid定义（官网）
A globally unique address for a row in a database.
Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.
An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row. The encoding characters are A-Z, a-z, 0-9, +, and /.
Example 12-1 queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.
Example 12-1 ROWID Pseudocolumn
SQL> SELECT ROWID FROM employees WHERE employee_id = 100;

ROWID
------------------
AAAPecAAFAAAABSAAA
Figure 12-8 illustrates the format of an extended rowid.
Figure 12-8 ROWID Format

Description of "Figure 12-8 ROWID Format"
An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:
• OOOOOO
The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.
• FFF
The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).
• BBBBBB
The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.
• RRR
The row number identifies the row in the block (row AAA in Example 12-1).
After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example, if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on. If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.
Note:
Internally, the database performs row movement as if the row were physically deleted and reinserted. However, row movement is considered an update, which has implications for triggers.
二、一些说明
rowid是伪列（pseudocolumn），伪劣的意思是实际上这一列本身在数据字典中并不存在，在查询结果输出时它被构造出来的。
rowid并不会真正存在于表的data block中,但是他会存在于index当中，用来通过rowid来寻找表中的行数据。

ROWID 格式:
扩展的ROWID 在磁盘上需要10 个字节的存储空间，并使用18 个字符来显示。

它包含下列组成元素：
1. 数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的
2. 相关文件编号：此编号对于表空间中的每个数据文件是唯一的
3. 块编号：表示包含此行的块在数据文件中的位置
4. 行编号：标识块头中行目录位置的位置

在内部，存储的10个字节(bytes)，即80位(bit)又按如下规则进行划分：
（1）数据对象编号需要32 bit
（2）相关文件编号需要10 bit
（3）块编号需要22 bit
（4）行编号需要16 bit
查看制定rowid内容
SQL> select file_id,relative_fno,block_id from dba_extents where owner='CENTER' and segment_name='TEST';

FILE_ID RELATIVE_FNO BLOCK_ID
---------- ------------ ----------
6 6 136

SQL> select rowid,file_id,relative_fno,block_id from dba_extents where owner='CENTER' and segment_name='TEST';

select rowid,file_id,relative_fno,block_id from dba_extents where owner='CENTER' and segment_name='TEST'
---rowed不保存在数据字典中
ORA-01446: 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_rowid_type NUMBER;
3 v_OBJECT_NUMBER NUMBER;
4 v_RELATIVE_FNO NUMBER;
5 v_BLOCK_NUMBERE_FNO NUMBER;
6 v_ROW_NUMBER NUMBER;
7 BEGIN
8 DBMS_ROWID.rowid_info (
9 rowid_in => 'AAASUSAAGAAAACNAAA',
10 rowid_type => v_rowid_type,
11 object_number => v_OBJECT_NUMBER,
12 relative_fno => v_RELATIVE_FNO,
13 block_number => v_BLOCK_NUMBERE_FNO,
14 ROW_NUMBER => v_ROW_NUMBER);
15 DBMS_OUTPUT.put_line ('ROWID_TYPE: ' || TO_CHAR (v_rowid_type));
16 DBMS_OUTPUT.put_line ('OBJECT_NUMBER: ' || TO_CHAR (v_OBJECT_NUMBER));
17 DBMS_OUTPUT.put_line ('RELATIVE_FNO: ' || TO_CHAR (v_RELATIVE_FNO));
18 DBMS_OUTPUT.put_line ('BLOCK_NUMBER: ' || TO_CHAR (v_BLOCK_NUMBERE_FNO));
19 DBMS_OUTPUT.put_line ('ROW_NUMBER: ' || TO_CHAR (v_ROW_NUMBER));
20 END;
21 /

ROWID_TYPE: 1
OBJECT_NUMBER: 75026
RELATIVE_FNO: 6
BLOCK_NUMBER: 141
ROW_NUMBER: 0
1、创建坏块
1) 创建表空间，用户center，指定表空间tps，创建表test，制定到表空间，插入数据
2) 各种方式修改文件，创建坏块，比如编辑数据文件，删除修改其中一个字符，ultraedit工具可行

2、查询坏块
比如查询坏块的表白文件号10，快好100----ORA-01578: ORACLE 数据块损坏
3、查询其它信息
The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,,,,0) LOW_RID
from DUAL;

The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,,,+1,0) HI_RID
from DUAL;
1) 根据段名找到数据对象号
查找段名
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =13
AND 100 between block_id AND block_id + blocks - 1 ;
查找数据对象号
SELECT data_object_id
FROM dba_objects
WHERE object_name = 'XXX---上一部找的set_name' and owner='上一部查找的owner';
结果比如是12345
查找最小快rowid和最大rowid
select dbms_rowid.rowid_create(1, 12345,10,100,0) from dual;
--结果是AAAS+/AANAAAAF1AAA
select dbms_rowid.rowid_create(1, 12345,10,101,0) from dual;
---结果是AAAS+/AANAAAAF2AAA

4、找回
1、找到坏块数据量&保存插入临时表
SELECT /*+ ROWID(A) */ COUNT(*) FROM test
WHERE ROWID>='AAAS+/AANAAAAF2AAA';

CREATE TABLE test_TMP TABLESPACE XXX
AS
SELECT /*+ ROWID(A) */ * FROM test
WHERE ROWID>='AAAS+/AANAAAAF2AAA';
SELECT /*+ ROWID(A) */ COUNT(*) FROM test
WHERE ROWID