ADG GAP修复场景2_主库上归档文件不存在有新增数据文件_unresolvable gap

原创董小姐 2025-03-17

559

该文档场景：主库上归档文件不存在，主库集群状态unresolvable gap，主库有新增数据文件

问题描述

由于ADG主备库数据实时同步，查看主库同步状态时发现主备库存在gap。

模拟gap

首先，模拟备库断电，主库切几个最新的归档，然后手工删掉，重新开启DG同步。

备库停止DG同步进程

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate

主库切换多次归档

sqlplus / as sysdba
alter system switch logfile;

主库增加数据文件

--查看表空间和数据文件的关系
set linesize 999
set pagesize 999
select tablespace_name,file_name from dba_data_files order by 1;


--创建表空间，大小5G，开启自动扩展
create tablespace entservice datafile '/oradata/fuwa/entservice01.dbf' size 5M autoextend on  MAXSIZE UNLIMITED;
-- 根据需要添加一定数量的数据文件
alter tablespace entservice add datafile '/oradata/fuwa/entservice02.dbf' size 1M autoextend on  MAXSIZE UNLIMITED;
--创建临时表空间
create temporary tablespace entservicetemp1 tempfile '/oradata/fuwa/entservicetemp02.dbf'
size 50m autoextend on  maxsize unlimited;

--扩undo
alter tablespace UNDOTBS1 add datafile '/oradata/fuwa/undotbs02.dbf' size 1M autoextend on  MAXSIZE UNLIMITED;

--再次切归档
alter system switch logfile;

主库删除最近几个归档日志

--查看归档路径
SYS@fuwa SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /oradata/archivelog
Oldest online log sequence     46
Next log sequence to archive   48
Current log sequence	       48

--操作系统磁盘上删除归档日志文件
cd /oradata/archivelog && ls -lhtr
-rw-r----- 1 oracle oinstall 1.1M Mar  5 17:41 1_27_1194792401.dbf
-rw-r----- 1 oracle oinstall 480K Mar  6 09:45 1_39_1194792401.dbf
-rw-r----- 1 oracle oinstall 3.3M Mar  6 10:38 1_40_1194792401.dbf
-rw-r----- 1 oracle oinstall 3.0M Mar  6 11:12 1_41_1194792401.dbf
-rw-r----- 1 oracle oinstall 434K Mar  6 11:20 1_42_1194792401.dbf
-rw-r----- 1 oracle oinstall 469K Mar  6 11:35 1_43_1194792401.dbf
-rw-r----- 1 oracle oinstall 2.0K Mar  6 11:35 1_44_1194792401.dbf
-rw-r----- 1 oracle oinstall 2.5K Mar  6 11:35 1_45_1194792401.dbf
-rw-r----- 1 oracle oinstall 1.0K Mar  6 11:35 1_46_1194792401.dbf
-rw-r----- 1 oracle oinstall 496K Mar  6 11:47 1_47_1194792401.dbf

[oracle@db01:/oradata/archivelog]$ rm -f 1_39*
[oracle@db01:/oradata/archivelog]$ rm -f 1_40*
[oracle@db01:/oradata/archivelog]$ rm -f 1_41*
[oracle@db01:/oradata/archivelog]$ rm -f 1_42*
[oracle@db01:/oradata/archivelog]$ rm -f 1_43*
[oracle@db01:/oradata/archivelog]$ rm -f 1_44*
[oracle@db01:/oradata/archivelog]$ rm -f 1_45*
[oracle@db01:/oradata/archivelog]$ rm -f 1_46*
[oracle@db01:/oradata/archivelog]$ rm -f 1_47*

备库开启同步进程

startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

或并行
alter database recover managed standby database parallel 10 using current logfile disconnect from session;

分析过程

查主备同步状态

法一：v$database

SWITCHOVER_STATUS状态有以下值，区别如下：

RESOLVABLE GAP 归档都在，可以自己解决

UNRESOLVABLE GAP 归档丢了，解决不了

主库

SWITCHOVER_STATUS状态 UNRESOLVABLE GAP说明主库和备库之间存在 GAP

--主库信息   switchover显示to standby或者session active为正常
set linesize 999
col open_mode for a30
select name,open_mode,database_role,protection_mode,switchover_status from v$database;

输出如下：

SYS@fuwa SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME	  OPEN_MODE			 DATABASE_ROLE	  PROTECTION_MODE      SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
FUWA	  READ WRITE			 PRIMARY	  MAXIMUM PERFORMANCE  UNRESOLVABLE GAP

