ADG GAP修复场景1_主库上归档文件存在

原创董小姐 2024-10-22

673

问题描述

由于ADG主库和备库磁盘都是裸盘，主库磁盘20T 备库磁盘4T ，备库磁盘满导致主备库数据不同步。现场新申请一块10T新数据盘，将新申请的10T数据盘做成逻辑卷挂载到新磁盘/gsnew，停止备库，将备库旧数据盘/gs里所有文件拷贝到新磁盘/gsnew，文件全部拷贝完成后，分别卸载umount -l /gsnew和/gs，并调换新旧数据盘的挂载目录，即将新数据盘挂载到/gs目录，将旧数据盘挂载到/gsnew目录。数据盘替换后启动备库开启备库应用实时同步，查看主库同步状态时发现主备库存在gap。

注意：本文档主库归档存在时间较长，即gap之间的归档文件在主库上存在。

分析过程

查主备同步状态

主库

SWITCHOVER_STATUS状态 RESOLVABLE 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@TOPICIS 2024-10-21 22:06:22> set linesize 999
sys@TOPICIS 2024-10-21 22:11:32> col open_mode for a30
sys@TOPICIS 2024-10-21 22:11:32> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME                                                                             OPEN_MODE                      DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
-------------------------------------------------------------------------------- ------------------------------ ---------------- -------------------- --------------------
TOPICIS                                                                          READ WRITE                     PRIMARY          MAXIMUM PERFORMANCE  RESOLVABLE GAP

备库

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

输出如下：

SQL> set linesize 999
SQL> col open_mode for a30
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

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

查看当前SCN号

主库

select  to_char(current_scn) from v$database;

输出如下：

sys@TOPICIS 2024-10-21 22:13:49> select  to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
11297814564

备库

--查看当前scn号
select  to_char(current_scn) from v$database;

select to_char(dbms_flashback.get_system_change_number) scn from dual;

输出如下：

SQL>select  to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
10289501369


SQL>select to_char(dbms_flashback.get_system_change_number) scn from dual;

TO_CHAR(SCN)
----------------------------------------
10289501369

主库查看备库当前scn号对应的归档时间

select scn_to_timestamp(10289501369) scn from dual;

注意：
10289501369对应的值是
select to_char(dbms_flashback.get_system_change_number) scn from dual;的值

输出如下：

sys@TOPICIS 2024-10-21 22:16:34> select scn_to_timestamp(10289501369) scn from dual;

SCN
---------------------------------------------------------------------------
18-OCT-24 01.19.21.000000000 AM

主库查看归档保留情况

查看主库归档信息

archive log list;

输出如下：

sys@TOPICIS 2024-10-21 22:18:15> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /gs/oradata/archivelog/
Oldest online log sequence     78097
Next log sequence to archive   78099
Current log sequence           78099

查看归档目录下最早归档文件时间

幸好主库的归档最早时间早于备库当前SCN的时间，即主库的归档还保留着

[root@djzc-31:/root]$ ls -lhtr /gs/oradata/archivelog/ | head -5
total 7.6T
-rw-r----- 1 oracle oinstall 539M Oct 14 03:05 1_64361_1149287986.dbf
-rw-r----- 1 oracle oinstall 540M Oct 14 03:07 1_64362_1149287986.dbf
-rw-r----- 1 oracle oinstall 539M Oct 14 03:09 1_64363_1149287986.dbf
-rw-r----- 1 oracle oinstall 531M Oct 14 03:11 1_64364_1149287986.dbf

解决办法

启用日志实时应用

--备库open read only用
alter database recover managed standby database parallel 10 using current logfile disconnect from session;

查看日志

主库

cd $ORACLE_BASE/diag/rdbms/topicis/topicis/trace
tail -300f alert_topicis.log

输出如下：

备库

cd $ORACLE_BASE/diag/rdbms/btopicis/btopicis/trace
tail -300f alert_btopicis.log

输出如下：

查主备同步状态

由于差3-4天数据，关闭远程等第二天上午查看同步情况。

主库

