AI 助理
备案控制台
开发者社区 数据库 文章 正文

dataguard主库丢失archivelog，如何不重建备库？

2017-11-08 1158
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据仓库AnalyticDB MySQL版，基础版 8ACU 100GB 1个月
简介：

dataguard主库丢失archivelog，如何不重建备库？

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 提供oracle技术支持服务

本文链接地址:dataguard主库丢失archivelog，如何不重建备库？

昨天群中有网友问到dataguard环境中，由于主库archivelog丢失，且尚未同步到standby，问如何在避免
重建standby的情况下来将standby恢复成功的。 下面是我的测试过程，供参考！

—-主库

SQL>SELECT database_role FROM v$database;
 
DATABASE_ROLE
----------------PRIMARY
 
SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch
Oldest online log SEQUENCE382NEXT log SEQUENCETO archive   383CURRENT log SEQUENCE383
SQL>SELECTCOUNT(1)FROM roger.test;
 
  COUNT(1)----------30

—-备库

SQL>SELECT database_role FROM v$database;
 
DATABASE_ROLE
----------------
PHYSICAL STANDBY
 
SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch2
Oldest online log SEQUENCE382NEXT log SEQUENCETO archive   0CURRENT log SEQUENCE383
SQL> 
SQL>ALTERDATABASE recover managed standby DATABASE cancel;
 
DATABASE altered.
 
SQL>ALTERDATABASEOPENREADONLY;
 
DATABASE altered.
 
SQL>SELECTCOUNT(1)FROM roger.test;
 
  COUNT(1)----------30

模拟主库丢失归档的情况：

—主库

SQL>SELECT*FROM v$Log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------11383104857601 NO  CURRENT74063810-OCT-1221382209715201 YES INACTIVE                74063310-OCT-12
 
SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch
Oldest online log SEQUENCE382NEXT log SEQUENCETO archive   383CURRENT log SEQUENCE383
SQL>ALTER system SET log_archive_dest_state_2 ='defer';
 
System altered.
 
SQL>DELETEFROM roger.test WHERE rownum <11;
 
10ROWS deleted.
 
SQL> commit;
 
Commit complete.
 
SQL>SELECTCOUNT(*)FROM roger.test;
 
  COUNT(*)----------20
 
SQL>ALTER system switch logfile;
 
System altered.
 
SQL>/
 
System altered.
 
SQL>/
 
System altered.
 
SQL> !
[oracle@primarydb ~]$ cd /arch
[oracle@primarydb arch]$ ls -ltr|tail -10-rw-r-----  1 oracle dba  3456000 Oct 10 15:18 1_374_726529113.dbf-rw-r-----  1 oracle dba   593408 Oct 10 15:23 1_375_726529113.dbf-rw-r-----  1 oracle dba     4608 Oct 10 15:23 1_376_726529113.dbf-rw-r-----  1 oracle dba     1024 Oct 10 15:23 1_377_726529113.dbf-rw-r-----  1 oracle dba    68096 Oct 10 15:25 1_378_726529113.dbf-rw-r-----  1 oracle dba  1297408 Oct 10 15:34 1_379_726529113.dbf-rw-r-----  1 oracle dba     2048 Oct 10 18:35 1_382_726529113.dbf-rw-r-----  1 oracle dba   166400 Oct 10 18:44 1_383_726529113.dbf-rw-r-----  1 oracle dba     2560 Oct 10 18:44 1_384_726529113.dbf-rw-r-----  1 oracle dba     1024 Oct 10 18:44 1_385_726529113.dbf[oracle@primarydb arch]$ rm 1_383_726529113.dbf  ---删除archivelog[oracle@primarydb arch]$ rm 1_384_726529113.dbf 
[oracle@primarydb arch]$ exit
exit
 
SQL>ALTER system SET log_archive_dest_state_2 ='enable';
 
System altered.
 
SQL>

—备库

SQL>ALTERDATABASE recover managed standby DATABASE disconnect FROMSESSION;
 
DATABASE altered.
 
SQL> 
 
