Oracle Physical DataGuard使用RMAN增量备份修复GAP

当Physical DataGuard主库归档日志丢失，使用RMAN或者OS命令删除了归档日志，并且归档日志未传输到Standby端，这样Standby端会产生GAP，可以将Primary端的归档拷贝到Standby端然后使用register命令注册，

例如ALTER DATABASE REGISTER LOGFILE '/arch/ARC0000000068_0839199018.0001'，在Primary归档日志被删除的情况下，可以使用RMAN增量备份来修复GAP，实验环境Oracle10g

DataGuard搭建步骤参考

官方文档参考

Using RMAN Incremental Backups to Refresh a Standby Database

--解决步骤
--Standby查询缺少归档日志号，可以看到seq# 38 ~ seq# 67的归档日志丢失未应用
SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;


FIRST_TIME          FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
------------------- ------------- ------------ ----------
2014-02-10 18:04:16        599380       629351          1
2014-02-10 18:05:28        629351       637595          2
2014-02-10 18:08:38        637595       637597          3
2014-02-10 18:08:39        637597       637601          4
2014-02-10 18:08:45        637601       637603          5
2014-02-10 18:08:46        637603       639724          6
2014-02-10 19:04:45        639724       639862          7
2014-02-10 19:04:49        639862       640638          8
2014-02-10 19:29:05        640638       640664          9
2014-02-10 19:29:54        640664       640666         10
2014-02-10 19:29:55        640666       640668         11
2014-02-10 19:29:56        640668       640670         12
2014-02-10 19:29:58        640670       643748         13
2014-02-10 21:42:28        643748       643879         14
2014-02-10 21:47:02        643879       643983         15
2014-02-10 21:50:36        643983       644027         16
2014-02-10 21:51:33        644027       644177         17
2014-02-10 21:56:47        644177       644196         18
2014-02-10 21:56:56        644196       644256         19
2014-02-10 21:58:42        644256       644274         20
2014-02-10 22:50:18        644275       650789          1
2014-02-10 23:03:43        650789       650791          2
2014-02-10 23:03:46        650791       650794          3
2014-02-10 23:03:51        650794       650798          4
2014-02-10 23:04:01        650798       675198          5
2014-02-11 12:43:59        675198       679827          6
2014-02-11 14:49:31        679827       679929          7
2014-02-11 14:52:05        679929       680026          8
2014-02-11 14:54:09        680026       680132          9
2014-02-11 14:56:43        680132       681334         10
2014-02-11 15:25:54        681334       701447         11
2014-02-13 22:08:41        701447       721714         12
2014-02-13 22:10:53        721714       742653         13
2014-02-13 22:17:29        742653       744515         14
2014-02-13 22:20:27        744515       744524         15
2014-02-13 22:20:51        744524       744527         16
2014-02-13 22:20:55        744527       746370         17
2014-02-13 23:05:53        746370       746373         18
2014-02-13 23:05:55        746373       746376         19
2014-02-13 23:05:59        746376       746378         20
2014-02-13 23:06:01        746378       746787         21
2014-02-13 23:15:51        746787       748433         22
2014-02-13 23:40:04        748433       748484         23
2014-02-13 23:42:25        748484       748572         24
2014-02-13 23:46:44        748572       749008         25
2014-02-13 23:57:59        749008       749010         26
2014-02-13 23:57:59        749010       749013         27
2014-02-13 23:58:05        749013       770685         28
2014-02-16 22:01:05        770685       780391         29
2014-02-16 22:01:56        780391       780398         30
2014-02-16 22:02:12        780398       782583         31
2014-02-16 23:10:40        782583       782585         32
2014-02-16 23:10:40        782585       782587         33
2014-02-16 23:10:43        782587       782917         34
2014-02-16 23:11:55        782917       782919         35
2014-02-16 23:11:55        782919       782922         36
2014-02-16 23:12:01        782922       783058         37


57 rows selected.


SQL> select sequence#,applied from v$archived_log where applied='YES';


 SEQUENCE# APP
---------- ---
        19 YES
        20 YES
        18 YES
        21 YES
        17 YES
        22 YES
        23 YES
        24 YES
        25 YES
        26 YES
        27 YES
        28 YES
        29 YES
        30 YES
        31 YES
        32 YES
        33 YES
        34 YES
        35 YES
        36 YES
        37 YES


21 rows selected.


SQL> select sequence#,applied from v$archived_log;


 SEQUENCE# APP
