mysql indentify_ORA-01157: cannot identify/lock data file导致表空间无法on

朋友和我聊到一个案例,一个表空间本来有一个数据文件(已经有大量数据进入),然后他加入7个数据文件,这个时候他发现他加多了文件,想删除这些数据文件,可是又不怎么懂数据库,直接offline 表空间,然后rm掉相关数据文件,最后表空间无法online.我对其场景进行了模

朋友和我聊到一个案例,一个表空间本来有一个数据文件(已经有大量数据进入),然后他加入7个数据文件,这个时候他发现他加多了文件,想删除这些数据文件,可是又不怎么懂数据库,直接offline 表空间,然后rm掉相关数据文件,最后表空间无法online.我对其场景进行了模拟恢复测试,使用bbed和dul对其进行恢复

模拟场景

表空间有一个数据文件,里面有数据,然后加入一个新数据文件,进入offline tbs,然后rm数据文件

offline表空间后,该表空间存在的数据文件也会offline

SQL> create tablespace xifenfei datafile

2 '/u01/oracle/ora9i/oradata/xifenfei01.dbf' size 10M autoextend on next 16M;

Tablespace created.

SQL> create table chf.t_xifenfei01 tablespace xifenfei

2 as select * from dba_objects;

Table created.

SQL> create table chf.t_xifenfei02 tablespace xifenfei

2 as select * from dba_tables;

Table created.

SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI01;

COUNT(*)

----------

30758

SQL> C/01/02

1* SELECT COUNT(*) FROM CHF.T_XIFENFEI02

SQL> /

COUNT(*)

----------

865

SQL> select tablespace_name,segment_name from dba_segments

2 where segment_name like 'T_XIFENFEI%';

TABLESPACE_NAME SEGMENT_NAME

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

XIFENFEI T_XIFENFEI01

XIFENFEI T_XIFENFEI02

SQL> ALTER tablespace xifenfei add datafile

2 '/u01/oracle/ora9i/oradata/xifenfei02.dbf' size 16m;

Tablespace altered.

SQL> alter tablespace xifenfei offline;

Tablespace altered.

SQL> select file#,status$ from file$;

FILE# STATUS$

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

1 2

2 2

3 2

4 2

5 2

6 2

7 2

8 2

9 2

10 2

11 2

12 2

12 rows selected.

SQL> set pages 1000

SQL> /

FILE# STATUS$

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

1 2

2 2

3 2

4 2

5 2

6 2

7 2

8 2

9 2

10 2

11 2

12 2

12 rows selected.

SQL> select file#,status from v$datafile;

FILE# STATUS

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

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 ONLINE

7 ONLINE

8 ONLINE

9 ONLINE

10 ONLINE

11 OFFLINE

12 OFFLINE

12 rows selected.

SQL> select file#,status from v$datafile_header;

FILE# STATUS

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

1 ONLINE

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 ONLINE

7 ONLINE

8 ONLINE

9 ONLINE

10 ONLINE

11 OFFLINE

12 OFFLINE

12 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

CWMLITE ONLINE

DRSYS ONLINE

EXAMPLE ONLINE

INDX ONLINE

ODM ONLINE

TOOLS ONLINE

USERS ONLINE

XDB ONLINE

XIFENFEI OFFLINE

12 rows selected.

SQL> select ts#,name,online$ from ts$;

TS# NAME ONLINE$

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

0 SYSTEM 1

1 UNDOTBS1 1

2 TEMP 1

3 CWMLITE 1

4 DRSYS 1

5 EXAMPLE 1

6 INDX 1

7 ODM 1

8 TOOLS 1

9 USERS 1

10 XDB 1

11 UNDOTBS2 3

12 XIFENFEI 2

13 rows selected.

SQL> col name for a50

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /u01/oracle/ora9i/oradata/ora9i/system01.dbf

2 /u01/oracle/ora9i/oradata/ora9i/undotbs01.dbf

3 /u01/oracle/ora9i/oradata/ora9i/cwmlite01.dbf

4 /u01/oracle/ora9i/oradata/ora9i/drsys01.dbf

5 /u01/oracle/ora9i/oradata/ora9i/example01.dbf

6 /u01/oracle/ora9i/oradata/ora9i/indx01.dbf

7 /u01/oracle/ora9i/oradata/ora9i/odm01.dbf

8 /u01/oracle/ora9i/oradata/ora9i/tools01.dbf

9 /u01/oracle/ora9i/oradata/ora9i/users01.dbf

10 /u01/oracle/ora9i/oradata/ora9i/xdb01.dbf