oracle备份能备份索引吗,【备份恢复】恢复丢失的索引表空间

1.创建测试表空间ts_idx

SYS@PROD1>create tablespace ts_idx datafile '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf' size 10m

2  autoextend on;

Tablespace created.

2.创建测试表，创建索引且使用ts_idx

SCOTT@PROD1>create table t1(x int) tablespace users;

Table created.

SCOTT@PROD1>insert into t1 select rownum from dual connect by rownum<=10;

10 rows created.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>alter table t1 add constraint pk_t1 primary key(x) using index tablespace ts_idx;

Table altered.

3.验证索引表空间已使用(status字段表示非分区索引是否失效或无法使用)

SCOTT@PROD1>select INDEX_NAME,STATUS,TABLESPACE_NAME from user_indexes where table_name='T1';

INDEX_NAME                     STATUS   TABLESPACE_NAME

------------------------------ -------- ------------------------------

PK_T1                          VALID    TS_IDX

4.删除ts_idx表空间的数据文件

SCOTT@PROD1>! rm /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf

SCOTT@PROD1>! ls /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf

ls: /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf: No such file or directory

5.此时数据库已无法一致性关闭，只能强制关闭

SYS@PROD1>shutdown immediate;

ORA-01116: error in opening database file 8

ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SYS@PROD1>select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SYS@PROD1>shutdown abort;

ORACLE instance shut down.

6.将数据库启动到mount模式下

SYS@PROD1>startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             528485456 bytes

Database Buffers          301989888 bytes

Redo Buffers                2371584 bytes

Database mounted.

7.将丢失的数据文件offline

SYS@PROD1>alter database datafile 8 offline;

Database altered.

8.打开数据库

SYS@PROD1>alter database open;

Database altered.

9.查看索引的状态，显然这个状态是不对的

SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';

INDEX_NAME                     STATUS   TABLESPACE_NAME

------------------------------ -------- ------------------------------

PK_T1                          VALID    TS_IDX

10.创建一个新的表空间，用于存放索引，替代丢失了数据文件的ts_idx表空间

SYS@PROD1>create tablespace ts_idx_2 datafile '/u01/app/oracle/oradata/PROD1/ts_idx02.dbf' size 10m

2  autoextend on;

Tablespace created.

11.将索引PK_T1重建到ts_idx_1上

SYS@PROD1>alter index scott.pk_t1 rebuild tablespace ts_idx_2;

alter index scott.pk_t1 rebuild tablespace ts_idx_2

*

ERROR at line 1:

ORA-00376: file 8 cannot be read at this time

ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf'

在重建时，会对丢失的表空间进行读操作

12.只能将索引手动改为不可用状态

SYS@PROD1>alter index scott.PK_T1 unusable;

Index altered.

SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';

INDEX_NAME                     STATUS   TABLESPACE_NAME

------------------------------ -------- ------------------------------

PK_T1                          UNUSABLE TS_IDX

13.再次rebuild到新的表空间

SYS@PROD1>alter index scott.pk_t1 rebuild tablespace ts_idx_2;

Index altered.

SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';

INDEX_NAME                     STATUS   TABLESPACE_NAME

------------------------------ -------- ------------------------------

PK_T1                          VALID    TS_IDX_2

14.删除丢失数据文件的ts_idx表空间

SYS@PROD1>drop tablespace ts_idx;

Tablespace dropped.

注如果数据量比较大，创建索引会是非常漫长的一个过程，通过PARALLEL和NOLOGGING可以减少创建时间