丢失索引表空间恢复

来源：互联网发布：网络黑白花无涯下载编辑：程序博客网时间：2024/04/30 04:00
--丢失索引表空间恢复
SYS@PROD2> create tablespace ind datafile '/u01/app/oracle/oradata/PROD2/ind.dbf' size 10m;Tablespace created.SYS@PROD2> conn scott/tigerConnected.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.dbfSCOTT@PROD2> conn / as sysdbaConnected.SYS@PROD2> alter system switch logfile;System altered.SYS@PROD2> /System altered.SYS@PROD2> /ERROR:ORA-03114: not connected to ORACLEalter system switch logfile*ERROR at line 1:ORA-03113: end-of-file on communication channelProcess ID: 26263Session ID: 17 Serial number: 75SYS@PROD2> conn / as sysdbaConnected to an idle instance.SYS@PROD2> startupORACLE instance started.Total System Global Area  958341120 bytesFixed Size    1348972 bytesVariable Size  322964116 bytesDatabase Buffers  629145600 bytesRedo Buffers    4882432 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 6 - see DBWR trace fileORA-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 timeORA-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_IPK_EMPSCOTT@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 sysdbaConnected.SYS@PROD2> drop tablespace ind;Tablespace dropped.只有在索引表空间中包括索引才能这样恢复，如果存在数据需要通过备份进行介质恢复。
0 0