Oracle 2023-02-15

数据库ORA-01196故障-归档日志丢失恢复详解

问题：

由于机房停电，其中一DG备库无法open，启动时报错

启动数据库时报下面的错误

SQL> alter database open;
alter database open
*

第 1 行出现错误:

ORA-10458: standby database requiresrecovery
ORA-01196: 文件 1 由于介质恢复会话失败而不一致
ORA-01110: 数据文件 1:’+DATA/htdb7/datafile/system.313.884996245′

查看归档日志应用情况，发现一部分日志没应用

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

–再和其它备库或主库的归档日志做对比,很明显发现这个备库没有同步并应用主库的日志
–此备库：
[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

–其它正常的备库
[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
…..

解决办法：

需要从其它备库或主库上面把此备库缺失的归档日志手动传输过来，然后再进行open操作

步骤如下：

1. 在另一正常的备库用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′;

启动 backup 于 25-3月 -17

使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始复制归档日志

输入归档日志线程=1 序列=328759 RECID=328754 STAMP=939510652

输出文件名=/home/oracle/arcbak/thread_1_seq_328759.4592.939510649 RECID=328794STAMP=939571923

通道 ORA_DISK_1: 归档日志复制完成, 经过时间: 00:00:03

完成 backup 于 25-3月 -17
……

. 备份完成后，把归档传输到丢失归档的备库
[oracle@hotel05 arcbak]$ scp * hotel07:/home/oracle/arcbak/

3. 然后在此备库上进行恢复操作

— 编制归档文件目录
[oracle@hotel07 ~]$ rman target /

恢复管理器: Release 11.2.0.2.0 – Production on 星期六 3月 25 15:42:112017
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
已连接到目标数据库: HTDB4 (DBID=1083719948, 未打开)

RMAN> catalog start with ‘/home/oracle/arcbak’;

搜索与样式 /home/oracle/arcbak 匹配的所有文件

数据库未知文件的列表
=====================================
文件名: /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

是否确实要将上述文件列入目录(输入 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

— 恢复归档日志
RMAN> copy archivelog ‘/home/oracle/arcbak/thread_1_seq_328757.1420.939481575’ to ‘+fra’;

启动 backup 于 25-3月 -17

使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始复制归档日志

输入归档日志线程=1 序列=328760 RECID=149368 STAMP=939573701
输出文件名=+FRA/htdb7/archivelog/2017_03_25/thread_1_seq_328760.474.939573739RECID=149375 STAMP=939573738

通道 ORA_DISK_1: 归档日志复制完成, 经过时间: 00:00:01

完成 backup 于 25-3月 -17
……

4. 最后就可以open数据库了

SQL> alter database open;
SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY WITH APPLY

— 查看日志，归档日志正常进行应用
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

总结：

在由于停电和网络原因，造成主备数据不同步，日志丢失的情况，主要学会使用rman工具把归档文件在fs和asm之间传输。在数据库恢复时会经常用到。

另外，如果数据库开启了闪回功能，也可以使用闪回数据库的某个时点进行恢复。可以参考另一篇博文：oracle数据库ORA-01196错误解决办法分享。

希望对大家有所帮助，感谢阅读。

ORACLE 技术文档数据库

数据运维技术 » 数据库ORA-01196故障-归档日志丢失恢复详解

分享到：

相关推荐