ORA-00376/ORA-01110 故障处理办法

最新推荐文章于 2025-06-27 14:56:47 发布
最新推荐文章于 2025-06-27 14:56:47 发布
阅读量1.6w 收藏 8
点赞数
本文记录了一次Oracle数据库中9号数据文件发生故障后的详细恢复过程，包括缺失归档日志的恢复、数据文件的介质恢复及SCN一致性检查。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

转自 http://www.linuxidc.com/Linux/2016-09/135227.htm?utm_source=tuicool&utm_medium=referral

Oracle数据库实例启动之后发现，9号数据文件发生故障，file 9 cannot be read at this time，

ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (8, 21) on object 66190.
Mon Aug 29 12:15:06 EAT 2016
Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '+ILN_DATA/ilndb/datafile/ilearn_indx1.dbf'
ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (338, 42) on object 66177.
Mon Aug 29 12:15:08 EAT 2016
Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '+ILN_DATA/ilndb/datafile/ilearn_indx1.dbf'
ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (339, 45) on object 66190.
Mon Aug 29 12:15:10 EAT 2016
Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '+ILN_DATA/ilndb/datafile/ilearn_indx1.dbf'
ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (340, 23) on object 66190.
Mon Aug 29 12:15:11 EAT 2016
Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '+ILN_DATA/ilndb/datafile/ilearn_indx1.dbf'
ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (341, 20) on object 66177.
Mon Aug 29 12:15:13 EAT 2016
Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '+ILN_DATA/ilndb/datafile/ilearn_indx1.dbf'

根据数据库日志的提示，发现9号数据发生故障，尝试介质恢复。

oracle@elndb1[ilndb2]:/home/oracle$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Aug 29 12:13:41 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ILNDB (DBID=140433242)

RMAN> recover datafile 9;

Starting recover at 29-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=6481 instance=ilndb2 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=6001 instance=ilndb2 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=6000 instance=ilndb2 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=5999 instance=ilndb2 devtype=DISK

starting media recovery

archive log thread 3 sequence 205311 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205311.4265.921154275
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/29/2016 12:14:34
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205310 lowscn 19240848472
RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147416 lowscn 19240848247
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151889 lowscn 19240848211
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151888 lowscn 19240845047
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205309 lowscn 19240844632
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205308 lowscn 19240742241
RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147415 lowscn 19240632693
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151887 lowscn 19240631896
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205307 lowscn 19240631896
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151886 lowscn 19240560360
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205306 lowscn 19240500521
RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147414 lowscn 19240439862
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151885 lowscn 19240308923
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205305 lowscn 19240301377
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151884 lowscn 19240124380
RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147413 lowscn 19240106434
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151883 lowscn 19239991180
RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205304 lowscn 19239911439

发现缺少需要的日志，应该已经完成备份，在磁盘组中删除了。
联系备份小组，对需要的日志进行恢复，恢复到原有目录。

run {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
SEND 'NSR_ENV=(NSR_SERVER=bksvr2p_svc,NSR_CLIENT=elndb1-vip)';
restore archivelog sequence between 151882 and 151890 thread 1;
restore archivelog sequence between 147312 and 147417 thread 2;
restore archivelog sequence between 205303 and 205311 thread 3;
release channel ch00;
release channel ch01;
}

日志恢复完毕之后，重新执行介质恢复

oracle@elndb1[ilndb2]:/home/oracle/product/admin/ilndb/bdump$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Aug 29 12:53:11 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ILNDB (DBID=140433242)

RMAN>  recover datafile 9;

Starting recover at 29-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=5707 instance=ilndb2 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=5706 instance=ilndb2 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=5705 instance=ilndb2 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=5704 instance=ilndb2 devtype=DISK

starting media recovery