备库

--备库信息
set linesize 999
col open_mode for a30
select name,open_mode,database_role,protection_mode,switchover_status from v$database;

输出如下：

SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME	  OPEN_MODE			 DATABASE_ROLE	  PROTECTION_MODE      SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
FUWA	  READ ONLY WITH APPLY		 PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED

法二：v$archive_dest_status

视图V$ARCHIVE_DEST_STATUS存储归档位置状态信息，这些信息是未从当前节点读取，而是从归档日志文件中收集而来的，可显示指定归档注册组或归档位置的归档日志的当前位置、传输状态等信息。

该视图在主库查

主库

select status,recovery_mode,gap_status from v$archive_dest_status;

输出如下：

SYS@fuwa SQL> select status,recovery_mode,gap_status from v$archive_dest_status;

STATUS	  RECOVERY_MODE 	  GAP_STATUS
--------- ----------------------- ------------------------
VALID	  IDLE
VALID	  MANAGED REAL TIME APPLY UNRESOLVABLE GAP
INACTIVE  IDLE
INACTIVE  IDLE
......

31 rows selected.

备库

select status,recovery_mode,gap_status from v$archive_dest_status;

输出如下：

SQL> select status,recovery_mode,gap_status from v$archive_dest_status;

STATUS	  RECOVERY_MODE 	        GAP_STATUS
--------- ----------------------- ------------------------
VALID	  MANAGED REAL TIME APPLY
VALID	  IDLE			                NO GAP
......

31 rows selected.

检查数据库mrp 进程

主库

set line 200 pages 300
select process, client_process, sequence#,thread#,status from v$managed_standby;

输出如下：

PROCESS   CLIENT_P  SEQUENCE#	 THREAD# STATUS
--------- -------- ---------- ---------- ------------
ARCH	  ARCH		   33	       1 OPENING
ARCH	  ARCH		   34	       1 CLOSING
ARCH	  ARCH		   35	       1 CLOSING
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   30	       1 CLOSING
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   39	       1 CLOSING
ARCH	  ARCH		   39	       1 CLOSING
ARCH	  ARCH		   37	       1 CLOSING
ARCH	  ARCH		   38	       1 CLOSING
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   32	       1 CLOSING
ARCH	  ARCH		   33	       1 CLOSING
LNS 	  LNS		     40	       1 WRITING

31 rows selected.

备库

set line 200 pages 300
select process, client_process, sequence#,thread#,status from v$managed_standby;

输出如下：

PROCESS   CLIENT_P  SEQUENCE#	 THREAD# STATUS
--------- -------- ---------- ---------- ------------
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   39	       1 CLOSING
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
RFS	    ARCH		    0	       0 IDLE
RFS	    UNKNOWN	    0	       0 IDLE
RFS	    LGWR		   40	       1 IDLE
MRP0	  N/A		     33	       1 APPLYING_LOG

34 rows selected.

查询数据库中的scn

查询出备库当前scn 号与主库scn 相差比较大，到主库寻找相应的归档，已经没有，所以，只能对主库进行rman 增量备份。

主库

