數(shù)據(jù)庫(kù)ORA-01196故障-歸檔日志丟失恢復(fù)詳解

2024-08-29 14:00:46

字體：大中小

供稿：網(wǎng)友

問題：

由于機(jī)房停電，其中一DG備庫(kù)無法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)一部分日志沒應(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ù)沒有同步并應(yīng)用主庫(kù)的日志--此備庫(kù)：[oracle@hotel07 ~]$ asmcmd -pASMCMD [+fra/htdb7/ARCHIVELOG] > cd 2017_03_24/ASMCMD [+fra/htdb7/ARCHIVELOG/2017_03_24]> ls......thread_1_seq_328754.390.939478683thread_1_seq_328755.543.939479395thread_1_seq_328756.795.939480431thread_1_seq_328757.1255.939481573 --其它正常的備庫(kù)[oracle@hotel05 ~]$ asmcmd -pASMCMD [+fra/htdb5/ARCHIVELOG/2017_03_24]> lsthread_1_seq_328754.4124.939478683thread_1_seq_328755.349.939479395thread_1_seq_328756.852.939480431thread_1_seq_328757.1420.939481575thread_1_seq_328758.3356.939510647thread_1_seq_328759.4592.939510649thread_1_seq_328760.3205.939510647thread_1_seq_328761.5308.939510649thread_1_seq_328762.5227.939510653.....

解決辦法：

需要從其它備庫(kù)或主庫(kù)上面把此備庫(kù)缺失的歸檔日志手動(dòng)傳輸過來，然后再進(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: 正在開始復(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)過時(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, 未打開)

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: 正在開始復(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)過時(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 openData Guard Broker initializing...Data Guard Broker initialization completeBeginning standby crash recovery.Serial Media Recovery startedManaged Standby Recovery starting Real TimeApplyMedia Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328757.499.939573737Media Recovery Log/home/oracle/arcbak/thread_1_seq_328758.3356.939510647Sat Mar 25 16:43:57 2017Incomplete Recovery applied until change91347484119 time 03/24/2017 15:06:26Completed standby crash recovery.Sat Mar 25 16:43:58 2017SMON: enabling cache recoveryDictionary check beginningDictionary check completeDatabase Characterset is ZHS16GBKNo Resource Manager plan activereplication_dependency_tracking turned off(no async multimaster replication found)Physical standby database opened for readonly access.Completed: alter database openSat Mar 25 16:44:01 2017ALTER DATABASE RECOVER MANAGED STANDBYDATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILEAttempt to start background Managed StandbyRecovery process (htdb7)Sat Mar 25 16:44:01 2017MRP0 started with pid=47, OS id=9619MRP0: Background Managed Standby Recoveryprocess started (htdb7) started logmerger processSat Mar 25 16:44:06 2017Managed Standby Recovery starting Real TimeApplyParallel Media Recovery started with 16slavesWaiting 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.939510647Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328759.1574.939573739Completed: ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILEMedia Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328760.922.939573741Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328761.695.939573743Media Recovery Log+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328762.1769.939573745Media 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ù)開啟了閃回功能，也可以使用閃回?cái)?shù)據(jù)庫(kù)的某個(gè)時(shí)點(diǎn)進(jìn)行恢復(fù)。

希望對(duì)大家有所幫助，感謝閱讀。

注：相關(guān)教程知識(shí)閱讀請(qǐng)移步到oracle教程頻道。

上一篇：Oracle中正則表達(dá)式的使用實(shí)例教程

下一篇：oracle中if/else的三種實(shí)現(xiàn)方式詳解