生产中索引表空间的恢复策略

在日常的生常中索引表空间通常不作备份，原因：数据表空间大，索引表空间也跟着大，数据量不少，备份艰难，降低系统效率

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/