Oracle dataguard主库删除备库需要的归档时,会导致gap事情的产生,或者备库由于网络或物理故障原因,倒是备库远远落后于主库,都会产生gap事件,本例模拟gap事件的产生以及处理.

1. 查看当前dataguard同步状态

  • primary库查看
select THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 as MB,ARCHIVED,STATUS from v$log;

   THREAD#     GROUP#  SEQUENCE#     MB    ARCHIVED  STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 1 16   100     YES INACTIVE
1 2 17   100     YES INACTIVE
1 3 18   100     NO CURRENT #可以看到primary库当前最新的联机日志文件序列号是18 column NAME format a60
select NAME,sequence#,standby_dest,archived,applied from v$archived_log; NAME               SEQUENCE# STANDBY_D ARCHIVED APPLIED
------------------------------------------------------------ ---------- --------- --------- ---------------------------
/u01/app/oracle/arch/1_4_947274260.dbf   4       NO YES NO
/u01/app/oracle/arch/1_5_947274260.dbf   5       NO YES NO
/u01/app/oracle/arch/1_6_947274260.dbf   6       NO YES NO
/u01/app/oracle/arch/1_7_947274260.dbf   7       NO YES NO
/u01/app/oracle/arch/1_8_947274260.dbf   8       NO YES NO
userdata2             8       YES YES YES
userdata2             9       YES YES YES
/u01/app/oracle/arch/1_9_947274260.dbf   9       NO YES NO
/u01/app/oracle/arch/1_10_947274260.dbf    10       NO YES NO
userdata2             10       YES YES YES
userdata2             11       YES YES YES
/u01/app/oracle/arch/1_11_947274260.dbf    11       NO YES NO
userdata2             12       YES YES YES
/u01/app/oracle/arch/1_12_947274260.dbf    12       NO YES NO
userdata2             13       YES YES YES
/u01/app/oracle/arch/1_13_947274260.dbf    13       NO YES NO
userdata2             14       YES YES YES
/u01/app/oracle/arch/1_14_947274260.dbf    14       NO YES NO
userdata2            15       YES YES YES
/u01/app/oracle/arch/1_15_947274260.dbf    15       NO YES NO
userdata2             16       YES YES YES
/u01/app/oracle/arch/1_16_947274260.dbf    16       NO YES NO
userdata2             17       YES YES NO
/u01/app/oracle/arch/1_17_947274260.dbf    17       NO YES NO #primary和standby数据库的17号日志都已经归档
  •  standby库查看
select THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 as MB,ARCHIVED,STATUS from v$log order by SEQUENCE#;

   THREAD#     GROUP#  SEQUENCE#     MB   ARCHIVED    STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 1 16     100   YES CLEARING
1 2 17     100   YES CLEARING
1 3 18     100   YES CURRENT select process,status,thread#,sequence#,block#,blocks from v$managed_standby; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------------- ---------------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 17 174080   236
ARCH CLOSING 1 16 167936   135
ARCH CONNECTED 0 0    0   0
ARCH CLOSING 1 15 163840   391
RFS IDLE    0 0   0   0
RFS IDLE    1 18 42794   1
RFS IDLE    0 0   0   0
MRP0 APPLYING_LOG 204800 #可以看到standby库当前正在从primary读取最新的18号日志文件

2. 模拟产生gap事件

  2.1. 停掉备库的dataguard进程和数据库实例

alter database recover managed standby database cancel;
shutdown immediate;
$ lsnrctl stop

 2.2. 主库切5个归档文件,并将新产生的5个归档文件剪切出归档目录

alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current; select THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 as MB,ARCHIVED,STATUS from v$log; THREAD# GROUP# SEQUENCE# MB ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 1 22   100     YES   INACTIVE
1 2 23   100     NO CURRENT
1 3 21   100     YES INACTIVE select NAME,sequence#,archived,applied from v$archived_log where SEQUENCE# > 17;
#可以看到刚才手动切了5次,多了5个归档文件,序列号为18~22,一会儿通过手动注册的方式解决18~22的gap事件 NAME               SEQUENCE# ARCHIVED APPLIED
------------------------------------------------------------ ---------- --------- ---------------------------
/u01/app/oracle/arch/1_18_947274260.dbf     18     YES NO
/u01/app/oracle/arch/1_19_947274260.dbf     19     YES NO
/u01/app/oracle/arch/1_20_947274260.dbf     20     YES NO
/u01/app/oracle/arch/1_21_947274260.dbf     21     YES NO
/u01/app/oracle/arch/1_22_947274260.dbf     22     YES NO host mkdir /u01/app/oracle/archbak
host mv /u01/app/oracle/arch/1_{18,19,20,21,22}_947274260.dbf /u01/app/oracle/archbak/ #将18~22归档文件剪切出归档目录,模拟序列号为18~22的归档文件丢失的现象
host ls -lh /u01/app/oracle/archbak/
total 22M
-rw-r----- 1 oracle oinstall 22M Jun 28 01:47 1_18_947274260.dbf
-rw-r----- 1 oracle oinstall 2.0K Jun 28 01:47 1_19_947274260.dbf
-rw-r----- 1 oracle oinstall 4.0K Jun 28 01:48 1_20_947274260.dbf
-rw-r----- 1 oracle oinstall 2.0K Jun 28 01:48 1_21_947274260.dbf
-rw-r----- 1 oracle oinstall 1.5K Jun 28 01:48 1_22_947274260.dbf

 2.3. 主库再切5个归档文件

alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current; select THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 as MB,ARCHIVED,STATUS from v$log;
#这里再切5个归档的作用是为了产生归档日志文件的序列号不连贯的情况,即0~27,18~22,23~27,缺少中间5个归档 THREAD# GROUP# SEQUENCE# MB ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 1 28 100 NO CURRENT
1 2 26 100 YES INACTIVE
1 3 27 100 YES INACTIVE select NAME,sequence#,archived,applied from v$archived_log where SEQUENCE# > 22; NAME SEQUENCE# ARCHIVED APPLIED
------------------------------------------------------------ ---------- --------- ---------------------------
/u01/app/oracle/arch/1_23_947274260.dbf 23 YES NO
/u01/app/oracle/arch/1_24_947274260.dbf 24 YES NO
/u01/app/oracle/arch/1_25_947274260.dbf 25 YES NO
/u01/app/oracle/arch/1_26_947274260.dbf 26 YES NO
/u01/app/oracle/arch/1_27_947274260.dbf 27 YES NO

2.4. 主库再切5个归档文件,并将新产生的5个新归档文件剪切出归档目录

alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current; select THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 as MB,ARCHIVED,STATUS from v$log; THREAD# GROUP# SEQUENCE# MB   ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 1 31   100     YES INACTIVE
1 2 32   100     YES INACTIVE
1 3 33   100     NO CURRENT select NAME,sequence#,archived,applied from v$archived_log where SEQUENCE# > 27;
#这里再切5个归档的也是为了产生第二个gap事件,即缺少28~32的归档,一会儿通过增量备份来解决缺少28~32的事件 NAME     SEQUENCE# ARCHIVED APPLIED
-------------------------------------------- ---------- --------- ---------------------------
/u01/app/oracle/arch/1_28_947274260.dbf 28     YES NO
/u01/app/oracle/arch/1_29_947274260.dbf 29     YES NO
/u01/app/oracle/arch/1_30_947274260.dbf 30     YES NO
/u01/app/oracle/arch/1_31_947274260.dbf 31     YES NO
/u01/app/oracle/arch/1_32_947274260.dbf 32     YES NO host mv /u01/app/oracle/arch/1_{28,29,30,31,32}_947274260.dbf /u01/app/oracle/archbak/
host ls -lh /u01/app/oracle/archbak/ total 23M
-rw-r----- 1 oracle oinstall 22M Jun 28 01:47 1_18_947274260.dbf
-rw-r----- 1 oracle oinstall 2.0K Jun 28 01:47 1_19_947274260.dbf
-rw-r----- 1 oracle oinstall 4.0K Jun 28 01:48 1_20_947274260.dbf
-rw-r----- 1 oracle oinstall 2.0K Jun 28 01:48 1_21_947274260.dbf
-rw-r----- 1 oracle oinstall 1.5K Jun 28 01:48 1_22_947274260.dbf
-rw-r----- 1 oracle oinstall 948K Jun 28 02:34 1_28_947274260.dbf
-rw-r----- 1 oracle oinstall 2.0K Jun 28 02:34 1_29_947274260.dbf
-rw-r----- 1 oracle oinstall 3.0K Jun 28 02:34 1_30_947274260.dbf
-rw-r----- 1 oracle oinstall 1.0K Jun 28 02:34 1_31_947274260.dbf
-rw-r----- 1 oracle oinstall 1.5K Jun 28 02:34 1_32_947274260.dbf

