修复missing的datafile

  • Post author:
  • Post category:IT
  • Post comments:0评论

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

发表评论