--主库信息   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@TOPICIS 2024-10-22 09:00:12> set linesize 999
sys@TOPICIS 2024-10-22 09:00:12> col open_mode for a30
sys@TOPICIS 2024-10-22 09:00:14> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME                                                                             OPEN_MODE                      DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
-------------------------------------------------------------------------------- ------------------------------ ---------------- -------------------- --------------------
TOPICIS                                                                          READ WRITE                     PRIMARY          MAXIMUM PERFORMANCE  SESSIONS ACTIVE

备库

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

输出如下：

SQL> set linesize 999
SQL> col open_mode for a30
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

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

主库日志中报错处理

问题描述

监控主备同步过程中发现主库日志中提示如下报错：

.......
Tue Oct 22 09:46:24 2024
Archived Log entry 159476 added for thread 1 sequence 79198 ID 0xcd940315 dest 1:
Tue Oct 22 09:46:32 2024
LNS: Standby redo logfile selected for thread 1 sequence 79199 for destination LOG_ARCHIVE_DEST_2
Tue Oct 22 09:46:36 2024
ARCa: Archive log rejected (thread 1 sequence 79198) at host 'btopicis'
FAL[server, ARCa]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance topicis - Archival Error. Archiver continuing.
Tue Oct 22 09:46:56 2024
Thread 1 cannot allocate new log, sequence 79200
Checkpoint not complete
  Current log# 2 seq# 79199 mem# 0: /gs/oradata/topicis/redo02.log
Thread 1 advanced to log sequence 79200 (LGWR switch)
  Current log# 3 seq# 79200 mem# 0: /gs/oradata/topicis/redo03.log
Tue Oct 22 09:47:00 2024
Archived Log entry 159478 added for thread 1 sequence 79199 ID 0xcd940315 dest 1:
Tue Oct 22 09:47:03 2024
LNS: Standby redo logfile selected for thread 1 sequence 79200 for destination LOG_ARCHIVE_DEST_2
Tue Oct 22 09:47:33 2024
Thread 1 cannot allocate new log, sequence 79201
.......

日志中

ARCa: Archive log rejected (thread 1 sequence 79198) at host 'btopicis'

说明：thread 1 sequence 79198 文件可能损坏了，比如备库归档或磁盘满了，文件100m，实际上传到备库只传了80m

分析过程

查主库thread 1 sequence 79198 文件是否存在

[root@djzc-31:/root]$ cd /gs/oradata/archivelog
[root@djzc-31:/gs/oradata/archivelog]$ ls -l *79198*
-rw-r----- 1 oracle oinstall 615416832 Oct 22 09:46 1_79198_1149287986.dbf

查备库thread 1 sequence 79198 文件是否存在

[root@djzc-31:/root]$ cd /gs/oradata/archivelog
[root@djzc-31:/gs/oradata/archivelog]$ ls -l *79198*
-rw-r----- 1 oracle oinstall 615416832 Oct 22 09:46 1_79198_1149287986.dbf

解决办法

在备库上把这个文件先移动到其他位置，该文档移动到/home/oracle目录下。

只要主库上归档文件存在，正常gap自动处理会再从主库拿文件进行应用。

[oracle@djzc-51:/gs/oradata/archivelog]$ mv 1_79198_1149287986.dbf /home/oracle/

扩展

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博客

墨力计划

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

关注作者

ADG GAP修复场景1_主库上归档文件存在

问题描述

分析过程

查主备同步状态

主库

备库

查看当前SCN号

主库

备库

主库查看备库当前scn号对应的归档时间

主库查看归档保留情况

查看主库归档信息

查看归档目录下最早归档文件时间

解决办法

启用日志实时应用

查看日志

主库

备库

查主备同步状态

主库

备库

主库日志中报错处理

问题描述

分析过程

查主库thread 1 sequence 79198 文件是否存在

查备库thread 1 sequence 79198 文件是否存在

解决办法

扩展

scn 和时间的对应关系

查询当前scn

scn 转换成时间

时间转化成scn

scn和时间的对应关系

评论