« 警惕:Oracle中删除的分区不会进入回收站(Recyclebin) | Blog首页 | 在Mac上通过Docker部署Oracle Database 12.2版本 »
Oracle数据库中 MISSING 文件名称的出现和处理
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2017/05/oracle_missing_file_recovery.html
链接:https://www.eygle.com/archives/2017/05/oracle_missing_file_recovery.html
在数据库中,异常情况下,你可能会看到某些文件带有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文章:
>> 2007-05-17文章:
>> 2006-05-17文章:
By eygle on 2017-05-17 18:49 | Comments (0) | Backup&Recovery | 3251 |