oracle数据库ORA-01196错误解决办法分享
上一篇文章中我们了解到oracle常见故障类别及规划解析，接下来，我们看看oracle数据库ORA-01196错误解决的相关内容，具体如下：
在使用shutdownabort停DataGuard备库后，备库不能open，报ORA-01196错误。
发现一备库不能应用日志，查看备库日志没发现报错，怀疑是备库应用日志服务停止，于是尝试重启备库；
可能因为备库是读业务比较繁忙，在shutdownimmediate关闭备库时等时间过长，于是使用了shutdownabort命令；
但后面在启动备库时发生报错，造成数据文件损坏，控制文件和数据文件的scn号不一致。
--启动备库时报错 SQL>startup ORACLE例程已经启动。 TotalSystemGlobalArea2.0310E+10bytes FixedSize2235256bytes VariableSize9328133256bytes DatabaseBuffers1.0939E+10bytes RedoBuffers40894464bytes
数据库装载完毕。
ORA-10458:standbydatabaserequiresrecovery
ORA-01196:文件1由于介质恢复会话失败而不一致
ORA-01110:数据文件1:'+DATA/htdb5/datafile/system.261.759082693'
--查看日志
alterdatabaseopen DataGuardBrokerinitializing... DataGuardBrokerinitializationcomplete Beginningstandbycrashrecovery. SerialMediaRecoverystarted ManagedStandbyRecoverystartingRealTimeApply MediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180068.1541.885192077 ThuJul1612:00:472015 Errorsinfile/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc: ORA-01013:用户请求取消当前的操作 ORA-10567:Redoisinconsistentwithdatablock(file#47,block#1187724,fileoffsetis1139900416bytes) ORA-10564:tablespaceJDYWP_IDX ORA-01110:数据文件47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805' ORA-10561:blocktype'TRANSACTIONMANAGEDINDEXBLOCK',dataobject#251837 Errorsinfile/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc: ORA-00339:归档日志未包含任何重做 ORA-00334:归档日志:'+DATA/htdb5/onlinelog/group_2.280.759082845' ORA-10567:Redoisinconsistentwithdatablock(file#47,block#1187724,fileoffsetis1139900416bytes) ORA-10564:tablespaceJDYWP_IDX ORA-01110:数据文件47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805' ORA-10561:blocktype'TRANSACTIONMANAGEDINDEXBLOCK',dataobject#251837 Errorsinfile/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc(incident=116743): ORA-00600:内部错误代码,参数:[3020],[47],[1187724],[198320012],[],[],[],[],[],[],[],[] ORA-10567:Redoisinconsistentwithdatablock(file#47,block#1187724,fileoffsetis1139900416bytes) ORA-10564:tablespaceJDYWP_IDX ORA-01110:数据文件47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805' ORA-10561:blocktype'TRANSACTIONMANAGEDINDEXBLOCK',dataobject#251837 Incidentdetailsin:/u01/app/ora11g/diag/rdbms/htdb5/htdb5/incident/incdir_116743/htdb5_ora_10154_i116743.trc UseADRCIorSupportWorkbenchtopackagetheincident. SeeNote411.1atMyOracleSupportforerrorandpackagingdetails. Standbycrashrecoveryabortedduetoerror600. Errorsinfile/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc: ORA-00600:内部错误代码,参数:[3020],[47],[1187724],[198320012],[],[],[],[],[],[],[],[] ORA-10567:Redoisinconsistentwithdatablock(file#47,block#1187724,fileoffsetis1139900416bytes) ORA-10564:tablespaceJDYWP_IDX ORA-01110:数据文件47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805' ORA-10561:blocktype'TRANSACTIONMANAGEDINDEXBLOCK',dataobject#251837 Recoveryinterrupted! Somerecovereddatafilesmaybeleftmediafuzzy Mediarecoverymaycontinuebutopenresetlogsmayfail Completedstandbycrashrecovery. Errorsinfile/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc: ORA-10458:standbydatabaserequiresrecovery ORA-01196:文件1由于介质恢复会话失败而不一致 ORA-01110:数据文件1:'+DATA/htdb5/datafile/system.261.759082693' ORA-10458signalledduring:alterdatabaseopen... ThuJul1612:00:492015 Sweep[inc][116743]:completed Sweep[inc2][116743]:completed ThuJul1612:00:492015 Dumpingdiagnosticdataindirectory=[cdmp_20150716120049],requestedby(instance=1,osid=10154),summary=[incident=116743]. ThuJul1612:01:502015
解决办法：
把备库闪回到正常的状态的时点。
--前提数据库闪回之前已经打开
SQL>selectFLASHBACK_ONfromv$database;
FLASHBACK_ON
------------------
YES
SQL>Flashbackdatabasetotimestampto_timestamp('2015-07-164:00:05','yyyy-mm-ddhh24:mi:ss');
--或是使用Flashbackdatabasetoscn947921
SQL>alterdatabaseopen;
SQL>selectopen_modefromv$database;
OPEN_MODE
--------------------
READONLY
--启动实时应用
SQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECT;
SQL>selectopen_modefromv$database;
OPEN_MODE
--------------------
READONLYWITHAPPLY
--查看日志看到日志已经从闪回的时点开始应用
ThuJul1613:36:012015
Flashbackdatabasetotimestampto_timestamp('2015-07-164:00:05','yyyy-mm-ddhh24:mi:ss')
FlashbackRestoreStart
ThuJul1613:39:302015
FlashbackRestoreComplete
FlashbackMediaRecoveryStart
startedlogmergerprocess
ParallelMediaRecoverystartedwith16slaves
FlashbackMediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180047.2212.885180637
ThuJul1613:41:542015
FlashbackMediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180061.2611.885182343
ThuJul1613:42:042015
FlashbackMediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537
ThuJul1613:42:122015
IncompleteRecoveryapplieduntilchange71489772016time07/16/201504:00:06
FlashbackMediaRecoveryComplete
Completed:Flashbackdatabasetotimestampto_timestamp('2015-07-164:00:05','yyyy-mm-ddhh24:mi:ss')
ThuJul1613:43:252015
DeletedOraclemanagedfile+FRA/htdb5/archivelog/2015_07_15/thread_1_seq_179690.2885.885083087
ThuJul1613:43:252015
Standbycontrolfileconsistentwithprimary
RFS[3]:Selectedlog8forthread1sequence180122dbid1083719948branch759079182
ArchivedLogentry180115addedforthread1sequence180121ID0x40a48484dest1:
ThuJul1613:45:412015
alterdatabaseopen
DataGuardBrokerinitializing...
DataGuardBrokerinitializationcomplete
SMON:enablingcacherecovery
Dictionarycheckbeginning
Dictionarycheckcomplete
DatabaseCharactersetisZHS16GBK
NoResourceManagerplanactive
replication_dependency_trackingturnedoff(noasyncmultimasterreplicationfound)
Physicalstandbydatabaseopenedforreadonlyaccess.
Completed:alterdatabaseopen
ThuJul1613:45:442015
ALTERDATABASERECOVERMANAGEDSTANDBYDATABASETHROUGHALLSWITCHOVERDISCONNECTUSINGCURRENTLOGFILE
AttempttostartbackgroundManagedStandbyRecoveryprocess(htdb5)
ThuJul1613:45:442015
MRP0startedwithpid=51,OSid=14743
MRP0:BackgroundManagedStandbyRecoveryprocessstarted(htdb5)
startedlogmergerprocess
ThuJul1613:45:502015
ManagedStandbyRecoverystartingRealTimeApply
ParallelMediaRecoverystartedwith16slaves
Waitingforallnon-currentORLstobearchived...
Allnon-currentORLshavebeenarchived.
MediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537
Completed:ALTERDATABASERECOVERMANAGEDSTANDBYDATABASETHROUGHALLSWITCHOVERDISCONNECTUSINGCURRENTLOGFILE
ThuJul1613:46:082015
MediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180063.3683.885182777
ThuJul1613:46:352015
MediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180064.2542.885183119
ThuJul1613:47:072015
MediaRecoveryLog+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180065.2717.885183615
以上就是本文关于oracle数据库ORA-01196错误解决办法分享的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：ORACLESQL语句优化技术要点解析、OracleRMAN自动备份控制文件方法介绍、oracle数据库启动阶段分析等，有什么问题可以直接留言，小编会及时回复大家的。感谢朋友们对本站的支持！这里推荐几本oracle相关的书籍，供广大编程爱好及工作者学习、参考。
构建Oracle高可用环境(陈吉平)中文pdf扫描版
https://www.nhooo.com/books/554126.html
oracle中文手册合集CHM版
https://www.nhooo.com/books/547791.html
希望大家能够喜欢！