在日常的生常中索引表空间通常不作备份,原因:数据表空间大,索引表空间也跟着大,数据量不少,备份艰难,降低系统效率 SQL> create tablespace test_index 2 datafile '/u01/disk1/test_index.dbf' 3 size 2M; Tablespace created. SQL> create index index_1 on scott.dept(dname) tablespace test_index nologging;——用nologging参数,不走redo操作,建索引会快些,提高了系统效率,因为也不想用备份来恢复它们,我们想用重建它们,因此也没必要记录, 提示:最好把创建索引表空间和创建索引的命令单独保存在一个脚步中,方便以后恢复时,一跑脚步就OK,不用手工敲代码 Index created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> ho ls /u01/disk1/test_index.dbf /u01/disk1/test_index.dbf SQL> ho rm -f /u01/disk1/test_index.dbf SQL> ho ls /u01/disk1/test_index.dbf ls: /u01/disk1/test_index.dbf: No such file or directory SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 62916852 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/u01/disk1/test_index.dbf' SQL> alter database datafile 7 offline; alter database datafile 7 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL> alter database datafile 7 offline drop; Database altered. SQL> alter database open; Database altered. SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE TEST_UNDO ONLINE TEST_INDEX ONLINE 虽然删除了,但是显示还是online SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS from user_indexes 2 where INDEX_NAME = 'INDEX_1'; INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS --------------- ------------- --------------- -------- INDEX_1 DEPT TEST_INDEX VALID ——还是显示可用 SQL> select a.FILE_ID,a.FILE_NAME,b.STATUS 2 from dba_data_files a,v$datafile b 3 where a.FILE_ID = b.file#; FILE_ID FILE_NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE 3 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE 4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE 5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE 6 /u01/disk1/test_undo.dbf ONLINE 7 /u01/disk1/test_index.dbf OFFLINE SQL> drop tablespace TEST_INDEX including contents; Tablespace dropped. SQL> select a.FILE_ID,a.FILE_NAME,b.STATUS 2 from dba_data_files a,v$datafile b 3 where a.FILE_ID = b.file#; FILE_ID FILE_NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE 3 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE 4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE 5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE 6 /u01/disk1/test_undo.dbf ONLINE 查看验证了TEST_INDEX被真正地删掉了 重建表可空间和索引 SQL> create tablespace test_index 2 datafile '/u01/disk1/test_index.dbf' 3 size 2M; Tablespace created. SQL> create index index_1 on scott.dept(dname) tablespace test_index nologging; Index created. SQL> select a.FILE_ID,a.FILE_NAME,b.STATUS 2 from dba_data_files a,v$datafile b 3 where a.FILE_ID = b.file#; FILE_ID FILE_NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE 3 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE 4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE 5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE 6 /u01/disk1/test_undo.dbf ONLINE 7 /u01/disk1/test_index.dbf ONLINE ——显示回来了 7 rows selected. 总结: 基本思路:模拟丢失,如果不是重要的数据表空间(例如:临时表空间,索引表空间)可以用offline drop ,然后startup后重建即可,如果有关键数据的表空间,那就要recover了
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26465805/viewspace-712597/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26465805/viewspace-712597/