此时alert log信息如下：
Tue Nov 1314:45:262012
MRP0: Background Managed Standby Recovery process started (test)
Managed Standby Recovery NOTUSINGREALTIME Apply
 parallel recovery started WITH2 processes
Tue Nov 1314:45:312012
Waiting FORALL non-CURRENT ORLs TO be archived...
Media Recovery Waiting FOR thread 1SEQUENCE383
Tue Nov 1314:45:322012
Completed: ALTERDATABASE recover managed standby DATABASE disconnect FROMSESSION
Tue Nov 1314:45:592012
Redo Shipping Client Connected AS PUBLIC
-- Connected User is Valid
RFS[2]: Assigned TO RFS process 9542
RFS[2]: IDENTIFIEDDATABASETYPEAS'physical standby'
RFS[2]: Archived Log: '/arch2/1_385_726529113.dbf'
Tue Nov 1314:46:02 2012
Fetching gap SEQUENCEIN thread 1, gap SEQUENCE383-384
Tue Nov 1314:47:02 2012
FAL[client]: Failed TO request gap SEQUENCE 
 GAP - thread 1SEQUENCE383-384
 DBID 2024668720 branch 726529113
FAL[client]: ALL defined FAL servers have been attempted.-------------------------------------------------------------CHECK that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter IS defined TO a VALUE that IS sufficiently LARGE
enough TO maintain adequate log switch information TO resolve
archivelog gaps.

如何在重建standby的情况下搞好备库呢？mos上,其实也有文章进行描述的,就是利用rman进行增量scn的恢复,下面我来进行展示：

1)首先定位到scn

SQL>SELECTSEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM v$archived_log WHERESEQUENCE# >382ORDERBY1;
 
 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------383740638740911384740911740915385740915740917385740915740917

2)根据scn，进行rman增量备份

[oracle@primarydb ~]$ cd $ORACLE_HOME/bin
[oracle@primarydb bin]$ ./rman target /
 
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 10 18:51:57 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
connected to target database: TEST (DBID=2024668720)
 
RMAN> backup device type disk incremental from scn 740638 database format '/tmp/test_db_incre.bbk';
 
Starting backup at 10-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle/product/oradata/test/test01.dbf
input datafile fno=00007 name=/oracle/product/oradata/test/sysaux01.dbf
input datafile fno=00009 name=/oracle/product/oradata/test/test03.dbf
input datafile fno=00006 name=/oracle/product/oradata/test/undo02.dbf
input datafile fno=00001 name=/oracle/product/oradata/test/system01.dbf
input datafile fno=00005 name=/oracle/product/oradata/test/perfstat.dbf
input datafile fno=00003 name=/oracle/product/oradata/test/rman.dbf
input datafile fno=00002 name=/oracle/product/oradata/test/undo01.dbf
input datafile fno=00008 name=/oracle/product/oradata/test/test02.dbf
input datafile fno=00010 name=/oracle/product/oradata/test/test04.dbf
input datafile fno=00011 name=/oracle/product/oradata/test/test05.dbf
channel ORA_DISK_1: starting piece 1 at 10-OCT-12
 
channel ORA_DISK_1: finished piece 1 at 10-OCT-12
piece handle=/tmp/test_db_incre.bbk tag=TAG20121010T185204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:26
Finished backup at 10-OCT-12
 
RMAN>

3) 拷贝增量备份到standby

[oracle@primarydb bin]$ cd /tmp
[oracle@primarydb tmp]$ scp test_db_incre.bbk 192.168.3.176:/tmp/backup
The authenticity of host '192.168.3.176 (192.168.3.176)' can't be established.
RSA key fingerprint is a4:54:6b:bf:12:34:42:73:f5:ba:5f:38:c7:28:9c:b5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.3.176' (RSA) to the list of known hosts.
oracle@192.168.3.176's password: 
test_db_incre.bbk                             100%  736KB 736.0KB/s   00:00    
[oracle@primarydb tmp]$

4) standby进行recover

[oracle@standbydb bin]$ sqlplus "/as sysdba"SQL*Plus: Release 10.2.0.4.0 - Production ON Tue Nov 1315:03:462012
 
