索引表空间损坏的恢复（无RMAN备份集、热备份）

有时候为了考虑数据库的I/O性能，我们可能会考虑把索引表空间和数据表空间分开存放。索引被删除后，影响的仅仅是数据库的性能（原来走索引的执行计划，只能走全表扫描了），但不会使数据库丢数据，因此，只要重建索引即可，问题不大。那么问题来了，如果忘记了之前创建索引用到的语句，该怎么恢复索引呢？而且是在没有RMAN备份集、热备份的情况下

--创建索引表空间

SQL> create tablespace indx datafile '/u01/app/oracle/oradata/ora10g/indx01.dbf' size 50m;

Tablespace created.

--在emp表上创建2个索引

SQL> create index ind_no on emp(empno) tablespace indx;

Index created.

SQL> create index ind_name on emp(ename) tablespace indx;

Index created.

SQL> col segment_name for a15

SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='INDX'; 

SEGMENT_NAME    SEGMENT_TYPE       OWNER

--------------- ------------------ ------------------------------

IND_NO          INDEX              ZLM

IND_NAME        INDEX              ZLM

--破坏表空间数据文件

SQL> !

[oracle@ora10g ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/indx01.dbf

[oracle@ora10g ~]$ cat /u01/app/oracle/oradata/ora10g/indx01.dbf

abc

efg

hij

--提取元数据

SQL> set long 10000

SQL> select dbms_metadata.get_ddl('INDEX','IND_NO','ZLM') from dual;

DBMS_METADATA.GET_DDL('INDEX','IND_NO','ZLM')

--------------------------------------------------------------------------------

  CREATE INDEX "ZLM"."IND_NO" ON "ZLM"."EMP" ("EMPNO")

  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)

  TABLESPACE "INDX"

SQL> select dbms_metadata.get_ddl('INDEX','IND_NAME','ZLM') from dual;

DBMS_METADATA.GET_DDL('INDEX','IND_NAME','ZLM')

--------------------------------------------------------------------------------

  CREATE INDEX "ZLM"."IND_NAME" ON "ZLM"."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)

  TABLESPACE "INDX"

注意，虽然文件坏了，但仍然能找到元数据，因为元数据存在于 system 表空间的字典中，而没有存在 indx表空间

--删除损坏的索引表空间

SQL> drop tablespace indx including contents and datafiles;

drop tablespace indx including contents and datafiles

*

ERROR at line 1:

ORA-01122: database file 7 failed verification check

ORA-01110: data file 7: '/u01/app/oracle/oradata/ora10g/indx01.dbf'

ORA-01251: Unknown File Header Version read for file number 7

由于文件还是online状态，不能直接删除表空间，先把文件offline

SQL> alter database datafile 7 offline;

Database altered.

SQL> drop tablespace indx including contents and datafiles;

Tablespace dropped.

--重建索引表空间

SQL> create tablespace indx2 datafile '/u01/app/oracle/oradata/ora10g/indx02.dbf' size 20m;

Tablespace created.

--重建之前的索引

注意：一定要先提取元数据再删除损坏的表空间，因为表空间删除以后，元数据也被删除了

SQL>   CREATE INDEX "ZLM"."IND_NO" ON "ZLM"."EMP" ("EMPNO")

  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  5    TABLESPACE "INDX2";

Index created.

SQL> CREATE INDEX "ZLM"."IND_NAME" ON "ZLM"."EMP" ("ENAME")

  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  5    TABLESPACE "INDX2";

Index created.

这里重新创建的索引表空间为INDX2

SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='INDX2'; 

SEGMENT_NAME    SEGMENT_TYPE       OWNER

--------------- ------------------ ------------------------------

IND_NO          INDEX              ZLM

IND_NAME        INDEX              ZLM

--查看数据库中的表空间和数据文件

SQL> select file_name,tablespace_name,bytes/1024/1024 as "size(M)" from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME    size(M)

--------------------------------------------- --------------- ----------

/u01/app/oracle/oradata/ora10g/zlm01.dbf      ZLM                     50

/u01/app/oracle/oradata/ora10g/example01.dbf  EXAMPLE                100

/u01/app/oracle/oradata/ora10g/users01.dbf    USERS                   40

/u01/app/oracle/oradata/ora10g/sysaux01.dbf   SYSAUX                 270

/u01/app/oracle/oradata/ora10g/undotbs01.dbf  UNDOTBS1               165

/u01/app/oracle/oradata/ora10g/system01.dbf   SYSTEM                 560

/u01/app/oracle/oradata/ora10g/indx02.dbf     INDX2                   20

7 rows selected.

由于保留了之前索引的元数据，我们从数据字典中获取了元数据，然后再新建的索引表空间中重建索引，这是恢复索引表空间的另一种思路，当然，如果在有RMAN备份集、热备份的情况下，也可以直接对datafile 7进行还原和恢复操作，可以参考之前的博客内容