oracle数据库发现,Oracle数据库中 MISSING 文件名称的出现和处理

最新推荐文章于 2022-07-16 13:51:54 发布
最新推荐文章于 2022-07-16 13:51:54 发布
阅读量829 收藏 1
点赞数
文章标签： oracle数据库发现

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

6ee5639a40442445944d63b514b2dd02.png

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

CORE11.2.0.3.0Production

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 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

简单的测试，仅供参考。

By eygle on 2017-05-17 18:49 |

Comments (0) |

Backup&Recovery | 3251 |

姜海船没船
关注
VS连接上oracle数据库,VS2015连接Oracle数据库的详细步骤
weixin_29447549的博客
04-11 1276
开发环境宿主机：Win10 + VS2015 + ODP.Net for VS2015 虚拟机：Win7 + Oracle 11g + 桥接配置ODP.Net首先下载 Oracle Developer Tools for Visual Studio 2015 ，下载此文件需要注册Oracle社区账号并接受相关的协议，此文件提供了以下组件：Oracle Developer Tools for Vis...
查看oracle日志文件的scn,检查点和oracle数据库的恢复（一）SCN
weixin_42515302的博客
04-03 1790
SCN号研究什么是scn(system change number)号scn号是ORACLE数据库一个内部时钟，是数据库的历史时间记录，用来衡量ORACLE事务(transaction)发生的顺序，是一个时间概念。 scn号不断增加，可以理解为直至无穷大。什么时候scn号会增加scn号会实时增加的，而且只会增加不会减少，反映数据库的更新历史。怎样查询目前系统最新的scn号SQL>select...
对于创建控制文件后MISSINGnnnn文件的处理
weixin_34289744的博客
11-07 117
1 MISSINGnnnn文件产生的原因数据字典file$中存在某个数据文件，而新创建控制文件时没有指定该数据文件，则数据库会在新的控制文件中创建一个该数据文件条目，文件名为MISSINGnnnn其中nnnn为十进制文件号2 对于missingnnnn数据文件的处理如果MISSSINGnnnn文件属于只读或者正常离线，则可通过重命名MISSINGnnnn文件为真实文件来进行处理。如果MISSING...
Oracle(miss right parenthesit)
u014227715的专栏
05-17 447
create table i_teacher_curriculum (    curriculumId         varchar2(30) not null,    i_teacher_id       varchar2(5) default '' not null,      i_teacher_name     varchar2(20) default '' not null ,
oracle disk missing,raid5的一块盘状态为missing
weixin_28982301的博客
04-09 619
查了一下：可能是盘坏了http://allfaq.org/forums/t/77172.aspxProblem.For some reason your raid card seems to think one of the disks in the raid 5 set is a "foreign" disk, this means the disk has probably failed an...
登录Oracle数据库报错：ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist
uz2mmt的博客
11-26 182
处理步骤： 1、在Oracle安装路径下找到\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora， 打开该文件，找到“(ADDRESS = (PROTOCOL = TCP)(HOST = **************)(PORT = 1521))”，并复制 2、在Oracle安装路径下查找\oracle\admin\orcl\p...
tomcat 应用Message file 'oracle.net.mesg.Message' is missing问题处理
热门推荐
csdn's blog
07-21 1万+
1，问题描述 同事说crm登录hang住了，tomcat后台应用报错如下： 2016-06-28 10:30:11,214 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0] WARN [com.mchange.v2.resourcepool.BasicResourcePool] - com.mchang...
Oracle数据库表定时备份表 表名动态拼接时间戳存储过程
01-06
1、备份Oracle数据库表时添加动态时间戳，解决了ORA-00922: missing or invalid option 错误。 2、备份表结构和数据，还备份索引、序列、触发器等对象，提高了备份速度。但需要手动备份原始表的序列。 3、备份策略是...
Oracle数据库表定时备份表 表名动态拼接时间戳存储过程-解决了ORA-00922
最新发布
05-17
1、备份Oracle数据库表时添加动态时间戳，解决了ORA-00922: missing or invalid option 错误和ORA-00922: 选项缺失或无效错误。 2、备份表结构和数据，还备份索引、序列、触发器等对象，提高了备份速度。但需要手动...
oracledb_exporter：Prometheus Oracle数据库导出器
02-03
Oracle数据库导出器 目录 描述 Oracle的导出器以MySQL导出器为模型。 我不是DBA或经验丰富的Go开发人员，因此PR绝对受欢迎。 当前公开以下指标。 oracledb_exporter_last_scrape_duration_seconds oracledb_...
Oracle安装时出现未找到文件错误时的解决办法
amazing_banana的博客
04-23 7406
Oracle安装的详细教程我给大家转载一个博主的文章写得特别清楚： https://blog.csdn.net/qq_43560721/article/details/88123730 也有相应的安装包下载链接~ 在我们安装Oracle时到最后一步安装大概在30%左右会出现找不到文件的错误 “F:\app\Administrator\product\11.2.0\dbhome_1\oc4...
IDEA Download missing driver files 下载失败解决方法
只为成功找方法 不为失败找借口
07-16 7738
idea
oracle数据库链接报错
qh865448624的博客
05-10 1552
Tomcat 7 服务器发布项目时，显示java.sql.SQLException: Io 异常: Message file 'oracle.net.mesg.Message' is missing. PL/SQL也无法链接 经查得知为数据了process数以达上限，需要扩增。
ORA-00931: missing identifier ORA-06512: at "SYS.DBMS_UTILITY"
weixin_30677475的博客
11-27 2323
Database db = DatabaseFactory.CreateDatabase(); string sql = "SELECT * FROM table";DbCommand cmd = db.GetSqlStringCommand(sql);//添加这句就不会出现错误 DataSet ds = db.ExecuteDa...
删除 oracle EXAMPLE,oracle删除被遗漏掉的数据文件MISSING00005 --example表空间
weixin_33594621的博客
04-07 346
今天查看表空间发现个MISSING00005数据文件--->原因：编辑控制文件的时候被遗漏掉的数据文件(example表空间)1) select file#,status,name from v$datafile;file#statusname--------------------------------------------------------------------1SY...
oracle missing00007,探索Oracle之RMAN_07整个业务表空间丢失恢复
weixin_28683165的博客
04-13 186
2.5恢复方法二，离线恢复开始执行恢复操作，分为五个步骤：1、强制将数据库启动到mount状态SQL> startupforce mount;Oracle instancestarted.Total SystemGlobal Area 285212672 bytesFixed Size 1218968 bytesVariable Size...
oracle数据库如何导入ifx文件
06-06
要将IFX文件导入Oracle数据库，您需要使用External Tables功能。这是一种将外部数据源（如文本文件）映射到Oracle表的方法。 以下是一些步骤，可用于将IFX文件导入Oracle数据库： 1. 确保您的Oracle数据库中启用了...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值