问题描述
在 Oracle 数据库的 v$datafile 中，出现了包含 MISSING 字样的文件名，是怎么回事？应该如何处理
专家解答
在数据库中，异常情况下，你可能会看到某些文件带有MISSING的名称，以下和大家一起分析这个问题，并解决。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create tablespace eygle datafile '/u01/app/oracle/oradata/enmot1/eygle01.dbf' size 10M;
Tablespace created.
SQL> create table enmo tablespace eygle as select * from dba_users;
Table created.
生成重建控制文件的脚本：
SQL> alter database backup controlfile to trace;
Database altered.
SQL> select name,value from v$diag_info where name='Default Trace File';
NAME
----------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
Default Trace File
/u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_8511.trc
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ENMOT1" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4674
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/enmot1/redo01a.log',
'/u01/app/oracle/oradata/enmot1/redo01b.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u01/app/oracle/oradata/enmot1/redo02a.log',
'/u01/app/oracle/oradata/enmot1/redo02b.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u01/app/oracle/oradata/enmot1/redo03a.log',
'/u01/app/oracle/oradata/enmot1/redo03b.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/enmot1/system01.dbf',
'/u01/app/oracle/oradata/enmot1/sysaux01.dbf',
'/u01/app/oracle/oradata/enmot1/undotbs01.dbf',
'/u01/app/oracle/oradata/enmot1/users02.dbf',
'/u01/app/oracle/oradata/enmot1/rock01.dbf',
'/u01/app/oracle/oradata/enmot1/xxx.dbf',
'/u01/app/oracle/oradata/enmot1/minor.01.dbf',
'/u01/app/oracle/oradata/enmot1/shghserol01.dbf',
'/u01/app/oracle/oradata/enmot1/zdb.dbf',
'/u01/app/oracle/oradata/enmot1/appmon.dbf',
'/u01/app/oracle/oradata/enmot1/dbadata01.dbf',
'/u01/app/oracle/oradata/enmot1/data01.dbf',
'/u01/app/oracle/oradata/enmot1/data02.dbf',
'/u01/app/oracle/oradata/enmot1/data03.dbf',
'/u01/app/oracle/oradata/enmot1/data04.dbf',
'/u01/app/oracle/oradata/enmot1/data05.dbf',
'/u01/app/oracle/oradata/enmot1/data06.dbf',
'/u01/app/oracle/oradata/enmot1/data07.dbf',
'/u01/app/oracle/oradata/enmot1/data08.dbf',
'/u01/app/oracle/oradata/enmot1/users03.dbf',
'/u01/app/oracle/oradata/enmot1/undotbs02.dbf',
'/u01/app/oracle/oradata/enmot1/users04.dbf',
'/u01/app/oracle/oradata/enmot1/eygle01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch2/arch_standby/1_1_791199500.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch2/arch_standby/1_1_912362625.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- No tempfile entries found to add.
--
重建控制文件，如果我们去掉最后一个新增的文件，启动数据库之后：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 830967808 bytes
Fixed Size 2232840 bytes
Variable Size 742395384 bytes
Database Buffers 83886080 bytes
Redo Buffers 2453504 bytes
CREATE CONTROLFILE REUSE DATABASE "ENMOT1" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4674
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/enmot1/redo01a.log',
'/u01/app/oracle/oradata/enmot1/redo01b.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u01/app/oracle/oradata/enmot1/redo02a.log',
'/u01/app/oracle/oradata/enmot1/redo02b.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u01/app/oracle/oradata/enmot1/redo03a.log',
'/u01/app/oracle/oradata/enmot1/redo03b.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/enmot1/system01.dbf',
'/u01/app/oracle/oradata/enmot1/sysaux01.dbf',
'/u01/app/oracle/oradata/enmot1/undotbs01.dbf',
'/u01/app/oracle/oradata/enmot1/users02.dbf',
'/u01/app/oracle/oradata/enmot1/rock01.dbf',
'/u01/app/oracle/oradata/enmot1/xxx.dbf',
'/u01/app/oracle/oradata/enmot1/minor.01.dbf',
'/u01/app/oracle/oradata/enmot1/shghserol01.dbf',
'/u01/app/oracle/oradata/enmot1/zdb.dbf',
'/u01/app/oracle/oradata/enmot1/appmon.dbf',
'/u01/app/oracle/oradata/enmot1/dbadata01.dbf',
'/u01/app/oracle/oradata/enmot1/data01.dbf',
'/u01/app/oracle/oradata/enmot1/data02.dbf',
'/u01/app/oracle/oradata/enmot1/data03.dbf',
'/u01/app/oracle/oradata/enmot1/data04.dbf',
'/u01/app/oracle/oradata/enmot1/data05.dbf',
'/u01/app/oracle/oradata/enmot1/data06.dbf',
'/u01/app/oracle/oradata/enmot1/data07.dbf',
'/u01/app/oracle/oradata/enmot1/data08.dbf',
'/u01/app/oracle/oradata/enmot1/users03.dbf',
'/u01/app/oracle/oradata/enmot1/undotbs02.dbf',
'/u01/app/oracle/oradata/enmot1/users04.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 709395945 generated at 05/26/2016 10:36:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch2/arch_standby/1_8_912362625.arc
ORA-00280: change 709395945 for thread 1 is in sequence #8
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/enmot1/system01.dbf'
SQL> select member from v$logfile;
MEMBER
---------------------------------------------
/u01/app/oracle/oradata/enmot1/redo03a.log
/u01/app/oracle/oradata/enmot1/redo03b.log
/u01/app/oracle/oradata/enmot1/redo02a.log
/u01/app/oracle/oradata/enmot1/redo02b.log
/u01/app/oracle/oradata/enmot1/redo01a.log
/u01/app/oracle/oradata/enmot1/redo01b.log
6 rows selected.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 709395945 generated at 05/26/2016 10:36:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch2/arch_standby/1_8_912362625.arc
ORA-00280: change 709395945 for thread 1 is in sequence #8
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/enmot1/redo03a.log
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
这是数据库中会出现一个 MISSING 文件，也就是数据字典中存在，但是控制文件中丢失了，数据库自动增加一个MISSING的命名：
SQL> col name for a120
SQL> select name from v$datafile where name like '%MISSING%';
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00023
Thu May 26 10:42:15 2016
SMON: enabling cache recovery
Incremental checkpoint up to RBA [0x1.3.0], current log tail at RBA [0x1.3.0]
ARCt: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[9722] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2296415402 end:2296416042 diff:640 (6 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_TEST' #13 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_TEST02' #14 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_TEST03' #15 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'EYGLE' #27 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Thu May 26 10:42:21 2016
File #23 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00023' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
*********************************************************************SMON: enabling tx recovery
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
Empty temporary tablespace: TEMP_TEST
Empty temporary tablespace: TEMP_TEST02
Empty temporary tablespace: TEMP_TEST03
*********************************************************************
Database Characterset is ZHS16GBK
Opening with Resource Manager plan: MIXED_WORKLOAD_PLAN
Thu May 26 10:42:22 2016
Starting background process VKRM
Thu May 26 10:42:22 2016
VKRM started with pid=59, OS id=9875
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu May 26 10:42:23 2016
QMNC started with pid=60, OS id=9877
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
通过重命名，恢复，如果日志齐全，可以将文件重新加回到数据库中：
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- -----------------------
23 OFFLINE OFFLINE FILE MISSING 0
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00023' to '/u01/app/oracle/oradata/enmot1/eygle01.dbf';
dbf';
Database altered.
SQL> recover tablespace eygle;
ORA-00279: change 709395945 generated at 05/26/2016 10:36:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch2/arch_standby/1_8_912362625.arc
ORA-00280: change 709395945 for thread 1 is in sequence #8
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/enmot1/1_8_912362625.arc
Log applied.
Media recovery complete.
SQL> alter tablespace eygle online;
Tablespace altered.
SQL> select count(*) from enmo;
COUNT(*)
----------
59
这就是这种情况出现的原因，和可能的处理办法。