2.5. 主库再切5个归档文件

alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current; select THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 as MB,ARCHIVED,STATUS from v$log; THREAD# GROUP# SEQUENCE#   MB ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1   1 37   100   YES INACTIVE
1   2 38   100   NO CURRENT
1   3 36   100   YES INACTIVE select NAME,sequence#,archived,applied from v$archived_log where SEQUENCE# > 32; NAME            SEQUENCE# ARCHIVED APPLIED
-------------------------------------------------- ---------- --------- ---------------------------
/u01/app/oracle/arch/1_33_947274260.dbf 33       YES NO
/u01/app/oracle/arch/1_34_947274260.dbf 34       YES NO
/u01/app/oracle/arch/1_35_947274260.dbf 35       YES NO
/u01/app/oracle/arch/1_36_947274260.dbf 36       YES NO
/u01/app/oracle/arch/1_37_947274260.dbf 37       YES NO

2.6. 启动备库的数据库实例和dataguard进程

$ lsnrctl start
startup mount;
alter database recover managed standby database using current logfile disconnect from session;

2.7. 查看当前的gap信息

  • primary查看
select NAME,sequence#,archived,applied from v$archived_log where NAME='userdata2' order by SEQUENCE#;

NAME                            SEQUENCE#   ARCHIVED    APPLIED
------------------------------- ---------- --------- ---------------------------
userdata2 8     YES   YES
userdata2 9     YES   YES
userdata2 10     YES   YES
userdata2 11     YES   YES
userdata2 12     YES   YES
userdata2 13     YES   YES
userdata2 14     YES   YES
userdata2 15     YES   YES
userdata2 16     YES   YES
userdata2 17     YES   YES
userdata2 23     YES   NO
userdata2 24     YES   NO
userdata2 25     YES   NO
userdata2 26     YES   NO
userdata2 27     YES   NO
userdata2 33     YES   NO
userdata2 34     YES   NO
userdata2 35     YES   NO
userdata2 36     YES   NO
userdata2 37     YES   NO
userdata2 38     YES   NO

可以看到备库缺少了18~22,28~32等10个归档日志

  • standby库查看
select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 17 22

3. gap事件解决

  3.1 通过手动注册归档日志的方式恢复18~22号归档文件

    3.1.1 将备库丢失的18~22号归档文件拷贝到standby节点

 scp /u01/app/oracle/archbak/1_{,,,,}_947274260.dbf oracle@ec2t-userdata-:/home/oracle/db_restore/

    3.1.2 向standby库手动注册18~22号归档文件

alter database register logfile '/home/oracle/db_restore/1_18_947274260.dbf';
alter database register logfile '/home/oracle/db_restore/1_19_947274260.dbf';
alter database register logfile '/home/oracle/db_restore/1_20_947274260.dbf';
alter database register logfile '/home/oracle/db_restore/1_21_947274260.dbf';
alter database register logfile '/home/oracle/db_restore/1_22_947274260.dbf';

  3.1.2 查看standby库日志应用情况

select NAME,sequence#,archived,applied from v$archived_log order by SEQUENCE#;

