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