oracle数据库ORA-01196错误解决办法分享

上一篇文章中我们了解到，接下来，我们看看oracle数据库ora-01196错误解决的相关内容，具体如下：

问题现象

在使用shutdown abort停dataguard备库后，备库不能open，报ora-01196错误。

发现一备库不能应用日志，查看备库日志没发现报错，怀疑是备库应用日志服务停止，于是尝试重启备库；
可能因为备库是读业务比较繁忙，在shutdown immediate关闭备库时等时间过长，于是使用了shutdown abort命令；
但后面在启动备库时发生报错，造成数据文件损坏，控制文件和数据文件的scn号不一致。

--启动备库时报错
sql> startup
oracle 例程已经启动。
 
total system global area 2.0310e+10 bytes
fixed size         2235256 bytes
variable size      9328133256 bytes
database buffers     1.0939e+10 bytes
redo buffers        40894464 bytes

数据库装载完毕。

ora-10458: standby database requiresrecovery
ora-01196: 文件 1 由于介质恢复会话失败而不一致
ora-01110: 数据文件 1:'+data/htdb5/datafile/system.261.759082693'

--查看日志

alter database open
data guard brokerinitializing...
data guard brokerinitialization complete
beginning standby crash recovery.
serial media recovery started
managed standby recoverystarting real time apply
media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180068.1541.885192077
thu jul 16 12:00:47 2015
errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:
ora-01013: 用户请求取消当前的操作
ora-10567: redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)
ora-10564: tablespace jdywp_idx
ora-01110: 数据文件 47:'+data/htdb5/datafile/jdywp_idx.336.856967805'
ora-10561: block type'transaction managed index block', data object# 251837
errors in file/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: redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)
ora-10564: tablespace jdywp_idx
ora-01110: 数据文件 47:'+data/htdb5/datafile/jdywp_idx.336.856967805'
ora-10561: block type'transaction managed index block', data object# 251837
errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc (incident=116743):
ora-00600: 内部错误代码, 参数: [3020],[47], [1187724], [198320012], [], [], [], [], [], [], [], []
ora-10567: redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)
ora-10564: tablespace jdywp_idx
ora-01110: 数据文件 47:'+data/htdb5/datafile/jdywp_idx.336.856967805'
ora-10561: block type'transaction managed index block', data object# 251837
incident details in:/u01/app/ora11g/diag/rdbms/htdb5/htdb5/incident/incdir_116743/htdb5_ora_10154_i116743.trc
use adrci or support workbenchto package the incident.
see note 411.1 at my oraclesupport for error and packaging details.
standby crash recovery aborteddue to error 600.
errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:
ora-00600: 内部错误代码, 参数: [3020],[47], [1187724], [198320012], [], [], [], [], [], [], [], []
ora-10567: redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)
ora-10564: tablespace jdywp_idx
ora-01110: 数据文件 47:'+data/htdb5/datafile/jdywp_idx.336.856967805'
ora-10561: block type'transaction managed index block', data object# 251837
recovery interrupted!
some recovered datafiles maybeleft media fuzzy
media recovery may continue butopen resetlogs may fail
completed standby crashrecovery.
errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:
ora-10458: standby databaserequires recovery
ora-01196: 文件 1 由于介质恢复会话失败而不一致
ora-01110: 数据文件 1:'+data/htdb5/datafile/system.261.759082693'
ora-10458 signalled during:alter database open...
thu jul 16 12:00:49 2015
sweep [inc][116743]: completed
sweep [inc2][116743]: completed
thu jul 16 12:00:49 2015
dumping diagnostic data indirectory=[cdmp_20150716120049], requested by (instance=1, osid=10154),summary=[incident=116743].
thu jul 16 12:01:50 2015

解决办法：

把备库闪回到正常的状态的时点。

--前提数据库闪回之前已经打开
sql> select flashback_on from v$database;
flashback_on
------------------
yes
 
sql> flashback database to timestamp to_timestamp('2015-07-16 4:00:05','yyyy-mm-ddhh24:mi:ss');
--或是使用flashbackdatabase to scn 947921
sql> alter database open;
 
sql> select open_mode from v$database;
open_mode
--------------------
read only
--启动实时应用
sql> alter database recover managed standby database using current logfile disconnect;
sql> select open_mode from v$database;
open_mode
--------------------
read only with apply

--查看日志看到日志已经从闪回的时点开始应用

thu jul 16 13:36:01 2015
flashback database to timestampto_timestamp('2015-07-16 4:00:05','yyyy-mm-dd hh24:mi:ss')
flashback restore start
thu jul 16 13:39:30 2015
flashback restore complete
flashback media recovery start
 started logmerger process
parallel media recovery startedwith 16 slaves
flashback media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180047.2212.885180637
thu jul 16 13:41:54 2015
flashback media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180061.2611.885182343
thu jul 16 13:42:04 2015
flashback media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537
thu jul 16 13:42:12 2015
incomplete recovery applieduntil change 71489772016 time 07/16/2015 04:00:06
flashback media recoverycomplete
completed: flashback databaseto timestamp to_timestamp('2015-07-16 4:00:05','yyyy-mm-dd hh24:mi:ss')
thu jul 16 13:43:25 2015
deleted oracle managed file+fra/htdb5/archivelog/2015_07_15/thread_1_seq_179690.2885.885083087
thu jul 16 13:43:25 2015
standby controlfile consistentwith primary
rfs[3]: selected log 8 forthread 1 sequence 180122 dbid 1083719948 branch 759079182
archived log entry 180115 addedfor thread 1 sequence 180121 id 0x40a48484 dest 1:
thu jul 16 13:45:41 2015
alter database open
data guard brokerinitializing...
data guard brokerinitialization complete
 
smon: enabling cache recovery
dictionary check beginning
dictionary check complete
database characterset iszhs16gbk
no resource manager plan active
replication_dependency_trackingturned off (no async multimaster replication found)
physical standby databaseopened for read only access.
completed: alter database open
thu jul 16 13:45:44 2015
alter database recover managedstandby database through all switchoverdisconnect using current logfile
attempt to start backgroundmanaged standby recovery process (htdb5)
thu jul 16 13:45:44 2015
mrp0 started with pid=51, osid=14743
mrp0: background managedstandby recovery process started (htdb5)
 started logmerger process
thu jul 16 13:45:50 2015
managed standby recoverystarting real time apply
parallel media recovery startedwith 16 slaves
waiting for all non-currentorls to be archived...
all non-current orls have beenarchived.
media recovery log +fra/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537
completed: alter databaserecover managed standby database throughall switchover disconnect using currentlogfile
thu jul 16 13:46:08 2015
media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180063.3683.885182777
thu jul 16 13:46:35 2015
media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180064.2542.885183119
thu jul 16 13:47:07 2015
media recovery log+fra/htdb5/archivelog/2015_07_16/thread_1_seq_180065.2717.885183615

总结

以上就是本文关于oracle数据库ora-01196错误解决办法分享的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：oracle sql语句优化技术要点解析、oracle rman自动备份控制文件方法介绍、等，有什么问题可以直接留言，小编会及时回复大家的。感谢朋友们对本站的支持！这里推荐几本oracle相关的书籍，供广大编程爱好及工作者学习、参考。

构建oracle高可用环境 (陈吉平) 中文pdf扫描版

oracle中文手册合集 chm版

希望大家能够喜欢！