- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
3#
发表于 2012-4-25 21:16:29
提供 一个思路 ,这不是唯一的解决方案:
通过rowid access的方式将 丢失数据文件的 表空间 重建到 别的表空间上, 并将原损坏表空间 DROP掉。
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create tablespace lost datafile '/s01/lost1.dbf' size 10M;
Tablespace created.
SQL> create table losttest tablespace lost as select * from dba_objects;
Table created.
SQL> alter tablespace lost add datafile '/s01/lost2.dbf' size 10M;
Tablespace altered.
SQL> insert /*+ append */ into losttest select * from losttest;
50722 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into losttest select * from losttest;
insert /*+ append */ into losttest select * from losttest
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.LOSTTEST by 128 in tablespace LOST
SQL> alter system checkpoint;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from losttest;
COUNT(*)
----------
101444
SQL> select object_id,data_object_id from dba_objects where object_name='LOSTTEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
53710 53710
SQL> select RELATIVE_FNO,BLOCK_ID,BLOCKS from dba_extents where segment_name='LOSTTEST' order by 1,2;
RELATIVE_FNO BLOCK_ID BLOCKS
------------ ---------- ----------
6 9 8
6 17 8
6 25 8
6 33 8
6 41 8
6 49 8
6 57 8
6 65 8
6 73 8
6 81 8
6 89 8
RELATIVE_FNO BLOCK_ID BLOCKS
------------ ---------- ----------
6 97 8
6 105 8
6 113 8
6 121 8
6 129 8
6 137 128
6 265 128
6 393 128
6 521 128
6 649 128
6 777 128
RELATIVE_FNO BLOCK_ID BLOCKS
------------ ---------- ----------
6 905 128
6 1033 128
7 9 128
7 137 128
7 265 128
7 393 128
7 521 128
7 649 128
7 777 128
7 905 128
7 1033 128
33 rows selected.
SQL> col file_name for a40
SQL> select RELATIVE_FNO,FILE_ID,FILE_NAME from dba_data_files where TABLESPACE_NAME='LOST';
RELATIVE_FNO FILE_ID FILE_NAME
------------ ---------- ----------------------------------------
6 6 /s01/lost1.dbf
7 7 /s01/lost2.dbf
RELATIVE_FNO 6 /s01/lost1.dbf rowid from block 9 to (1033+128)
RELATIVE_FNO 7 /s01/lost2.dbf rowid from block 9 to (1033+128)
示例表 losttest 在 RFN 6 上的存储范围是 从 9号块到 1033+128号块(不包括该块)
示例表 losttest 在 RFN 7 上的存储范围是 从 9号块到 1033+128号块(不包括该块)
我们利用以上信息构建 rowid
rowid between dbms_rowid.rowid_create(1,53710,6,9,0) and dbms_rowid.rowid_create(1,53710,6,1033+128,0)
SQL> alter database datafile '/s01/lost2.dbf' offline drop;
Database altered.
SQL> select count(*) from losttest;
select count(*) from losttest
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/s01/lost2.dbf'
SQL> set linesize 140 pagesize 1400
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 OFFLINE OFFLINE 1533717 25-APR-12
SQL> select dbms_rowid.rowid_create(1,53710,6,9,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAANHOAAGAAAAAJAAA ==> 这是RFN 6上的 低位ROWID
SQL> select dbms_rowid.rowid_create(1,53710,6,1033+128,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAANHOAAGAAAASJAAA ==> 这是RFN 6上的 高位ROWID
注意 不能让SQL FULL Table Scan Losttest ,全表扫描必然触发 ORA-00376错误。 需要让SQL走 rowid access的路径,所以要用到 /*+ ROWID(A) */的 HINT
因为RFN 6上的ROWID 总是小于 RFN 7上的rowid,所以我们仅指定 小于 上面获得的高位ROWID即可
SQL> select /*+ ROWID(A) */ count(*) from losttest A where rowid<'AAANHOAAGAAAASJAAA';
COUNT(*)
----------
82158
SQL> create table backup_losttest tablespace users as select /*+ ROWID(A) */ * from losttest A where rowid<'AAANHOAAGAAAASJAAA';
Table created.
SQL> select count(*) from backup_losttest;
COUNT(*)
----------
82158
SQL> drop table losttest purge;
Table dropped.
SQL> drop tablespace lost;
Tablespace dropped. |
|