Oracle DataGuard主库丢失归档日志后备库的RMAN增量恢复一例

第一部分  问题描述和环境状态确认

----1. 问题场景

Oracle DataGuard主库丢失archivelog，如何不重建备库完成同步？

在Oracle DataGuard主从同步过程中可能出现主库archivelog丢失，备库出现gap错误。
此时，除了重新完整搭建DataGuard之外，可以通过主库增量备份完成备份重新同步。

----2. 解决方式
【主要步骤】
1.在备库上找出当前scn；
2.根据此scn，在主库上使用rman完成增量备份；
3.传输增量备份到备库，同时在备库上恢复；
4.重新启动备库同步。

----3. 增量恢复详细过程

==查看主库信息==
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> archive log list;

==查看备库信息==
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

--3.1.主、备库上查看日志信息
SQL> select group#,thread#,sequence#,bytes,members,archived,status,first_change#,first_time from v$log;

---- #在主库上  查看 LOG GAP 状态
SQL>select dest_name,destination, database_mode, recovery_mode,gap_status from  v$archive_dest_status where dest_id=2;

---- #在备库上查询
SELECT * FROM V$ARCHIVE_GAP;

 

====3.2 验证主库到备库的归档日志传输情况和备库上归档日志的APPLY情况

====3.2.1 在备库上查看归档日志应用情况:  val.applied='YES' --已应用; val.applied='NO' --待应用;
PhyStdby:SQL>select thread#, sequence#, val.applied
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#;

PhyStdby:SQL>select max(lh.SEQUENCE#) "Last applied arc",max(al.SEQUENCE#) "Last recieved arc"
from v$log_history lh,v$archived_log al;

------ 验证最大日志序列号
Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

------ "Last Standby Seq Applied"
PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES'
group by thread# order by 1;

 

----================================================================
====第二部分  利用rman进行增量scn的恢复

==利用rman进行增量scn的恢复==
1.在备库上取消日志应用
SQL> alter database recover managed standby database cancel;

2.查看备库scn
SQL> select current_scn from v$database;
---- select to_char(current_scn) from v$database;
     CURRENT_SCN
 ---------------
  13402877625847

3.根据scn，在主库上进行rman增量备份
Primary: rman>  rman target /

RMAN> backup incremental from scn 13402877625847 database format 'E:\ForStandby_%U.bka' tag 'forstandby';
------backup as compressed backupset incremental from scn 13402877625847 database format 'E:\ForStandby_%U.bka' tag 'forstandby';
RMAN> backup current controlfile for standby format 'E:\ForStandbyCTRL.bkc';

4.查看增量备份信息

E:\temp> dir

5.copy增量备份到备库主机 一目录，如:  E:\temp
 

6.将备库启动到mount状态
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> exit

7.rman恢复增量备份和控制文件备份

rman target / nocatalog

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-15';

RMAN> catalog start with 'E:\temp';  ####注册增量备份文件

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

RMAN> RECOVER DATABASE NOREDO;  #### 注意 NOREDO 选项

SQL> shutdown;

SQL> startup nomount;

RMAN> restore standby controlfile from 'E:\temp\ForStandbyCTRL.bkc';

RMAN> shutdown;

RMAN> exit

 

8.启动备库到mount状态
SQL> startup nomount;
SQL> alter database mount standby database;

9.由于恢复了控制文件，因此需要重新添加新的standby redo log file(若先前有，可忽略此步)
alter database add standby logfile group 11 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY11.LOG') size 1024m;
alter database add standby logfile group 12 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY12.LOG') size 1024m;
alter database add standby logfile group 13 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY13.LOG') size 1024m;
alter database add standby logfile group 14 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY14.LOG') size 1024m;
alter database add standby logfile group 15 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY15.LOG') size 1024m;
alter database add standby logfile group 16 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY16.LOG') size 1024m;

10.启动备库同步
SQL> alter database open read only;
Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.

11.查看归档日志应用以及表数据是否恢复
SQL> select max(lh.SEQUENCE#) "Lastapplied arc", max(al.SEQUENCE#) "Last recieved arc"
       from v$log_history lh,v$archived_log al;

SQL> select count(1) from dba_tables;

----在备库上查看归档日志应用情况:  val.applied='YES' --已应用; val.applied='NO' --待应用;
PhyStdby:SQL>select thread#, sequence#, val.applied
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#;