合 RAC环境下主库丢失归档，备库DG的恢复过程

【故障处理】DG归档丢失的恢复

前言部分

导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 主库丢失归档，物理DG的恢复

② 其他常用SQL语句

故障分析及解决过程

故障环境介绍

故障发生现象及报错信息

由于客户这边有N套库，我过来时间短，没有完全整理，昨天对一套rac执行健康检查的时候发现带有一套物理DG,但是DG库不同步，而且同步的日志号已经断档很久了，于是决定把这套DG恢复一下，下边简单记录一下。

数据库信息：

DG库信息：

可以看到实例一当前是14311，但是DG库才应用到968，而实例二当前是13403，DG应用到644，下边着手恢复备库，恢复的原理可以参考之前的文档 【DATAGUARD】物理dg在主库丢失归档文件的情况下的恢复(七)： http://blog.itpub.net/26736162/viewspace-1780863/ 。

故障分析及解决过程

在主库看了下，968、644的日志早都不见了，没办法只能对主库进行基于SCN号的增量备份：

先查找最小的scn号：

SELECT (SELECT MIN(d.CHECKPOINT_CHANGE#) FROM v$datafile d) datafile_scn,

(SELECT MIN(d.CHECKPOINT_CHANGE#)

FROM v$datafile_header d

WHERE rownum = 1) datafile_header_scn,

(SELECT current_scn FROM v$database) current_scn,

(SELECT min(b.NEXT_CHANGE#)

FROM v$archived_log b

WHERE b.SEQUENCE# in (968,644)

AND resetlogs_change# =

(SELECT d.RESETLOGS_CHANGE# FROM v$database d)) NEXT_CHANGE#

FROM dual;

我们取12232942713886为备份的SCN号：

备份：

run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

backup as compressed backupset incremental from SCN 12232942713886 database format '/archive/standbynew%d%T%U.bak' include current controlfile for standby filesperset=5 tag 'FOR STANDBY new';

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

将日志传递到备库：

root@ZHLHRDB7:/archive# l

total 478725456

drwxr-xr-x 2 oracle dba 524288 Apr 22 08:56 arch

-rw-r--r-- 1 oracle dba 2253 Apr 08 2015 initnetr.ora

-rwxr-xr-x 1 oracle dba 21708800 Apr 08 2015 standby.ctl

-rwxr-xr-x 1 oracle dba 22414245888 Apr 20 09:06 standby_ORANET_20160130_0bqsm476_1_1.bak

-rwxr-xr-x 1 oracle dba 22140502016 Apr 20 09:36 standby_ORANET_20160130_0cqsm477_1_1.bak

-rwxr-xr-x 1 oracle dba 13977583616 Apr 20 09:57 standby_ORANET_20160130_0dqsmdf6_1_1.bak

-rwxr-xr-x 1 oracle dba 14525480960 Apr 20 10:16 standby_ORANET_20160130_0eqsmdkv_1_1.bak

-rwxr-xr-x 1 oracle dba 14335983616 Apr 20 10:34 standby_ORANET_20160130_0fqsmkgt_1_1.bak

-rwxr-xr-x 1 oracle dba 16120840192 Apr 20 10:55 standby_ORANET_20160130_0gqsmkvf_1_1.bak

-rwxr-xr-x 1 oracle dba 16035766272 Apr 20 11:16 standby_ORANET_20160130_0hqsmrlg_1_1.bak

-rwxr-xr-x 1 oracle dba 16075489280 Apr 20 11:37 standby_ORANET_20160130_0iqsmspa_1_1.bak

-rwxr-xr-x 1 oracle dba 16070926336 Apr 20 11:58 standby_ORANET_20160130_0jqsn37g_1_1.bak

-rwxr-xr-x 1 oracle dba 16039673856 Apr 20 12:19 standby_ORANET_20160130_0kqsn4b0_1_1.bak

-rwxr-xr-x 1 oracle dba 15593078784 Apr 20 13:47 standby_ORANET_20160131_0lqsnadm_1_1.bak

-rwxr-xr-x 1 oracle dba 15463137280 Apr 20 14:07 standby_ORANET_20160131_0mqsnbfu_1_1.bak

-rwxr-xr-x 1 oracle dba 15369084928 Apr 20 14:27 standby_ORANET_20160131_0nqsnhb1_1_1.bak

-rwxr-xr-x 1 oracle dba 15504777216 Apr 20 14:47 standby_ORANET_20160131_0oqsnibd_1_1.bak

-rwxr-xr-x 1 oracle dba 15410495488 Apr 20 15:09 standby_ORANET_20160131_0pqsno4l_1_1.bak

-rwxr-xr-x 1 oracle dba 4063232 Apr 20 15:09 standby_ORANET_20160131_0qqsnp6t_1_1.bak

-rwxr-xr-x 1 oracle dba 4063232 Apr 20 15:09 standby_ORANET_20160131_0rqsnulk_1_1.bak

root@ZHLHRDB7:/archive#

备库恢复控制文件

从健康检查中可以找到最新的控制文件备份集名称是/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak，我们在备库首先启动到nomount状态，然后恢复控制文件：

SQL> show parameter cont

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 31

control_files string +DATA/oranetr/controlfile/cont

rol01.ctl, +DATA/oranetr/contr

olfile/control02.ctl, +DATA/or

anetr/controlfile/control03.ct

l

control_management_pack_access string DIAGNOSTIC+TUNING

global_context_pool_size string

SQL>

col name format a60

set line 9999 pagesize 9999

SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

1 +DATA/oranetr/datafile/system.293.876478571 12233018392104 SYSTEM

2 +DATA/oranetr/datafile/sysaux.291.876478569 12233018392104 ONLINE

3 +DATA/oranetr/datafile/undotbs1.292.876478569 12233018392104 ONLINE

4 +DATA/oranetr/datafile/undotbs2.288.876478511 12233018392104 ONLINE

5 +DATA/oranetr/datafile/users.290.876478513 12233018392104 ONLINE

6 +DATA/oranetr/datafile/bocnet_tbs.260.876478197 12233018392104 ONLINE

7 +DATA/oranetr/datafile/bocnet_tbs.280.876478353 12233018392104 ONLINE

8 +DATA/oranetr/datafile/bocnet_tbs.271.876478273 12233018392104 ONLINE

9 +DATA/oranetr/datafile/bocnet_tbs.283.876478429 12233018392104 ONLINE

10 +DATA/oranetr/datafile/bocnet_tbs.287.876478509 12233018392104 ONLINE

11 +DATA/oranetr/datafile/bocnet_tbs.284.876478431 12233018392104 ONLINE

12 +DATA/oranetr/datafile/bocnet_tbs.289.876478511 12233018392104 ONLINE

13 +DATA/oranetr/datafile/bocnet_tbs.276.876478349 12233018392104 ONLINE

14 +DATA/oranetr/datafile/bocnet_tbs.277.876478349 12233018392104 ONLINE

15 +DATA/oranetr/datafile/bocnet_tbs.272.876478273 12233018392104 ONLINE

16 +DATA/oranetr/datafile/bocnet_tbs.279.876478351 12233018392104 ONLINE

17 +DATA/oranetr/datafile/bocnet_tbs.278.876478351 12233018392104 ONLINE

18 +DATA/oranetr/datafile/bocnet_tbs.258.876478117 12233018392104 ONLINE

19 +DATA/oranetr/datafile/bocnet_tbs.267.876478039 12233018392104 ONLINE

20 +DATA/oranetr/datafile/bocnet_tbs.256.876478039 12233018392104 ONLINE

21 +DATA/oranetr/datafile/bocnet_tbs.266.876478115 12233018392104 ONLINE

22 +DATA/oranetr/datafile/bocnet_tbs.257.876478117 12233018392104 ONLINE

23 +DATA/oranetr/datafile/bocnet_tbs.268.876478039 12233018392104 ONLINE

24 +DATA/oranetr/datafile/bocnet_tbs.263.876478039 12233018392104 ONLINE

25 +DATA/oranetr/datafile/bocnet_tbs.269.876478039 12233018392104 ONLINE

26 +DATA/oranetr/datafile/bocnet_tbs.265.876478119 12233018392104 ONLINE

27 +DATA/oranetr/datafile/bocnet_tbs.259.876478117 12233018392104 ONLINE

28 +DATA/oranetr/datafile/bocnet_tbs.264.876478195 12233018392104 ONLINE

29 +DATA/oranetr/datafile/bocnet_tbs.281.876478427 12233018392104 ONLINE

30 +DATA/oranetr/datafile/bocnet_tbs.274.876478275 12233018392104 ONLINE

31 +DATA/oranetr/datafile/bocnet_tbs.261.876478195 12233018392104 ONLINE

32 +DATA/oranetr/datafile/bocnet_tbs.286.876478509 12233018392104 ONLINE

33 +DATA/oranetr/datafile/bocnet_tbs.282.876478427 12233018392104 ONLINE

34 +DATA/oranetr/datafile/bocnet_tbs.275.876478275 12233018392104 ONLINE

35 +DATA/oranetr/datafile/bocnet_tbs.270.876478197 12233018392104 ONLINE

36 +DATA/oranetr/datafile/bocnet_tbs.262.876478195 12233018392104 ONLINE

37 +DATA/oranetr/datafile/bocnet_tbs.273.876478273 12233018392104 ONLINE

38 +DATA/oranetr/datafile/bocnet_tbs.285.876478431 12233018392104 ONLINE

39 +DATA/oranetr/datafile/bocnet_tbs.313.876559505 12233018392104 ONLINE

40 +DATA/oranetr/datafile/bocnet_tbs.314.876559507 12233018392104 ONLINE

41 +DATA/oranetr/datafile/bocnet_tbs.315.876559509 12233018392104 ONLINE

42 +DATA/oranetr/datafile/bocnet_tbs.316.876559509 12233018392104 ONLINE

43 +DATA/oranetr/datafile/bocnet_tbs.317.876559511 12233018392104 ONLINE

44 +DATA/oranetr/datafile/bocnet_tbs.318.876559511 12233018392104 ONLINE

45 +DATA/oranetr/datafile/bocnet_tbs.319.876559513 12233018392104 ONLINE

46 +DATA/oranetr/datafile/bocnet_tbs.320.876559513 12233018392104 ONLINE

47 +DATA/oranetr/datafile/bocnet_tbs.321.876559515 12233018392104 ONLINE

48 +DATA/oranetr/datafile/bocnet_tbs.322.876559517 12233018392104 ONLINE

49 +DATA/oranetr/datafile/bocnet_tbs.323.876559517 12233018392104 ONLINE

50 +DATA/oranetr/datafile/bocnet_tbs.324.876559519 12233018392104 ONLINE

51 +DATA/oranetr/datafile/bocnet_tbs.325.876559521 12233018392104 ONLINE

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。