Copyright (c)1982,2007, Oracle.ALL Rights Reserved.
 
 
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP,DATA Mining ANDREAL Application Testing options
 
SQL>ALTERDATABASE recover managed standby DATABASE cancel;
 
DATABASE altered.
 
SQL> exit
Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP,DATA Mining ANDREAL Application Testing options
[oracle@standbydb ~]$ cd $ORACLE_HOME/bin
[oracle@standbydb bin]$ ./rman target /
 
Recovery Manager: Release 10.2.0.4.0 - Production ON Tue Nov 1314:58:252012
 
Copyright (c)1982,2007, Oracle.ALL rights reserved.
 
connected TO target DATABASE: TEST (DBID=2024668720,NOTOPEN)
 
RMAN> catalog backuppiece '/tmp/backup/test_db_incre.bbk';
 
USING target DATABASE control file instead OF recovery catalog
cataloged backuppiece
backup piece handle=/tmp/backup/test_db_incre.bbk recid=49 stamp=799253957
 
RMAN>  recover DATABASE noredo;
 
Starting recover at 13-NOV-12USING target DATABASE control file instead OF recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=97 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s)TO restore FROM backup SET
destination FOR restore OF datafile 00001: /oracle/product/oradata/test/system01.dbf
destination FOR restore OF datafile 00002: /oracle/product/oradata/test/undo01.dbf
destination FOR restore OF datafile 00003: /oracle/product/oradata/test/rman.dbf
destination FOR restore OF datafile 00004: /oracle/product/oradata/test/test01.dbf
destination FOR restore OF datafile 00005: /oracle/product/oradata/test/perfstat.dbf
destination FOR restore OF datafile 00006: /oracle/product/oradata/test/undo02.dbf
destination FOR restore OF datafile 00007: /oracle/product/oradata/test/sysaux01.dbf
destination FOR restore OF datafile 00008: /oracle/product/oradata/test/test02.dbf
destination FOR restore OF datafile 00009: /oracle/product/oradata/test/test03.dbf
destination FOR restore OF datafile 00010: /oracle/product/oradata/test/test04.dbf
destination FOR restore OF datafile 00011: /oracle/product/oradata/test/test05.dbf
channel ORA_DISK_1: reading FROM backup piece /tmp/backup/test_db_incre.bbk
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/backup/test_db_incre.bbk tag=TAG20121010T185204
channel ORA_DISK_1: restore complete, elapsed TIME: 00:00:01
Finished recover at 13-NOV-12
 
RMAN> 
 
如下信息是recover时，alert log的记录信息：
Tue Nov 1315:04:05 2012
Completed: ALTERDATABASE recover managed standby DATABASE cancel
Tue Nov 1315:04:552012
Incremental restore complete OF datafile 2/oracle/product/oradata/test/undo01.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 8/oracle/product/oradata/test/test02.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 10/oracle/product/oradata/test/test04.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 11/oracle/product/oradata/test/test05.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 3/oracle/product/oradata/test/rman.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 5/oracle/product/oradata/test/perfstat.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 1/oracle/product/oradata/test/system01.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 6/oracle/product/oradata/test/undo02.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 4/oracle/product/oradata/test/test01.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 7/oracle/product/oradata/test/sysaux01.dbf
  checkpoint IS741134
Incremental restore complete OF datafile 9/oracle/product/oradata/test/test03.dbf
  checkpoint IS741134

5) 开启standby同步，检查是否ok。

SQL>ALTERDATABASE recover managed standby DATABASE disconnect FROMSESSION;
 
DATABASE altered.
 
SQL>SELECT database_role,open_mode FROM v$database;
 
DATABASE_ROLE    OPEN_MODE
---------------- ----------
PHYSICAL STANDBY MOUNTED
 
--主库切换日志
SQL>SELECT database_role,open_mode FROM v$database;
 
DATABASE_ROLE    OPEN_MODE
---------------- ----------PRIMARYREADWRITE
 
SQL> 
SQL>ALTER system switch logfile;
 