archive log thread 1 sequence 151883 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151883.4080.921155791
archive log thread 1 sequence 151884 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151884.2542.921155791
archive log thread 1 sequence 151885 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151885.2136.921155791
archive log thread 1 sequence 151886 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151886.2115.921155815
archive log thread 1 sequence 151887 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151887.2496.921155815
archive log thread 1 sequence 151888 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151888.2593.921155817
archive log thread 1 sequence 151889 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151889.1397.921155817
archive log thread 1 sequence 151890 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151890.2469.921155571
archive log thread 2 sequence 147413 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147413.2362.921156057
archive log thread 2 sequence 147414 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147414.1828.921156073
archive log thread 2 sequence 147415 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147415.3869.921156067
archive log thread 2 sequence 147416 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147416.2487.921156077
archive log thread 2 sequence 147417 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147417.3331.921155015
archive log thread 2 sequence 147418 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147418.4018.921155341
archive log thread 2 sequence 147419 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147419.3104.921155879
archive log thread 3 sequence 205304 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205304.1826.921156603
archive log thread 3 sequence 205305 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205305.1558.921156603
archive log thread 3 sequence 205306 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205306.1981.921156605
archive log thread 3 sequence 205307 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205307.668.921156605
archive log thread 3 sequence 205308 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205308.632.921156605
archive log thread 3 sequence 205309 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205309.714.921156623
archive log thread 3 sequence 205310 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205310.1391.921156625
archive log thread 3 sequence 205311 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205311.4265.921154275
archive log thread 3 sequence 205312 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205312.3633.921155013
archive log thread 3 sequence 205313 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205313.2490.921155571
archive log thread 3 sequence 205314 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205314.868.921156127
archive log thread 3 sequence 205315 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205315.545.921156559
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147413.2362.921156057 thread=2 sequence=147413
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151883.4080.921155791 thread=1 sequence=151883
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205304.1826.921156603 thread=3 sequence=205304
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151884.2542.921155791 thread=1 sequence=151884
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205305.1558.921156603 thread=3 sequence=205305
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151885.2136.921155791 thread=1 sequence=151885
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147414.1828.921156073 thread=2 sequence=147414
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205306.1981.921156605 thread=3 sequence=205306
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151886.2115.921155815 thread=1 sequence=151886
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151887.2496.921155815 thread=1 sequence=151887
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205307.668.921156605 thread=3 sequence=205307
archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147415.3869.921156067 thread=2 sequence=147415
media recovery complete, elapsed time: 00:00:10
Finished recover at 29-AUG-16

9号数据文件介质恢复成功，查看文件状态为OFFLINE，将数据文件online

SQL> select STATUS from v$datafile where  TS# =9;

STATUS
---------------------
OFFLINE

SQL> 
SQL> 
SQL> 
SQL> alter database datafile 9 online;

Database altered.

检查数据库所有数据文件的scn是否一致

  FILE_NUM FILE_NAME                                          SCN                  STATUS   SEQUENCE
