在一次迁移中，原来的数据库中存在一些missing的datafile，如MISSING00006这样的datafile，这些数据文件经查已经在os上不存在，且该数据文件上的信息也已经不需要。一般情况下，我们是将仍旧需要表从这个表空间move到另外的表空间，再将这整个表空间drop掉。但是由于表空间中的对象很多，依赖关系复杂，且missing的表空间只是少数，所以可以用下面的方法清理掉。
注：
|
1 2 3 |
1. 该方法是次选，首选应该是drop表空间的方法。 2. 该方法适合非undo的datafile missing 3. 建议测试环境使用。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
--发现数据文件中有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> |