生产环境Oracle 18c一条命令修改DG GAP

1. 18c或更高版本GAP处理思路

适用于单机和RAC。

采用rman的新功能recover using service，可以通过网络远程恢复数据库，当主数据库和备用数据库之间存在相当大的差异(GAP)时， 12c可以通过service name直连主库，在线修复，省去rman基于scn备份后再传输到备库恢复的冗繁步骤。

语法：
RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;
- 确认主库的TNS已配置，这里的<PRIMARY DB SERVICE NAME >即 TNSNAME。

18c 新特性是在 12c 的基础上，将 recover standby database 命令与 from service 子句一起使用，以通过对主数据库进行更改来刷新物理备用数据库

- 备库可以直接在开启状态进行刷新。
语法：
recover standby database from service <TNS ALIAS FOR PRIMARY DATABASE>;

- 如果是RAC，要关闭其他实例，节点1启动到mount状态后执行。

2. 执行recover standby database from service刷新备库

- 使用一行命令在线修复dg gap：
- 备库取消日志应用：
sqlplus / as sysdba
alter database recover managed standby database cancel;

- 备库执行修复命令，开始在线刷新备库：
- 在mount状态下执行
recover standby database from service <TNS ALIAS FOR PRIMARY DATABASE>;
- 确认备库的TNS已配置，这里的<TNS ALIAS FOR PRIMARY DATABASE>即 TNSNAME。

- 例如主库在tnsnames.ora中配置的服务名是prod，备库是prodstd,写法如下：
recover standby database from service prod;

3. 修复过程分析

- 执行过程详情LOGS：
variable size 600785772 bytes
database buffers 331350016 bytes
redo buffers 3678208 bytes

- 重新从主库恢复新的 standby control
- 终止并重新启动实例到 mount
starting recover at 28-may-23
using target database control file instead of recovery catalog
oracle instance started

total system global area 973716976 bytes
fixed size 8903280 bytes
contents of memory script:
{
 restore standby controlfile from service 'prod';
 alter database mount standby database;
}
executing memory script

starting restore at 28-may-23
allocated channel: ora_disk_1
channel ora_disk_1: sid=38 device type=disk

channel ora_disk_1: starting datafile backup set restore
channel ora_disk_1: using network backup set from service prod
channel ora_disk_1: restoring control file
channel ora_disk_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/prod/control01.ctl
output file name=/oradata/prod/control02.ctl
finished restore at 28-may-23

released channel: ora_disk_1
statement processed.

• 切换为备库文件管理为手动模式

alter system set standby_file_management=manual;

• 自动恢复gap期间在主库新建的数据文件，并更新控制文件里面的名称;

• • 修改备库控制文件中数据库文件路径，包括临时文件，redo log和datafile。

  contents of memory script:
  {
  set newname for datafile 6 to 
  "/oradata/prod/prod02.dbf";
   restore from service 'prod' datafile 6;
   catalog datafilecopy "/oradata/prod/prod02.dbf";
   switch datafile all;
  }
  executing memory script
  executing command: set newname
  
  starting restore at 28-may-23
  starting implicit crosscheck backup at 28-may-23
  allocated channel: ora_disk_1
  channel ora_disk_1: sid=70 device type=disk
  crosschecked 77 objects
  finished implicit crosscheck backup at 28-may-23
  
  starting implicit crosscheck copy at 28-may-23
  using channel ora_disk_1
  finished implicit crosscheck copy at 28-may-23
  
  searching for all files in the recovery area
  cataloging files...
  cataloging done
  
  list of cataloged files
  
  file name: /home/oracle/flash/prodstd/autobackup/2023_05_27/o1_mf_s_1127252921_kx7oqc3v_.bkp
  
  using channel ora_disk_1
  
  channel ora_disk_1: starting datafile backup set restore
  channel ora_disk_1: using network backup set from service prod
  channel ora_disk_1: specifying datafile(s) to restore from backup set
  channel ora_disk_1: restoring datafile 00006 to /oradata/prod/prod02.dbf
  channel ora_disk_1: restore complete, elapsed time: 00:00:01
  finished restore at 28-may-23
  
  cataloged datafile copy
  datafile copy file name=/oradata/prod/prod02.dbf recid=3 stamp=112729362
  2
  datafile 6 switched to datafile copy
  input datafile copy recid=3 stamp=1127293622 file name=/oradata/prod/prod02.dbf
  

  • 进行增量recover恢复

  contents of memory script:
  {
   recover database from service 'prod';
  }
  executing memory script
  
  starting recover at 28-may-23
  using channel ora_disk_1
  skipping datafile 6; already restored to scn 1210063
  channel ora_disk_1: starting incremental datafile backup set restore
  channel ora_disk_1: using network backup set from service prod
  destination for restore of datafile 00001: /oradata/prod/system01.dbf
  channel ora_disk_1: restore complete, elapsed time: 00:00:03
  channel ora_disk_1: starting incremental datafile backup set restore
  channel ora_disk_1: using network backup set from service prod
  destination for restore of datafile 00002: /oradata/prod/sysaux01.dbf
  channel ora_disk_1: restore complete, elapsed time: 00:00:07
  channel ora_disk_1: starting incremental datafile backup set restore
  channel ora_disk_1: using network backup set from service prod
  destination for restore of datafile 00003: /oradata/prod/undotbs01.dbf
  channel ora_disk_1: restore complete, elapsed time: 00:00:07
  channel ora_disk_1: starting incremental datafile backup set restore
  channel ora_disk_1: using network backup set from service prod
  destination for restore of datafile 00007: /oradata/prod/users01.dbf
  channel ora_disk_1: restore complete, elapsed time: 00:00:01
  channel ora_disk_1: starting incremental datafile backup set restore
  channel ora_disk_1: using network backup set from service prod
  destination for restore of datafile 00005: /oradata/prod/prod01.dbf
  channel ora_disk_1: restore complete, elapsed time: 00:00:01
  
  starting media recovery
  
  archived log for thread 1 with sequence 198 is already on disk as file /oracle/app/oracle/oradata
  /prod/arch/1_198_1112197870.arcarchived log file name=/oradata/prod/arch/1_198_1112197870.arc thread=1 sequenc
  e=198media recovery complete, elapsed time: 00:00:01
  finished recover at 28-may-23
  executing: alter system set standby_file_management=auto
  finished recover at 28-may-23
  

  GAP已修复完成，18C这个新特性将一些步骤进行了省略和封装，进一步减少了恢复的操作步骤，但是内部的原理仍然是一致的。

  通过以上执行过程，可以看到：

  1）recover standby database命令重新启动备用实例。

  2）从主数据库刷新控制文件，并自动重命名数据文件，临时文件和联机日志。

  3）还原添加到主数据库中的新数据文件，并还原到当前时间的备用数据库中。

  4. 主库切日志，备库开启日志应用

  • • 主库切几次归档：

    sqlplus / as sysdba
    alter system switch logfile;
    /
    /
    

  • • 开启备库应用日志：

    sqlplus / as sysdba
    alter database open;
    alter database recover managed standby database using current logfile disconnect from session;
    
    - 查看备库同步是否正常：
    sqlplus / as sysdba
    select process,status,thread#,sequence#,block# from v$managed_standby;
    
    - 查看备库是否有GAP
    SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
    
    - 主库多切换几次日志，查看备库应用情况
    alter system switch logfile;
    
    - 主库插入数据验证：
    create table t1 as select * from v$database;
    commit;
    

    - 主备库查看
    select * from t1;