ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 生产中索引表空间的恢复策略

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

原创 Linux操作系统 作者:oracleclub 时间:2011-12-02 09:06:18 0 删除 编辑
在日常的生常中索引表空间通常不作备份,原因:数据表空间大,索引表空间也跟着大,数据量不少,备份艰难,降低系统效率

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 反向键索引
请登录后发表评论 登录
全部评论