ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 记一次ADG缺失归档恢复案例

记一次ADG缺失归档恢复案例

原创 Oracle 作者：Curry_0316 时间：2015-12-21 11:36:36 0 删除 编辑
案例背景：
环境 数据库版本 是否rac 系统版本
生产库 11.2.0.3
 两节点
 redhat Linux 6.5
容灾备库（ADG）
 11.2.0.3
 单实例 redhat Linux 5.8
生产备库（ADG）
 11.2.0.3 
 四节点 exdata X3-2  11.2.3.3.1


在容灾备库进行容灾演练，12月17号从physical standby模式切换到snapshot standby模式，切换顺利，18号下午演练完成后将snapshot standby切回physical standby模式，回切成功，但是发现mrp进程恢复到一个归档后就一直卡在那不动了，查看V$DATAGUARD_STATUS.message无任何报错，也没有gap。


1、在容灾备库查看当前数据库归档，发现归档有部分归档缺失（缺失的归档是12月17号的，且从physical standby模式切换到snapshot standby模式后生成的归档）
2、查看生产库发现12月17号归档已被删除
3、查看数据库备份策略发现归档的删除策略为sysdate-2，但是很郁闷的是17号的归档被删除了，这里就不深究为何被删除了，且数据库未配置：CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
4、接下来的思路就是从生产备库获取归档，然后传到容灾备库进行恢复



方案如下：
①、登录生产主库，生成从rman中copy归档到文件系统的脚本
select 'copy archivelog '''||NAME||''' to ''/expdp/arch/'||substr(name,40)||''';' 
from v$archived_log 
where  SEQUENCE#>=29837 
and SEQUENCE#<=29861 
and thread#=1 ;


select 'copy archivelog '''||NAME||''' to ''/expdp/arch/'||substr(name,40)||''';' 
from v$archived_log 
where  SEQUENCE#>=28563
and SEQUENCE#<=28590 
and thread#=2;


确定生成归档的大小（大概17G）


---生成容灾备库注册的日志文件的脚本：
select 'ALTER DATABASE REGISTER PHYSICAL LOGFILE ''/tmp/' ||
       substr(name, 40) || ''';' 
from v$archived_log 
where  SEQUENCE#>=29837 
and SEQUENCE#<=29861 
and thread#=1;


select 'ALTER DATABASE REGISTER PHYSICAL LOGFILE ''/tmp/' ||
       substr(name, 40) || ''';'
from v$archived_log 
where  SEQUENCE#>=28563
and SEQUENCE#<=28590 
and thread#=2;