NAME                                        SEQUENCE# ARCHIVED    APPLIED
-------------------------------------------------- ---------- --------- ---------------------------
/u01/app/oracle/arch/1_7_947274260.dbf 7     YES   YES
/u01/app/oracle/arch/1_8_947274260.dbf 8     YES   YES
/u01/app/oracle/arch/1_9_947274260.dbf 9     YES   YES
/u01/app/oracle/arch/1_10_947274260.dbf   10     YES   YES
/u01/app/oracle/arch/1_11_947274260.dbf   11     YES   YES
/u01/app/oracle/arch/1_12_947274260.dbf   12     YES    YES
/u01/app/oracle/arch/1_13_947274260.dbf   13     YES   YES
/u01/app/oracle/arch/1_14_947274260.dbf   14     YES   YES
/u01/app/oracle/arch/1_15_947274260.dbf   15     YES   YES
/u01/app/oracle/arch/1_16_947274260.dbf   16     YES    YES
/u01/app/oracle/arch/1_17_947274260.dbf   17     YES    YES
/home/oracle/db_restore/1_18_947274260.dbf 18     YES   YES
/home/oracle/db_restore/1_19_947274260.dbf 19     YES    YES
/home/oracle/db_restore/1_20_947274260.dbf 20     YES   YES
/home/oracle/db_restore/1_21_947274260.dbf 21     YES   YES
/home/oracle/db_restore/1_22_947274260.dbf 22     YES   YES
/u01/app/oracle/arch/1_23_947274260.dbf   23     YES   YES
/u01/app/oracle/arch/1_24_947274260.dbf   24     YES   YES
/u01/app/oracle/arch/1_25_947274260.dbf   25     YES   YES
/u01/app/oracle/arch/1_26_947274260.dbf   26     YES   YES
/u01/app/oracle/arch/1_27_947274260.dbf   27     YES   IN-MEMORY #可以看到standby库需要的归档日志文件已经从28号开始
/u01/app/oracle/arch/1_33_947274260.dbf   33     YES   NO
/u01/app/oracle/arch/1_34_947274260.dbf   34     YES    NO
/u01/app/oracle/arch/1_35_947274260.dbf   35     YES    NO
/u01/app/oracle/arch/1_36_947274260.dbf   36     YES    NO
/u01/app/oracle/arch/1_37_947274260.dbf   37     YES   NO
/u01/app/oracle/arch/1_38_947274260.dbf   38     YES   NO
/u01/app/oracle/arch/1_39_947274260.dbf   39     YES   NO
/u01/app/oracle/arch/1_40_947274260.dbf   40     YES   NO

  3.2 通过增量备份的方式恢复28~32号归档文件

      3.2.1 查看standby库gap

select * from v$archive_gap;                                         #可以看到standby库恢复过程中需要的归档从28~32之间

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 28 32 $ cat /u01/app/oracle/diag/rdbms/userdata2/userdata/trace/alert_userdata.log .......................................................................
edia Recovery Waiting for thread 1 sequence 28
Fetching gap sequence in thread 1, gap sequence 28-32
Fri Jun 30 04:47:21 2017
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 28-32
DBID 3890525137 branch 947274260
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------

      3.2.1 关闭standby库的dataguard恢复进程

alter database recover managed standby database cancel;

      3.2.2 查看standby库最新的scn

column CURRENT_SCN format 999999999999999
select CURRENT_SCN from v$database; CURRENT_SCN
----------------
1566409 select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY' ; MIN(F.FHSCN)
------------------------------------------------
1566410

      3.2.3 在primary库做数据库增量和控制文件备份

backup as compressed backupset incremental from scn 1566409 database format '/home/oracle/archbak/stb_arch_%U.bak';
backup current controlfile for standby format '/home/oracle/archbak/stb_ctl_%U.bak';

      3.2.4 将备份复制到standby节点

scp -r /home/oracle/archbak oracle@ec2t-userdata-:/home/oracle/

      3.2.5 将standby库重启带nomount模式

shutdown immediate;
startup nomount;

      3.2.6 恢复standby库的控制文件

