关于非归档下OFFLINE数据文件如何处理？
OS：AIX 5.3DB:10.2.0.4 单实例
问题描述：
前几天发现A库表空间达到阈值，在新的/oradata目录添加了一个数据文件。后来/oradata所挂的存储出现问题了。
创建在/oradata的23号数据文件无法访问，后台大量ORA错误。
因为库不是很重要，所以就offline drop 23号数据文件。
问题：
23号数据文件现在读取（OS级）可能会有问题，数据恢复可能很难。这时候我该如何解决这个问题呢？
指点一二，不胜感激！ 库已经跑了几天了，没有归档 只能exp tablespace 后重建表空间吗？ FYI
这是一种处理方法，但我们并不建议这么做：
在一次迁移中，原来的数据库中存在一些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>
$
$ 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>
from http://www.oracleblog.org/working-case/fix-missing-datafile/
页:
[1]