ADG主库上归档丢失恢复备库测试

模拟了下在主库上丢失归档导致备库出现gap，无需重建恢复备库的情况
1制造gap
主库
ALTER system SET log_archive_dest_state_2 = ‘defer’;
一些dml操作
alter system switch logfile;

备库此时的mrp是等待log状态
SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS CLIENT_PROCESS SEQUENCE# STATUS

---

ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 22 CLOSING
RFS UNKNOWN 0 IDLE
RFS ARCH 0 IDLE
MRP0 N/A 23 WAIT_FOR_LOG

SQL> select * from v$archive_gap;

no rows selected
现在的这种情况oracle不认为是gap，所以没记录

主库上查看当前sequence
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arch
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27

从库查看应用的归档
SQL> select sequence#, applied from v$archived_log where applied=’YES’ order by sequence#;

SEQUENCE# APPLIED

---

10 YES11 YES12 YES13 YES14 YES14 YES15 YES15 YES16 YES16 YES17 YES

SEQUENCE# APPLIED

---

18 YES19 YES20 YES21 YES22 YES

主库上删除24,25，26三个sequence归档
select sequence#,name from v$archived_log;
主库
ALTER system SET log_archive_dest_state_2 = ‘enable’;

在备库上查看
select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---

 1        24         26

发现已经丢失了24,25,26三个归档文件
SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

---

10 YES11 YES12 YES13 YES14 YES15 YES15 YES14 YES16 YES16 YES17 YES

SEQUENCE# APPLIED

---

17 NO18 YES18 NO19 YES20 YES21 YES22 YES23 YES27 NO

停止从库的同步
SELECT (SELECT MIN(d.CHECKPOINT_CHANGE#) FROM vdatafile d) datafile_scn,  
       (SELECT MIN(d.CHECKPOINT_CHANGE#)  
        FROM   vdatafile_header d
WHERE rownum = 1) datafile_header_scn,
(SELECT current_scn FROM vdatabase) current_scn,  
       (SELECT b.NEXT_CHANGE#  
        FROM   varchived_log b
WHERE b.SEQUENCE# = 23
AND resetlogs_change# =
(SELECT d.RESETLOGS_CHANGE# FROM v$database d)
AND rownum = 1) NEXT_CHANGE#
FROM dual;
查找最小的scn
DATAFILE_SCN DATAFILE_HEADER_SCN CURRENT_SCN NEXT_CHANGE#

---

 1017228         1017228     1017227      1017228

在主库备份增量
RMAN> backup as compressed backupset incremental from SCN 1017227 database format ‘/home/oracle/standby_%d_%T_%U.bak’ include current controlfile for standby filesperset=5 tag ‘FOR STANDBY’;
传递到备库，注册应用
重启备库到nomount
show paramete control
rman恢复控制文件
RMAN> restore standby controlfile to ‘/data/oracle/app/oradata/dbadb/control01.ctl’ from ‘/home/oracle/standby_DBADB_20170422_0js2b2hl_1_1.bak’

RMAN> restore standby controlfile to ‘/data/oracle/app/fast_recovery_area/dbadb/control02.ctl’ from ‘/home/oracle/standby_DBADB_20170422_0js2b2hl_1_1.bak’;
RMAN> alter database mount;
RMAN> catalog start with ‘/home/oracle/archivelog’;

searching for all files that match the pattern /home/oracle/archivelog

List of Files Unknown to the Database

File Name: /home/oracle/archivelog/standby_DBADB_20170422_0is2b2hk_1_1.bak

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files

File Name: /home/oracle/archivelog/standby_DBADB_20170422_0is2b2hk_1_1.bak

RMAN> recover database noredo;

alter database recover managed standby database using current logfile disconnect from session;
SQL> SELECT * FROM V$ARCHIVE_GAP;

no rows selected

select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED

---

28 YES

此时的备库是mount状态的，需要到open状态
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
验证是否正常
参考：
http://www.cnblogs.com/lhrbest/p/4754289.html