使用dbms_rowid恢复可用数据

最近看到同事的一个案例，觉得很有趣 现在测试下：
SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
首先创建表空间again 分别对应两个datafile again01 again02 ，创建表c1 c2

SQL> create tablespace again datafile ‘/u01/again01.dtf’ size 1M;

Tablespace created.

SQL> drop table c1;
drop table c1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table c1 (id int);

Table created.

SQL> drop table c1;

Table dropped.

SQL> create table c1 (id int) tablespace again;

Table created.

SQL> begin
2 for i in 1..5 loop
3 insert into c1 values (i);
4 commit;
5 end loop ;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter tablespace again add datafile ‘/u01/again02.dtf’ size 1M;

Tablespace altered.

SQL> create table c2 (id int) tablespace again;

Table created.

SQL> begin
2 for i in 1..100000 loop
3 insert into c2 values (i);
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.C2 by 128 in tablespace AGAIN
ORA-06512: at line 3

SQL> select count (*) from c2;

COUNT(*)
———-
77880

SQL> col segment_name for a30
SQL> /

FILE_ID SEGMENT_NAME BLOCK_ID BLOCKS
———- —————————— ———- ———-
8 C1 9 8
8 C2 17 8
8 C2 25 8
8 C2 33 8
8 C2 41 8
8 C2 49 8
8 C2 57 8
8 C2 65 8
8 C2 73 8
8 C2 81 8
8 C2 89 8

FILE_ID SEGMENT_NAME BLOCK_ID BLOCKS
———- —————————— ———- ———-
8 C2 97 8
8 C2 105 8
8 C2 113 8
8 C2 121 8
9 C2 9 8
9 C2 17 8

17 rows selected.

SQL> select data_object_id from dba_objects where object_name=’C2′;

DATA_OBJECT_ID
———-
52039

SQL> select dbms_rowid.rowid_create(1,52039,8,129,1) from dual;

DBMS_ROWID.ROWID_C
——————
AAAMtHAAIAAAACBAAB

使用dbms_rowid.rowid_create 构建c2的rowid 129=max(block_id)+blocks

SQL> alter database datafile 9 offline;

使用datafile offline 模拟datafile丢失

SQL> select count (*) from c2;
select count (*) from c2
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: ‘/u01/again02.dtf’

SQL> select /*+rowid (c2) */ count (*) from c2 where rowid<'AAAMtHAAIAAAACBAAB'; COUNT(*) ---------- 67980 通过对datafile 8最后一个block的rowid的范围指定 强制sql扫描可用块 --------------------------------------------------------------------------------------------------------------------------- 测试中出现另一种情况： SQL> select owner from dba_tables where table_name=’MYTABLE’;

OWNER
——————————
SYS
SQL> set line 200
SQL> /

FILE_ID BLOCK_ID BLOCKS SEGMENT_NAME
———- ———- ———- ———————————————————————————
5 9 8 MYTABLE
5 137 128 MYTABLE
5 265 128 MYTABLE
5 393 128 MYTABLE
5 521 128 MYTABLE
5 649 128 MYTABLE
6 9 8 MYTABLE
6 17 8 MYTABLE
6 25 8 MYTABLE
6 33 8 MYTABLE
6 41 8 MYTABLE

FILE_ID BLOCK_ID BLOCKS SEGMENT_NAME
———- ———- ———- ———————————————————————————
6 49 8 MYTABLE
6 57 8 MYTABLE
6 65 8 MYTABLE
6 73 8 MYTABLE
6 81 8 MYTABLE
6 89 8 MYTABLE
6 97 8 MYTABLE
6 105 8 MYTABLE
6 113 8 MYTABLE
6 121 8 MYTABLE
6 137 128 MYTABLE

FILE_ID BLOCK_ID BLOCKS SEGMENT_NAME
———- ———- ———- ———————————————————————————
6 265 128 MYTABLE
6 393 128 MYTABLE
6 521 128 MYTABLE
6 649 128 MYTABLE
6 777 128 MYTABLE
6 905 128 MYTABLE
7 9 128 MYTABLE
7 137 128 MYTABLE
7 265 128 MYTABLE
7 393 128 MYTABLE
7 521 128 MYTABLE

