oracle數(shù)據(jù)庫ORA-01196錯(cuò)誤解決辦法分享

更新時(shí)間：2017年10月20日 17:01:55 作者：常飛夢(mèng)

這篇文章主要介紹了oracle數(shù)據(jù)庫ORA-01196錯(cuò)誤解決辦法分享，小編覺得還是挺不錯(cuò)的，這里分享給大家，供需要的朋友參考。

上一篇文章中我們了解到oracle常見故障類別及規(guī)劃解析，接下來，我們看看oracle數(shù)據(jù)庫ORA-01196錯(cuò)誤解決的相關(guān)內(nèi)容，具體如下：

問題現(xiàn)象

在使用shutdown abort停DataGuard備庫后，備庫不能open，報(bào)ORA-01196錯(cuò)誤。

發(fā)現(xiàn)一備庫不能應(yīng)用日志，查看備庫日志沒發(fā)現(xiàn)報(bào)錯(cuò)，懷疑是備庫應(yīng)用日志服務(wù)停止，于是嘗試重啟備庫；
可能因?yàn)閭鋷焓亲x業(yè)務(wù)比較繁忙，在shutdown immediate關(guān)閉備庫時(shí)等時(shí)間過長(zhǎng)，于是使用了shutdown abort命令；
但后面在啟動(dòng)備庫時(shí)發(fā)生報(bào)錯(cuò)，造成數(shù)據(jù)文件損壞，控制文件和數(shù)據(jù)文件的scn號(hào)不一致。

--啟動(dòng)備庫時(shí)報(bào)錯(cuò)
SQL> startup
ORACLE 例程已經(jīng)啟動(dòng)。
 
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

數(shù)據(jù)庫裝載完畢。

ORA-10458: standby database requiresrecovery
ORA-01196: 文件 1 由于介質(zhì)恢復(fù)會(huì)話失敗而不一致
ORA-01110: 數(shù)據(jù)文件 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: 用戶請(qǐng)求取消當(dāng)前的操作
ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)
ORA-10564: tablespace JDYWP_IDX
ORA-01110: 數(shù)據(jù)文件 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: 數(shù)據(jù)文件 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: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [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: 數(shù)據(jù)文件 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: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [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: 數(shù)據(jù)文件 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 由于介質(zhì)恢復(fù)會(huì)話失敗而不一致
ORA-01110: 數(shù)據(jù)文件 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

解決辦法：

把備庫閃回到正常的狀態(tài)的時(shí)點(diǎn)。

--前提數(shù)據(jù)庫閃回之前已經(jīng)打開
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
--啟動(dòng)實(shí)時(shí)應(yīng)用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

--查看日志看到日志已經(jīng)從閃回的時(shí)點(diǎn)開始應(yīng)用

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

總結(jié)

以上就是本文關(guān)于oracle數(shù)據(jù)庫ORA-01196錯(cuò)誤解決辦法分享的全部?jī)?nèi)容，希望對(duì)大家有所幫助。感興趣的朋友可以繼續(xù)參閱本站：ORACLE SQL語句優(yōu)化技術(shù)要點(diǎn)解析、Oracle RMAN自動(dòng)備份控制文件方法介紹、oracle 數(shù)據(jù)庫啟動(dòng)階段分析等，有什么問題可以直接留言，小編會(huì)及時(shí)回復(fù)大家的。感謝朋友們對(duì)本站的支持！這里推薦幾本oracle相關(guān)的書籍，供廣大編程愛好及工作者學(xué)習(xí)、參考。

構(gòu)建Oracle高可用環(huán)境 (陳吉平) 中文pdf掃描版

http://www.dhdzp.com/books/554126.html

oracle中文手冊(cè)合集 CHM版

http://www.dhdzp.com/books/547791.html

希望大家能夠喜歡！

您可能感興趣的文章: