修复missing的datafile

小荷 2014-09-08 16:45

在一次迁移中,原来的数据库中存在一些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>

[返回] [原文链接]