33 rows selected.

SQL> select data_object_id from dba_objects where object_name=’MYTABLE’;

DATA_OBJECT_ID
———-
19617

SQL> select max(block_id +blocks) from dba_extents where segment_name=’MYTABLE’ and file_id=5;

MAX(BLOCK_ID+BLOCKS)
——————–
777

SQL> select dbms_rowid.rowid_create(1,19617,5,777,1) from dual;

DBMS_ROWID.ROWID_C
——————
AAAEyhAAFAAAAMJAAB

SQL> alter database datafile 6 offline;

Database altered.

SQL> select /*+rowid (mytable) */ count (*) from mytable where rowid<'AAAEyhAAFAAAAMJAAB'; select /*+rowid (mytable) */ count (*) from mytable where rowid<'AAAEyhAAFAAAAMJAAB' * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/oradata/MYDB/datafile/o1_mf_mytbs_7915sqh7_.dbf' SQL> set autot trace exp;
SQL> select /*+rowid (mytable) */ count (*) from mytable where rowid<'AAAEyhAAFAAAAMJAAB'; Execution Plan ---------------------------------------------------------- Plan hash value: 912055137 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 615 (1)| 00:00:08 | | 1 | SORT AGGREGATE | | 1 | 12 | | | |* 2 | TABLE ACCESS BY ROWID RANGE| MYTABLE | 4 | 48 | 615 (1)| 00:00:08 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(ROWID<'AAAEyhAAFAAAAMJAAB') SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.

SQL> select /*+rowid (mytable) */ count (*) from mytable where rowid<'AAAEyhAAFAAAAMJAAB'; Execution Plan ---------------------------------------------------------- Plan hash value: 912055137 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 616 (1)| 00:00:08 | | 1 | SORT AGGREGATE | | 1 | 12 | | | |* 2 | TABLE ACCESS BY ROWID RANGE| MYTABLE | 72822 | 853K| 616 (1)| 00:00:08 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(ROWID<'AAAEyhAAFAAAAMJAAB') Note ----- - dynamic sampling used for this statement SQL> alter database datafile 6 offline;

Database altered.

SQL> select /*+rowid (mytable) */ count (*) from mytable where rowid<'AAAEyhAAFAAAAMJAAB'; Execution Plan ---------------------------------------------------------- Plan hash value: 912055137 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 615 (1)| 00:00:08 | | 1 | SORT AGGREGATE | | 1 | 12 | | | |* 2 | TABLE ACCESS BY ROWID RANGE| MYTABLE | 4 | 48 | 615 (1)| 00:00:08 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(ROWID<'AAAEyhAAFAAAAMJAAB') SQL> set autot off
SQL> /

COUNT(*)
———-
49509

直接offline之后 使用rowid强制扫描 还是回去扫描offline的datafile 再次online 执行该sql 再次offline 成功扫描出记录
起初怀疑是dynamic sampling影响 设置optimizer_dynamic_sampling=0 仍然产生类似情况 后续将跟进

针对此次试验，如果datafile中存在多张表，我们可以使用sqlplus copy 将表直接转移到测试库 再通过impdp导入到生产库

提供scripts参考：

select ‘copy from system/oracle@css to system/oracle@hong create ‘ ||
object_name || ‘ using select /*+rowid(‘ || object_name ||
‘) */ * from ‘ || OWNER || ‘.’ || object_name ||
‘ where rowiddbms_rowid.rowid_create(1,’ || data_object_id ||
‘,10,1,1 ) ;’
from dba_objects
where object_name in (select distinct segment_name
from dba_extents
where file_id = 9
and owner = ‘SYS’)

产生类似脚本：

copy from system/oracle@css to system/oracle@hong create C2 using select /*+rowid(C2) */ * from SYS.C2 where
rowiddbms_rowid.rowid_create(1,52039,10,1,1 ) ;

continuing…