oracle 19c adg GAP恢复

最新推荐文章于 2023-02-01 14:59:09 发布
最新推荐文章于 2023-02-01 14:59:09 发布
阅读量2.8k 收藏 6
点赞数 1
分类专栏： 故障 文章标签： oracle 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fanzhuozhuo/article/details/122753393
版权

oracle 19c adg GAP恢复


继上篇文章，回退成功后。发现我们在回退的过程中，为了不影响备库，把log_archive_dest_state_2设置为了defer。但是后面想想，已经来不及了，因为主库刷新数据字典的过程中，已经会把字典的变化同步到了备库。我们设置defer的时间迟了，应该在刷新数据字典之前，就把log_archive_dest_state_2设置为defer，让备库不会应用数据字典的变化。如果此时发生切换，switchover肯定不行，failover的时候，直接把备库切换为主库，这也不失为一种好的回退方法。
本篇文章不讨论回退方法，由于设置为了defer，恢复时间用了将近8h，主库的归档删除策略是华为的备份软件强制删除，所以导致备库缺少归档，adg同步异常。下面就说下恢复方法。

故障现象

环境信息：
2节点RAC，GI和DB的补丁应用到2020年10月份。数据库版本：19.9.0.0.0
os版本：RHEL 7.6
备库adg同步信息如下：

