合 RAC环境下主库丢失归档,备库DG的恢复过程
【故障处理】DG归档丢失的恢复
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① 主库丢失归档,物理DG的恢复
② 其他常用SQL语句
故障分析及解决过程
故障环境介绍
项目 | source db | PHYSICAL STANDBY |
---|---|---|
db 类型 | RAC | RAC |
db version | 11.2.0.3 | 11.2.0.3 |
db 存储 | ASM | ASM |
OS版本及kernel版本 | AIX 64位 6.1.0.0 | AIX 64位 6.1.0.0 |
故障发生现象及报错信息
由于客户这边有N套库,我过来时间短,没有完全整理,昨天对一套rac执行健康检查的时候发现带有一套物理DG,但是DG库不同步,而且同步的日志号已经断档很久了,于是决定把这套DG恢复一下,下边简单记录一下。
数据库信息:
DG库信息:
THREAD# | DEST_ID | DEST_NAME | TARGET | DATABASE_MODE | DB_UNIQUE_NAME | DESTINATION | CURRENT_SEQ# | LAST_ARCHIVED | APPLIED_SEQ# |
---|---|---|---|---|---|---|---|---|---|
1 | 1 | LOG_ARCHIVE_DEST_1 | LOCAL PRIMARY | OPEN | NONE | /archive/arch | 14311 | 14310 | |
1 | 2 | LOG_ARCHIVE_DEST_2 | PHYSICAL STANDBY | OPEN_READ-ONLY | oraNETR | oraNETR | 14311 | 14310 | 968 |
2 | 1 | LOG_ARCHIVE_DEST_1 | LOCAL PRIMARY | OPEN | NONE | /archive/arch | 13403 | 13402 | |
2 | 2 | LOG_ARCHIVE_DEST_2 | PHYSICAL STANDBY | OPEN_READ-ONLY | oraNETR | oraNETR | 13403 | 13402 | 644 |
可以看到实例一当前是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