select to_char(current_scn) from v$database;
select distinct MIN(to_char(checkpoint_change#)) FROM V$DATAFILE_HEADER;

输出如下：

select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
1122685

select distinct MIN(to_char(checkpoint_change#)) FROM V$DATAFILE_HEADER;

MIN(TO_CHAR(CHECKPOINT_CHANGE#))
----------------------------------------
1117174

备库

取最小的那个值1115613

说明:

上面一个为控制文件中记录的SCN号

另一个为数据文件头记录的SCN号

需要选择较小SCN号(1115613)在主库上进行增量备份

有些场景如下查询发现显示0或是空,这种情况，以第一个查询获取到的值为准

select to_char(current_scn) from v$database;
select distinct MIN(to_char(checkpoint_change#)) FROM V$DATAFILE_HEADER;

输出如下：

select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
1115613

select distinct MIN(to_char(checkpoint_change#)) FROM V$DATAFILE_HEADER;

MIN(TO_CHAR(CHECKPOINT_CHANGE#))
----------------------------------------
1115614

查主库是否在scn后增加过数据文件

如果任何数据文件在备份SCN(在例子中是scn 1105290)之后被添加到主数据库，那么无论standby_file_management参数设置如何，这些数据文件都不会自动在备用服务器上创建。需要将添加的数据文件恢复到备用服务器。有关更多详细信息，请参阅以下内容:注意:1531031.1在数据文件被添加到主数据库时使用RMAN增量备份向前滚动备用数据库的步骤)

SELECT FILE#,NAME FROM V$DATAFILE WHERE CREATION_CHANGE#>=1115613;

     FILE# NAME
---------- --------------------------------------------------------------------------------
	 5 /oradata/fuwa/entservice01.dbf
	 6 /oradata/fuwa/entservice02.dbf
	 7 /oradata/fuwa/undotbs02.dbf

SELECT FILE#,NAME FROM V$TEMPFILE WHERE CREATION_CHANGE#>=1115613;

     FILE# NAME
---------- --------------------------------------------------------------------------------
	 3 /oradata/fuwa/entservicetemp02.dbf

查看归档日志应用情况

注意：

REGISTRAR = RFS and APPLIED = NO:--然后日志文件已经收到，但是还没有被应用。

REGISTRAR = RFS and APPLIED = IN-MEMORY:--日志文件被应用到内存中，但是数据文件还没有被更新

REGISTRAR = RFS and APPLIED = YES:--然后应用日志文件并更新数据文件

主库

set lines 200 pages 2000
col name for a80
select name,thread#,sequence#,REGISTRAR,applied,status from v$archived_log where applied='NO';

输出如下：

备库

set lines 200 pages 2000
col name for a80
select name,thread#,sequence#,REGISTRAR,applied,status from v$archived_log where applied='NO';

输出如下：

查看备库是否有standby log

--查询日志组
set linesize 999
col MEMBER for a60
select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#=b.group# order by a.group#;

--查询日志组
col member for a60
select group#,member from v$logfile order by 1;
    GROUP# MEMBER
---------- --------------------------------------------------
	 1 /u01/app/oracle/oradata/dhh/redo01.log
	 2 /u01/app/oracle/oradata/dhh/redo02.log
	 3 /u01/app/oracle/oradata/dhh/redo03.log
	 4 /u01/app/oracle/oradata/dhh/redo04.log
	 5 /u01/app/oracle/oradata/dhh/redo05.log
	 6 /u01/app/oracle/oradata/dhh/redo06.log
	 7 /u01/app/oracle/oradata/dhh/redo07.log

select group#,thread#,sequence#,bytes,status from v$standby_log;
    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- --------------------
	 4	    0	       0   52428800 ACTIVE
	 5	    0	       0   52428800 UNASSIGNED
	 6	    0	       0   52428800 UNASSIGNED
	 7	    0	       0   52428800 UNASSIGNED

解决办法

如果gap 归档缺的少的话，直接把归档复制过来。然后restore,recover

查看GAP

在备库查询，如果在主库查询返回0行

sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD#    LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
 1     42     47

SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
42

📢 注意： 当前DG数据库已存在GAP，GAP日志为：32~35。

备库取消实时日志应用

--备库关闭日志实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

依据当前的SCN 号，进行数据库的增量备份

主库上操作

--创建存放备份文件的目录
mkdir -p /home/oracle/backup/wafu

--rman target / 
run {
allocate channel cl type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup incremental from scn 1115613 database format '/home/oracle/backup/wafu/fuwa_incre_%u' tag 'fuwa';
sql 'alter system switch logfile';
backup current controlfile for standby format '/home/oracle/backup/wafu/fuwa_control.ctl';
release channel cl;
release channel c2;
release channel c3;
release channel c4;
}

📢 注意： 确认备库的磁盘空间是否足够存放。

如果主库归档较大，备库磁盘剩余空间较小，可将备库的归档全删，主库保留2-3天归档

主库删除归档：

crosscheck archivelog all;
delete noprompt expired archivelog all;
list archivelog all completed before 'sysdate-1';
delete FORCE archivelog all completed before 'sysdate-1';

备库删除所有归档：

crosscheck archivelog all;
delete noprompt expired archivelog all;
list archivelog all;
DELETE FORCE ARCHIVELOG ALL;

将增量备份和控制文件拷贝到备库上

主库拷贝增量备份和控制文件至备库：

cd /home/oracle/backup
scp -r wafu oracle@db02:/home/oracle

📢 注意： 确认备库的磁盘空间是否足够存放。

如果主库归档较大，备库磁盘剩余空间较小，可将备库的归档全删，主库保留2-3天归档

主库删除归档：

crosscheck archivelog all;
delete noprompt expired archivelog all;
list archivelog all completed before 'sysdate-1';
delete FORCE archivelog all completed before 'sysdate-1';

备库删除所有归档：

crosscheck archivelog all;
delete noprompt expired archivelog all;
list archivelog all;
DELETE FORCE ARCHIVELOG ALL;

使用新的控制文件将备库启动到mount状态

备库关闭数据库实例，开启至nomount状态：

sqlplus / as sysdba
shutdown immediate
startup nomount

备库恢复新的控制文件：

rman target /
restore controlfile from '/home/oracle/wafu/fuwa_control.ctl';

备库开启到mount状态：

alter database mount;

增量备份注册到RMAN的catalog，取消日志应用，恢复增量备份

确认备库已关闭DG同步进程

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

返回如下：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

备库rman注册增量备份文件

切换操作将更新控制文件，使其包含备用控制文件中数据文件的正确位置和名称

rman target /
catalog start with '/home/oracle/wafu/' noprompt;
crosscheck backup;
delete noprompt expired backup;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

备库还原主库新增的数据文件

RMAN> restore datafile 5;
RMAN> restore datafile 6;
RMAN> restore datafile 7;

备库开启恢复增量备份

rman target /
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

开启备库的恢复进程

备库开启日志同步进程：

sqlplus / as sysdba
alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

或并行
alter database recover managed standby database parallel 10 using current logfile disconnect from session;

主库重新激活同步

sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;

清空日志组(根据场景变通)

如果采用了standby log则不需要清空，如果没有采用standby log模式，有几组需要清空几组

--查询日志组
select group#,bytes from v$log;

    GROUP#	BYTES
---------- ----------
	 1   52428800
	 2   52428800
	 3   52428800

--清空日志组
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

查询是否存在GAP，确认主备是否同步

备库操作

sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
SELECT max(sequence#) from v$archived_log where applied='YES';
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

至此，DG GAP已被修复，以上方式为常规修复方式，各个版本都通用。

扩展

scn 和时间的对应关系

查询当前scn

SQL> select current_scn from v$database;
 CURRENT_SCN
------------
 49733519430
 
SQL>  select dbms_flashback.get_system_change_number scn from dual;
   SCN
---------------
    49733520950

scn 转换成时间

SQL> select scn_to_timestamp(49733520950) scn from dual;
SCN
---------------------------------------------------------------------------
18-11月-14 04.44.57.000000000 下午

时间转化成scn

SQL> select timestamp_to_scn(to_timestamp('2014-11-10 18:19:20.123456789','YYYY-MM-DD HH24:MI:SS.FF')) scn from dual; 
   SCN
---------------
    49733322505

scn和时间的对应关系

SQL> select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time from sys.smon_scn_time where rownum<10 order by 1,2;
   SCN                   TIME
--------------- ---------------------------------------------------------
    49733225694 2014-10-30 22:05:40
    49733230790 2014-10-31 00:50:13
    49733230906 2014-10-31 00:55:25
    49733231047 2014-10-31 01:00:34
    49733231152 2014-10-31 01:05:42
    49733234525 2014-10-31 03:24:43
    49733234648 2014-10-31 03:29:52
    49733234760 2014-10-31 03:35:03
    49733234861 2014-10-31 03:40:12
9 rows selected.

参考链接：scn 和时间的对应关系_根据scn查归档文件时间-CSDN博客

https://mp.weixin.qq.com/s/A4Yp9VdhQlakSa2Uat16FA

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ADG GAP修复场景2_主库上归档文件不存在有新增数据文件_unresolvable gap

问题描述

模拟gap

备库停止DG同步进程

主库切换多次归档

主库增加数据文件

主库删除最近几个归档日志

备库开启同步进程

分析过程

查主备同步状态

法一：v$database

主库

备库

法二：v$archive_dest_status

主库

备库

检查数据库mrp 进程

主库

备库

查询数据库中的scn

主库

备库

查主库是否在scn后增加过数据文件

查看归档日志应用情况

主库

备库

查看备库是否有standby log

解决办法

查看GAP

备库取消实时日志应用

依据当前的SCN 号，进行数据库的增量备份

将增量备份和控制文件拷贝到备库上

使用新的控制文件将备库启动到mount状态

增量备份注册到RMAN的catalog，取消日志应用，恢复增量备份

确认备库已关闭DG同步进程

备库rman注册增量备份文件

备库还原主库新增的数据文件

备库开启恢复增量备份

开启备库的恢复进程

主库重新激活同步

清空日志组(根据场景变通)

查询是否存在GAP，确认主备是否同步

扩展

scn 和时间的对应关系

查询当前scn

scn 转换成时间

时间转化成scn

scn和时间的对应关系

评论