有时候为了考虑数据库的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进行还原和恢复操作,可以参考之前的博客内容