oracle数据库修复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>