---------- -------------------------------------------------- ---------------- ---------- ----------
         1 +ILN_DATA/ilndb/datafile/system.256.7572           19241144540            8196     151891
         2 +ILN_DATA/ilndb/datafile/undotbs1.258.75           19241144540               4     151891
         3 +ILN_DATA/ilndb/datafile/sysaux.257.7572           19241144540               4     151891
         4 +ILN_DATA/ilndb/datafile/users.259.75726           19241144540               4     151891
         5 +ILN_DATA/ilndb/datafile/example.265.757           19241144540               4     151891
         6 +ILN_DATA/ilndb/datafile/undotbs2.266.75           19241144540               4     151891
         7 +ILN_DATA/ilndb/datafile/undotbs3.267.75           19241144540               4     151891
         8 +ILN_DATA/ilndb/datafile/ilearn_data1.db           19241144540               4     151891
         9 +ILN_DATA/ilndb/datafile/ilearn_indx1.db           19240106434               4     147413
        10 +ILN_DATA/ilndb/datafile/ilearn_data2.db           19241144540               4     151891
        11 +ILN_DATA/ilndb/datafile/ilearn_data3.db           19241144540               4     151891
        12 +ILN_DATA/ilndb/datafile/ilearn_data3.db           19241144540               4     151891
        13 +ILN_DATA/ilndb/datafile/ilearn_data4.db           19241144540               4     151891
        14 +ILN_DATA/ilndb/datafile/ilearn_data5.db           19241144540               4     151891
        15 +ILN_DATA/ilndb/datafile/ilearn_data6.db           19241144540               4     151891
        16 +ILN_DATA/ilndb/datafile/ilearn_data7.db           19241144540               4     151891
        17 +ILN_DATA/ilndb/datafile/ilearn_data8.db           19241144540               4     151891
        18 +ILN_DATA/ilndb/datafile/ilearn_data9.db           19241144540               4     151891
        19 +ILN_DATA/ilndb/datafile/ilearn_data10.d           19241144540               4     151891
        20 +ILN_DATA/ilndb/datafile/ilearn_data11.d           19241144540               4     151891
        21 +ILN_DATA/ilndb/datafile/ilearn_data12.d           19241144540               4     151891
        22 +ILN_DATA/ilndb/datafile/ilearn_data13.d           19241144540               4     151891
        23 +ILN_DATA/ilndb/datafile/ilearn_data14.d           19241144540               4     151891
        24 +ILN_DATA/ilndb/datafile/ilearn_data15.d           19241144540               4     151891
        25 +ILN_DATA/ilndb/datafile/ilearn_data16.d           19241144540               4     151891
        26 +ILN_DATA/ilndb/datafile/ilearn_data17.d           19241144540               4     151891
        27 +ILN_DATA/ilndb/datafile/ilearn_data18.d           19241144540               4     151891
        28 +ILN_DATA/ilndb/datafile/ilearn_data19.d           19241144540               4     151891
        29 +ILN_DATA/ilndb/datafile/ilearn_data20.d           19241144540               4     151891
        30 +ILN_DATA/ilndb/datafile/undotbs4.dbf              19241144540               4     151891
        31 +ILN_DATA/ilndb/datafile/undotbs5.dbf              19241144540               4     151891
        32 +ILN_DATA/ilndb/datafile/undotbs6.dbf              19241144540               4     151891
        33 +ILN_DATA/ilndb/datafile/ilearn_data21.d           19241144540               4     151891
        34 +ILN_DATA/ilndb/datafile/ilearn_data22.d           19241144540               4     151891
        35 +ILN_DATA/ilndb/datafile/ilearn_data23.d           19241144540               4     151891
        36 +ILN_DATA/ilndb/datafile/ilearn_data24.d           19241144540               4     151891

发现刚刚恢复的9号数据文件SCN与其他数据文件SCN不一致，手动触发检查点更新

alter system checkpoint GLOBAL;

再次检查数据文件的SCN一致性，发现已全部更新。

 FILE_NUM FILE_NAME                                          SCN                  STATUS   SEQUENCE