---将归档从ASM复制到文件系统中
rman target /
执行如下sql：
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29837.15170.898683289' to '/expdp/arch/thread_1_seq_29837.15170.898683289';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29838.23482.898684755' to '/expdp/arch/thread_1_seq_29838.23482.898684755';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29839.8296.898686273' to '/expdp/arch/thread_1_seq_29839.8296.898686273';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29840.23380.898688373' to '/expdp/arch/thread_1_seq_29840.23380.898688373';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29841.13793.898690231' to '/expdp/arch/thread_1_seq_29841.13793.898690231';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29842.23443.898692715' to '/expdp/arch/thread_1_seq_29842.23443.898692715';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29843.23370.898694495' to '/expdp/arch/thread_1_seq_29843.23370.898694495';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29844.23803.898696081' to '/expdp/arch/thread_1_seq_29844.23803.898696081';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29845.15667.898697807' to '/expdp/arch/thread_1_seq_29845.15667.898697807';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29846.17191.898700109' to '/expdp/arch/thread_1_seq_29846.17191.898700109';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29847.22891.898702563' to '/expdp/arch/thread_1_seq_29847.22891.898702563';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29848.11200.898704741' to '/expdp/arch/thread_1_seq_29848.11200.898704741';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29849.24551.898706777' to '/expdp/arch/thread_1_seq_29849.24551.898706777';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29850.13686.898708345' to '/expdp/arch/thread_1_seq_29850.13686.898708345';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29851.21488.898709951' to '/expdp/arch/thread_1_seq_29851.21488.898709951';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29852.4063.898711833' to '/expdp/arch/thread_1_seq_29852.4063.898711833';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29853.10761.898713557' to '/expdp/arch/thread_1_seq_29853.10761.898713557';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29854.23063.898715211' to '/expdp/arch/thread_1_seq_29854.23063.898715211';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29855.23462.898717015' to '/expdp/arch/thread_1_seq_29855.23462.898717015';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29856.9659.898718881' to '/expdp/arch/thread_1_seq_29856.9659.898718881';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29857.19071.898720925' to '/expdp/arch/thread_1_seq_29857.19071.898720925';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29858.3837.898723459' to '/expdp/arch/thread_1_seq_29858.3837.898723459';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29859.16249.898726197' to '/expdp/arch/thread_1_seq_29859.16249.898726197';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29860.23090.898729743' to '/expdp/arch/thread_1_seq_29860.23090.898729743';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_1_seq_29861.6843.898732207' to '/expdp/arch/thread_1_seq_29861.6843.898732207';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28563.20951.898681349' to '/expdp/arch/thread_2_seq_28563.20951.898681349';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28564.11495.898683173' to '/expdp/arch/thread_2_seq_28564.11495.898683173';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28565.18975.898685159' to '/expdp/arch/thread_2_seq_28565.18975.898685159';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28566.23451.898686831' to '/expdp/arch/thread_2_seq_28566.23451.898686831';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28567.24028.898688673' to '/expdp/arch/thread_2_seq_28567.24028.898688673';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28568.24540.898690465' to '/expdp/arch/thread_2_seq_28568.24540.898690465';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28569.21573.898692617' to '/expdp/arch/thread_2_seq_28569.21573.898692617';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28570.23352.898694529' to '/expdp/arch/thread_2_seq_28570.23352.898694529';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28571.2037.898696833' to '/expdp/arch/thread_2_seq_28571.2037.898696833';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28572.9930.898698953' to '/expdp/arch/thread_2_seq_28572.9930.898698953';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28573.5692.898701325' to '/expdp/arch/thread_2_seq_28573.5692.898701325';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28574.22297.898702991' to '/expdp/arch/thread_2_seq_28574.22297.898702991';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28575.19555.898704393' to '/expdp/arch/thread_2_seq_28575.19555.898704393';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28576.7341.898705929' to '/expdp/arch/thread_2_seq_28576.7341.898705929';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28577.10621.898707475' to '/expdp/arch/thread_2_seq_28577.10621.898707475';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28578.23328.898709243' to '/expdp/arch/thread_2_seq_28578.23328.898709243';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28579.11264.898710591' to '/expdp/arch/thread_2_seq_28579.11264.898710591';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28580.22457.898711949' to '/expdp/arch/thread_2_seq_28580.22457.898711949';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28581.7713.898713319' to '/expdp/arch/thread_2_seq_28581.7713.898713319';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28582.5591.898715023' to '/expdp/arch/thread_2_seq_28582.5591.898715023';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28583.13715.898716741' to '/expdp/arch/thread_2_seq_28583.13715.898716741';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28584.4354.898718485' to '/expdp/arch/thread_2_seq_28584.4354.898718485';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28585.17368.898720287' to '/expdp/arch/thread_2_seq_28585.17368.898720287';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28586.13319.898722157' to '/expdp/arch/thread_2_seq_28586.13319.898722157';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28587.3174.898724125' to '/expdp/arch/thread_2_seq_28587.3174.898724125';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28588.10972.898726901' to '/expdp/arch/thread_2_seq_28588.10972.898726901';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28589.4546.898729615' to '/expdp/arch/thread_2_seq_28589.4546.898729615';
copy archivelog '+RECO_FD01/gisdb/archivelog/2015_12_17/thread_2_seq_28590.8524.898730693' to '/expdp/arch/thread_2_seq_28590.8524.898730693';






②、把归档日志文件，scp传到灾备
scp /expdp/arch/thread* oracle@192.168.112.83:/tmp






③、注册一下归档日志
sqlplus / as sysdba
执行如下sql：
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29837.15170.898683289';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29838.23482.898684755';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29839.8296.898686273';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29840.23380.898688373';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29841.13793.898690231';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29842.23443.898692715';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29843.23370.898694495';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29844.23803.898696081';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29845.15667.898697807';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29846.17191.898700109';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29847.22891.898702563';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29848.11200.898704741';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29849.24551.898706777';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29850.13686.898708345';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29851.21488.898709951';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29852.4063.898711833';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29853.10761.898713557';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29854.23063.898715211';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29855.23462.898717015';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29856.9659.898718881';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29857.19071.898720925';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29858.3837.898723459';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29859.16249.898726197';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29860.23090.898729743';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_1_seq_29861.6843.898732207';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28563.20951.898681349';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28564.11495.898683173';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28565.18975.898685159';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28566.23451.898686831';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28567.24028.898688673';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28568.24540.898690465';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28569.21573.898692617';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28570.23352.898694529';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28571.2037.898696833';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28572.9930.898698953';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28573.5692.898701325';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28574.22297.898702991';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28575.19555.898704393';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28576.7341.898705929';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28577.10621.898707475';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28578.23328.898709243';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28579.11264.898710591';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28580.22457.898711949';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28581.7713.898713319';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28582.5591.898715023';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28583.13715.898716741';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28584.4354.898718485';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28585.17368.898720287';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28586.13319.898722157';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28587.3174.898724125';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28588.10972.898726901';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28589.4546.898729615';
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/tmp/thread_2_seq_28590.8524.898730693';






④、重启mrp进程
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29953799/viewspace-1875950/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 记一次ADG standby异常诊断、恢复
下一篇： 集群启动问题总结
请登录后发表评论 登录
全部评论
Curry_0316

注册时间：2014-10-11

  • 博文量
    89
  • 访问量
    280148

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593