---------- ---
        19 YES
        20 YES
        18 YES
        21 YES
        17 YES
        22 YES
        23 YES
        24 YES
        25 YES
        26 YES
        27 YES
        28 YES
        29 YES
        30 YES
        31 YES
        32 YES
        33 YES
        34 YES
        35 YES
        36 YES
        37 YES
        68 NO
        69 NO
        70 NO
        71 NO
        72 NO
        73 NO
        74 NO
        75 NO
        76 NO
        77 NO


31 rows selected.





--Standby查询GAP信息，发现seq# 38 ~ seq# 67丢失
SQL> select * from v$archive_gap;


   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1            38             67


SQL> 





--在Primary上查询seq#丢失的的归档是否存在，这里查询结果是不存在
SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 38 and 67;





--在Primary找到丢失归档的SCN
SQL> select first_change# from v$archived_log where sequence#=38;


FIRST_CHANGE#
-------------
       783058
  

  

  
--Primary增量备份，将SCN大于783058的block备份出来，并且传到Standby的一个空目录中
[oracle@vzwc arch]$ rman target /


Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 17 00:08:38 2014


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


connected to target database: DBSERVER (DBID=66428446)


RMAN> backup device type disk incremental from scn 783058 database
2> format '/u01/app/oracle/arch/%U';


Starting backup at 17-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=513 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/dbserver/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/dbserver/sysaux01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/dbserver/zwc.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/dbserver/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/dbserver/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/dbserver/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-FEB-14
channel ORA_DISK_1: finished piece 1 at 17-FEB-14
piece handle=/u01/app/oracle/arch/0pp0q956_1_1 tag=TAG20140217T000910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-FEB-14
channel ORA_DISK_1: finished piece 1 at 17-FEB-14
piece handle=/u01/app/oracle/arch/0qp0q95l_1_1 tag=TAG20140217T000910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-FEB-14


RMAN> 






--在Primary生成standby controlfile，并且传到Standby
RMAN> backup current controlfile for standby format '/tmp/%U';


Starting backup at 17-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=509 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 17-FEB-14
channel ORA_DISK_1: finished piece 1 at 17-FEB-14
piece handle=/tmp/0rp0qavt_1_1 tag=TAG20140217T004029 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-FEB-14






--Standby RMAN注册备份文件,Standby库要是mount状态
[oracle@dgstb arch]$ rman target /


Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 17 00:13:39 2014


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


connected to target database: DBSERVER (DBID=66428446, not open)


RMAN> catalog start with '/u01/app/oracle/arch';


using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/arch


List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/arch/0pp0q956_1_1
File Name: /u01/app/oracle/arch/0qp0q95l_1_1


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


List of Cataloged Files
=======================
File Name: /u01/app/oracle/arch/0pp0q956_1_1
File Name: /u01/app/oracle/arch/0qp0q95l_1_1

--Standby还原控制文件
RMAN> shutdown immediate


using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down


RMAN> startup nomount


connected to target database (not started)
Oracle instance started


Total System Global Area     612368384 bytes


Fixed Size                     2085872 bytes
Variable Size                167775248 bytes
Database Buffers             436207616 bytes
Redo Buffers                   6299648 bytes


RMAN> restore standby controlfile from '/tmp/0rp0qavt_1_1';


Starting restore at 17-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/standby.ctl
Finished restore at 17-FEB-14


RMAN> alter database mount
2> ;


database mounted
released channel: ORA_DISK_1


RMAN> 

--Standby恢复数据库，注意这样并不会把Standby库recover到一致状态，因为主库备份时是在线的，但是recover完之后的状态是可以来做standby的managed recovery的，Standby库是mount状态。
RMAN> recover database noredo;


Starting recover at 17-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=513 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/standby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/standby/undotbs01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/standby/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/standby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/standby/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/standby/zwc.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/arch/0pp0q956_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/arch/0pp0q956_1_1 tag=TAG20140217T000910
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 17-FEB-14


RMAN> 




--Standby应用日志
SQL> alter database recover managed standby database disconnect from session;


Database altered.

--Standby alert.log

