Oracle 19C DG 修复 Gap

2019年2月10日下午16：58分出现服务器异常重启，目前尚未知其根本原因。导致所有生产环境统一宕机，造成30分钟的异常行为。所幸到了下班时间经过基本排查，排除电源故障（UPC双路电源）、服务器500天未重启原因。

经重启服务器后检查所有生产环境，发现19C主从数据库所在的服务器设置了IP限制，导致主从之间无法访问，造成主库归当日志无法传输到从库。出现三百多份归档日志未同步到从库。导致数据库出现GAP，使用GAP默认修复方式加归档日志拉起备库。

此处使用了 GAP+Archive log 修复从库。默认情况下，DG主从出现GAP，只需要通过GAP方式即可修复从库。但是当前情况下，单独使用GAP方式无法拉起从库，所以结合了主库归档日志进行修复

1、添加主从服务器各自SSH IP限制功能

$ vi /etc/hosts.allow
$ vi /etc/hosts.deny

2、从库执行介质恢复

SQL>shutdown immediate
SQL>startup 
SQL>recover managed standby database using current logfile disconnect from session;

3、从库执行压缩备份恢复
注意：此处执行该方式的目的是为了同步从库所有数据文件的SCN，保证其和主库SCN一致。从库使用RMAN通过主库service name 连接执行恢复

RMAN> recover database from service dw noredo using compressed backupset;
Starting recover at 10-FEB-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2916 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2918 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=25 device type=DISK
skipping datafile 1; already restored to SCN 8262336617092
skipping datafile 2; already restored to SCN 8262336617093
skipping datafile 4; already restored to SCN 8262336617106
skipping datafile 5; already restored to SCN 8262336617125
skipping datafile 7; already restored to SCN 8262336622109
skipping datafile 8; already restored to SCN 8262336624591
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service hekdw
destination for restore of datafile 00003: /data/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished recover at 10-FEB-25

4、查询主从库数据文件SCN
注意：此处只需要保证主从SCN一致，接着恢复从库控制文件。如果未一致，再次执行从库压缩备份恢复

# 主库查询
SQL> col name for a40;
SQL> col FHSCN for a10
SQL> col file_num for a15;
SQL> set linesize 120
SQL>select hxfil file_num,substr(HXFNM,1,40) name ,fhscn from x$kcvfh;
---------- ---------------------------------------- ----------
1          /data/oracle/oradata/system01.dbf  8262336617092        
2          /data/oracle/oradata/users06.dbf   8262336617093
3         /data/oracle/oradata/sysaux01.dbf   8262336669030     
4        /data/oracle/oradata/undotbs01.dbf   8262336617106

# 从库查询
SQL> col name for a40;
SQL> col FHSCN for a10
SQL> col file_num for a15;
SQL> set linesize 120
SQL>select hxfil file_num,substr(HXFNM,1,40) name ,fhscn from x$kcvfh;
---------- ---------------------------------------- ----------
1          /data/oracle/oradata/system01.dbf  8262336617092
2          /data/oracle/oradata/users06.dbf   8262336617093
3         /data/oracle/oradata/sysaux01.dbf   8262336669030     
4        /data/oracle/oradata/undotbs01.dbf   8262336617106

5、从库恢复控制文件

SQL>shutdown immediate
SQL>satrtup nomount

RMAN> restore standby controlfile from service dw;

Starting restore at 10-FEB-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2912 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service hekdw
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oracle/oradata/control01.ctl
output file name=/data/oracle/oradata/control02.ctl
Finished restore at 10-FEB-25

SQL>alter database mount;

6、检查从库归档日志应用位置
注意：回到主库将对应的归档日志及后续所有归档日志发送到从库

# 主从查询归档日志差异
select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;

select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

# 主库发送归档日志到从库
$ scp archive.log [从库IP]:[path]
$ chown -R oracle:oinstall *

7、从库注册新发送过来的归档日志
注意：归档日志可以直接发送到原归档日志保存路径，在注册时，系统仅会选择未注册到控制文件中的归档日志进行注册。

RMAN> catalog start with '/data/oracle/tmp_arc';

searching for all files that match the pattern /data/oracle/tmp_arc

List of Files Unknown to the Database
=====================================
File Name: /data/oracle/tmp_arc/test_1_106658_1056118119.arc
File Name: /data/oracle/tmp_arc/test_1_106659_1056118119.arc
File Name: /data/oracle/tmp_arc/test_1_106660_1056118119.arc
File Name: /data/oracle/tmp_arc/test_1_106661_1056118119.arc
File Name: /data/oracle/tmp_arc/test_1_106662_1056118119.arc

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

List of Cataloged Files
=======================
File Name: /data/oracle/tmp_arc/test_1_106658_1056118119.arc
File Name: /data/oracle/tmp_arc/test_1_106659_1056118119.arc
File Name: /data/oracle/tmp_arc/test_1_106660_1056118119.arc

8、从库执行归档日志恢复
注意：此处指定从缺少的归档日志恢复到目前主库正在生成的归档日志的前一份归档日志即可

RMAN> restore archivelog from sequence 106658 until sequence 106805;

Starting restore at 11-FEB-25
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

archived log for thread 1 with sequence 106658 is already on disk as file /data/oracle/tmp_arc/test_1_106658_1056118119.a                    rc
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 11-FEB-25

# 从库查询恢复进度
select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;

select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

9、从库取消介质恢复
注意：该操作需要等待从库执行完归档日志恢复后再执行

SQL> recover managed standby database cancel;
Media recovery complete.

10、从库启动到read only

SQL> alter database open read only;

11、从库开启实时日志同步

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

12、校验是否同步

# 主库执行日志切换
SQL>alter system switch logfile;
# 从库查询日志接收
select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;

select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

13、主库未发送日志到从库
注意：主从已经修复同步，但是主库执行日志切换后，并未发送归档日志到从库。经检查发现，主库使用的是原spfile启动的。而原spfile并未包含DG配置。所以此处需要使用DG的参数文件重启主库，并创建spfile。方便后续启动直接加载DG

# 查看主库是否启用DG相关参数，如未使用则指定DG参数文件启动数据库
SQL>show parameter archive
# 指定DG参数文件进行启动
SQL>startup pfile='initorcl.ora';
SQL>create spfile from pfile; -- 下次重启默认使用spfile

14、主从日志监控

$ tail -f alert_orcl.log

1、查看从库进程
其中 MRP 进程是从库核心进程。从库利用MRP进程执行归档日志恢复。其中SEQUENCE#显示恢复到的归档日志编号

SQL> select process,status,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
DGRD      ALLOCATED             0
DGRD      ALLOCATED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG     106657

2、查看日志是否正常应用

select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;

select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;