--丢失索引表空间恢复
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.
只有在索引表空间中包括索引才能这样恢复,如果存在数据需要通过备份进行介质恢复。