索引表空間損壞的恢複（無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> !

[[email protected] ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/indx01.dbf [[email protected] ~]$ 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進行還原和恢複操作，可以參考之前的部落格内容