---------- -------------------------------------------------- ---------------- ---------- ----------
         1 +ILN_DATA/ilndb/datafile/system.256.7572           19241541927            8196     147421
         2 +ILN_DATA/ilndb/datafile/undotbs1.258.75           19241541927               4     147421
         3 +ILN_DATA/ilndb/datafile/sysaux.257.7572           19241541927               4     147421
         4 +ILN_DATA/ilndb/datafile/users.259.75726           19241541927               4     147421
         5 +ILN_DATA/ilndb/datafile/example.265.757           19241541927               4     147421
         6 +ILN_DATA/ilndb/datafile/undotbs2.266.75           19241541927               4     147421
         7 +ILN_DATA/ilndb/datafile/undotbs3.267.75           19241541927               4     147421
         8 +ILN_DATA/ilndb/datafile/ilearn_data1.db           19241541927               4     147421
         9 +ILN_DATA/ilndb/datafile/ilearn_indx1.db           19241541927               4     147421
        10 +ILN_DATA/ilndb/datafile/ilearn_data2.db           19241541927               4     147421
        11 +ILN_DATA/ilndb/datafile/ilearn_data3.db           19241541927               4     147421
        12 +ILN_DATA/ilndb/datafile/ilearn_data3.db           19241541927               4     147421
        13 +ILN_DATA/ilndb/datafile/ilearn_data4.db           19241541927               4     147421
        14 +ILN_DATA/ilndb/datafile/ilearn_data5.db           19241541927               4     147421
        15 +ILN_DATA/ilndb/datafile/ilearn_data6.db           19241541927               4     147421
        16 +ILN_DATA/ilndb/datafile/ilearn_data7.db           19241541927               4     147421
        17 +ILN_DATA/ilndb/datafile/ilearn_data8.db           19241541927               4     147421
        18 +ILN_DATA/ilndb/datafile/ilearn_data9.db           19241541927               4     147421
        19 +ILN_DATA/ilndb/datafile/ilearn_data10.d           19241541927               4     147421
        20 +ILN_DATA/ilndb/datafile/ilearn_data11.d           19241541927               4     147421
        21 +ILN_DATA/ilndb/datafile/ilearn_data12.d           19241541927               4     147421
        22 +ILN_DATA/ilndb/datafile/ilearn_data13.d           19241541927               4     147421
        23 +ILN_DATA/ilndb/datafile/ilearn_data14.d           19241541927               4     147421
        24 +ILN_DATA/ilndb/datafile/ilearn_data15.d           19241541927               4     147421
        25 +ILN_DATA/ilndb/datafile/ilearn_data16.d           19241541927               4     147421
        26 +ILN_DATA/ilndb/datafile/ilearn_data17.d           19241541927               4     147421
        27 +ILN_DATA/ilndb/datafile/ilearn_data18.d           19241541927               4     147421
        28 +ILN_DATA/ilndb/datafile/ilearn_data19.d           19241541927               4     147421
        29 +ILN_DATA/ilndb/datafile/ilearn_data20.d           19241541927               4     147421
        30 +ILN_DATA/ilndb/datafile/undotbs4.dbf              19241541927               4     147421
        31 +ILN_DATA/ilndb/datafile/undotbs5.dbf              19241541927               4     147421
        32 +ILN_DATA/ilndb/datafile/undotbs6.dbf              19241541927               4     147421
        33 +ILN_DATA/ilndb/datafile/ilearn_data21.d           19241541927               4     147421
        34 +ILN_DATA/ilndb/datafile/ilearn_data22.d           19241541927               4     147421
        35 +ILN_DATA/ilndb/datafile/ilearn_data23.d           19241541927               4     147421
        36 +ILN_DATA/ilndb/datafile/ilearn_data24.d           19241541927               4     147421

至此9号数据文件恢复完成

