数据库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错误解决办法分享。

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

原文链接：http://blog.csdn.net/lichangzai/article/details/65977749

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

相关文章

热门资讯