System altered.
SQL>SELECTMAX(al.SEQUENCE#)"Last Seq Recieved",2MAX(lh.SEQUENCE#)"Last Seq Applied"3FROM v$archived_log al, v$log_history lh;  
 
LAST Seq Recieved LAST Seq Applied
----------------- ----------------386386
 
SQL>--备库
 
SQL>SELECTMAX(al.SEQUENCE#)"Last Seq Recieved",2MAX(lh.SEQUENCE#)"Last Seq Applied"3FROM v$archived_log al, v$log_history lh;  
 
LAST Seq Recieved LAST Seq Applied
----------------- ----------------386382
 
SQL> 
SQL>SELECT*FROM v$archive_gap;   
 
   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------1383384
 
备库archivelog：
[oracle@standbydb arch2]$ ls -ltr |tail -5-rw-r-----  1 oracle dba     2048 Nov 13 14:33 1_381_726529113.dbf-rw-r-----  1 oracle dba     2048 Nov 13 14:34 1_382_726529113.dbf-rw-r-----  1 oracle dba     1024 Nov 13 14:45 1_385_726529113.dbf-rw-r-----  1 oracle dba  1262080 Nov 13 15:06 1_386_726529113.dbf
 
备库alert log此时的信息：
Tue Nov 1315:06:172012ALTERDATABASE recover managed standby DATABASE disconnect FROMSESSION
Tue Nov 1315:06:172012
Attempt TOSTART background Managed Standby Recovery process (test)
MRP0 started WITH pid=19, OS id=13497
Tue Nov 1315:06:172012
MRP0: Background Managed Standby Recovery process started (test)
Managed Standby Recovery NOTUSINGREALTIME Apply
 parallel recovery started WITH2 processes
Tue Nov 1315:06:222012
Waiting FORALL non-CURRENT ORLs TO be archived...
Media Recovery Waiting FOR thread 1SEQUENCE383
Fetching gap SEQUENCEIN thread 1, gap SEQUENCE383-384
Tue Nov 1315:06:232012
Completed: ALTERDATABASE recover managed standby DATABASE disconnect FROMSESSION
Tue Nov 1315:06:542012
Redo Shipping Client Connected AS PUBLIC
-- Connected User is Valid
RFS[3]: Assigned TO RFS process 13596
RFS[3]: IDENTIFIEDDATABASETYPEAS'physical standby'PRIMARYDATABASEISIN MAXIMUM PERFORMANCE mode
PRIMARYDATABASEISIN MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 4: '/oracle/product/oradata/test/redo04.log'
Tue Nov 1315:06:542012
RFS[2]: Successfully opened standby log 3: '/oracle/product/oradata/test/redo03.log'
Tue Nov 1315:07:232012
FAL[client]: Failed TO request gap SEQUENCE 
 GAP - thread 1SEQUENCE383-384
 DBID 2024668720 branch 726529113
FAL[client]: ALL defined FAL servers have been attempted.-------------------------------------------------------------CHECK that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter IS defined TO a VALUE that IS sufficiently LARGE
enough TO maintain adequate log switch information TO resolve
archivelog gaps.

我们可以看到，虽然备库，仍然在提示383,384是gap ，但是实际上已经是同步的了。

6)最后来验证下数据

SQL>ALTERDATABASE recover managed standby DATABASE cancel;
 
DATABASE altered.
 
SQL>SELECT file#,checkpoint_change# FROM v$datafile ORDERBY1;
 
     FILE# CHECKPOINT_CHANGE#
---------- ------------------174063827406383740638474063857406386740638774063887406389740638107406381174063811ROWS selected.
 
SQL>SELECT file#,checkpoint_change# FROM v$datafile_header ORDERBY1;
 
     FILE# CHECKPOINT_CHANGE#
---------- ------------------174113427411343741134474113457411346741134774113487411349741134107411341174113411ROWS selected.
 
SQL>ALTERDATABASEOPENREADONLY;
ALTERDATABASEOPENREADONLY*
ERROR at line 1:
ORA-16004: backup DATABASE requires recovery
ORA-01152: file 1 was NOT restored FROM a sufficiently OLD backup
ORA-01110: DATA file 1: '/oracle/product/oradata/test/system01.dbf'
 
重建备库的standby controlfile：
SQL>ALTERDATABASECREATE standby controlfile AS'/tmp/standby.ctl';        
 
DATABASE altered.
 
SQL> exit
Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP,DATA Mining ANDREAL Application Testing options
[oracle@primarydb tmp]$ scp standby.ctl 192.168.3.176:/tmp/backup
oracle@192.168.3.176's password: standby.ctl                                                 100% 3928KB   3.8MB/s   00:00    [oracle@primarydb tmp]$  [oracle@standbydb bin]$ ./rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Tue Nov 13 15:19:09 2012 Copyright (c) 1982, 2007, Oracle.  All rights reserved. connected to target database (not started) RMAN> startup nomount    Oracle instance started Total System Global Area     264241152 bytes Fixed Size                     1266944 bytesVariable Size                209718016 bytesDatabase Buffers              50331648 bytesRedo Buffers                   2924544 bytes RMAN> restore controlfile from '/tmp/backup/standby.ctl'; Starting restore at 13-NOV-12using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=101 devtype=DISK channel ORA_DISK_1: copied control file copyoutput filename=/oracle/product/oradata/test/control01.ctlFinished restore at 13-NOV-12 RMAN> startup mount database is already starteddatabase mountedreleased channel: ORA_DISK_1 RMAN> exit  Recovery Manager complete.[oracle@standbydb bin]$ sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 13 15:20:02 2012 Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.  Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> alter database recover managed standby database cancel; Database altered. SQL> SQL> SQL> alter database open read only; Database altered. SQL> select count(1) from roger.test;   COUNT(1)----------        20 

 















本文转自东方之子736651CTO博客，原文链接：http://blog.51cto.com/ecloud/1273338 ，如需转载请自行联系原作者





文章标签：
云原生数据仓库AnalyticDB MySQL版
关系型数据库
Oracle
SQL
数据库
OLAP
相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL，玩转SQL开发等功能！本教程介绍如何在AnalyticDB MySQL中，一键加载内置数据集，并基于自动生成的查询脚本，运行复杂查询语句，秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库，高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准，可以对海量数据进行即时的多维分析透视和业务探索，快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
技术小甜
目录
相关文章
姚远OracleACE
|
数据库
在备库上进行热备份--11.2.0.4之后
一个经典的备份语句：backup database plus archivelog;
姚远OracleACE
50 0
姚远OracleACE
|
数据库
在备库上进行热备份--10G之前
切换主库日志的脚本 logswitch.sh
姚远OracleACE
69 0
姚远OracleACE
|
数据库
在备库上进行冷备份的三个步骤
数据库处于mount状态不一定是一致的，要看mrp进程是否存在！
姚远OracleACE
59 0
姚远OracleACE
修复DataGuard备库中的datafile坏块一例
模拟备库块损坏 使用swingbench给主库施加一定的压力
姚远OracleACE
123 0
小麦苗
|
SQL 存储 数据库
在DG环境中，主库丢失归档，对主库进行基于SCN的增量备份来恢复物理DG环境
在DG环境中，主库丢失归档，对主库进行基于SCN的增量备份来恢复物理DG环境
小麦苗
452 0
snowofsummer
|
Oracle 关系型数据库
dataguard 增量恢复
dataguard 增量恢复
snowofsummer
131 0
snowofsummer
dataguard级联备库设置
dataguard级联备库设置
snowofsummer
121 0
金雪人
|
运维
简单记录一次ADG备库同步故障
这是一套11g的老库，主库3节点，备库1节点。项目上于昨天晚上做某测试扩容了表空间，在其他位置新建了9个数据文件，在备库无法创建这个非标准位置的datafile，从而导致同步中断。
金雪人
425 0
李博 bluemind
|
SQL 监控 Oracle
Oracle主库归档丢失，备库日志有gap，在不重建备库的情况下，恢复备库
李博 bluemind
1223 0
技术小甜
|
Oracle 关系型数据库 数据库
oracle11g dataguard物理备库搭建（关闭主库cp数据文件到备库）
技术小甜
1684 0