博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
故障处理-ORA-00376/ORA-01110
阅读量:5015 次
发布时间:2019-06-12

本文共 18983 字,大约阅读时间需要 63 分钟。

数据库实例启动之后发现,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 2016Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:ORA-00376: file 9 cannot be read at this timeORA-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 2016Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:ORA-00376: file 9 cannot be read at this timeORA-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 2016Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:ORA-00376: file 9 cannot be read at this timeORA-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 2016Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:ORA-00376: file 9 cannot be read at this timeORA-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 2016Errors in file /home/oracle/product/admin/ilndb/bdump/ilndb2_smon_14168.trc:ORA-00376: file 9 cannot be read at this timeORA-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 2016Copyright (c) 1982, 2007, Oracle.  All rights reserved.connected to target database: ILNDB (DBID=140433242)RMAN> recover datafile 9;Starting recover at 29-AUG-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=6481 instance=ilndb2 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=6001 instance=ilndb2 devtype=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: sid=6000 instance=ilndb2 devtype=DISKallocated channel: ORA_DISK_4channel ORA_DISK_4: sid=5999 instance=ilndb2 devtype=DISKstarting media recoveryarchive log thread 3 sequence 205311 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205311.4265.921154275RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 08/29/2016 12:14:34RMAN-06053: unable to perform media recovery because of missing logRMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205310 lowscn 19240848472RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147416 lowscn 19240848247RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151889 lowscn 19240848211RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151888 lowscn 19240845047RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205309 lowscn 19240844632RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205308 lowscn 19240742241RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147415 lowscn 19240632693RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151887 lowscn 19240631896RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205307 lowscn 19240631896RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151886 lowscn 19240560360RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205306 lowscn 19240500521RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147414 lowscn 19240439862RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151885 lowscn 19240308923RMAN-06102: no channel to restore a backup or copy of log thread 3 seq 205305 lowscn 19240301377RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151884 lowscn 19240124380RMAN-06102: no channel to restore a backup or copy of log thread 2 seq 147413 lowscn 19240106434RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 151883 lowscn 19239991180RMAN-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 2016Copyright (c) 1982, 2007, Oracle.  All rights reserved.connected to target database: ILNDB (DBID=140433242)RMAN>  recover datafile 9;Starting recover at 29-AUG-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=5707 instance=ilndb2 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=5706 instance=ilndb2 devtype=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: sid=5705 instance=ilndb2 devtype=DISKallocated channel: ORA_DISK_4channel ORA_DISK_4: sid=5704 instance=ilndb2 devtype=DISKstarting media recoveryarchive log thread 1 sequence 151883 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151883.4080.921155791archive log thread 1 sequence 151884 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151884.2542.921155791archive log thread 1 sequence 151885 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151885.2136.921155791archive log thread 1 sequence 151886 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151886.2115.921155815archive log thread 1 sequence 151887 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151887.2496.921155815archive log thread 1 sequence 151888 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151888.2593.921155817archive log thread 1 sequence 151889 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151889.1397.921155817archive log thread 1 sequence 151890 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151890.2469.921155571archive log thread 2 sequence 147413 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147413.2362.921156057archive log thread 2 sequence 147414 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147414.1828.921156073archive log thread 2 sequence 147415 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147415.3869.921156067archive log thread 2 sequence 147416 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147416.2487.921156077archive log thread 2 sequence 147417 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147417.3331.921155015archive log thread 2 sequence 147418 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147418.4018.921155341archive log thread 2 sequence 147419 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147419.3104.921155879archive log thread 3 sequence 205304 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205304.1826.921156603archive log thread 3 sequence 205305 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205305.1558.921156603archive log thread 3 sequence 205306 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205306.1981.921156605archive log thread 3 sequence 205307 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205307.668.921156605archive log thread 3 sequence 205308 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205308.632.921156605archive log thread 3 sequence 205309 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205309.714.921156623archive log thread 3 sequence 205310 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205310.1391.921156625archive log thread 3 sequence 205311 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205311.4265.921154275archive log thread 3 sequence 205312 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205312.3633.921155013archive log thread 3 sequence 205313 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205313.2490.921155571archive log thread 3 sequence 205314 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205314.868.921156127archive log thread 3 sequence 205315 is already on disk as file +ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205315.545.921156559archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147413.2362.921156057 thread=2 sequence=147413archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151883.4080.921155791 thread=1 sequence=151883archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205304.1826.921156603 thread=3 sequence=205304archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151884.2542.921155791 thread=1 sequence=151884archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205305.1558.921156603 thread=3 sequence=205305archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151885.2136.921155791 thread=1 sequence=151885archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147414.1828.921156073 thread=2 sequence=147414archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205306.1981.921156605 thread=3 sequence=205306archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151886.2115.921155815 thread=1 sequence=151886archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_1_seq_151887.2496.921155815 thread=1 sequence=151887archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_3_seq_205307.668.921156605 thread=3 sequence=205307archive log filename=+ILN_DATA/ilndb/archivelog/2016_08_29/thread_2_seq_147415.3869.921156067 thread=2 sequence=147415media recovery complete, elapsed time: 00:00:10Finished recover at 29-AUG-16

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

SQL> select STATUS from v$datafile where  TS# =9;STATUS---------------------OFFLINESQL> 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号数据文件恢复完成

转载于:https://www.cnblogs.com/wangrongxin/p/5818657.html

你可能感兴趣的文章
22. Generate Parentheses
查看>>
POJ1125-Stockbroker Grapevine Floyd算法多源最短路径
查看>>
计算机操作系统实验之_进程观测_实验报告
查看>>
将四个按钮放入一个父控件的好处:方便移动,只需要改变父控件的y值,就可移动四个按钮...
查看>>
异或运算实现加密与解密字符串
查看>>
http 报错码对应的错误原因
查看>>
不依赖任何js框架的日期控件My97 DatePicker——支持小时、分钟、秒钟
查看>>
linux之sort用法
查看>>
linux下FTP使用
查看>>
16.python的网络编程
查看>>
18.python的打包和发布
查看>>
css可视化格式模型
查看>>
SQL语句操作——2017.08.16
查看>>
Samba无法保存文件问题
查看>>
linux系统删除空间后系统分区空间仍不释放问题
查看>>
牛客网 牛客小白月赛1 F.三视图
查看>>
LinkQueue by c
查看>>
vim下Java自动补全插件javacomplete
查看>>
关于Oracle和SQLServer数据库在.net中拼接数据库语句的不同
查看>>
为程序添加“打开文件”对话框和“另存为”对话框
查看>>