SQL> set pages 1000 lines 1000
SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
FROM (select thread# thrd, MAX(sequence#) almax
FROM v$archived_log
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al,
(SELECT thread# thrd,   2    3    4    5  MAX(sequence#) lhmax
FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;  6    7    8  

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
	 1	       32301		32262
	 2	       28725		28700
SQL> col client_pid for a10
SELECT inst_id, thread#, process, pid, status, client_process, client_pid,
sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;SQL>   2  

   INST_ID    THREAD# PROCESS	PID			 STATUS       CLIENT_P CLIENT_PID  SEQUENCE#	 BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- ---------- --------- ------------------------ ------------ -------- ---------- ---------- ---------- ------------- ------------
	 1	    0 RFS	30765			 IDLE	      UNKNOWN  35906		   0	      0 	    0		 0
	 1	    0 RFS	30767			 IDLE	      UNKNOWN  142075		   0	      0 	    0		 0
	 1	    0 RFS	30769			 IDLE	      UNKNOWN  35888		   0	      0 	    0		 0
	 1	    0 RFS	30774			 IDLE	      UNKNOWN  141997		   0	      0 	    0		 0
	 1	    0 RFS	30776			 IDLE	      UNKNOWN  142133		   0	      0 	    0		 0
	 1	    0 RFS	30778			 IDLE	      UNKNOWN  35867		   0	      0 	    0		 0
	 1	    0 DGRD	31472			 ALLOCATED    N/A      N/A		   0	      0 	    0		 0
	 1	    0 DGRD	31474			 ALLOCATED    N/A      N/A		   0	      0 	    0		 0
	 2	    0 DGRD	85340			 ALLOCATED    N/A      N/A		   0	      0 	    0		 0
	 2	    0 DGRD	85344			 ALLOCATED    N/A      N/A		   0	      0 	    0		 0
	 1	    1 MRP0	101677			 WAIT_FOR_GAP N/A      N/A	       32263	      0 	  128	       128
	 1	    1 RFS	28815			 IDLE	      Archival 141782		   0	      0 	    0		 0
	 1	    1 RFS	28906			 IDLE	      LGWR     136732	       32302	 398785 	    0		 0
	 1	    2 RFS	28820			 IDLE	      Archival 35812		   0	      0 	    0		 0
	 1	    2 RFS	28912			 IDLE	      LGWR     91132	       28726	  10390 	    0		 0
	 1	    2 ARCH	31470			 CLOSING      ARCH     31470	       28698	  81920 	    0		 0
	 1	    2 ARCH	31478			 CLOSING      ARCH     31478	       28700	      1 	    0		 0
	 1	    2 ARCH	31480			 CLOSING      ARCH     31480	       28699	  18432 	    0		 0
	 1	    2 ARCH	31482			 CLOSING      ARCH     31482	       28701	  28672 	    0		 0
	 2	    2 ARCH	85325			 CLOSING      ARCH     85325	       24857	  36864 	    0		 0
	 2	    2 ARCH	85377			 CLOSING      ARCH     85377	       24852	  22528 	    0		 0
	 2	    2 ARCH	85379			 CLOSING      ARCH     85379	       24855	      1 	    0		 0
	 2	    2 ARCH	85381			 CLOSING      ARCH     85381	       24853	  26624 	    0		 0

23 rows selected.
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID
---------- ------------- -------------- ----------
	 1	   32263	  32284 	 1

可见我们现在缺少thread 1的seq 32263-32284之间21个归档日志。

故障分析

参考前面的文章：https://www.modb.pro/db/175815#_405
恢复GAP有两种方法：
一、gap较少，或者归档日志还存在，或者可以从备份集中恢复出来可以直接将缺少的归档scp到standby，在standby手工注册下即可。
二、gap较多，在primary 做基于scn的backup，同时创建一个新的standbycontrolfile，将备份好的backupset ，standby controlfile 拷贝到备库的相应目录下，进行restore、recover的操作即可。
此处的GAP较少，可以采用恢复出删除的归档日志进行恢复。但是前提要由备份，和要能恢复出来。
主要以下两个命令：
RMAN> list archivelog from sequence 32263 until sequence 32284 thread 1;
RMAN> list backup of archivelog from sequence 32263 until sequence 32284 thread 1;
c397da86d76e64b9d5ae75be01bb80f.jpg
44cc332966a4f119fa902d6c9fe9d8f.jpg
可见归档日志确实被删除，但是这些归档日志都已经被备份了。我们可以尝试从备份集中恢复出这些被删除的归档日志。
先尝试恢复一个：
edb0442c93aff413a4b37743b58bca9.png
恢复报错。由于没有写parms参数。
经咨询，备份采用的是华为备份软件DPA。华为自己的维护人员也不知道应该怎么恢复，而且只能全量恢复，不能恢复单个归档日志。此处吐槽一下，不知道是现场人员的问题还是DPA本身的问题，对于不能恢复单个归档日志，实在是不应该，这是最常用的场景。如果这里有熟悉DPA的人，也可以和我沟通下，怎么恢复。
类似nbu恢复这种，就可以正常恢复：
image.png
所以此处不再考虑恢复删除的归档日志恢复，采用基于scn的增量备份恢复的方案来恢复ADG GAP。

GAP恢复

参考mos：18c Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)
12c之后恢复ADG的GAP步骤简单了，有新特性了。
Typically, when rolling forward a physical standby database using primary incremental backup, multiple steps are required:

Identify the Start SCN on Standby for performing incremental backup on primary
Perform incremental backup on primary with FROM SCN clause
Move the backup-pieces from primary to standby
Catalog the backup-piece on Standby
Perform recovery on standby using recover database noredo
Refresh standby controlfile again from primary

Starting from 12.1, we could use "RECOVER DATABASE FROM SERVICE"command which will automate a few steps like performing incremental backup on primary, transfer the backup-pieces to standby over network and perform recovery on standby. However, we still had to manually refresh the standby controlfile and manually restore newly-added datafiles. These steps required manual efforts and are error prone especially when standby files are physically located in a path different to that of primary.

Starting with 18.1, we can use a single command to refresh the standby with changes made on primary:

RMAN> RECOVER STANDBY DATABASE FROM SERVICE primary_connect_identifier;

This command will internally keep track of standby file locations, refresh standby controlfile from primary, update the new standby controlfile with standby file names, perform incremental backup on primary, transfer the backup-pieces over network to standby and perform recovery on standby
12.1之前，主要的恢复步骤，参考之前的文章。
12.1之后，需要recover database from service ，然后重新恢复控制文件，恢复新增数据文件。
18.1之后，更简单了，直接一条命令就包括了所有的恢复步骤。

正式开始前需要注意两个地方：
1、确认主库的TNS已配置，这里的< PRIMARY DB SERVICE NAME >即 TNSNAME。
2、If the standby is RAC with more than one instance, make sure only the instance from which recover standby command will be executed is mounted and all other instances are shutdown to avoid RMAN-05157。
必须保证出恢复节点外，剩余节点的实例都是关闭状态。否则报错RMAN-05157.这其实和手动duplicate的时候的要求一致。

RMAN> recover standby database from service priqhrimdb;
Starting recover at 2022-01-18 20:35:02
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/18/2022 20:35:03
RMAN-05157: The database must not be mounted on any other instance for RECOVER STANDBY DATABASE command.

具体步骤如下：

  1. 执行增量恢复
  2. 启动mrp，open数据库
  3. 修改standby日志和redo log没有转化过来的文件路径
  4. 启动mrp
  5. 检查同步状态
  • 执行增量恢复
    RMAN> recover standby database from service priqhrimdb;
qhrimdb1:/oracle/app/oracle/product/19.0.0/db/network/admin(qhrimdb1)$rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 18 20:51:36 2022
Version 19.9.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: QHRIMDB (DBID=1354654563)

RMAN>  recover standby database from service priqhrimdb;

Starting recover at 2022-01-18 20:51:43

Oracle instance started

Total System Global Area  387620796704 bytes

Fixed Size                    30951712 bytes
Variable Size             112541564928 bytes
Database Buffers          274877906944 bytes
Redo Buffers                 170373120 bytes

contents of Memory Script:
{
   restore standby controlfile from service  'priqhrimdb';  --重新恢复控制文件
   alter database mount standby database;
}
executing Memory Script

Starting restore at 2022-01-18 20:52:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3188 instance=qhrimdb1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service priqhrimdb
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATADG1/QHRIMDB/CONTROLFILE/control_files01.ctl
Finished restore at 2022-01-18 20:52:12

released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile  1 to 
 "+DATADG1/QHRIMDBSTD/TEMPFILE/temp.315.1048266439";
set newname for tempfile  2 to 
 "+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/TEMPFILE/temp.316.1048266441";
set newname for tempfile  3 to 
 "+DATADG1/QHRIMDBSTD/ABF515494C5840B0E053243DE60ADF28/TEMPFILE/temp.339.1048266865";
.........
contents of Memory Script:
{
set newname for tempfile  1 to 
 "+DATADG1/QHRIMDBSTD/TEMPFILE/temp.315.1048266439";
set newname for tempfile  2 to 
 "+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/TEMPFILE/temp.316.1048266441";
set newname for tempfile  3 to 
 "+DATADG1/QHRIMDBSTD/ABF515494C5840B0E053243DE60ADF28/TEMPFILE/temp.339.1048266865";
.........
set newname for datafile  483 to 
 "+DATADG2/QHRIMDBSTD/DATAFILE/undotbs2.396.1094268467";
set newname for datafile  484 to 
 "+DATADG2/QHRIMDBSTD/DATAFILE/sysaux.397.1094268911";
   catalog datafilecopy  "+DATADG1/QHRIMDBSTD/DATAFILE/system.287.1048266159", 
 "+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/DATAFILE/system.278.1048266179", 
 "+DATADG1/QHRIMDBSTD/DATAFILE/sysaux.267.1048266165", 
 "+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/DATAFILE/sysaux.281.1048266177", 
..........
 "+DATADG2/QHRIMDBSTD/B198F463B60BD808E053243DE60AC13B/DATAFILE/tbs_roam_data.395.1093864019", 
 "+DATADG2/QHRIMDBSTD/DATAFILE/undotbs2.396.1094268467", 
 "+DATADG2/QHRIMDBSTD/DATAFILE/sysaux.397.1094268911";
   switch datafile all;                              --刷新控制文件中，修改备库控制文件中数据文件路径
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME
.........
executing command: SET NEWNAME

cataloged datafile copy
datafile copy file name=+DATADG1/QHRIMDBSTD/DATAFILE/system.287.1048266159 RECID=1 STAMP=1094331155
cataloged datafile copy
datafile copy file name=+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/DATAFILE/system.278.1048266179 RECID=2 STAMP=1094331155
cataloged datafile copy
datafile copy file name=+DATADG1/QHRIMDBSTD/DATAFILE/sysaux.267.1048266165 RECID=3 STAMP=1094331155
..........
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1094331155 file name=+DATADG1/QHRIMDBSTD/DATAFILE/system.287.1048266159
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1094331155 file name=+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/DATAFILE/system.278.1048266179
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1094331155 file name=+DATADG1/QHRIMDBSTD/DATAFILE/sysaux.267.1048266165
.........
datafile 484 switched to datafile copy
input datafile copy RECID=418 STAMP=1094331169 file name=+DATADG2/QHRIMDBSTD/DATAFILE/sysaux.397.1094268911
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_5.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_5.258.1048266211'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_6.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_6.259.1048266215'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_7.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_7.271.1048266219'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_8.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_8.296.1048266223'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_9.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_9.295.1048266227'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_10.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_10.294.1048266233'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_11.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_11.293.1048266237'
Oracle error from target database: 
ORA-19528: redo logs being cleared may need access to files
RMAN-05535: warning: All redo log files were not defined properly.
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_12.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_12.270.1048266241'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_13.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_13.291.1048266245'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_14.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_14.290.1048266249'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_15.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_15.286.1048266255'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_16.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_16.284.1048266259'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_17.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_17.279.1048266263'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_18.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_18.275.1048266267'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_19.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_19.274.1048266271'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_20.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_20.273.1048266277'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_21.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_21.269.1048266281'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_22.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_22.268.1048266285'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_23.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_23.265.1048266289'
Executing: alter database rename file '+DATADG1/MUST_RENAME_THIS_LOGFILE_24.4294967295.4294967295' to '+DATADG1/QHRIMDBSTD/ONLINELOG/group_24.264.1048266293'
contents of Memory Script:
{
  recover database from service  'priqhrimdb';    --直接在线进行增量备份和恢复。
}
executing Memory Script
Starting recover at 2022-01-18 20:57:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1594 instance=qhrimdb1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service priqhrimdb
destination for restore of datafile 00001: +DATADG1/QHRIMDBSTD/DATAFILE/system.287.1048266159
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service priqhrimdb
destination for restore of datafile 00002: +DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/DATAFILE/system.278.1048266179
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 priqhrimdb
destination for restore of datafile 00003: +DATADG1/QHRIMDBSTD/DATAFILE/sysaux.267.1048266165
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
..............
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service priqhrimdb
destination for restore of datafile 00484: +DATADG2/QHRIMDBSTD/DATAFILE/sysaux.397.1094268911
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
archived log for thread 1 with sequence 32307 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32307.1018.1094332275
archived log for thread 1 with sequence 32308 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32308.1084.1094334077
archived log for thread 1 with sequence 32309 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32309.1047.1094335877
archived log for thread 1 with sequence 32310 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32310.923.1094335885
archived log for thread 1 with sequence 32311 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32311.1010.1094335889
archived log for thread 2 with sequence 28731 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28731.1000.1094332275
archived log for thread 2 with sequence 28732 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28732.1007.1094334077
archived log for thread 2 with sequence 28733 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28733.1077.1094335877
archived log for thread 2 with sequence 28734 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28734.1092.1094335885
archived log for thread 2 with sequence 28735 is already on disk as file +ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28735.332.1094335889
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32307.1018.1094332275 thread=1 sequence=32307
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28731.1000.1094332275 thread=2 sequence=28731
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32308.1084.1094334077 thread=1 sequence=32308
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28732.1007.1094334077 thread=2 sequence=28732
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32309.1047.1094335877 thread=1 sequence=32309
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28733.1077.1094335877 thread=2 sequence=28733
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28734.1092.1094335885 thread=2 sequence=28734
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32310.923.1094335885 thread=1 sequence=32310
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_1_seq_32311.1010.1094335889 thread=1 sequence=32311
archived log file name=+ARCHIVEDG/QHRIMDBSTD/ARCHIVELOG/2022_01_18/thread_2_seq_28735.332.1094335889 thread=2 sequence=28735
media recovery complete, elapsed time: 00:00:21
Finished recover at 2022-01-18 22:30:37
Executing: alter system set standby_file_management=auto
Finished recover at 2022-01-18 22:30:37

可以看到，此命令执行后，分别执行了如下过程：
This command will internally keep track of standby file locations, refresh standby controlfile from primary, update the new standby controlfile with standby file names, perform incremental backup on primary, transfer the backup-pieces over network to standby and perform recovery on standby。
熟悉复制duplicate的人都可以看出，此过程和duplicate adg的过程极为相似。
从输出可以看出，过程分为以下三个部分：
1、重新恢复备库控制文件，启动standby到mount状态
contents of Memory Script:
{
restore standby controlfile from service ‘priqhrimdb’;
alter database mount standby database;
}
2、修改standby控制文件中数据库文件路径，包括临时文件和redo log，datafile。
虽然primary和standby之间路径并不相同，但是在参数文件中已经通过db_file_name_convert，log_file_name_convert等初始化参数的方式重定义数据文件和日志文件路径，因此执行复制时，不需要指定其他子句或命令进行转化。
contents of Memory Script:
{
set newname for tempfile 1 to
“+DATADG1/QHRIMDBSTD/TEMPFILE/temp.315.1048266439”;
set newname for tempfile 2 to
“+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/TEMPFILE/temp.316.1048266441”;
。。。。。
set newname for datafile 484 to
“+DATADG2/QHRIMDBSTD/DATAFILE/sysaux.397.1094268911”;
catalog datafilecopy “+DATADG1/QHRIMDBSTD/DATAFILE/system.287.1048266159”,
“+DATADG1/QHRIMDBSTD/AAC3F48E60DFEF9CE053243DE60AC007/DATAFILE/system.278.1048266179”,
。。。。。
“+DATADG2/QHRIMDBSTD/DATAFILE/undotbs2.396.1094268467”,
“+DATADG2/QHRIMDBSTD/DATAFILE/sysaux.397.1094268911”;
switch datafile all;
}
3、在线增量备份，和恢复。没看到增量备份的过程，只有增量恢复的过程。
contents of Memory Script:
{
recover database from service ‘priqhrimdb’;
}

  • 启动mrp，open数据库
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
  • 修改standby日志路径
    查看redo和standby日志路径，重建路径错误的日志。
  G  T STATUS	  TYPE		    M STATUS	 FIRST_CHANGE# NEXT_CHANGE# MEMBER							 First Time
--- -- ---------- ---------- -------- ---------- ------------- ------------ ------------------------------------------------------------ -------------------
  5  1 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG1/QHRIMDBSTD/ONLINELOG/group_5.662.1094337555	 20220118 18:11:06
     1 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG2/QHRIMDBSTD/ONLINELOG/group_5.420.1094337563	 20220118 18:11:06
.........
 13  1 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG1/QHRIMDBSTD/ONLINELOG/group_13.291.1094337661	 20220118 18:11:03
     1 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG2/QHRIMDBSTD/ONLINELOG/group_13.429.1094337669	 20220118 18:11:03
 14  1 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG1/QHRIMDBSTD/ONLINELOG/group_14.290.1094337675	 20220118 18:11:04
     1 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG2/QHRIMDBSTD/ONLINELOG/group_14.430.1094337681	 20220118 18:11:04
 91  1 ACTIVE	  STANDBY	 4096		    1.5349E+13		    +DATADG1/QHRIMDBSTD/ONLINELOG/group_91.651.1094331283	 20220118 22:41:26
     1 ACTIVE	  STANDBY	 4096		    1.5349E+13		    +DATADG2/QHRIMDBSTD/ONLINELOG/group_91.409.1094331289	 20220118 22:41:26
 92  1 UNASSIGNED STANDBY	 4096					    +DATADG1/QHRIMDBSTD/ONLINELOG/group_92.652.1094331295
     1 UNASSIGNED STANDBY	 4096					    +DATADG2/QHRIMDBSTD/ONLINELOG/group_92.410.1094331301
.........
 99  1 UNASSIGNED STANDBY	 4096					    +DATADG2/QHRIMDBSTD/ONLINELOG/group_99.417.1094331393
     1 UNASSIGNED STANDBY	 4096					    +DATADG1/QHRIMDBSTD/ONLINELOG/group_99.659.1094331387
###  1 UNASSIGNED STANDBY	 4096					    +DATADG1/QHRIMDBSTD/ONLINELOG/group_100.660.1094331399
     1 UNASSIGNED STANDBY	 4096					    +DATADG2/QHRIMDBSTD/ONLINELOG/group_100.418.1094331407
###  1 UNASSIGNED STANDBY	 4096					    +DATADG1/QHRIMDBSTD/ONLINELOG/group_101.661.1094331413
     1 UNASSIGNED STANDBY	 4096					    +DATADG2/QHRIMDBSTD/ONLINELOG/group_101.419.1094331419
 15  2 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG1/QHRIMDBSTD/ONLINELOG/group_15.286.1094337687	 20220118 18:11:05
     2 UNUSED	  ONLINE	 4096		    1.5349E+13	 1.5349E+13 +DATADG2/QHRIMDBSTD/ONLINELOG/group_15.431.1094337695	 20220118 18:11:05
 ........
 24  2 CLEARING   ONLINE	 4096 INVALID	    1.5349E+13	 1.5349E+13 +DATADG1/QHRIMDBSTD/ONLINELOG/group_24.264.1094337805	 20220118 18:10:59
     2 CLEARING   ONLINE	 4096 INVALID	    1.5349E+13	 1.5349E+13 +DATADG2							 20220118 18:10:59
###  2 UNASSIGNED STANDBY	 4096					    +DATADG1/QHRIMDBSTD/ONLINELOG/group_102.641.1094331153
     2 UNASSIGNED STANDBY	 4096					    +DATADG2/QHRIMDBSTD/ONLINELOG/group_102.399.1094331161
###  2 ACTIVE	  STANDBY	 4096		    1.5349E+13		    +DATADG1/QHRIMDBSTD/ONLINELOG/group_103.642.1094331167	 20220118 22:41:29
     2 ACTIVE	  STANDBY	 4096		    1.5349E+13		    +DATADG2/QHRIMDBSTD/ONLINELOG/group_103.400.1094331175	 20220118 22:41:29
.........
84 rows selected.

如上结果，只有group24 ，有一组成员没有转化过来。我们此处选择重建，即删除，重新创建。有人担心重建会出问题，但是注意，这里是standby端，只读状态，redo log根本就用不到，standby log只是用来接收存储主库的日志，即使删掉，数据库也可以重新接收。所以此处放心大胆的重建。

SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database drop logfile group 24;
alter database drop logfile group 24
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files
SQL> alter database clear logfile group 24;
Database altered.
SQL> alter database drop logfile group 24;
Database altered.
SQL>  alter database add logfile thread 2 '+DATADG1','+DATADG2' size 4294967296;
Database altered.
  1. 启动mrp
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
  1. 检查同步状态
SQL> alter system set standby_file_management=auto;
System altered.
SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
FROM (select thread# thrd, MAX(sequence#) almax
FROM v$archived_log
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al,
(SELECT thread# thrd,   2    3    4    5  MAX(sequence#) lhmax
FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;  6    7    8  
    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
	 1	       32312		32312
	 2	       28736		28736
SQL> col client_pid for a10
SELECT inst_id, thread#, process, pid, status, client_process, client_pid,
sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;SQL>   2  
  I  T PROCESS	 PID			  STATUS     CLIENT_P CLIENT_PID  SEQUENCE#	BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--- -- --------- ------------------------ ---------- -------- ---------- ---------- ---------- ------------- ------------
  1  0 DGRD	 136580 		  ALLOCATED  N/A      N/A		  0	     0		   0		0
  1  0 DGRD	 136582 		  ALLOCATED  N/A      N/A		  0	     0		   0		0
  1  1 RFS	 105736 		  IDLE	     LGWR     136697	      32313	182495		   0		0
  1  1 ARCH	 136590 		  CLOSING    ARCH     136590	      32312	743424		   0		0
  1  1 RFS	 163497 		  IDLE	     Archival 141782		  0	     0		   0		0
  1  2 RFS	 106402 		  IDLE	     LGWR     91071	      28737	  3906		   0		0
  1  2 ARCH	 136578 		  CLOSING    ARCH     136578	      28735	     1		   0		0
  1  2 ARCH	 136588 		  CLOSING    ARCH     136588	      28736	126976		   0		0
  1  2 ARCH	 136596 		  CLOSING    ARCH     136596	      28734	     1		   0		0
  1  2 RFS	 137702 		  IDLE	     Archival 35812		  0	     0		   0		0
  1  2 MRP0	 40547			  APPLYING_L N/A      N/A	      28737	  3906		 128	      128
					  OG
11 rows selected.

至此，恢复完成，ADG同步状态正常。

总结

优势：
1、18c之后，如果没有有效的归档日志，恢复ADG的GAP，只需要一条命令就可以完成。替代了以前繁琐的过程，但是要清楚这条命令包含的许多东西，其实原理和之前的增量备份恢复一摸一样，只是多个步骤被集成为了一条命令可见随着oracle版本的升级，越来愈自动化了。
2、节省空间，再也不用担心primary和standby要有额外空间来存储增量备份集了。特别对于库比较大，GAP比较大的。增量备份集所需空间很大。
3、通过网络在线传输，不用手动在主备之间进行备份集传输了。
劣势：
对版本有要求，要18c之后的版本才支持。

参考

Rolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)
18c Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)

P10ZHUO
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle 19c DG断档恢复
loveLAxin的博客
03-22 1275
Oracle 19c DG 一条命令解决断档恢复，无需进行源端增量备份，备端恢复，效率大大提高
【12c】12c RMAN新特性之通过网络远程恢复数据库（RESTORE/Recover from Service）
weixin_34238633的博客
07-13 230
  【12c】12c RMAN新特性之通过网络远程恢复数据库（RESTORE/Recover from Service）     通过网络远程恢复数据库（Restore/Recover from Service） 在Oracle 12c中，可以在主数据库和备用数据库之间用一个服务名重新获得或...
Oracle 19c 备份恢复-导入导出
07-10
Oracle 19c 备份恢复-导入导出
Oracle19c-Windows客户端
04-26
Oracle19c-Windows客户端
oracle19c cdb rac to rac adg部署20200307.pdf
03-07
oracle 19c容器多租户 rac to rac的adg测试部署方案，完全实测步骤。 使用了DG_BROCKER管理，方便switchover。
Oracle-通过(RECOVER STANDBY DATABASE FROM SERVICE)方式修复DataGuard
sinat_36757755的博客
11-01 1277
Oracle在18c对(RESTORE/RECOVER ...FROM SERVICE)功能进行了加强，推出了(RECOVER STANDBY DATABASE ... FROM SERVICE)通过网络服务直接增量恢复备库数据,这个新特性也大大简化了DataGuard主备的问题修复。
执行RMAN恢复的高级场景_通过网络还原和恢复文件
jetliu05的博客
12-20 682
通过网络还原和恢复文件
oracle 通过service 同步dg
whn1977的博客
10-29 329
oracle 18c只有有个新特性，通过service name可以同步备库数据，对于丢失归档，少量差异甚至全库重新初始化都比较方便。 在备库上 rman target / shutdown immediate; startup mount; recover standby database from service TNS_PRI; exit 等一会就同步了，前提是dg之前配置都正确。 sqlplus / as sysdba alter database recover managed standby d
Oracle DG备库出现GAP解决
weixin_52444902的博客
02-01 1462
Oracle dg备库出现GAP解决方法
ADG备库MRP0状态WAIT_FOR_GAP
喝醉酒的小白
06-13 1282
重启MRP 查看日志 显示已应用，切归档中存在。。。从主库传归档到备库，注册后重启MRP，OPEN备库。
Oracle 19c rac+adg.docx
12-21
Oracle 19c rac+adg.docx
oracle19c客户端（百度云盘）
11-03
txt文件里面有百度云盘下载地址，这个是oracle19c客户端，windows64位系统直接双击setup.exe安装即可
DG知识点记录
weixin_43475880的博客
07-19 1584
搭建操作 alter databaes recover managed standby database disconnect from session 此时备库是归档日志传输，所以需要在主库进行alter system switch logfile 若再执行一次alter database recover managed standby database disconnect from session.此时数据库的状态为read only with apply.主库传输文件时只需要commit就可以.
oracle recover database,standby库，在sqlplus下用recover standby database进行手工恢复
weixin_36371906的博客
04-05 892
一个standby库出问题，丢了1个归档检查主库的日志，发现丢失的归档的SEQ的redo还未被覆盖，马上将其拷贝出来传递到备库上尝试catalog，虽然没有报错，但是实际没有注册成功RMAN> catalog archivelog '/u01/app/oracle/oradata/ORCL/redo03_281.arc';cataloged archived logarchived log ...
Oracle 12c｜RMAN (FROM SERVICE)
huangliang0703的专栏
09-09 491
在12cR1开始，RMAN提供了一个from service的子句让备库可以通过网络来执行recover和restore命令。 在以下情况下可以使用这个新特性呢 当备库出现GAP，而主库丢失归档需要做增量备份的时候 当备库丢失数据文件、控制文件以及表空间的需要restore的时候 1、检查备库 GAP 的情况 备库状态： SQL> select * from v$...
在Oracle Data Guard环境中的RMAN备份和还原
jetliu05的博客
04-26 2867
Oracle Data Guard环境中的RMAN备份和还原
DataGuard问题集2 -- MRP0一直显示WAIT_FOR_GAP
weixin_56440136的博客
04-08 2784
DataGuard问题集2 – MRP0一直显示WAIT_FOR_GAP 现象： MRP0一直显示WAIT_FOR_GAP的状态。 有了上次的教训，这次看到MRP0一直是WAIT_FOR_GAP的状态，先看看StandbyREDO是否正确构建了，答案是完全没问题。 接下来确认各种信息： Standby端的结果： SQL> select process,status from gv$managed_standby where process like 'MRP%'; PROCESS STATUS
wait_for_gap -- 从主库归档备库恢复归档
喝醉酒的小白
07-02 832
restore
sqlldr 直接路径加载direct=true的副作用
fanzhuozhuo的博客
09-03 3603
sqlldr 直接路径加载direct=true的副作用sqlldr帮助文件执行导入提升加载速度总结参考 接上篇文章sqlldr加载19c pdb最佳实践。最后提升加载速度的内容，其中有一项，是使用直接路径加载：direct=true。直接加载全部记录，提高速度。但是此方法有个副作用，就是会使索引失效。mos上面也有说明，根据文章Common Maintenance Commands That Cause Indexes to Become Unusable (Doc ID 165917.1)的描述： Si
oracle19C ADG新特性重定向DML
最新发布
05-30
Oracle 19c中的ADG（Active Data Guard）引入了一个新特性，可以通过重定向DML语句来实现对只读副本的修改。具体来说，这个新特性可以让DML语句在只读副本上执行，然后将执行结果重定向到主库上进行修改，从而实现对只读副本的修改。这个特性可以提高系统的可用性和性能，同时也可以减少对主库的负载。需要注意的是，这个特性需要在主库和只读副本上都进行配置和启用。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值