oracle數據庫ORA-01196錯誤解決辦法分享

2024-08-29 14:00:45

字體：大中小

來源：轉載

供稿：網友

上一篇文章中我們了解到oracle常見故障類別及規劃解析，接下來，我們看看oracle數據庫ORA-01196錯誤解決的相關內容，具體如下：

問題現象

在使用shutdown abort停DataGuard備庫后，備庫不能open，報ORA-01196錯誤。

發現一備庫不能應用日志，查看備庫日志沒發現報錯，懷疑是備庫應用日志服務停止，于是嘗試重啟備庫；
可能因為備庫是讀業務比較繁忙，在shutdown immediate關閉備庫時等時間過長，于是使用了shutdown abort命令；
但后面在啟動備庫時發生報錯，造成數據文件損壞，控制文件和數據文件的scn號不一致。

--啟動備庫時報錯SQL> startupORACLE 例程已經啟動。 Total System Global Area 2.0310E+10 bytesFixed Size         2235256 bytesVariable Size      9328133256 bytesDatabase Buffers     1.0939E+10 bytesRedo Buffers        40894464 bytes

數據庫裝載完畢。

ORA-10458: standby database requiresrecovery
ORA-01196: 文件 1 由于介質恢復會話失敗而不一致
ORA-01110: 數據文件 1:'+DATA/htdb5/datafile/system.261.759082693'

--查看日志

alter database openData Guard Brokerinitializing...Data Guard Brokerinitialization completeBeginning standby crash recovery.Serial Media Recovery startedManaged Standby Recoverystarting Real Time ApplyMedia Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180068.1541.885192077Thu Jul 16 12:00:47 2015Errors 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_IDXORA-01110: 數據文件 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837Errors 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_IDXORA-01110: 數據文件 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837Errors 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_IDXORA-01110: 數據文件 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837Incident details in:/u01/app/ora11g/diag/rdbms/htdb5/htdb5/incident/incdir_116743/htdb5_ora_10154_i116743.trcUse 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_IDXORA-01110: 數據文件 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837Recovery interrupted!Some recovered datafiles maybeleft media fuzzyMedia recovery may continue butopen resetlogs may failCompleted standby crashrecovery.Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:ORA-10458: standby databaserequires recoveryORA-01196: 文件 1 由于介質恢復會話失敗而不一致ORA-01110: 數據文件 1:'+DATA/htdb5/datafile/system.261.759082693'ORA-10458 signalled during:alter database open...Thu Jul 16 12:00:49 2015Sweep [inc][116743]: completedSweep [inc2][116743]: completedThu Jul 16 12:00:49 2015Dumping 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 947921SQL> 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 2015Flashback database to timestampto_timestamp('2015-07-16 4:00:05','yyyy-mm-dd hh24:mi:ss')Flashback Restore StartThu Jul 16 13:39:30 2015Flashback Restore CompleteFlashback Media Recovery Start started logmerger processParallel Media Recovery startedwith 16 slavesFlashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180047.2212.885180637Thu Jul 16 13:41:54 2015Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180061.2611.885182343Thu Jul 16 13:42:04 2015Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537Thu Jul 16 13:42:12 2015Incomplete Recovery applieduntil change 71489772016 time 07/16/2015 04:00:06Flashback Media RecoveryCompleteCompleted: Flashback databaseto timestamp to_timestamp('2015-07-16 4:00:05','yyyy-mm-dd hh24:mi:ss')Thu Jul 16 13:43:25 2015Deleted Oracle managed file+FRA/htdb5/archivelog/2015_07_15/thread_1_seq_179690.2885.885083087Thu Jul 16 13:43:25 2015Standby controlfile consistentwith primaryRFS[3]: Selected log 8 forthread 1 sequence 180122 dbid 1083719948 branch 759079182Archived Log entry 180115 addedfor thread 1 sequence 180121 ID 0x40a48484 dest 1:Thu Jul 16 13:45:41 2015alter database openData Guard Brokerinitializing...Data Guard Brokerinitialization complete SMON: enabling cache recoveryDictionary check beginningDictionary check completeDatabase Characterset isZHS16GBKNo Resource Manager plan activereplication_dependency_trackingturned off (no async multimaster replication found)Physical standby databaseopened for read only access.Completed: alter database openThu Jul 16 13:45:44 2015ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILEAttempt to start backgroundManaged Standby Recovery process (htdb5)Thu Jul 16 13:45:44 2015MRP0 started with pid=51, OSid=14743MRP0: Background ManagedStandby Recovery process started (htdb5) started logmerger processThu Jul 16 13:45:50 2015Managed Standby Recoverystarting Real Time ApplyParallel Media Recovery startedwith 16 slavesWaiting 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.885182537Completed: ALTER DATABASERECOVER MANAGED STANDBY DATABASE THROUGHALL SWITCHOVER DISCONNECT USING CURRENTLOGFILEThu Jul 16 13:46:08 2015Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180063.3683.885182777Thu Jul 16 13:46:35 2015Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180064.2542.885183119Thu Jul 16 13:47:07 2015Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180065.2717.885183615

總結

以上就是本文關于oracle數據庫ORA-01196錯誤解決辦法分享的全部內容，希望對大家有所幫助。有什么問題可以直接留言，小編會及時回復大家的。感謝朋友們對本站的支持！這里推薦幾本oracle相關的書籍，供廣大編程愛好及工作者學習、參考。

注：相關教程知識閱讀請移步到oracle教程頻道。

上一篇：oracle普通表轉化為分區表的方法

下一篇：oracle常見故障類別及規劃解析