1.创建测试表空间ts_idx
SYS@PROD1>create tablespace ts_idx datafile '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf' size 10m
2 autoextend on;

Tablespace created.

2.创建测试表，创建索引且使用ts_idx
SCOTT@PROD1>create table t1(x int) tablespace users;

Table created.

SCOTT@PROD1>insert into t1 select rownum from dual connect by rownum<=10;

10 rows created.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>alter table t1 add constraint pk_t1 primary key(x) using index tablespace ts_idx;

Table altered.

3.验证索引表空间已使用(status字段表示非分区索引是否失效或无法使用)
SCOTT@PROD1>select INDEX_NAME,STATUS,TABLESPACE_NAME from user_indexes where table_name='T1';

INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 VALID TS_IDX

4.删除ts_idx表空间的数据文件
SCOTT@PROD1>! rm /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf

SCOTT@PROD1>! ls /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf
ls: /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf: No such file or directory

5.此时数据库已无法一致性关闭，只能强制关闭
SYS@PROD1>shutdown immediate;
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SYS@PROD1>select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SYS@PROD1>shutdown abort;
ORACLE instance shut down.

6.将数据库启动到mount模式下
SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 528485456 bytes
Database Buffers 301989888 bytes
Redo Buffers 2371584 bytes
Database mounted.

7.将丢失的数据文件offline
SYS@PROD1>alter database datafile 8 offline;

Database altered.

8.打开数据库
SYS@PROD1>alter database open;

Database altered.

9.查看索引的状态，显然这个状态是不对的
SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';

INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 VALID TS_IDX

10.创建一个新的表空间，用于存放索引，替代丢失了数据文件的ts_idx表空间
SYS@PROD1>create tablespace ts_idx_2 datafile '/u01/app/oracle/oradata/PROD1/ts_idx02.dbf' size 10m
2 autoextend on;

Tablespace created.

11.将索引PK_T1重建到ts_idx_1上
SYS@PROD1>alter index scott.pk_t1 rebuild tablespace ts_idx_2;
alter index scott.pk_t1 rebuild tablespace ts_idx_2
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf'

在重建时，会对丢失的表空间进行读操作

12.只能将索引手动改为不可用状态
SYS@PROD1>alter index scott.PK_T1 unusable;

Index altered.

SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';

INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 UNUSABLE TS_IDX

13.再次rebuild到新的表空间
SYS@PROD1>alter index scott.pk_t1 rebuild tablespace ts_idx_2;

Index altered.

SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';

INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 VALID TS_IDX_2

14.删除丢失数据文件的ts_idx表空间
SYS@PROD1>drop tablespace ts_idx;

Tablespace dropped.

注如果数据量比较大，创建索引会是非常漫长的一个过程，通过PARALLEL和NOLOGGING可以减少创建时间

ITPub博客

【备份恢复】恢复丢失的索引表空间

最新文章