丢失索引表空间恢复

--丢失索引表空间恢复

SYS@PROD2> create tablespace ind datafile '/u01/app/oracle/oradata/PROD2/ind.dbf' size 10m;

Tablespace created.

SYS@PROD2> conn scott/tiger
Connected.
SCOTT@PROD2> create index emp_ename_i on emp(ename) tablespace ind;  --创建索引在ind表空间内

Index created.

SCOTT@PROD2> ho rm /u01/app/oracle/oradata/PROD2/ind.dbf

SCOTT@PROD2> conn / as sysdba
Connected.
SYS@PROD2> alter system switch logfile;

System altered.

SYS@PROD2> /

System altered.

SYS@PROD2> /
ERROR:
ORA-03114: not connected to ORACLE


alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 26263
Session ID: 17 Serial number: 75


SYS@PROD2> conn / as sysdba
Connected to an idle instance.
SYS@PROD2> startup
ORACLE instance started.

Total System Global Area  958341120 bytes
Fixed Size		    1348972 bytes
Variable Size		  322964116 bytes
Database Buffers	  629145600 bytes
Redo Buffers		    4882432 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD2/ind.dbf'

SYS@PROD2> alter database datafile 6 offline drop;

Database altered.

SYS@PROD2> alter database open;  --删除数据文件数据库能正常开启

Database altered.

SYS@PROD2> conn scott/tiger 
Connected.
SCOTT@PROD2> insert into emp(empno,ename) values (11,'A11');   --插入索引相关列报错
insert into emp(empno,ename) values (11,'A11')
            *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD2/ind.dbf'

SCOTT@PROD2> select index_name from user_indexes where table_name='EMP';

INDEX_NAME
------------------------------
EMP_ENAME_I
PK_EMP

SCOTT@PROD2> select dbms_metadata.get_ddl('INDEX','EMP_ENAME_I') from dual;

DBMS_METADATA.GET_DDL('INDEX','EMP_ENAME_I')
--------------------------------------------------------------------------------

  CREATE INDEX "SCOTT"."EMP_ENAME_I" ON "SCOTT"."EMP" ("ENAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "IND"
  
SCOTT@PROD2> drop index emp_ename_i;  --删除旧索引

Index dropped.

SCOTT@PROD2> CREATE INDEX "SCOTT"."EMP_ENAME_I" ON "SCOTT"."EMP" ("ENAME") TABLESPACE USERS;  --重建索引指定新表空间

Index created.

SCOTT@PROD2> insert into emp(empno,ename) values (11,'AA');  --插入成功

1 row created.

SCOTT@PROD2> conn / as sysdba
Connected.
SYS@PROD2> drop tablespace ind;

Tablespace dropped.

只有在索引表空间中包括索引才能这样恢复，如果存在数据需要通过备份进行介质恢复。