Mr.Lpp
关注
存储故障后oracle报—ORA-01122/ORA-01207故障处理---惜分飞
05-06 1039
客户存储异常,通过硬件恢复解决存储故障之后,oracle数据库无法正常启动(存储cache丢失),尝试recover数据库报ORA-00283 ORA-01122 ORA-01110 ORA-01207错误。数据库无法通过正常recover的思路解决.通过屏蔽一致性,强制打开数据库,alert日志报ORA-600 2662错误。这个错误比较明显,由于28号回滚段异常导致,对异常回滚段进行处理,重建undo,数据库恢复主要工作完成。通过修改数据库scn,open数据库报ORA-600 4137。
oracle ora错误文档,Oracle 在备份和恢复中发现的ORA-376 ORA-00376: 此时无法读取文件 错误的常见原因和解决方案...
weixin_33895396的博客
04-02 1624
适用于：Oracle Database – Enterprise Edition – 版本 9.0.1.0 到 11.2.0.3 [Release 9.0.1 to 11.2]本文信息适用于任何平台。目的夯实ORA-376错误的常见原因和解决方案。故障排除步骤SCOPE & APPLICATION——————-Customers facing ORA-376 or analysts req...
ORA-00376: 此时无法读取文件
热门推荐
谁伴我闯荡
08-21 3万+
ORA-00376: 此时无法读取文件 4 ORA-01110: 数据文件 4: 'D:\APP\WJ\ORADATA\ORCL11G\USERS01.DBF'   昨天删除了一些数据文件,现在找不到数据文件. 1.检查数据文件状态   SQL> select file#,name,status from v$datafile; FILE# NAME STATUS ---...
ORA-01152-01110
05-08
oracle数据库ora-01152和ora-01110的解决办法
88-Oracle 11g Windows-ORA-01113 ORA-01110修复
最新发布
远方的专栏
06-27 988
成功打开数据库后，使用expdp或exp全库导出同时也用工具连接导出可以插入的sql文件，迁移到新库。杜绝继续在生产环境使用修复后的库。
ORA-00376 & ORA-01110
tworain
09-23 917
-- 报错: ORA-00376: file 10 cannot be read at this time ORA-01110: data file 10: '+NEW_DATA/test/datafile/test_dat.262.1083956499' -- 查看数据文件状态： SQL> select file#,status from v$datafile where status = 'OFFLINE'; FILE# STATUS ---------- -------
ORA-00376-ORA-01110
weixin_44074288的博客
02-13 1853
问题是数据文件无法读取。 解决过程： 1.登录到数据库中（dba），查询datafile的状态， select * from v$datafile ;发现出现问题的文件状态是recover，然后查询具体原因，到相应的数据文件挂载点新建文件，发现可读可写，说明挂载点没有问题，那就只是这个文件的问题。直接恢复：recover datafile 5 （5是datafile），返回成功后再次查询状态是o...
ORA-00376 ORA-01110
cool0528的博客
04-06 387
执行SQL语句是报错。SQL>select * from table_name;ORA-00376: file 12 cannot be read at this timeORA-01110:...
ORA-00376、ORA-01110数据文件异常故障修复
Eric.zhong
08-08 5802
[真实案例，信息脱敏] [故障情况] 今天 16点收到部门领导致电反馈某客户现场服务器发送报警信息。我立刻与客户联络并远程到服务器查看问题。 初步检查服务器是正常启动，数据库服务也是正常。我就以为没有问题，但不久客户反馈登录出现异常，提示ORA-00376、ORA-01110。 [处理步骤] 检查数据文件状态 set lines 256 set pages 500 select name , s...
dataguard- ORA-16004/ORA-01196/ORA-01110
qq_44906603的博客
04-25 290
dataguard- ORA-16004/ORA-01196/ORA-01110
ora-01113 oracle8i,分享一次ORA-01113 ORA-01110故障处理过程
weixin_36155560的博客
04-03 707
近期编写了小工具,对于此类问题,可以实现一键恢复,参考:一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools数据库报ORA-01113 ORA-01110无法正常openSQL> alter database open;alter database open*ERROR at line 1:ORA-01113: file 1 needs media re...
数据库ORA-01196故障-归档日志丢失恢复详解
09-09
数据库ORA-01196错误是Oracle数据库在运行归档模式时可能出现的一种故障，通常与数据文件不一致和归档日志丢失或未正确应用有关。这种情况通常发生在数据库需要介质恢复，即由于硬件故障、操作系统崩溃或者如描述中...
诊断：ORA-00376 & ORA-01110
06-06 435
现象： Errors in file /path/of/diag/rdbms/prod/PROD/trace/PROD_ora_13447.trc: ORA-00376: 此时无法读取文件 61 ORA-01110: 数据文件 61: '/path/of/datafile/cuxd05.dbf' 变化： 硬件故障，IO层面错误。 分析：此时从v$dataf...
不需要删dbf文件，ORA-00376、ORA-01110无法读取dbf文件导致 数据库无法启动
weixin_43978552的博客
10-06 2128
太多的博客说删除dbf 文件启动数据库 我气的脑血压上表 你生产环境你删一个数据文件试试看
故障处理-ORA-00376/ORA-01110
weixin_30763397的博客
08-29 311
数据库实例启动之后发现，9号数据文件发生故障，file 9 cannot be read at this time， ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (8, 21) on object 66190. Mon Aug 29 12:15:06 EAT 2016 ...
Oracle 10G 报错 ORA-00604 ORA-00376 ORA-01110
weixin_34368949的博客
06-09 793
如果在做数据库迁移，由于你删除undotbs01.dbf 导致报如下错误：ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/app/o...
linux下ora 01110,ORA-00376/ORA-01110 故障处理办法
weixin_39895977的博客
05-25 533
Oracle数据库实例启动之后发现，9号数据文件发生故障，file 9 cannot be read at this time，ORACLE Instance ilndb2 (pid = 16) - Error 376 encountered while recovering transaction (8, 21) on object 66190.Mon Aug 29 12:15:06 EAT 2...
ORA-00376: file X cannot be read at this time 问题解决
ITer-Tony
07-31 5216
今天在做坏块测试的时候，破坏数据数据块，然后进行数据文件恢复后，使用DBV测试也恢复正常。但就是读取数据文件里的数据时提示ora-00376。有人说重启数据库，但没用，我甚至OS也重启了，最后将数据文件OFFLINE，再ONLINE，问题解决 oracle@oracle10g[/home/oracle]dbv file='/tmp/test01.dbf' DBVERIFY: Releas
Oracle-ora00376
安潞的专栏
11-29 650
2.如果数据文件只是offline,则只需要把数据文件脱机即可;ORA-01110: 数据文件 4: 'D:\APP\WJ\ORADATA\ORCL11G\USERS01.DBF'线程 1 序列 4 的归档日志已作为文件 D:\WJARC00004_0853710748.001 存在于磁盘上。1.既然数据文件不能访问,首先要检查数据文件状态以及数据文件是否在磁盘上.发现数据文件4脱机,数据文件12是recover状态。把数据文件12联机,再检查数据文件12状态。介质恢复完成, 用时: 00:00:04。
ora-01458 ora-01157 ora-01110
03-15
### 关于 Oracle 数据库错误代码 ORA-01458、ORA-01157 和 ORA-01110 的原因及解决方案 #### 错误描述与可能原因 1. **ORA-01458**: 此错误表示尝试插入或更新的数据违反了唯一约束条件。具体来说，数据中存在重复值或者 NULL 值不符合索引定义的要求[^1]。此问题通常发生在创建唯一索引时，表中的某些列已经包含了重复值。 2. **ORA-01157**: 这一错误表明无法获取控制文件所需的资源锁。常见原因是磁盘空间不足、操作系统级别的锁定冲突或其他进程正在占用相关资源[^4]。 3. **ORA-01110**: 该错误提供了受影响的数据文件名称及其编号的信息。它通常是其他更严重错误（如 ORA-01157 或 ORA-01119）的一部分，用于指示具体的文件路径和位置。 --- #### 解决方案 ##### 对于 ORA-01458: 为了修复这一问题，可以采取以下措施： - 验证目标字段是否存在重复值： ```sql SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1; ``` - 如果发现重复记录，则需要清理这些冗余数据后再重新执行操作。 - 确认是否允许 NULL 值参与唯一索引构建；如果不允许，在设计阶段应避免这种情况发生。 ##### 对于 ORA-01157: 针对此类情况可考虑如下处理方式： - 检查是否有足够的可用存储容量来满足数据库需求； - 查看系统日志以识别潜在的竞争者并终止不必要的会话； - 使用 `ALTER SYSTEM CHECKPOINT` 手动触发检查点从而释放部分已修改缓冲区到磁盘上。 ##### 对于 ORA-01110: 由于这是辅助性的诊断消息而非独立存在的异常状况，因此重点在于解决引发它的根本性事件 (比如前面提到过的那些)。一旦基础层面恢复正常运作之后，这类提示自然也会消失不见。 ```bash ls -lh $(grep 'datafile' mesdb2_ora_5920.trc | awk '{print $NF}') ``` 上述命令可以帮助快速定位实际物理文件大小与其逻辑结构之间的一致性状态[^3]。 --- #### 总结 通过以上分析可以看出每种类型的报错背后都隐藏着特定的技术背景以及相应的调试技巧。当面对复杂场景下的多重叠加型故障现象时，建议按照先易后难的原则逐一排查直至最终找到症结所在为止。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值