修复missing的datafile

在一次迁移中，原来的数据库中存在一些missing的datafile，如MISSING00006这样的datafile，这些数据文件经查已经在os上不存在，且该数据文件上的信息也已经不需要。一般情况下，我们是将仍旧需要表从这个表空间move到另外的表空间，再将这整个表空间drop掉。但是由于表空间中的对象很多，依赖关系复杂，且missing的表空间只是少数，所以可以用下面的方法清理掉。

注：

1. 该方法是次选，首选应该是drop表空间的方法。

2. 该方法适合非undo的datafile missing

3. 建议测试环境使用。

–发现数据文件中有missing的datafile，见下面的MISSING00006和MISSING00007

SQL> select tablespace_name,file_name,STATUS,ONLINE_status from dba_data_files order by 1;

TABLESPACE_NAME FILE_NAME STATUS ONLINE_

—————————— ———————————————————— ——— ——-

SYSAUX /u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf AVAILABLE ONLINE

SYSTEM /u01/ora10g/app/oracle/oradata/ora10g/system01.dbf AVAILABLE SYSTEM

TEST /u01/ora10g/app/oracle/oradata/ora10g/test01.dbf AVAILABLE ONLINE

TEST /u01/ora10g/app/oracle/product/10.2.0/db_1/dbs/MISSING00006 AVAILABLE RECOVER

TEST /u01/ora10g/app/oracle/product/10.2.0/db_1/dbs/MISSING00007 AVAILABLE RECOVER

UNDOTBS1 /u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf AVAILABLE ONLINE

USERS /u01/ora10g/app/oracle/oradata/ora10g/users01.dbf AVAILABLE ONLINE

7 rows selected.

–在v$datafile中也能看到：

SQL> select FILE#,name,STATUS,ENABLED from v$datafile;

   FILE# NAME STATUS ENABLED

———- ———————————————————— ——- ———-

   1 /u01/ora10g/app/oracle/oradata/ora10g/system01.dbf SYSTEM READ WRITE

   2 /u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf ONLINE READ WRITE

   3 /u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf ONLINE READ WRITE

   4 /u01/ora10g/app/oracle/oradata/ora10g/users01.dbf ONLINE READ WRITE

   5 /u01/ora10g/app/oracle/oradata/ora10g/test01.dbf ONLINE READ WRITE

   6 /u01/ora10g/app/oracle/product/10.2.0/db_1/dbs/MISSING00006 RECOVER READ WRITE

   7 /u01/ora10g/app/oracle/product/10.2.0/db_1/dbs/MISSING00007 RECOVER READ WRITE

7 rows selected.

— 下面，我们来开始清理，先清除数据字典基表的信息。清理完成后，在v$datafile中就会没有。

SQL> delete file$ where file#=6;

1 row deleted.

SQL> delete file$ where file#=7;

1 row deleted.

SQL> commit;

Commit complete.

–虽然上述步骤使得在v$datafile中信息没有了，但是在dba_data_files中还会存在该信息，所以我们重建控制文件：

SQL> alter database backup controlfile to trace as ‘/tmp/cfile.111’;

Database altered.

SQL>

SQL>

SQL>

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

[oracle10g@testdb oracle]$

