eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 警惕:Oracle中删除的分区不会进入回收站(Recyclebin) | Blog首页 | 在Mac上通过Docker部署Oracle Database 12.2版本 »

Oracle数据库中 MISSING 文件名称的出现和处理
modb.pro

在数据库中,异常情况下,你可能会看到某些文件带有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

简单的测试,仅供参考。


历史上的今天...
    >> 2012-05-17文章:
    >> 2008-05-17文章:
           Oracle10g新进程 MMON 和 MMNL
    >> 2007-05-17文章:
    >> 2006-05-17文章:

By eygle on 2017-05-17 18:49 | Comments (0) | Backup&Recovery | 3251 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com