Example of Fixing missing datafile in oracle Database

During a migration, some missing datafiles, such as MISSING00006, exist in the original database. These data files have not been found on the OS, and the information on the data file is no longer required. In general, we move the table from this tablespace to another tablespace, and then drop the entire tablespace. However, because there are many objects in the tablespace, the dependency is complex, and the number of missing tablespaces is only a few, you can use the following method to clear them.

Note:

1. This method is a secondary selection. The preferred method is to drop the tablespace.
2. This method is suitable for non-undo datafile missing.
3. We recommend that you use the test environment.
-- If the data file contains missing datafile, see MISSING00006 and MISSING00007 below.
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.
 
-- You can also see in 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.
 
 
-- Next, we will clean up the data dictionary base table first. After cleaning, it will not exist in v $ datafile.
SQL> delete file $ where file # = 6;
 
1 row deleted.
 
SQL> delete file $ where file # = 7;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
 
-- Although the above steps make the information in v $ datafile disappear, this information still exists in dba_data_files, so we recreate the control file:
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,201 0, 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 50 M,
9 GROUP 2'/u01/ora10g/app/oracle/oradata/ora10g/redo02.log 'SIZE 50 M,
10 GROUP 3'/u01/ora10g/app/oracle/oradata/ora10g/redo03.log 'SIZE 50 M
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.
 
 
-- We can see that it has disappeared:
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
 
-- It is also OK to add a new data file:
SQL> alter tablespace test add datafile '/u01/ora10g/app/oracle/oradata/ora10g/test02_ B .dbf' size 1 m;
 
Tablespace altered.
 
SQL>
SQL> alter tablespace test add datafile '/u01/ora10g/app/oracle/oradata/ora10g/test03.dbf' size 1 m;
 
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>