數(shù)據(jù)庫(kù)ORA-01196故障-歸檔日志丟失恢復(fù)詳解
問(wèn)題：
由于機(jī)房停電，其中一DG備庫(kù)無(wú)法open，啟動(dòng)時(shí)報(bào)錯(cuò)
啟動(dòng)數(shù)據(jù)庫(kù)時(shí)報(bào)下面的錯(cuò)誤
SQL> alter database open; alter database open *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-10458: standby database requiresrecovery
ORA-01196: 文件 1 由于介質(zhì)恢復(fù)會(huì)話失敗而不一致
ORA-01110: 數(shù)據(jù)文件 1:'+DATA/htdb7/datafile/system.313.884996245'
查看歸檔日志應(yīng)用情況，發(fā)現(xiàn)一部分日志沒(méi)應(yīng)用
SQL> Select Name,Sequence#,applied,completion_time From v$archived_log Order By Sequence# Desc; Name, Sequence# applied completion_time +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328776.705.939567729 328776 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328775.713.939567727 328775 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328774.777.939567727 328774 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328773.771.939567725 328773 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328772.422.939567721 328772 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328771.482.939567721 328771 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328770.755.939567721 328770 YES NO 2017/3/2515:02 +FRA/htdb7/archivelog/2017_03_24/thread_1_seq_328757.1255.939481573 328757 YES NO 2017/3/2415:06 +FRA/htdb7/archivelog/2017_03_24/thread_1_seq_328756.795.939480431 328756 YES YES 2017/3/2414:47 +FRA/htdb7/archivelog/2017_03_24/thread_1_seq_328755.543.939479395 328755 YES YES 2017/3/2414:29 +FRA/htdb7/archivelog/2017_03_24/thread_1_seq_328754.390.939478683 328754 YES YES 2017/3/2414:18 +FRA/htdb7/archivelog/2017_03_24/thread_1_seq_328753.1845.939477943 328753 YES YES 2017/3/2414:05 --再和其它備庫(kù)或主庫(kù)的歸檔日志做對(duì)比,很明顯發(fā)現(xiàn)這個(gè)備庫(kù)沒(méi)有同步并應(yīng)用主庫(kù)的日志 --此備庫(kù)： [oracle@hotel07 ~]$ asmcmd -p ASMCMD [+fra/htdb7/ARCHIVELOG] > cd 2017_03_24/ ASMCMD [+fra/htdb7/ARCHIVELOG/2017_03_24]> ls ...... thread_1_seq_328754.390.939478683 thread_1_seq_328755.543.939479395 thread_1_seq_328756.795.939480431 thread_1_seq_328757.1255.939481573 --其它正常的備庫(kù) [oracle@hotel05 ~]$ asmcmd -p ASMCMD [+fra/htdb5/ARCHIVELOG/2017_03_24]> ls thread_1_seq_328754.4124.939478683 thread_1_seq_328755.349.939479395 thread_1_seq_328756.852.939480431 thread_1_seq_328757.1420.939481575 thread_1_seq_328758.3356.939510647 thread_1_seq_328759.4592.939510649 thread_1_seq_328760.3205.939510647 thread_1_seq_328761.5308.939510649 thread_1_seq_328762.5227.939510653 .....
解決辦法：
需要從其它備庫(kù)或主庫(kù)上面把此備庫(kù)缺失的歸檔日志手動(dòng)傳輸過(guò)來(lái)，然后再進(jìn)行open操作
步驟如下：
1. 在另一正常的備庫(kù)用rman備份缺失的歸檔日志
[oracle@hotel05 ~]$ rman target / RMAN> copy archivelog'+fra/htdb5/ARCHIVELOG/2017_03_24/thread_1_seq_328759.4592.939510649' to'/home/oracle/arcbak/thread_1_seq_328759.4592.939510649';
啟動(dòng) backup 于 25-3月 -17
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在開(kāi)始復(fù)制歸檔日志
輸入歸檔日志線程=1 序列=328759 RECID=328754 STAMP=939510652
輸出文件名=/home/oracle/arcbak/thread_1_seq_328759.4592.939510649 RECID=328794STAMP=939571923
通道 ORA_DISK_1: 歸檔日志復(fù)制完成, 經(jīng)過(guò)時(shí)間: 00:00:03
完成 backup 于 25-3月 -17
......
. 備份完成后，把歸檔傳輸?shù)絹G失歸檔的備庫(kù)
[oracle@hotel05 arcbak]$ scp * hotel07:/home/oracle/arcbak/
3. 然后在此備庫(kù)上進(jìn)行恢復(fù)操作
-- 編制歸檔文件目錄
[oracle@hotel07 ~]$ rman target /
恢復(fù)管理器: Release 11.2.0.2.0 - Production on 星期六 3月 25 15:42:112017
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
已連接到目標(biāo)數(shù)據(jù)庫(kù): HTDB4 (DBID=1083719948, 未打開(kāi))
RMAN> catalog start with '/home/oracle/arcbak';
搜索與樣式 /home/oracle/arcbak 匹配的所有文件
數(shù)據(jù)庫(kù)未知文件的列表 ===================================== 文件名: /home/oracle/arcbak/thread_1_seq_328763.4773.939510653 文件名: /home/oracle/arcbak/thread_1_seq_328767.2765.939511033 文件名: /home/oracle/arcbak/thread_1_seq_328766.5854.939511023 文件名: /home/oracle/arcbak/thread_1_seq_328759.4592.939510649 文件名: /home/oracle/arcbak/thread_1_seq_328758.3356.939510647 文件名: /home/oracle/arcbak/thread_1_seq_328760.3205.939510647 文件名: /home/oracle/arcbak/thread_1_seq_328762.5227.939510653 文件名: /home/oracle/arcbak/thread_1_seq_328761.5308.939510649 文件名: /home/oracle/arcbak/thread_1_seq_328757.1420.939481575 文件名: /home/oracle/arcbak/thread_1_seq_328764.5801.939510653 文件名: /home/oracle/arcbak/thread_1_seq_328765.3298.939510657
是否確實(shí)要將上述文件列入目錄(輸入 YES 或 NO)? y
正在編制文件目錄...
目錄編制完畢
已列入目錄的文件的列表 ======================= 文件名: /home/oracle/arcbak/thread_1_seq_328763.4773.939510653 文件名: /home/oracle/arcbak/thread_1_seq_328767.2765.939511033 文件名: /home/oracle/arcbak/thread_1_seq_328766.5854.939511023 文件名: /home/oracle/arcbak/thread_1_seq_328759.4592.939510649 文件名: /home/oracle/arcbak/thread_1_seq_328758.3356.939510647 文件名: /home/oracle/arcbak/thread_1_seq_328760.3205.939510647 文件名: /home/oracle/arcbak/thread_1_seq_328762.5227.939510653 文件名: /home/oracle/arcbak/thread_1_seq_328761.5308.939510649 文件名: /home/oracle/arcbak/thread_1_seq_328757.1420.939481575 文件名: /home/oracle/arcbak/thread_1_seq_328764.5801.939510653 文件名: /home/oracle/arcbak/thread_1_seq_328765.3298.939510657
-- 恢復(fù)歸檔日志 RMAN> copy archivelog '/home/oracle/arcbak/thread_1_seq_328757.1420.939481575' to '+fra';
啟動(dòng) backup 于 25-3月 -17
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在開(kāi)始復(fù)制歸檔日志
輸入歸檔日志線程=1 序列=328760 RECID=149368 STAMP=939573701
輸出文件名=+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328760.474.939573739RECID=149375 STAMP=939573738
通道 ORA_DISK_1: 歸檔日志復(fù)制完成, 經(jīng)過(guò)時(shí)間: 00:00:01
完成 backup 于 25-3月 -17
......
4. 最后就可以open數(shù)據(jù)庫(kù)了
SQL> alter database open; SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY -- 查看日志 ，歸檔日志正常進(jìn)行應(yīng)用 alter database open Data Guard Broker initializing... Data Guard Broker initialization complete Beginning standby crash recovery. Serial Media Recovery started Managed Standby Recovery starting Real TimeApply Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328757.499.939573737 Media Recovery Log/home/oracle/arcbak/thread_1_seq_328758.3356.939510647 Sat Mar 25 16:43:57 2017 Incomplete Recovery applied until change91347484119 time 03/24/2017 15:06:26 Completed standby crash recovery. Sat Mar 25 16:43:58 2017 SMON: enabling cache recovery Dictionary check beginning Dictionary check complete Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off(no async multimaster replication found) Physical standby database opened for readonly access. Completed: alter database open Sat Mar 25 16:44:01 2017 ALTER DATABASE RECOVER MANAGED STANDBYDATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILE Attempt to start background Managed StandbyRecovery process (htdb7) Sat Mar 25 16:44:01 2017 MRP0 started with pid=47, OS id=9619 MRP0: Background Managed Standby Recoveryprocess started (htdb7) started logmerger process Sat Mar 25 16:44:06 2017 Managed Standby Recovery starting Real TimeApply Parallel Media Recovery started with 16slaves Waiting for all non-current ORLs to bearchived... All non-current ORLs have been archived. Media Recovery Log /home/oracle/arcbak/thread_1_seq_328758.3356.939510647 Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328759.1574.939573739 Completed: ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILE Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328760.922.939573741 Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328761.695.939573743 Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328762.1769.939573745 Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328763.1422.939573745
總結(jié)：
在由于停電和網(wǎng)絡(luò)原因，造成主備數(shù)據(jù)不同步，日志丟失的情況，主要學(xué)會(huì)使用rman工具把歸檔文件在fs和asm之間傳輸。在數(shù)據(jù)庫(kù)恢復(fù)時(shí)會(huì)經(jīng)常用到。
另外，如果數(shù)據(jù)庫(kù)開(kāi)啟了閃回功能 ，也可以使用閃回?cái)?shù)據(jù)庫(kù)的某個(gè)時(shí)點(diǎn)進(jìn)行恢復(fù)。可以參考另一篇博文：oracle數(shù)據(jù)庫(kù)ORA-01196錯(cuò)誤解決辦法分享。
希望對(duì)大家有所幫助，感謝閱讀。
相關(guān)文章
Oracle 獲得以百分號(hào)結(jié)尾的腳本有三種寫(xiě)法
本節(jié)主要介紹了Oracle 獲得以百分號(hào)結(jié)尾的腳本有三種寫(xiě)法，以獲得ID=4443的數(shù)據(jù)行為例2014-07-07
oracle基礎(chǔ)教程之多表關(guān)聯(lián)查詢(xún)
在實(shí)際開(kāi)發(fā)中每個(gè)表的信息都不是獨(dú)立的,而是若干個(gè)表之間存在一定的聯(lián)系,如果用戶(hù)查詢(xún)某一個(gè)表的信息時(shí),可能需要查詢(xún)關(guān)聯(lián)表的信息,這就是多表關(guān)聯(lián)查詢(xún),這篇文章主要給大家介紹了關(guān)于oracle基礎(chǔ)教程之多表關(guān)聯(lián)查詢(xún)的相關(guān)資料,需要的朋友可以參考下2023-12-12
Oracle數(shù)據(jù)庫(kù)TNS常見(jiàn)錯(cuò)誤的解決方法匯總
這篇文章主要介紹了Oracle數(shù)據(jù)庫(kù)TNS常見(jiàn)錯(cuò)誤的解決方法,需要的朋友可以參考下2014-07-07
在Oracle數(shù)據(jù)庫(kù)中，主要有兩種日志操作模式，分別為非歸檔模式與歸檔模式。默認(rèn)情況下，數(shù)據(jù)庫(kù)采用的是非歸檔模式。作為一個(gè)合格的數(shù)據(jù)庫(kù)管理員，應(yīng)當(dāng)深入了解這兩種日志操作模式的特點(diǎn)，并且在數(shù)據(jù)庫(kù)建立時(shí)，選擇合適的操作模式2013-01-01
Oracle執(zhí)行計(jì)劃及性能調(diào)優(yōu)詳解使用方法
在Oracle數(shù)據(jù)庫(kù)中,通過(guò)使用EXPLAIN PLAN、AWR、SQL Trace等工具可以對(duì)SQL性能進(jìn)行詳細(xì)分析,EXPLAIN PLAN可以展示SQL執(zhí)行計(jì)劃和關(guān)鍵性能指標(biāo)如操作類(lèi)型、成本、行數(shù)等,本文給大家介紹Oracle執(zhí)行計(jì)劃及性能調(diào)優(yōu)詳解使用方法,感興趣的朋友跟隨小編一起看看吧2024-09-09
這篇文章主要介紹了oracle中exp,imp的使用詳解,需要的朋友可以參考下2015-07-07
Oracle數(shù)據(jù)庫(kù)安全策略分析(二）
Oracle數(shù)據(jù)庫(kù)安全策略分析(二）...2007-03-03
Oracle用decode函數(shù)或CASE-WHEN實(shí)現(xiàn)自定義排序
這篇文章主要介紹了Oracle用decode函數(shù)或CASE-WHEN實(shí)現(xiàn)自定義排序功能，本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-05-05
Oracle客戶(hù)端與plsql查詢(xún)數(shù)據(jù)亂碼修改成中文的快速解決方法
這篇文章主要介紹了Oracle客戶(hù)端與plsql查詢(xún)數(shù)據(jù)亂碼修改成中文的快速解決方法的相關(guān)資料,需要的朋友可以參考下2016-08-08
由于需要，這會(huì)兒需要卸載掉本機(jī)上的oracle 11g數(shù)據(jù)庫(kù)（我是在Windows 7系統(tǒng)上裝的），在網(wǎng)上搜的了挺多方法的，有些說(shuō)的不清楚。今天腳本之家小編給大家分享一下關(guān)于oracle11g卸載步驟，需要的朋友參考下吧2016-11-11