[oracle10g@testdb oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.5.0 – Production on Thu Jul 17 03:44:02 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1191182336 bytes

Fixed Size 2095832 bytes

Variable Size 369100072 bytes

Database Buffers 805306368 bytes

Redo Buffers 14680064 bytes

SQL>

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 ‘/u01/ora10g/app/oracle/oradata/ora10g/redo01.log’ SIZE 50M,

9 GROUP 2 ‘/u01/ora10g/app/oracle/oradata/ora10g/redo02.log’ SIZE 50M,

10 GROUP 3 ‘/u01/ora10g/app/oracle/oradata/ora10g/redo03.log’ SIZE 50M

11 DATAFILE

12 ‘/u01/ora10g/app/oracle/oradata/ora10g/system01.dbf’,

13 ‘/u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf’,

14 ‘/u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf’,

15 ‘/u01/ora10g/app/oracle/oradata/ora10g/users01.dbf’,

16 ‘/u01/ora10g/app/oracle/oradata/ora10g/test01.dbf’

17 CHARACTER SET AL32UTF8

18 ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

–可以看到已经消失了：

SQL> select tablespace_name,file_name,STATUS,ONLINE_status from dba_data_files order by 1;

TABLESPACE_NAME FILE_NAME STATUS ONLINE_

—————————— ———————————————————— ——— ——-

SYSAUX /u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf AVAILABLE ONLINE

SYSTEM /u01/ora10g/app/oracle/oradata/ora10g/system01.dbf AVAILABLE SYSTEM

TEST /u01/ora10g/app/oracle/oradata/ora10g/test01.dbf AVAILABLE ONLINE

UNDOTBS1 /u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf AVAILABLE ONLINE

USERS /u01/ora10g/app/oracle/oradata/ora10g/users01.dbf AVAILABLE ONLINE

SQL> select FILE#,name,STATUS,ENABLED from v$datafile;

   FILE# NAME STATUS ENABLED

———- ———————————————————— ——- ———-

   1 /u01/ora10g/app/oracle/oradata/ora10g/system01.dbf SYSTEM READ WRITE

   2 /u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf ONLINE READ WRITE

   3 /u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf ONLINE READ WRITE

   4 /u01/ora10g/app/oracle/oradata/ora10g/users01.dbf ONLINE READ WRITE

   5 /u01/ora10g/app/oracle/oradata/ora10g/test01.dbf ONLINE READ WRITE

–添加新的数据文件也是ok的：

SQL> alter tablespace test add datafile ‘/u01/ora10g/app/oracle/oradata/ora10g/test02_b.dbf’ size 1m;

Tablespace altered.

SQL>

SQL> alter tablespace test add datafile ‘/u01/ora10g/app/oracle/oradata/ora10g/test03.dbf’ size 1m;

Tablespace altered.

SQL>

SQL> select tablespace_name,file_name,STATUS,ONLINE_status from dba_data_files order by 1;

TABLESPACE_NAME FILE_NAME STATUS ONLINE_

—————————— ———————————————————— ——— ——-

SYSAUX /u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf AVAILABLE ONLINE

SYSTEM /u01/ora10g/app/oracle/oradata/ora10g/system01.dbf AVAILABLE SYSTEM

TEST /u01/ora10g/app/oracle/oradata/ora10g/test01.dbf AVAILABLE ONLINE

TEST /u01/ora10g/app/oracle/oradata/ora10g/test02_b.dbf AVAILABLE ONLINE

TEST /u01/ora10g/app/oracle/oradata/ora10g/test03.dbf AVAILABLE ONLINE

UNDOTBS1 /u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf AVAILABLE ONLINE

USERS /u01/ora10g/app/oracle/oradata/ora10g/users01.dbf AVAILABLE ONLINE

7 rows selected.

SQL> select FILE#,name,STATUS,ENABLED from v$datafile;

   FILE# NAME STATUS ENABLED

———- ———————————————————— ——- ———-

   1 /u01/ora10g/app/oracle/oradata/ora10g/system01.dbf SYSTEM READ WRITE

   2 /u01/ora10g/app/oracle/oradata/ora10g/undotbs01.dbf ONLINE READ WRITE

   3 /u01/ora10g/app/oracle/oradata/ora10g/sysaux01.dbf ONLINE READ WRITE

   4 /u01/ora10g/app/oracle/oradata/ora10g/users01.dbf ONLINE READ WRITE

   5 /u01/ora10g/app/oracle/oradata/ora10g/test01.dbf ONLINE READ WRITE

   6 /u01/ora10g/app/oracle/oradata/ora10g/test02_b.dbf ONLINE READ WRITE

   7 /u01/ora10g/app/oracle/oradata/ora10g/test03.dbf ONLINE READ WRITE

7 rows selected.

SQL>