alter database recover managed standby database disconnect from session
Mon Feb 17 00:46:40 2014
Attempt to start background Managed Standby Recovery process (standby)
MRP0 started with pid=28, OS id=3968
Mon Feb 17 00:46:40 2014
MRP0: Background Managed Standby Recovery process started (standby)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Mon Feb 17 00:46:45 2014
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 78
Fetching gap sequence in thread 1, gap sequence 78-88
Mon Feb 17 00:46:46 2014
Completed: alter database recover managed standby database disconnect from session
Mon Feb 17 00:46:47 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /arch
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 3974
RFS[1]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
RFS LogMiner: Client disabled from further notification
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 3976
RFS[2]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 3978
RFS[3]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
RFS[1]: Archived Log: '/arch/ARC0000000080_0839199018.0001'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 3980
RFS[4]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 3982
RFS[5]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 3984
RFS[6]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 3988
RFS[7]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 3986
RFS[8]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
RFS[3]: Archived Log: '/arch/ARC0000000083_0839199018.0001'
Mon Feb 17 00:46:47 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9]: Assigned to RFS process 3990
RFS[9]: Identified database type as 'physical standby'
Mon Feb 17 00:46:47 2014
RFS[7]: Archived Log: '/arch/ARC0000000086_0839199018.0001'
Mon Feb 17 00:46:47 2014
RFS[9]: Archived Log: '/arch/ARC0000000081_0839199018.0001'
Mon Feb 17 00:46:47 2014
RFS[2]: Archived Log: '/arch/ARC0000000084_0839199018.0001'
Mon Feb 17 00:46:47 2014
RFS[8]: Archived Log: '/arch/ARC0000000085_0839199018.0001'
Mon Feb 17 00:46:47 2014
RFS[6]: Archived Log: '/arch/ARC0000000079_0839199018.0001'
Mon Feb 17 00:46:47 2014
RFS[5]: Archived Log: '/arch/ARC0000000082_0839199018.0001'
Mon Feb 17 00:46:47 2014
RFS[4]: Archived Log: '/arch/ARC0000000078_0839199018.0001'
Mon Feb 17 00:46:49 2014
RFS[3]: Archived Log: '/arch/ARC0000000088_0839199018.0001'
Mon Feb 17 00:46:49 2014
RFS[1]: Archived Log: '/arch/ARC0000000087_0839199018.0001'
Mon Feb 17 00:47:15 2014
Media Recovery Log /arch/ARC0000000078_0839199018.0001
Media Recovery Log /arch/ARC0000000079_0839199018.0001
Media Recovery Log /arch/ARC0000000080_0839199018.0001
Media Recovery Log /arch/ARC0000000081_0839199018.0001
Media Recovery Log /arch/ARC0000000082_0839199018.0001
Media Recovery Log /arch/ARC0000000083_0839199018.0001
Media Recovery Log /arch/ARC0000000084_0839199018.0001
Media Recovery Log /arch/ARC0000000085_0839199018.0001
Media Recovery Log /arch/ARC0000000086_0839199018.0001
Media Recovery Log /arch/ARC0000000087_0839199018.0001
Media Recovery Log /arch/ARC0000000088_0839199018.0001
Media Recovery Waiting for thread 1 sequence 89
Mon Feb 17 00:47:17 2014
RFS[2]: Archived Log: '/arch/ARC0000000089_0839199018.0001'
Mon Feb 17 00:47:45 2014
Media Recovery Log /arch/ARC0000000089_0839199018.0001
Media Recovery Waiting for thread 1 sequence 90
Mon Feb 17 00:48:29 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[10]: Assigned to RFS process 3995
RFS[10]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
Mon Feb 17 00:48:30 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[11]: Assigned to RFS process 3997
RFS[11]: Identified database type as 'physical standby'
RFS[11]: Archived Log: '/arch/ARC0000000090_0839199018.0001'
Mon Feb 17 00:49:00 2014
Media Recovery Log /arch/ARC0000000090_0839199018.0001
Media Recovery Waiting for thread 1 sequence 91 (in transit)






--在Primary和Standby上执行以下语句，如果一致说明GAP修复成功，我这里是一致的。
SQL> select max(sequence#) from v$archived_log;


MAX(SEQUENCE#)
--------------
            99


SQL> 






--Standby查看GAP信息，修复成功
SQL> select * from v$archive_gap;


no rows selected


SQL> select sequence#,applied from v$archived_log;


 SEQUENCE# APP
---------- ---
        80 YES
        83 YES
        86 YES
        81 YES
        84 YES
        85 YES
        79 YES
        82 YES
        78 YES
        88 YES
        87 YES


 SEQUENCE# APP
---------- ---
        89 YES
        90 YES
        91 YES
        92 YES
        93 YES
        94 YES
        95 YES
        96 YES
        97 YES
        98 YES
        99 YES


22 rows selected.


SQL> select sequence#,applied from v$archived_log where applied='YES';


 SEQUENCE# APP
---------- ---
        80 YES
        83 YES
        86 YES
        81 YES
        84 YES
        85 YES
        79 YES
        82 YES
        78 YES
        88 YES
        87 YES


 SEQUENCE# APP
---------- ---
        89 YES
        90 YES
        91 YES
        92 YES
        93 YES
        94 YES
        95 YES
        96 YES
        97 YES
        98 YES
        99 YES

22 rows selected.