多文件表空间中部分文件脱机恢复的质疑
多表空间中部分数据文件脱机恢复时，会不会造成这个文件与这个表空间中其他未脱机的文件不同步或某些对象关系出问题？
比如：
1。一个表在在线的文件里，而表的pk在脱机文件里，无法应用pk；
2。一部分数据和pk的一些值在一个文件里，而另一部分数据和pk的一些值在脱机的文件里，这时插入了一条数据，而这条数据的pk字段在脱机的文件里有，但此时却仍然插入了，当脱机文件恢复后，岂不pk冲突了？
3。或者让人查询时只能查到部分结果，从而做出了错误的决策；假如正在执行一个涨工资程序，所有人的工资都要涨20%，这时如果包括你的工资记录的数据文件被脱机了，那么别人的工资都update加了20%，而你的却没有加！！！
当然只是举几个例子，也许还有其他情况。
所以多文件表空间中，即使一个文件坏了，是不是还是应该让整个表空间脱机，然后再恢复那个损坏的数据文件？
-----------------------------------------------------------------------------------------------------------------
我做了个简单的实验，先说结论吧：
1.pk的datafile在offline之后，从数据字典里显示enble和valid，但是实际不可用。任何触碰它的操作都以error告终
2.这时因为pk的constraints是enable的，所以，当你insert时，oracle会触发一条内部recursive SQL，去check constraints，这时就会触碰index。所以这时会返回error
3.如果你表的一部分数据在offline的datafile时，这时你全表扫描也会已error告终。
下面是我的实验，前面一部分是验证第1，2点，后面是验证第3点。
验证第一二点：
SQL> create tablespace temptest datafile '/xxx/1.dbf' size 1m;
Tablespace created.
SQL> create table hao(id number) tablespace temptest;
Table created.
SQL> insert into hao select object_id from dba_objects where object_id is not null;
21750 rows created.
SQL> commit;
Commit complete.
--create a rubbish table to use up all the space in the tablespace temptest
SQL> create table rubbish (id number) tablespace temptest;
Table created.
set serveroutput on size 100000
declare
ct number:=0;
begin
while (true) loop
insert into rubbish values(999);
commit;
end loop;
end;
/
ERROR at line 1:
ORA-01653: unable to extend table SYS.RUBBISH by 8 in tablespace TEMPTEST
ORA-06512: at line 5
SQL> select FILE_ID,BLOCK_ID,BLOCKS from dba_free_space where TABLESPACE_NAME='TEMPTEST';
no rows selected
--add a datafile for PK future use:
SQL> alter tablespace TEMPTEST add datafile '/xxx/2.dbf' size 10m;
Tablespace altered.
--we now separate the table and PK:
SQL> create index pk on hao(id) tablespace temptest;
Index created.
SQL> alter table hao add constraint pk unique(id) using index pk;
Table altered.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from dba_constraints where table_name='HAO';
CONSTRAINT_NAME CON STATUS
---------------- --- ------------------------
PK U ENABLED
SQL> select distinct FILE_ID from dba_extents where WNER='SYS' and SEGMENT_NAME='PK';
FILE_ID
----------
13
SQL> select distinct FILE_ID from dba_extents where WNER='SYS' and SEGMENT_NAME='HAO';
FILE_ID
----------
12
SQL> alter database datafile 13 offline;
Database altered.
SQL> select INDEX_NAME,INDEX_TYPE,STATUS from dba_indexes where TABLE_NAME='HAO';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------------------------------------------------------------- ------------------------
PK NORMAL VALID
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from dba_constraints where table_name='HAO';
CONSTRAINT_NAME CON STATUS
------------------------------------------------------------------------------------------ --- ------------------------
PK U ENABLED
SQL> insert into hao values(28);
insert into hao values(28)
*
ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/xxx/2.dbf'
--PK can not be read
SQL> select /*+index(hao)*/ * from hao where id=999;
select /*+index(hao)*/ * from hao where id=999
*
ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/xxx/2.dbf'
--FTS can work
SQL> select /*+full(hao)*/ count(*) from hao;
COUNT(*)
----------
21750
SQL> select /*+full(hao)*/ * from hao where id=999;
ID
----------
999
----------------------------------
验证第三点:
SQL> create table hao(id number) tablespace hao;
Table created.
SQL> select FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where WNER='SYS' and SEGMENT_NAME='HAO';
FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
18 0 6505 8
SQL> select name,status,file# from v$datafile where ts#=10;
NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS FILE#
--------------------- ----------
/xxx/hao_2.dbf
ONLINE 8
/xxx/hao_01.dbf
ONLINE 18
SQL> alter table SYS.HAO allocate extent (size 1m datafile '/xxx/hao_2.dbf');
Table altered.
SQL> select FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where WNER='SYS' and SEGMENT_NAME='HAO';
FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
8 16 137 128
8 15 121 8
8 14 113 8
8 13 105 8
8 12 97 8
8 11 89 8
8 10 81 8
8 9 73 8
8 8 65 8
8 7 57 8
8 6 49 8
8 5 41 8
8 4 33 8
8 3 25 8
8 2 17 8
8 1 9 8
18 0 6505 8
17 rows selected.
SQL> insert into hao select object_id from dba_objects;
21740 rows created.
SQL> commit;
Commit complete.
SQL> alter table hao add primary key(id);
Table altered.
SQL> select FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where WNER='SYS' and SEGMENT_NAME='SYS_C009503';
FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
1 5 35881 8
1 4 35873 8
1 3 35865 8
1 2 35857 8
1 1 35849 8
1 0 35689 8
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),count(*) from hao
2 group by dbms_rowid.ROWID_RELATIVE_FNO(rowid)
3 ;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) COUNT(*)
------------------------------------ ----------
8 18440
18 3300
SQL> select id from hao where dbms_rowid.ROWID_RELATIVE_FNO(rowid)=8 and rownum<2;
ID
----------
3347
SQL> alter database datafile 8 offline;
Database altered.
SQL> select * from hao;
(...)
ERROR:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/xxx/hao_2.dbf'
SQL> insert into hao values(3347);
insert into hao values(3347)
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/xxx/hao_2.dbf'
SQL> insert into hao values(999999999);
insert into hao values(999999999)
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/xxx/hao_2.dbf'
SQL> select count(*) from hao where id=3347;
COUNT(*)
----------
1
SQL> select count(*) from hao where to_char(id)='3347';
select count(*) from hao where to_char(id)='3347'
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/xxx/hao_2.dbf'
SQL> select STATUS,DEFERRABLE,DEFERRED,VALIDATED,GENERATED,BAD,RELY from dba_constraints where TABLE_NAME='HAO';
STATUS DEFERRABLE DEFERRED VALIDATED
------------------------ ------------------------------------------ --------------------------- ---------------------------------------
GENERATED BAD RELY
------------------------------------------ --------- ------------
ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
GENERATED NAME
SQL> select status from dba_indexes where INDEX_NAME='SYS_C009501';
STATUS
------------------------
VALID
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13165828/viewspace-606362/ ，如需转载，请注明出处，否则将追究法律责任。