restore standby controlfile from '/home/oracle/archbak/stb_ctl_0cs82ldg_1_1.bak';

    3.2.7 将standby库启动到mount阶段

alter database mount;

      3.2.8 向standby库控制文件注册数据备份信息

catalog start with '/home/oracle/archbak/';

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

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/archbak/stb_ctl_0cs82ldg_1_1.bak
File Name: /home/oracle/archbak/stb_arch_0bs82lcp_1_1.bak
File Name: /home/oracle/archbak/stb_arch_0as82lci_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/archbak/stb_ctl_0cs82ldg_1_1.bak
File Name: /home/oracle/archbak/stb_arch_0bs82lcp_1_1.bak
File Name: /home/oracle/archbak/stb_arch_0as82lci_1_1.bak

    3.2.8 恢复standby库

recover database noredo;

Starting recover at 2017-06-30 05:34:55
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/userdata/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/userdata/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/userdata/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/userdata/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/archbak/stb_arch_0as82lci_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/archbak/stb_arch_0as82lci_1_1.bak tag=TAG20170630T052226
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 Finished recover at 2017-06-30 05:35:20

      3.2.9 启动standby库dataguard恢复进程

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

    3.2.10 验证dataguard同步状态

  • primary库
select NAME,sequence#,archived,applied from v$archived_log where NAME='userdata2' order by SEQUENCE#;

NAME                                        SEQUENCE# ARCHIVED  APPLIED
-------------------------------------------------- ---------- --------- ---------------------------
userdata2           8       YES YES
userdata2           9       YES YES
userdata2           10       YES YES
userdata2           11       YES YES
userdata2            12       YES YES
userdata2           13       YES YES
userdata2            14       YES YES
userdata2            15       YES YES
userdata2            16       YES YES
userdata2            17       YES YES
userdata2            23       YES YES
userdata2           24       YES YES
userdata2           25       YES YES
userdata2           26       YES YES
userdata2            27       YES YES
userdata2            33       YES YES
userdata2            34       YES YES
userdata2            35       YES YES
userdata2            36       YES YES
userdata2            37       YES YES
userdata2            38       YES YES
userdata2           39       YES YES
userdata2            40       YES YES
userdata2           41       YES YES alter system archive log current;
alter system archive log current;
alter system archive log current; select NAME,sequence#,archived,applied from v$archived_log where NAME='userdata2' order by SEQUENCE#; NAME             SEQUENCE# ARCHIVED APPLIED
-------------------------------------------------- ---------- --------- ---------------------------
userdata2           8       YES YES
userdata2           9       YES YES
userdata2            10       YES YES
userdata2           11       YES YES
userdata2           12       YES YES
userdata2           13       YES YES
userdata2           14       YES YES
userdata2            15       YES YES
userdata2           16       YES YES
userdata2           17       YES YES
userdata2            23       YES YES
userdata2            24       YES YES
userdata2           25       YES YES
userdata2            26       YES YES
userdata2           27       YES YES
userdata2            33       YES YES
userdata2           34       YES YES
userdata2           35       YES YES
userdata2           36       YES YES
userdata2           37       YES YES
userdata2           38       YES YES
userdata2           39       YES YES
userdata2           40       YES YES
userdata2           41       YES YES
userdata2           42       YES YES
userdata2           43       YES YES
userdata2           44       YES NO
  • standby库
select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#,BLOCK# from v$managed_standby;

PROCESS                   STATUS                         CLIENT_PROCESS          SEQUENCE#   BLOCK#
--------------------------- ------------------------------------ ------------------------ ---------- ----------
ARCH       CLOSING          ARCH    44 1
ARCH       CLOSING         ARCH    43 1
ARCH       CONNECTED        ARCH    0 0
ARCH       CLOSING         ARCH    42 1
RFS        IDLE           ARCH    0 0
RFS        IDLE           LGWR    45 484
RFS        IDLE           UNKNOWN    0 0
MRP0       APPLYING_LOG         N/A    45 484
05-11 10:59