数据和索引分离存储：数据一个表空间，索引是一个表空间
Recovering from a Lost Index Tablespace
If you have a tablespace that contains only indexes, recovering from a loss of a data file belonging to that tablespace can be simplified.
When a data file like this is lost, you can perform the following steps:
1. Drop the data file.
2. Drop the tablespace.
3. Re-create the index tablespace.
4. Re-create the indexes that were in the tablespace.
专门建立一个表空间存储索引
create tablespace inx datafile '/u01/app/oracle/inx.dbf' size 20M;
create table scott.t2 (id number primary key using index (create index scott.t2_ix on scott.t2(id) tablespace inx), name char(10) ) tablespace users ;
索引：scott.t2_ix 在 表空间inx
数据：scott.t2 表 在表空间users
insert into scott.t2 values(1,'tom');
commit;
有专门的索引表空间，如果损坏了，删除表空间，重建索引表空间，再重建索引。
SQL> conn as sysdba
Connected.
SQL> set autot on
SQL> select * from scott.t2 where id =1;
ID NAME
---------- ----------
1 tom
Execution Plan
--------------------------------------------------------
Plan hash value: 2342100761
--------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00
:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 25 | 1 (0)| 00:00
:01 |
|* 2 | INDEX RANGE SCAN | T2_IX #索引 1 | | 1 (0)| 00:00
删除文件/u01/app/oracle/inx.dbf
rm u01/app/oracle/inx.dbf
alter system flush buffer_cache;
exit
sqlplus as sysdba
SQL> set autot on
SQL> select * from scott.t2 where id =1;
select * from scott.t2 where id =1
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/app/oracle/inx.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
索引文件删除了，因为走索引，所以报错
SQL> select * from scott.t2;
ID NAME
---------- ----------
1 tom
Execution Plan
----------------------------------
Plan hash value: 1513984157
----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL #全表扫描 T2 | 1 | 25 | 3 (0)| 00:00:01 |
----------------------------------
走全表扫描，不走索引，不会报错。数据是好的，只是索引坏了。
TIPS：严格部署，数据一个表空间，索引一个表空间。
修复非常简单：删除索引表空间，重建索引表空间，后再重建索引
删除表空间
SQL> drop tablespace inx ;
drop tablespace inx
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
drop tablespace inx INCLUDING CONTENTS ; #失败
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
删除存在该表空间的唯一和主键约束的段
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where TABLE_NAME='T2' and owner='SCOTT';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C0011053 P T2
alter table scott.t2 drop constraint SYS_C0011053;
也可以参考drop tablespace 的语法：CASCADE CONSTRAINTS
DROP TABLESPACE inx INCLUDING CONTENTS CASCADE CONSTRAINTS;
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/inx.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
必须先离线，再删除
SQL> alter database datafile 8 offline;
Database altered.
SQL> drop tablespace inx;
再去重建一个表空间
create tablespace inx datafile '/u01/app/oracle/inx.dbf' size 20M;
重建索引
alter table scott.t2 add constraint pk_id primary key(id) using index (create index scott.ix_id2 on scott.t2(id) tablespace inx);
检查
select * from scott.t2 where id =1; #成功
索引重建：
如果rowid不正确，索引就会标记为无用。为了修复无用索引，必须使用ALTER INDEX…REBUILD命令。
索引重建过程需要额外的存储空间。
REBUILD命令语法具有多种选项，其中最重要的选项是TABLESPACE、ONLINE以及NOLOGGING。
在默认情况下，索引在其当前表空间内重建，不过如果使用TABLESPACE关键字指定某个表空间，重建就会移动至这个表空间内进行。同样在默认情况下，重建过程会为DML命令锁定指定的表，不过使用ONLINE关键字可以避免这种状况。NOLOGGING关键字则指示不为索引重建操作生成重做，这样能更快速完成重建。
启用NOLOGGING选项只是为索引重建禁用重做生成，在索引重建之后，针对该索引的所有DML命令都会像平时一样生成重做。
用上面测试过的用户和表，创建两个索引
SQL> create index n1_idx on testtab(n1);
Index created.
SQL> create index d1_idx on testtab(d1);
Index created.
查看索引状态有效，之后我们移动指定的表，再查状态变成无用索引
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
N1_IDX VALID
D1_IDX VALID
SQL> alter table testtab move;
Table altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
N1_IDX UNUSABLE
D1_IDX UNUSABLE
重建索引
SQL> alter index n1_idx rebuild online nologging PARALLEL 4;
Index altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
N1_IDX VALID
D1_IDX UNUSABLE
