基于主库SCN恢复物理DG

模拟环境：

主库归档丢失，备库未接收到主库已丢失的日志，导致产生gap

	主库	备库
db_name	orcl	orcl
instance_name	orcl	orclstd
版本号	11.2.0.4	11.2.0.4

 开始实验：

一、模拟归档丢失

1、在主库创建新表，并切换日志，查看日志应用状态

create table test_dg as select * from all_objects;

alter system switch logfile;

select recid,name,sequence#,archived,applied from v$archived_log order by sequence#;

2、关闭实时应用

alter database recover managed standby database cancel;

3、关闭实时传输日志

alter system set log_archive_dest_state_2=defer;

4、在主库新建表并切换日志

create table test_dg01 as select * from all_objects;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

5、删除新生成的部分归档日志

6、开启实时传输日志

alter system set log_archive_dest_state_2=enable;

7、开启实时应用

alter database recover managed standby database using current logfile disconnect from session;

8、查看alert日志发现存在gap，也可以通过v$archive_gap视图查询

select * from v$archive_gap;

二、开始恢复

1、查询最小SCN号

两种方法

      1、select FIRST_CHANGE#  from v$archived_log where SEQUENCE#  = 丢失的第一个归档号;--试验成功

      2、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 b.NEXT_CHANGE#
        FROM   v$archived_log b
        WHERE  b.SEQUENCE# = 丢失的第一个归档号
        AND    resetlogs_change# =
               (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
        AND    rownum = 1) NEXT_CHANGE#
FROM   dual;--试验失败

2、根据scn号启动主库的增量备份

假设上步得到的scn是2241214

backup as compressed backupset incremental from SCN 2241214 database format '/u05/oracle/oracle_bk/ORADG11G/standby_%d_%T_%U.bak' include current controlfile for standby filesperset=5  tag 'FOR STANDBY';

3、将备份文件传到备库

4、保存备库datafile绝对路径

select name from v$datafile;

5、启动备库到nomount状态，恢复控制文件

restore standby controlfile from '';

6、注册备份集到新的控制文件

catalog start with '';

7、查看incarnation，保证主备库一致

8、启动备库到mount状态，增量恢复数据文件

recover database noredo;

9、开启实时应用日志

alter database recover managed standby database using current logfile disconnect from session;

10、查看日志应用效果

select recid,name,sequence#,archived,applied from v$archived_log order by sequence#;

11、启动备库到open状态

12、测试主备是否实时同步